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 SQL Server's  implementation of SQL.

 

For working with SQL Server we can use SQL Server Management Studio (SSMS) or light weight options like sqlcmd command line option.

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 auto increment 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  CategoryId = 2

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<br />

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 foreign key 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)

 

 Following is the complete SELECT query. In the bracket I have specified the execution order.

SELECT (5)

FROM (1)

WHERE (2)

GROUP BY (3)

HAVING (4)

ORDER BY (6)

OFFSET - FETCH  (7)

1. FROM defined the data set

2. WHERE defines the ROW filter using predicates (a logical expression that evaluates to either true or false)

3. GROUP BY - Individual rows can be combined into groups based on grouping expression.

4. HAVING - Group filer

5 SELECT - Selects the record

6. ORDER BY - Presentation Order

7. OFFSET - FETCH - Paging Specification

 

SELECT WITHOUT FROM

SELECT 123, 2*3, sqrt(3)

123    6    1.73205080756888

 

The above command will not work with Oracle. It requires a dummy table.

 

SELECT 123 as Numbers, 2*3 as Multiple, sqrt(3) as Square

The above command introduce alias so there can be column name.

SELECT * from Products

Select * from <TABLENAME> extracts all the data from table.

 

Selecting records from multiple 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 Functionsselect 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 databasesp_renamedb 'Shalvin', 'ShalvinPD'

or
Alter Database Shalvin Modify Name = ShalvinPD

Listing all databases in an sql server Instancesp_helpdb


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

Listing the structure of a tablesp_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