Thursday, January 10, 2008

Transact Sql

Transact-Sql
SQL (Structured Query Language) is the computer language meant for working with RDBMS (Relational Database Management Systems).
Transact-Sql (T Sql) is the Microsoft's implementation of Sql.

Types of Sql Commands
DDL
Data Definition Language
Eg : CREATE TABLE

DML
Data Manipution Lanaguage
Eg : INSERT, UPDAT and DELETE

DCL
Data Control Language
Eg: GRANT and REVOKE

Creating a database
create database Shalvin

Switching to the newly created database
use Shalvin

Creating a new table, setting an auto increment field and primary key
--IDENTY is used for creating an autoincrement field. The parameters are seed and increment.
create table Categories (CategoryId int identity(1,1) primary key, CategoryName varchar(40), Description varchar(60))

Inserting values
insert into Categories values ('Beverages', 'Tea, Coffee, soft drinks, etc')
insert into Categories values ('Condiments', 'Sweets and savory sauces')
insert into Categories values ('Confections', 'Desserts, candies and sweet breads')
insert into Categories values ('Diary', 'Cheese')
Inserting values only to specific field(s)
insert into Categories (CategoryName) values ('Milk Products')

Updating a value in a Table
update Categories set Description = 'Bakery items' WHERE CategoryName = 'Confections'

Selecting Records
select * from Categories

Selecting Specific record(s) using WHERE clause
--Here I am using the Northwind database that is coming with Sql Server 2000.
use Northwind
SELECT CategoryId, CategoryName, Description FROM Categories WHERE CategoryId = 1

select * from categories where categoryid = 1 or categoryid = 2

In Clause
select * from Categories where CategoryId in(1, 2)

Creating a table for foreign key constraint
create table Products (ProductId int identity(10, 1) primary key, CategoryId int references Categories(CategoryId), ProductName varchar(40), UnitPrice numeric(6,2))

insert into Products values (1, 'Bru Coffee 100 g', 10)

insert into Products values (10, 'Tomato Sauce 200 ml', 20)

Adding constraints to an existing column in a Table

alter table expense add constraint expense_uk unique(expensename)

Demonstrating foreign key vioation error
--
Here there is no record in Categories table with CategoryId 5
insert into Products values (5, 'ASP.Net 3.5 Unleashed', 10)

select * from Products


Selecting Records from Multiples Tables
SELECT Categories.CategoryName, Products.ProductName, Products.UnitPrice FROM Categories, Products WHERE Categories.CategoryId = Products.CategoryId

Selecting Records from Multiples Tables using Table Alias
SELECT C.CategoryName, P.ProductName, P.UnitPrice FROM Categories C , Products P WHERE C.CategoryId = P.CategoryId

Need for Normalization
Lets take the case of an unnormalized Table. My intention is the track the employee information and their transactions.

create table NNEmployee (TraId int identity(1,1) primary key,EmployeeId int, EmployeeName varchar(40), DOJ DateTime, Department varchar(40), Year varchar(4), Month varchar(10), NoOfDaysPresent int)

insert into NNEmployee values (1, 'Shalvin', '12-Jan-08', 'IT', '09', 'Jan', 22)
insert into NNEmployee values (1, 'Shalvin', '12-Jan-08', 'IT', '09','Feb', 21)
insert into NNEmployee values (2, 'Ajith', '1-Jan-08', 'Networking', '09','Jan', 22)
insert into NNEmployee values (2, 'Ajith', '1-Jan-08', 'Networking', '09','Feb', 20)


select * from NNEmployee

Here I am duplication information like Date of Join, Department, etc.

Instead I can create three tables holding Department, Employee Master and Transaction Information.

Create table Dept (DeptNo int identity(1,1) primary key, DName varchar(40))

insert into Dept values ('IT')
insert into Dept values ('Networking')


select * from Dept

create table Emp (EmpNo int identity(10, 1) primary key, DeptNo int references Dept(DeptNo), EName varchar(40), DOJ DateTime)

insert into Emp values (1, 'Shalvin', '12-Jan-08')
insert into Emp values (2, 'Ajith', '1-Jan-08')


select * from emp

select E.EmpNo, D.DName, E.EName, E.DOJ FROM Dept D, Emp E WHERE D.DeptNo = E.DeptNo

create table EmpTran (TranId int identity(100, 1) primary key,EmpNo int references Emp(EmpNo), Year varchar(4), Month varchar(10), NoOfDaysPresent int)

insert into EmpTran values (10, '09', 'Jan', 22)insert into EmpTran values (10, '09', 'Feb', 20)
insert into EmpTran values (11, '09', 'Jan', 22)insert into EmpTran values (11, '09', 'Feb', 21)
select * from EmpTran

Aggregate Functions
Aggregated Functions act on a set of values and returns a single result row.
select max(sal) from emp
select min(sal) from emp
select avg(sal) from emp
select sum(sal) from emp;

select count(*) from emp;

Aritmetic Functions
select power(5,2)
select sqrt(25)

Date Functions
select getdate()
Result - 2009-07-22 16:00:03.590

Extracting the current year
select datepart(year, getdate())
Result - 2009

Extracting the current month
select datepart(month, getdate())
Result - 7

Extracting the current day
select datepart(day, getdate())
Result - 22

SubQuery
Subquery is a query nested inside another query.
For example, for extracting all the products from Products table of category 'Beverages' the subquery is

SELECT * FROM Products WHERE CategoryId IN (SELECT CatgoryId from Categories WHERE CategoryName = 'Beverages')

More Info

Renaming database
sp_renamedb 'Shalvin', 'ShalvinPD'


Listing all databases in an sql server Instance
sp_helpdb


Listing all tables within a database
SELECT * FROM sysobjects WHERE xtype = 'u'

Listing the structure of a table
sp_help Categories


Versions of Sql Server and Developer Tools
Sql Server 7
Sql Server 2000 - Query Analyser, Enterprise Manager
Sql Server 2005 - Sql Server Management Studio
Sql Server 2008

Related Blog
Sql Server Stored Procedures

sqlcmd and Sql Server 2005

Email : shalvin@gmail.com

1 comment: