Variables:

In T-SQL we can declare variables for storing data temporarily. we have,

1) Local variable:

Syntax:
declare @ variablename datatype[(size)];
set @ variablename = value;
select @ variablename = column from tablename[where clause];

2) Global variable:

Syntax:
declare @@ variablename datatype[(size)];
set @@variablename = value;
select @@ variablename = column from tablename[where clause];

If else statements:

If(boolean expression)
begin
sql_statements/sql_blocks
end
else
begin
sql_statements/statement_block
end
Query to display employee fullname for the given empid
declare @eid int;
set @eid = 7
select fname+lname as 'fullname' from employees where eid = @eid;
Query to display given employee details if employee salary is greater than 20000 else display given employees fullname(assuming user supplies eid value)
declare @eid int;
set @eid = 5;
select @sal=salary from employees where eid = @eid;
if (@sal>20000)
begin
select * from employees where eid=@eid;
end
else
begin
select fname+lname as 'fullname' from employess where eid=@eid;
end

While loop:

WHILE(expression)
begin
sql_statements
end
Query to display numbers from 1 to 100
declare @x int;
set @num=1;
while(@num<=100)
begin
print @num
set @num=@num+1;
end

Temporary tables:

-> while writing complex queries we might required to use temporary tables. -> All temporary tables are stored in the tempdb database. -> temporary tables are categorized into 2 types 1) local temporary tables 2) global temporary tables.

Local Temporary tables :

Syntax:
create table #tablename
(

)
Note: -> local temporary tables are visible only to the current instance. -> This temp table gets deleted once the user ends the sql server session.

Global Temporary tables:

Syntax:
create table ##tablename
(

)
Note: ->Global temporary tables will be visible to all the instances of sql server after creation. ->The table will be deleted if all users close the sql sessions. Query for deleting duplicate data present in the employees table using local temporary table.
select distinct * into empdetails from employees
delete employees
insert into employees select * from #empdetails
drop table#empdetails

Table variables:

Syntax:
declare @trainers table
(
tid int,
name varchar(40),
age int,
)
insert into @trainers values(1,'jyothi',20);
Query for deleting the duplicate data present in employees table using table variable
declare @t table
(
eid int,fname varchar(40),lname varchar(40),age int,salary int,dept varchar(40),doj date
)
insert into @t select distinct * from employees 
delete employees
insert into employees select * from @t