Learn SQL Server

use Tekardia

-- Table Department
create table tblDepartment (
intDeptId int primary key identity(1,1),
varDeptName varchar(40)
)
go
alter procedure spdept 
as
select * from tblDepartment  
go
alter procedure spdept 
as
select intDeptId "Dept ID", varDeptName "Dept Name"  from tblDepartment 
exec spdept 

insert into tblDepartment values('Development')
insert into tblDepartment values('Testing')
insert into tblDepartment values('HR')
insert into tblDepartment values('Marketing')

select * from tblDepartment

select * from tblEmployee emp  
 cross join tblDepartment dept where dept.intDeptId = 1

 -- Sub Query
 select * from
 (select emp.varEmpName,dept.varDeptName from tblDepartment dept
 inner join tblEmployee emp on emp.intDeptId = dept.intDeptId)
 sub 
 order by sub.varEmpName

 -- CTE
  ;with CTE 
  as
 (select emp.varEmpName,dept.varDeptName from tblDepartment dept
 inner join tblEmployee emp on emp.intDeptId = dept.intDeptId) 
 select * from CTE order by CTE.varEmpName


 select * from tblEmployee where varEmpName = (select top 1 varEmpName from tblEmployee1)
 
 -- Table Employee
create table tblEmployee(
intEmpId int primary key identity(1,1),
varEmpName varchar(60),
varGender varchar(10),
intDeptId int not null constraint fk_intDeptId foreign key references tblDepartment(intDeptId)
)

create table tblEmployee1(
intEmpId int primary key identity(1,1),
varEmpName varchar(60),
varGender varchar(10),
intDeptId int not null constraint fk_intDeptId foreign key references tblDepartment(intDeptId)
)

-- Delete Constraint
alter table tblEmployee drop constraint fk_intDeptId

-- Add Constraint with naming
alter table tblEmployee add constraint fk_intDeptId foreign key(intDeptId) references tblDepartment(intDeptId)

-- Add Constraint without naming
alter table tblEmployee add foreign key(intDeptId) references tblDepartment(intDeptId)

insert into tblEmployee values('Kumar','Male',1)
insert into tblEmployee values('Muthu','Male',2)
insert into tblEmployee values('Raja','Male',3)
insert into tblEmployee values('Devi','Female',4)
insert into tblEmployee values('Ravi','Male',2)
insert into tblEmployee values('Divya','Female',4)
insert into tblEmployee values('Arjun','Male',3)
insert into tblEmployee values('Ramya',Null,3)
insert into tblEmployee values('Kiruba',Null,4)

insert into tblEmployee1 values('Kumar','Male',1)
insert into tblEmployee1 values('Muthu','Male',2)
insert into tblEmployee1 values('Raja','Male',3)
insert into tblEmployee1 values('Devi','Female',4)
insert into tblEmployee values('Ravi','Male',2)
insert into tblEmployee values('Divya','Female',4)
insert into tblEmployee values('Arjun','Male',3)
insert into tblEmployee values('Ramya',Null,3)
insert into tblEmployee values('Kiruba',Null,4)

select * from tblEmployee
truncate table tblEmployee
 
update tblEmployee
set varEmpName = 'Divya Sri'
where intEmpId=6

update tblEmployee
set varGender = 'Female'
where varGender='FeMale'

update tblEmployee
set varGender = null
where varEmpName='Ramya'

select * from tblEmployee where varGender is null
select * from tblEmployee where varGender is not null

select * from tblEmployee where varGender='Female' and intDeptId = 3
select * from tblEmployee where varGender <> 'Male'
select * from tblEmployee where varEmpName >='D'
select * from tblEmployee where varEmpName like 'D%'
select * from tblEmployee where varEmpName like '%U'
select * from tblEmployee where intDeptId = 4 order by varEmpName desc
select * from tblEmployee where varEmpName like '%i' order by varEmpName desc

select varEmpName+' - '+varGender from tblEmployee
select concat(varEmpName,' - ',varGender) from tblEmployee

-- Operators
+	Addition
-	Subtraction
*	Multiplication
/	Division
%	Modulus
select varEmpName, intDeptId, intDeptId*2 as MultiplyDept from tblEmployee
select varEmpName, intDeptId, intDeptId*2 as MultiplyDept from tblEmployee where intDeptId >=3

-- Aggregate
-- COUNT(), SUM(), AVG(), MAX(), MIN()	 
select count(*) as AllRows from tblEmployee   -- 9
select sum(intDeptId) as SumofDeptId from tblEmployee   --(26)
select avg(intDeptId) as AvgDeptId from tblEmployee  -- 26/9
select max(intDeptId) from tblEmployee
select max(intDeptId), varEmpName from tblEmployee where varGender <> 'Female'  -- Check Error
select max(intDeptId) as MaxDeptId, varEmpName from tblEmployee where intDeptId >=2 group by varempName

select distinct * from tblEmployee Emp inner join tblDepartment Dept on Emp.intDeptId = Dept.intDeptId
select distinct * from tblEmployee Emp left outer join tblDepartment Dept on Emp.intDeptId = Dept.intDeptId
select distinct * from tblEmployee Emp right outer join tblDepartment Dept on Emp.intDeptId = Dept.intDeptId

-- Temp Table
create table #tempdb1(intId int primary key, varName varchar(30))

insert into #tempdb1 values(1,'Bala')

select * from #tempdb1

-- Global Temp Table
create table ##tempdb1(intId int primary key, varName varchar(30))

insert into ##tempdb1 values(1,'Bala')

select * from ##tempdb1

-- Table Variable
declare @TmpVar table(intId int primary key,varName varchar(30))
insert into @TmpVar values(1,'Kumar')
select * from @TmpVar
 
create table Company(
intEmpId int identity(1,1) primary key,
varName varchar(40),
intMgrId int
)

insert into Company values('Mgr 1',1)
insert into Company values('Mgr 2',2)
insert into Company values('Emp 1',1)
insert into Company values('Emp 2',2)

select * from Company

select a.varName, b.VarName
from Company a inner join Company b on a.intMgrid = b.intEmpId
order by b.varName
-- Self Join
select a.varName "Employee", b.VarName "Manager"
from Company a, Company b where a.intMgrid = b.intEmpId
order by b.varName