T/SQL Programming

 T/SQL stands for “Transact Structure Query Language. It is an extension of

SQL language. This T/SQL is same as PL/SQL in oracle.
 In SQL we can execute single line statement only where as in T/SQL we can
execute block of statements at a time.
 SQL does not support conditional and looping statements like IF-Else and
While loop. But we can implement these conditional and looping
statements in T/SQL.
 SQL language will not provide reusability facilities where as T/SQL
language will provide reusability facilities by defining objects such
as Procedures and Functions.
 T/SQL commands can be embedded inside the programs where program is a
block of code.
 T/SQL Program blocks can be divided into two types. Those are
1. Anonymous Blocks
2. Sub-Program Blocks

Anonymous Blocks: Anonymous Blocks are called as unnamed block of

code which is executed at any point of time and does not store on database.
These blocks can be written on a query window and execute.

Sub-Programs: Sub program Blocks are called as named block of code

which is executed at any point of time and stored on database. These blocks are
providing reusability of code.

Declaring Variables In T/SQL Program:

Syntax: Declare @ <var> [as] <data type > [size]…….
Ex: declare @ eid int; decalare @ename varchar (50)….

While declaring variable, we should be prefixed with @ symbol.

Assigning Values to variables: Values can be assigned by using a SET

statement.
Syntax: Set @ <var>=<value>

Ex: Set @Eid=101; Set @ename=‟SAI‟;

Printing Values of Variables: If we want to print the values we can use

the PRINT statement.

Syntax: Print @ <var>

Ex: Print @Eid;

Structure of T/SQL Program:

Syntax: Declare @ <var1> [data type][size]………………

Set @ <var>=<values>

<Statements>;

Print @<var>…….

Ex1:Write a T/SQL program to input two values and interchange the

variable values.
declare @a int,@b int,@c int;
set @a=10;
set @b=20;
set @c=@a;
set @a=@b;
set @b=@c;
print @a;
print @b;

Ex2: Write a T/SQL program to input student id,name,marks and find the total
marks of a student.
declare @stdno int,@stdname varchar(50),@m1 int,@m2 int,@m3 int,@tm int;
set @stdno=101;
set @stdname='SAI';
set @m1=75;
set @m2=85;
set @m3=65;
set @tm=@m1+@m2+@m3;
print @stdno
print @stdname;
print @tm

Ex3:Write a T/SQL programer to perform arithematic operation.

declare @a int,@b int,@c int,@d int,@e int,@f int;
set @a=10;
set @b=12;
set @c=@a+@b;
set @d=@a-@b;
set @e=@a*@b;
set @f=@a/@b;
print @c;
print @d;
print @e;
print @f;

Conditional Statements: It is a block of code, which executes based on

a condition.

If-Else Statement: In if-else conditional control statement, statements in if

block gets executed only when the condition is true and statements in else block
gets executed only when the condition is false.
Syntax:
If (condition)
{
Statements
}
Else
{
Statements
}

Ex:Write T/SQL program to find big number from two variables.

declare @a int,@b int;
set @a=30;
set @b=20;
if(@a>@b)
print 'a is big'
else if(@a=@b)
print 'Both are equal'
else
print 'B is big';

Ex:To find positive and negative.

declare @a int;
set @a=10;
if(@a>0)
print 'a is positive'
else
if(@a=0)
print 'a is neutral'
else
print 'a is negative'
Ex:To find the number is even or odd.

declare @a int
set @a=4
if((@a%2)=0)
print 'a is even';
else
print 'a is odd';
EX: If there are multiple statements being enclosed between each block then we
can put them under Begin and End Statements.

DECLARE @WEEK INT

SET @WEEK=DATEPART(DW, GETDATE())

IF @WEEK=1

PRINT 'SUNDAY'

ELSE IF @WEEK=2

PRINT 'MONDAY'
ELSE IF @WEEK=3

PRINT 'TUESDAY'

ELSE IF @WEEK=4

PRINT 'WEDNESDAY'

ELSE IF @WEEK=5

PRINT 'THURSDAY'

ELSE IF @WEEK=6

PRINT 'FRIDAY'

ELSE IF @WEEK=7

PRINT 'SATURDAY'

CASE FUNCTION: The case function what we have discussed under the System
Functions can also be used here as following:

DECLARE @WEEK INT

SET @WEEK=DATEPART(DW, GETDATE())

SELECT CASE @WEEK

WHEN 1 THEN 'SUNDAY'

 WHEN 2 THEN 'MONDAY'
WHEN 3 THEN 'TUESDAY'

WHEN 4 THEN 'WEDNESDAY'

WHEN 5 THEN 'THURSDAY'

WHEN 6 THEN 'FRIDAY'

WHEN 7 THEN 'SATURDAY'

END

-This can be written in the second style of the CASE Statement also that has been
discussed in the SQL as following:

DECLARE @WEEK INT

SET @WEEK=DATEPART(DW, GETDATE())

SELECT CASE

WHEN @WEEK=1 THEN 'SUNDAY'

WHEN @WEEK=2 THEN 'MONDAY'

WHEN @WEEK=3 THEN 'TUESDAY'

WHEN @WEEK=4 THEN 'WEDNESDAY'

WHEN @WEEK=5 THEN 'THURSDAY'

WHEN @WEEK=6 THEN 'FRIDAY'

WHEN @WEEK=7 THEN 'SATURDAY'

END

While Loop: Sets a condition for the repeated execution of an SQL statement or
statement block. The statements are executed repeatedly as long as the specified
condition is true. The execution of statements in the WHILE loop can be
controlled from inside the loop with the BREAK and CONTINUE keywords.

WHILE Boolean expression

 [BEGIN]

< sql_statement | statement_block

> [BREAK]

< sql_statement | statement_block

> [CONTINUE]

< sql_statement | statement_block >

[END]
-If there are multiple statements being enclosed then we can put them under
Begin and End Statements.

BREAK: Causes an exit from the innermost WHILE loop. Any statements that
appear after the END keyword, marking the end of the loop, are executed.

CONTINUE: Causes the WHILE loop to restart, ignoring any statements after the
CONTINUE keyword.

Program 1:

DECLARE @X INT

SET @X=0

WHILE @X<10
BEGIN

SET @X=@X+1

PRINT @X

END
Program 2:

DECLARE @X INT

SET @X=0

WHILE @X<10

BEGIN

SET @X=@X+1

IF @X=6 BREAK

PRINT @X

END

-In this case the break statement brings the control out of the loop printing from 1
to 5.

Program 3:

DECLARE @X INT

SET @X=0

WHILE @X<10

BEGIN

SET @X=@X+1

IF @X=6 CONTINUE

PRINT @X

END

-In this case the continue statement will skip the print statement when the value
of x is 6 so prints from 1 to 5 and 7 to 10.
Comments in TSQL: Comments will be ignored will executing the program, they
will increase the readability and aids understanding of the program.

 Single Line Comments (--)

 Multi Line Comments (/* ….. */)

Assinging values from columns into variables: Till now we were assigning static
values to the variables using the SET statement, but we can also assign values
from a column into the variables as following:

SELECT @<var>=<col_name> [, ……n] FROM <table_name>

[CONDITIONS]

SELECT @ENAME=ENAME FROM EMP WHERE EMPNO=1001

-A simple TSQL program which takes the Empno and prints the Name and Salary.

DECLARE @EMPNO INT, @ENAME VARCHAR(50), @SAL MONEY

SET @EMPNO=1005

SELECT @ENAME=ENAME, @SAL=SAL FROM EMP WHERE

EMPNO=@EMPNO

PRINT @ENAME + ' EARNS ' + CAST(@SAL AS VARCHAR)

-A Program which takes the Empno and increments the Salary of the person on
the following criteria:

If Job is President increment with 10%

If Job is Manager increment with 8%

If Job is Analyst increment with 6%

If Job is any thing other incrment with 5%

 DECLARE @EMPNO INT, @JOB VARCHAR(50)

SET @EMPNO=1005

SELECT @JOB=JOB FROM EMP WHERE EMPNO=@EMPNO

IF @JOB='PRESIDENT'

UPDATE EMP SET SAL = SAL + SAL * 0.1 WHERE

EMPNO=@EMPNO

ELSE IF @JOB='MANAGER'

UPDATE EMP SET SAL = SAL + SAL * 0.10 WHERE

EMPNO=@EMPNO

ELSE IF @JOB='ANALYST'

UPDATE EMP SET SAL = SAL + SAL * 0.06 WHERE

EMPNO=@EMPNO

ELSE

UPDATE EMP SET SAL = SAL + SAL * 0.05

WHERE EMPNO=@EMPNO

SUB PROGRAMS: A sub program is a named block of code that is

directly saved on the server and it can be executed when and where it is required.
We have two types of sub programs in SQL server.
 Stored Procedures/Procedure
 Stored Functions/Functions

Stored Procedures/Procedure: A stored procedure is a database

object which contains precompiled queries. Stored Procedures are a block of code
designed to perform a task whenever we called.
Why we need stored procedure: Whenever we want to execute a
SQL query from an application the SQL query will be first parsed (i.e.
complied) for execution where the process of parsing is time consuming because
parsing occurs each and every time we execute the query or statement.

To overcome the above problem we write SQL statements or query under stored
procedure and execute, because a stored procedure is a pre complied block of
code without parsing the statements gets executed whenever the procedures are
called which can increase the performance of an application.

Advantages of Stored Procedure:

 As there is no unnecessary compilation of queries, this will reduce burden on
database.
 Application performance will be improved
 User will get quick response
 Code reusability facility

How to Create Stored Procedures/Procedure

(Without parameter):
Syntax: Create Procedures <Procedures Name>

As

Begin

<Statements>

End

Once the Procedure is created it is physically saved on the server as a

Database Object which can be called whenever we required to the user.

We can call the above procedure from anywhere and from any application that is
developed using JAVA (or) .NET languages
How to Call a Stored Procedures/Procedure:
Syntax: Exec <Procedure name>

Examples on without parameters Procedures:

1) Write a simple procedure program (with out parameters) to print
WELCOME statement on the query window.

create procedure Test1

as
begin
print 'WELCOME TO STOREDPROCEDURES'
end

Passing Parameters to Procedures: If we want to pass

parameters to procedures then we are using the below syntax.

Syntax: Create Procedures <Procedures Name>

(Passing parameters)

As

Begin

<Statements>

End

Examples on Parameter Procedures:

1) Write a program to add the two values with Parameters Procedure.

create procedure test2(@a int,@b int)

as
begin
declare @c int
set @c=@a+@b;
print 'Addition of two variables are:-'+cast(@c as
varchar); end
2) Write a program to perform arithmetic operations of two values with Parameters
Procedure.

create procedure test3(@a int,@b int)

as
begin
declare @x int,@y int,@z int,@s int
set @x=@a+@b;
set @y=@a-@b;
set @z=@a*@b;
set @s=@a/@b;
print 'Add of two variables are:-'+cast(@x as varchar);
print 'Sub of two variables are:-'+cast(@y as varchar);
print 'Mul of two variables are:-'+cast(@z as varchar);
print 'Div of two variables are:-'+cast(@s as varchar);
end

3)create a procedure to display employee details to the user

create procedure spselect

as

begin

select * from Employee

end

Output: execute spselect

4)create a procedure to accept employee ID and delete the record from

employee table.

create procedure spdel

@eid int

as

begin

delete from Employee where EmpID=@eid

 end

Output: exec spdel 4

5)create a procedure to accept employee ID and update the employee details from
employee table.

create procedure spupdate

@eid int,@ename varchar(max),@salary money,@address char(30)

as

begin

update Employee set

EmpName=@ename,Salary=@salary,Address=@address where
EmpID=@eid

end

Output: exec spupdate 1,'kamal',88000,'vizag'

6)create a procedure to add records in employee table.

create procedure spinst

@eid int,@ename varchar(50),@salary money,@address varchar(50)

as

begin

insert into Employee values(@eid,@ename,@salary,@address)

end

Output: exec spinst 6 ,'Suman' ,41000 ,'chennai'

7)create a procedure to insert records in two tables.

create procedure spinserttwotables

@eid int,@ename varchar(50),@salary money,@Address
char(40),@Deptno int,@Dname char(30),@Loc char(20)
as
begin
insert into Employee values(@eid,@ename,@salary,@Address,@Deptno)
insert into Dept values(@Deptno,@Dname,@Loc)
end

Output: exec spinsert 7,'mohan',62000,'mumbai',10,'dotnet','hyd'

8) A Procedure with Default Values:

CREATE PROCEDURE PROC3(@X INT = 100, @Y INT)

AS

BEGIN

DECLARE @Z INT
SET @Z=@X+@Y

PRINT 'The SUM of the 2 Numbers is: ' + CAST(@Z

AS VARCHAR)

END

-Executing the above procedure:

1. EXEC PROC3 200, 25

2. EXEC PROC3 @X=200, @Y=25
3. EXEC PROC3 @X=DEFAULT, @Y=25
4. EXEC PROC3 @Y=25
-In the 3 and 4th case it uses the default value of 100 to the varibale X which
rd

has been given while creating the procedure.

9) A Procedure which takes the Empno and prints the Net Salary of the
Employee. CREATE PROCEDURE Net_Sal(@Empno int)

As
Begin

Declare @VSal money, @NSal money, @VPF money, @VPT money

EXEC Deductions @Empno, @VPF OUTPUT, @VPT OUTPUT

SELECT @Sal=Sal FROM Emp WHERE Empno=@Empno

SET @NSal = @VSal - @VPF - @VPT

Print „Net Salary of the Employee is: „ + Cast(@NSal as Varchar)

End

-Executing the above Procedure:

EXEC Net_Sal 1005

How To Drop Stored Procedure:

 Drop Procedure <Procedure Name>

Ex: Drop Procedure SP1

Stored Functions/Functions: Function is a block of code similar to a
stored procedure which is also used to perform an action and returns result as a
value. Function can be divided into two types, these are

1)Scalar-Valued Fuction: In this case we can return a attribute

datatype as an output from the function.
Syntax: Create Function <Function Name> (@parameter <Data Type> [size])

Returns <return attribute data type>

As

Begin

<Function Body>

Return <return attribute name>

End

How to Call Scalar valued Functions:

Syntax: Select dbo.<Function Name> (value)

1) Create a function to return the cube of the given value.

create function fcube (@x int)
returns int
as
begin
return @x*@x*@x
end

Output:select dbo.fcube(3)
 2) Create a function that takes an employee id and returns the salary of that
employee.
create function fsal(@eid int)
returns money
as
begin
declare @sal money
select @sal=salary from employee where empid=@eid
return @sal
end
Output:select dbo.fsal(1)

2)Table-Valued Fuction:In this case we can return a table as an output

from the function.

Syntax:
Create Function <Function Name> (@parameter <Data Type> [size])

Returns <Table>

As

Return <return select statement>

How to Call a Table-Valued Function:

Syntax: select * from functionname(value)
Ex: Create a function that accept the Address and returns the list of
employee working in given address from the table.

create function ft1(@add varchar(50))

returns table
as
return(select * from employee where address=@add)

Output:select * from ft1('hyd')

Ex:Create a function to get the deptno and return list of employee working in
EMP and DEPT tables.

create function saidata(@deptno int)

returns table
as
return(select e.eid,e.ename,e.salary,d.deptno,d.dname,d.location
from emp e inner join dept d on e.deptno=d.deptno where
e.deptno=@deptno)
Output: Select * from saidata(10)

How To Drop Functions:

 Drop Function <Function Name>

Ex: Drop Function Saidata

Difference between Function And Procedure:

 A function must return a value where as procedure never returns a value.
 A procedure can have parameters of both input (with parameters) and
output (without parameters) where as a function can have only input (with
parameters) parameters only.
 In procedure we can perform select, insert, update and delete
operation where as function can used only to perform select. Cannot
be used to perform insert, update and delete operations.
 A procedure provides the option for to perform transaction management
where as these operations are not permitted in a function.
 We call a procedure using execute command where as function are called
by using select command only.
TRIGGERS: A trigger is a special type of procedure that will used to provide
restrict on the tables when a language events executed. Sql server includes two
types of triggers are

 DML Triggers
 DDL Triggers

DML Triggers: DML triggers execute when the user tries to modify or change
data through data manipulation language events. Those are Inserting, Update
and Delete statements on the table.

DML triggers can be used to enforce business rules and data integrity. With the
help of a DML trigger we can enforce integrity which cannot be done with
constraints.

Syntax: Create Trigger <Trigger Name> on <table Name>

For [Insert, Update,Delete]

AS

Begin

<Statements>

End

Ex: A trigger that will convert the dname and location into upper case when
the user insert in lowercase.

create trigger per_trg

on person after insert
as
begin
declare @pid int,@pname varchar(50),@loc varchar(50)
select @pid=pid,@pname=pname,@loc=loc from inserted
update person set pname=upper(@pname),loc=upper(@loc) where pid=@pid
end
Ex:Create a trigger to restric DML operations on the table

create trigger nnn on person

for insert,update,delete
as
begin
print 'DML OPeration are Not Allowed'
rollback transaction
end

EX: A Trigger that will restrict the operations to be performed before 9 A.M
and after 5 P.M

CREATE TRIGGER EMP_TRG

ON EMP AFTER INSERT, UPDATE, DELETE

AS

BEGIN

DECLARE @DT INT

SET @DT=DATENAME(HH, GETDATE())

IF @DT NOT BETWEEN 9 AND 16

BEGIN

ROLLBACK

RAISERROR('CANNOT PERFORM DML OPERATIONS NOW', 15, 1)

END

END

-After the trigger is created try to perform any DML Operations on the EMP table
before 9 A.M and after 5 P.M the Trigger will fire and restrict the operations.
EX: A program which will restrict the Delete operation if the Job of the person
is Manager.

ALTER TRIGGER

EMP_DELETE_TRG ON EMP AFTER

DELETE AS

BEGIN
DECLARE @JOB VARCHAR(50)

SELECT @JOB=JOB FROM

DELETED IF @JOB='MANAGER'

BEGIN

ROLLBACK

RAISERROR('CANNOT DELETE MANAGER FROM THE TABLE', 15,

1)

END

END

-To test the following Trigger execute the following statement:

DELETE FROM EMP WHERE EMPNO=1002

EX: A Trigger which will restrict to update the Salary of the Employee if the New
Salary is less than the Old Salary.

CREATE TRIGGER

EMP_UPDATE_TRG ON EMP AFTER

UPDATE AS

BEGIN

DECLARE @OLDSAL MONEY

 DECLARE @NEWSAL MONEY

SELECT @OLDSAL=SAL FROM DELETED

SELECT @NEWSAL=SAL FROM INSERTED

IF @OLDSAL > @NEWSAL BEGIN

ROLLBACK

RAISERROR('NEW SAL CANNOT BE LESS THAN OLD SAL', 15, 1)

END

END
Dropping DML Triggers:
Syntax: Drop <Trigger> <Trigger Name>

EX: Drop Trigger rest drop

DDL Triggers: DDL triggers fire in response to a data definition language event
like create, Alter, drop etc.A DDL triggers is a special type of procedure that
executes in response to a server scoped or database scoped events.

Syntax:

Create Trigger <Trigger Name> on database after <Event type>

As

Begin

<Statements>

End
Ex: Write a trigger which restricts dropping of a table from the database.

create trigger restdrop on database after drop_table

as
begin
rollback
raiserror('Can not drop table under this database',1,1)
end

Ex2: Write a trigger which restricts Creating of a table from the database.

create trigger restcret on database after create_table

as
begin
rollback
raiserror('Can not create table under this database',1,1)
end

Ex3: Write a trigger which restricts Alter of a table from the database.

create trigger restalt on database after Alter_table

as
begin
rollback
raiserror('Can not Alter table under this database',1,1);end
Dropping DDL Triggers:
Syntax: Drop <Trigger> <Trigger Name> on Database

EX: Drop Trigger rest drop on database

Magic Tables: SQL Server allows you to define a Magic Table. Magic
Tables are invisible tables or virtual tables. You can see them only with the
help Triggers in SQL Server.

 Magic Tables are those tables which allow you to hold inserted, deleted and
updated values during insert delete and update DML operations on a table
in SQL Server.
 Basically there are two types of magic table in SQL server namely inserted
and deleted magic tables update can be performed with help of these twos.
 Generally we cannot see these two table, we can only see it with the help
Trigger's in SQL server.

Inserted Magic Table: Whenever you insert a record on that table, that
record will be shown in the INSERTED Magic Table. Now creating a trigger
to see the data in Inserted Magic table.

Example:

Create TRIGGER Trigger_ForInsertmagic

ON Employee
FOR INSERT
AS
Begin
SELECT * FROM INSERTED
End

Now insert a new record in Employee table to see data within Inserted virtual
tables.

Insert into Employee values (12, 'Rahul', 25000,‟HYD‟)

SELECT * FROM Employee

Now press F5 to execute it.

Deleted Magic Table: Whenever you delete the record on that table, that
record will be shown in the DELETED Magic Table Only. To create a trigger to
see the data in the deleted Magic table use the following,

Example:

Create TRIGGER Trigger_Fordeletemagic

ON Employee
FOR DELETE
AS
Begin
SELECT * FROM Deleted
End

Now delete a record in the Employee table to see the data in the Deleted
virtual tables.
Delete from Employee where Eid=12
SELECT * FROM Employee

Update the Record in Table: To update the record in the Employee

table, we use it for both virtual/Magic tables. One shows the inserted table and the
other shows the deleted table. The following trigger defines both the inserted table
and the deleted table:

Example:

Create TRIGGER Trigger_ForInsertdeletemagic

ON Employee
FOR UPDATE
AS
Begin
SELECT * FROM INSERTED
SELECT * FROM DELETED
End

Now update the records in the Employee table to see the data in the inserted
and deleted virtual tables.

Update employee set ename=‟sai‟ where

Eid=12 SELECT * FROM Employee
Now press F5 to execute it.

Exception Handling: We handle errors of a program both in a

programming language as well as databases also. whereas handling an error in a
programming language needs stopping the abnormal termination and allowing
the statements which are not related with the error to execute where as handling
as error in sqlserver means stopping the execution of statements which are related
with the error

Handling Errors In SQL Server: From sqlserver 2005 we are

provided with a structure error handling mechanism with the help of TRY and
CATCH blocks which should be used as following,

Begin Try

<Statements>

End Try

Begin Catch

<Statements>

End Catch
Ex: A procedure for dividing two numbers

CREATE PROCEDURE PDIV(@X INT,@Y INT)

AS
BEGIN
DECLARE @Z INT
BEGIN TRY
SET @Z=@X/@Y
PRINT 'THE RESULT IS:-'+CAST(@Z AS CHAR)
END TRY
BEGIN CATCH
PRINT ERROR_Message()
END CATCH
END

Exec PDIV 100,5

Error Message(): It is used to display the information about the error occurred.

CURSOR:Cursor is a memory location for storing database tables. cursor is a

temporary work area allotted to the client at server when a SQL statement is
executed. A cursor contains information on a select statement and the rows of data
accessed by it.

This temporary work area is used to store the data retrieved from the database,
and manipulate this data. A cursor can hold more than one row, but can process
only one row at a time. The set of rows the cursor holds is called the Result set.

There are two types of cursors in T/SQL:

Implicit Cursors: These cursors will be created by SQL server by default
when select statement will executed. Select statement will show records in the
table as a set or result set.

Explicit Cursors: When user can create a memory location to store the
tables then it is called as Explicit Cursors. These cursors will access the records in
the table record by record or one by one only. Whenever we want to go for record
by record manipulation then explicit cursors will be used.
Steps To Create Cursor:
1)Declaring A Cursor:In this process we define a cursor.

Syntax: Declare <cursorname> cursor for < select statement>

2)Opening A Cursor:When we open a cursor it will internally execute the select

statement that is associated with the cursor declartion and load the data into cursor.

Syntax: Open < cursorname>

3)Fetching Data From The Cursor:In this process we access row by row from
cursor.

Syntax: Fetch first/last/next/prior/absolute n/relative n from <cursorname> into

<variables>

4)Closing A Cursor: In this Process,it releases the current result set of the
cursor leaving the datastructure available for reopening.

Syntax: Close <cursorname>

5) Deallocate A Cursor: It removes the cursor reference and deallocate it

by destroye the data structure.

Syntax: Deallocate <cursorname>

@@Fetch_Status: It is global variable use to check wheather cursor variable

contains records or not.if record is there then the value will be zero other wise
value will be -1.
Example To Work with Cursor:

Ex: Create an explicit cursor to display all the records from the table.

Sol: declare @dno int,@dname char(20),@loc varchar(20)

declare c1 cursor for select * from dept
open c1
fetch next from c1 into @dno,@dname,@loc
 while @@FETCH_STATUS=0
begin
print @dno
print @dname
print @loc
fetch next from c1 into @dno,@dname,@loc
end
close c1
deallocate c1

Ex: Create an explicit cursor to display salaries of each employee in the table.

declare @ename varchar(50),@sal money

declare empcur cursor for select name,sal from employee
open empcur
fetch next from empcur into @ename,@sal
while @@FETCH_STATUS=0 begin

print 'Salary Of'+' '+@ename+'is:-'+cast(@sal as varchar)

fetch next from empcur into @ename,@sal
end
close empcur
deallocate empcur

Ex: Write a program to increment the salaries of all the employee basing
on the following criteria President 10%,Manager 5% and others 3%.

Declare @eno int,@job varchar(20)

Delcare empcur cursor for select Eid,Job from
employee Open empcur
Fetch next from empcur into
@eno,@job While @@Fetch_Status=0
Begin
If @job=‟president‟
Update employee set sal+=sal*0.10 where eid=@eno
Else if @job=‟Manager‟
Update employee set sal+=sal*0.05 where eid=@eno
Else
Update employee set sal+=sal*0.03 where eid=@eno
Fetch next from empcur into @eno,@job
End
Close empcur
Deallocate empcur

Forward only and Scroll Cursors:

If a cursor is declare as forward only it allows you to navigate only to the next
records in sequential order and more over it supports only a singleton fashion
method that is fetch next(one-by-one) where as a scroll cursor allows you to
navigate/fetch Bidirectionally that is top- bottam or bottom-top also.And it
supports six different fetch methods are
Fetch Next,Fetch First,Fetch Last,Fetch Prior,Fetch Absolute ,Fetch Relative.

Ex: Create an explicit cursor to fetch the records One-by-One manner(First-Last)

from the table.
Sol: declare c1 cursor for select * from dept
open c1
fetch next from c1
while @@FETCH_STATUS=0
begin
fetch next from c1
end
close c1
deallocate c1

Ex: Create an explicit cursor to fetch the records from bottom -first (Last-
First)from the table.

Sol: declare c1 cursor scroll for select * from dept

open c1
fetch last from c1
 while @@FETCH_STATUS=0
begin
fetch prior from c1
end
close c1
deallocate c1

Ex: Create an explicit cursor on fetching methods.

declare @id int

declare e cursor scroll
for select sid from student
open e
fetch next from e into @id
print @id
fetch last from e into @id
print @id
fetch prior from e into @id
print @id
fetch absolute 3 from e into @id
print @id
fetch relative -1 from e into @id
print @id
fetch first from e into @id
print @id
close e
deallocate e

Static & Dynamic Cursors: If a cursor is declare as static after opening

the cursor any modifications that are performed to the data in the table will not
be reflected into cursor so the cursor contains old values only in it.

Declare @sal money

Declare c1 cursor static for select sal from employee
Where eid=100
Open c1
Update employee set sal=25000 where eid=100
Fetch next from c1 into @sal
Print @sal
Close c1
Deal locate c1

Before executing the above program verify the salary of employee 100 and then
execute the program even if the program is updating the salary in the table the
fetch statement will still display us the old value of the table only but not the new
value.
If we want the change mode on the table to be reflected into the cursor
after opening the cursor declare the cursor as dynamic

DATA CONTROL LANGUAGE

Authentication: Authentication is a process of verifying the credentials of a

user to login into the system.

Authorization: Authorization is process of verifying whether the user

as permissions to perform any operation on the database.

Data Control Language: DCL commands are used to enforce database

security in multiple users’ database environment. These are two types….

 GRANT
 REVOKE

GRANT: Grant command is used for giving a privilege or permission for a user
to perform operations on the database.

Syntax: GRANT <Privilege Name> on <object name>

To {User} [With GRANT OPTION]

Privilege Name: Used to granted permission to the users for some rights are
ALL, EXECUTE and SELECT.
Object Name: It is the name of database objects like Table, Views and Stored
Procedure etc….

User: Used for to whom an access rights is being granted.

With Grant Option: Allows a user to grant access rights to other users.

REVOKE: Revoke command removes user access rights / privileges to the

database OR taking back the permission that is given to a user.

Syntax: Revoke <privilege name> on <object name > from {user}

Normalization: Normalization is the process of efficiently organizing data

in a database. There are two goals of the normalization process are,

 Eliminating redundant data (for example, storing the same data in more than one
table) and
 Ensuring data dependencies make sense (only storing related data in a table).

Both of these are worthy goals as they reduce the amount of space a database
consumes and ensure that data is logically stored.

There are several benefits for using Normalization in Database.

Benefits:

a. Eliminate data redundancy

b. Improve performance
c. Query optimization
d. Faster update due to less number of columns in one table
e. Index improvement

There are different types of Normalizations form available in the Database.

Let‟s see one by one.

1. First Normal Form (1NF): First normal form (1NF) sets the very
basic rules for an organized database:

 Eliminate duplicative columns from the same table.

  Create separate tables for each group of related data and identify each row
with a unique column or set of columns (the primary key).
a. Remove repetitive groups
b. Create Primary Key
Example:

Name State Country Phone1 Phone2 Phone3

John 101 1 488-511-3258 781-896-9897 425-983-9812
Bob 102 1 861-856-6987
Rob 201 2 587-963-8425 425-698-9684
PK [ Phone No’s ]
? ?
ID Name State Country Phone
1 John 101 1 488-511-3258
2 John 101 1 781-896-9897
3 John 101 1 425-983-9812
4 Bob 102 1 861-856-6987
5 Rob 201 2 587-963-8425
6 Rob 201 2 425-698-9684

2. Second Normal Form (2NF): Second normal form (2NF)

further addresses the concept of removing duplicative data:

 Meet all the requirements of the first normal form.

 Remove subsets of data that apply to multiple rows of a table and place
them in separate tables.
Remove columns which create duplicate data in a table and related a new table
with Primary Key – Foreign Key relationship
ID Name State Country Phone
1 John 101 1 488-511-3258
2 John 101 1 781-896-9897
3 John 101 1 425-983-9812
4 Bob 102 1 861-856-6987
5 Rob 201 2 587-963-8425
6 Rob 201 2 425-698-9684
ID Name State Country PhoneID ID Phone
1 John 101 1 1 488-511-3258
2 Bob 102 2 1 781-896-9897
3 Rob 201 3 1 425-983-9812
4 2 587-963-8425
5 3 587-963-8425
6 3 425-698-9684

3. Third Normal Form (3NF): Third normal form (3NF) goes one
large step further:

 Meet all the requirements of the second normal form.

 Remove columns those are not dependent upon the primary key.

Country can be derived from State also… so removing country

ID Name State Country

1 John 101 1
2 Bob 102 1
3 Rob 201 2
4. Fourth Normal Form (4NF): Finally, fourth normal form (4NF) has
one additional requirement:

 Meet all the requirements of the third normal form.

 A relation is in 4NF if it has no multi-valued dependencies.
If PK is composed of multiple columns then all non-key attributes should be
derived from FULL PK only. If some non-key attribute can be derived from
partial PK then remove it. The 4NF also known as BCNF NF

ID Name State
1 John 101
2 Bob 102
3 Rob 201

5. Fifth Normal Form (5NF):A database table is said to be in 5NF if

it is in 4NF and contains no redundant values or We can also said a table to be
in 5NF if it is in 4NF and contains no join dependencies.
How to Create New Login in SQL Server
Here is how to create login in SQL Server:

Step 1) To create login SQL server, Navigate to Security > Logins

Step 2) In the next screen, Enter

1. Login Name
2. Select SQL Server authentication
3. Enter Password for MySQL create user with password
4. Click Ok
Step 3) Login is created
You can also create a login using the T-SQL command for SQL server create login
and user.

CREATE LOGIN MyLogin WITH PASSWORD = '123';

How to Create a User in SQL Server Database

A user is an account that you can use to access the SQL server. To create user
SQL server, you can use any of the following two ways:

 Using T-SQL
 Using SQL Server Management Studio

How to Create User in SQL Server Management

Studio
Here is a step by step process on how to create a user in SQL Server
Management Studio:

You will be creating a user for the EDU_TSQL database.

Step 1) Connect to SQL server to create new user

1. Connect to SQL Server then expand the Databases folder from the Object
Explorer.
2. Identify the database for which you need to create the user and expand it.
3. Expand its Security folder.
4. Right-click the Users folder then choose “New User…”
Step 2) Enter User details

You will get the following screen,

1. Enter desired User name

2. Enter the Login name (created earlier)
3. Click OK
Step 3) User will be created

User is created
Create User using T-SQL
You can use the T-SQL’s create user command for SQL server add user to
database. The SQL create user command takes the following syntax:

create user <user-name> for login <login-name>

create user user1 for login MyLogin
Note: That the query should be executed within the query window. If a user is
already created for a Login, SQL Server will throw an error if you create a user
for the same login.

Assigning Permission to a User in SQL Server

Permissions refer to the rules that govern the levels of access that users have on
the secured SQL Server resources. Sql server allows you to grant, revoke and
deny such permissions. There are two ways to give SQL server user permissions:

 Using T-SQL
 Using SQL Server Management Studio

Assigning Permissions in SQL Server Management

Studio
Here is a step by step process on how to assign permissions to a user in SQL
server management studio:
Step 1) Connect to your SQL Server instance and expand the folders from the
Object Explorer as shown below. Right click on the name of the user, that is,
Guru99 then choose Properties.

Step 2) In the next screen,

1. Click the Securables option from the left.

2. Click on Search
Step 3) In the next window,

1. Select “All Objects belonging to the Schema.”

2. Select Schema name as “dbo”
3. Click OK

Step 4)

1. Identify Table you want to Grant Permission

2. In Explicit Permission select Grant
3. Click Okay
Step 5) The user Guru99 is granted SELECT permission on table Course.

Grant Permissions using T-SQL

To grant permission to a user using T-SQL, you first select the database using
the use statement. You then assign the permission to the user using the grant
statement. Here is the syntax for SQL server create user and grant permission:

use <database-name>
grant <permission-name> on <object-name> to <username\principle>
For example, the following command shows how you can grant the select
permission to the user user1 on the object (table) named Course within the
Database EDU_TSQL:

USE EDU_TSQL
GO
Grant select on Course to Guru99