W.A.Q with Example of Query,Joins,Functions and Stored Procedures in Sql Server

Create DB:-
create database SubsDB

use SubsDB

Create Tables:-
create table Emp(id int constraint pr_key primary key,name varchar(15),sal money,deptid int foreign key references Dept(deptid))

create table Dept(deptid int constraint pr_key1 primary key,dname varchar(15))

Insert Data into Tables:-
insert into Emp values(01,'sub',15000,103)
insert into Dept(deptid,dname) values(101,'Software')

Show Tables:-
select * from Emp
select * from Dept

For adding new column to the table:-
alter table Emp
add doj date

For finding top most highest sal:-
select MAX(sal) from emp

For finding only 3rd highest sal:-
select top 1 sal from emp where sal in(select top 3 sal from Emp order by sal desc) order by sal

For finding sal According to top 3 sal :-
select name,sal from Emp where sal in(select sal from Emp where sal in(select top 3 sal from Emp order by sal desc))

For finding only 5th row nos sal:-
select sal from Emp where id=5
or
select sal from Emp where id in(select top 1 id from Emp where id in(select top 5 id from Emp order by id)order by id desc)

For Show Date-Month-Year:-
select DATEPART(DD,'1/24/2014')as DDate,DATEPART(MM,'1/24/2014')as MMonth,DATEPART(YY,'1/24/2014') as YYear

For Show Month Name:-
select DATENAME(MONTH,'1/24/2014')

Find Addition Beween two Dates:-
select doj,DATEADD(DAY,5,doj) from Emp
OR
select DATEADD(DAY,5,'1/24/2014')

Find Difference Between two Dates(yy-mm-dd format):-
select DATEDIFF(DAY,'2014-01-01','2014-01-05')

Find Length of Employee:-
select name,LEN(name) as NameLength from Emp

select name,SUBSTRING(name,2,4) as SubStringName from Emp

Find emp name who are working last 5 yr
select doj,CAST(GETDATE() as Date) as CurrentDate,DATEDIFF(DAY,doj,CAST(GETDATE() as Date)) as DateDifferents from Emp where DATEDIFF(DAY,doj,CAST(GETDATE() as Date))>5*365

For Casting DateTime to Date Format:-
select CAST(GETDATE() as Date)

For Casting DateTime to Time Format:-
select CAST(GETDATE() as Time)

Find the details of emp who r working from 1990
select * from Emp where doj>'1990-01-01'

Find emp name who r working with software dept
select name from Emp where deptid=(select deptid from Dept where dname='software')

Find names from 2nd letter to 5th letter:-
select substring(name,2,5) from Emp

Find date and time:-
select GETDATE()

Joins:-

Inner Join:-

select e.id,e.name,e.sal,e.doj,d.dname from Emp e inner join Dept d

on e.deptid=d.deptid

Self Join:-

select e.id,e.name,e.sal,e.doj,d.mgrno from Emp e,Emp d where e.id=d.mgrno

Equi Join:-

select e.id,e.name,e.sal,e.doj,d.dname from Emp e,Dept d where e.deptid=d.deptid

Left Outer Join:-

select e.id,e.name,e.sal,e.doj,d.dname,e.deptid,d.deptid from Emp e left outer join 

Dept d on e.deptid=d.deptid

Right Outer Join:-

select e.id,e.name,e.sal,e.doj,d.dname,e.deptid,d.deptid from Emp e right outer join 

Dept d on e.deptid=d.deptid

Full Outer Join:-

select e.id,e.name,e.sal,e.doj,d.dname,e.deptid,d.deptid from Emp e full outer join

Dept d on e.deptid=d.deptid

Add Column to Table:-

alter table Emp

add mgrno int

create proc spViewEmpData
as
begin
select * from Emp
end

exec spViewEmpData

create proc spViewDeptData
as
Begin
select * from Dept
end

exec spViewDeptData

create proc spInsertDeptData(@deptid int,@dname varchar(15))
as
begin
insert into Dept(deptid,dname) values(@deptid,@dname)
end

exec spInsertDeptData 111,'sss'

create proc spUpdateDeptData(@deptid int,@dname varchar(15))
as
begin
update Dept set dname=@dname where deptid=@deptid
end

exec spUpdateDeptData 111,'jkl'

create proc spDeleteDeptData(@deptid int)
as
begin
delete Dept where deptid=@deptid
end

exec spDeleteDeptData 111

By Inner Join:-
create proc spFindEmpWrkingWthDept(@deptid int)
as
begin
select e.id,e.name,d.dname from Emp e inner join Dept d on e.deptid=d.deptid where
d.deptid=@deptid
end

exec spFindEmpWrkingWthDept 101

OR by Sub Query:-
create proc spFindEmpWrkingWthDept2(@deptid int)
as
begin
select id,name from Emp where deptid=(select deptid from Dept where deptid=@deptid)
end

exec spFindEmpWrkingWthDept2 101

Function:-

Create Function GetEmpNameById(@id int)
Returns @tbl Table
(
@name varchar(30)
)
As
Begin
Insert @tbl
select name from Emp where id=@id;
Return
End

Select name from dbo.GetEmpNameById(1)