create database SqlPracticeDB
use SqlPracticeDB
Dt:02-08-2023:-
--Change the table name
sp_rename 'dbo.Employee','EmployeeInfo'
--Change the column name
sp_rename 'dbo.EmployeeInfo.Id','EmployeeId'
sp_rename 'EmployeeInfo.Id','EmployeeId' --after SQL 2012 it supported with out dbo
--Add new column
alter table EmployeeInfo
add Department varchar(50),
Designation varchar(50),
Salary money,
DOJ datetime
--Drop the columns
alter table EmployeeInfo
drop column doj
--Change the datatype of the column
alter table EmployeeInfo
alter column DOJ datetime
--Remove table from database
drop table EmployeeInfo
--Space in table/column name
Create table [Student Info]([Roll No] int,Name varchar(50))
alter table [Student Info]
drop column [Roll No]
drop table [Student Info]
--Create table with constraints
create table EmployeeInfo
(EmpId int constraint pk_EmpId primary key identity,
Name varchar(50) not null,
Department varchar(30) check(Department in('IT','HR','IS','InfoSec','Finance')),
Salary money constraint chk_salary check(Salary between 10000 and 50000),
Location varchar(50) constraint def_location default 'Grape_Garden',
DOJ datetime default getdate(),
EmailId varchar(50) unique,
MobileNo bigint,
ReportsTo varchar(50)
)
alter table EmployeeInfo
drop constraint UQ__Employee__7ED91ACE62F28DA7
alter table EmployeeInfo
add constraint uq_email unique(EmailId)
create table ProjectInfo
(ProjId int constraint pk_ProjId primary key identity(10,10),
ProjName varchar(30) not null,
Domain varchar(30) not null
)
insert into ProjectInfo values('LOL','Manufacturing'),('Disney','Agtech')
--Foreign Key
alter table EmployeeInfo
add ProjId int constraint fk_proj_emp references ProjectInfo(ProjId)
insert into EmployeeInfo values('Subs','IT',10000,'Hyderabad','2023-08-02',null,9876543210,null,10)
--Insert with specific columns
insert into EmployeeInfo(Name,Department,Salary,EmailId) values('Scott','IT',20000,'scott@gmail.com')
Dt:03-08-2023:-
--Update null values
update EmployeeInfo set projid=20 where projid is null
--The DELETE statement conflicted with the REFERENCE constraint "fk_proj_emp"
delete from ProjectInfo
--If table created with--"On Cascade Delete" --can delete the records from both Parent and Child table.
--Count(*) --count both Null and Not Null values
--Count(Comm) --count only Not Null values
--Group by --Before grouping use 'Where' clause --After grouping use 'Having' clause
Dt:04-08-2023:-
No Session
Dt:07-08-2023:-
Joins
--Inner Join
--Left Outer Join --from 1st table returns table if No data then Null will return
--Right Outer Join --from 2nd table returns table if No data then Null will return
--Full Outer Join --from both table returns table if No data then Null will return
--Self Join
--ANY = OR --It will consider both value of (BLAKE,FORD) give all results of (greater than and eqaul to) records.
--ALL = AND --It will consider one greater value of (BLAKE,FORD) give all results of (greater than and eqaul to) records.
select ename,sal from emp where sal>= ANY(select sal from emp where ename in('blake','ford'))
select ename,sal from emp where sal>= ALL(select sal from emp where ename in('blake','ford'))
--Correlated Sub Query
select* from emp e,(select * from emp where empno in(select mgr from emp))m
where e.sal>m.sal and e.mgr=m.empno
--ROW_NUMBER() --its generate unidue ids e.g. 1,2,3,..
select e.*, ROW_NUMBER() over (order by salary desc) as 'RowNo' from EmployeeSal e
--RANK() --its generate unidue with duplicate ids with skip e.g. 1,1,3,4,5,..
select e.*, RANK() over (order by salary) as 'RowNo' from EmployeeSal e
--DENSE_RANK() --its generate unidue with duplicate ids with continues manner e.g. 1,1,2,3,4,5,..
select e.*, DENSE_RANK() over (order by salary) as 'RowNo' from EmployeeSal e
--Index --Data retrival is faster
--Cluster Index --it will generated on Primary Key column --only one cluster index in table
--Unidue Non Cluster Index --it will created on Primary Key column
--Non Cluster Index --it will created on other columns --Upto 999 non cluster index
create index inx_name on emp(ename)
create index inx_name_sal on emp(ename,sal)
create index inx_name_sal_dept on emp(ename,sal,deptno)
drop index inx_name on emp
--View() --Not occupy any memory
--Not have its own strucure
--Views are written bcoz complex query are not written multiple times
--Views are updatable --it will perform DML queries Insert,Update,Delete
create view vw_EmpEarningMoreMgr
as
select e.empno,e.ename,e.sal,e.mgr,e.deptno from emp e,(select * from emp where empno in(select mgr from emp))m
where e.sal>m.sal and e.mgr=m.empno
select * from vw_EmpEarningMoreMgr
create view emp_clerk
as
select * from emp where job='clerk'
insert into emp_clerk(empno,ename,job,sal) values(100,'Venu','HR',3005)
update emp_clerk set sal=sal+0.99
update emp_clerk set job='sales' --it will allow to update
--with check option
create view vw_empdept20
as
select * from emp where deptno=20
with check option
update vw_empdept20 set deptno=50 --it won't allow to update
select * from vw_empdept20
Dt:08-08-2023:-
TSQL
Declare @a int,@b int,@res int
set @a=10
set @b=20
set @res=@a+@b
print @res
print 'addition value is : '+str(@res)
print 'addition value is : '+cast(@res as varchar(10))
print 'addition value is : '+convert(varchar(10),@res)
--Stored Procedure
create procedure sp_updateEmpSalary
@dno int
as
begin
print 'Old Salary'
select * from emp where DEPTNO=@dno
if @dno=10
update emp set sal=sal+10 where DEPTNO=@dno
else if @dno=20
update emp set sal=sal+20 where DEPTNO=@dno
else
update emp set sal=sal+5 where DEPTNO=@dno
print 'New Salary'
select * from emp where DEPTNO=@dno
end
exec sp_updateEmpSalary 10
--Function
Create function fn_calculator(@a int,
@b int,
@op char)
returns int
as
begin
declare @res int
set @res=case @op
when '+' then @a+@b
when '-' then @a-@b
when '*' then @a*@b
when '/' then @a/@b
else 0
end
return @res
end
declare @res int
set @res=dbo.fn_calculator(10,20,'+')
print 'Result is '+str(@res)
create function fn_JobWiseEmployee(
@job varchar(30))
returns table
as
return select * from emp where job=@job
select * from dbo.fn_JobWiseEmployee('salesman')
create table Registration(RegNo int primary key identity,
Name varchar(30) not null,
Address varchar(max) not null,
MobNo bigint unique)
create procedure sp_InsertRegistration(@nm varchar(30),
@addr varchar(max),
@mno bigint)
as
begin
declare @newRegNo int
insert into Registration values(@nm,@addr,@mno)
select @newRegNo=@@IDENTITY
print 'New RegNo id'+ str(@newRegNo)
end
exec dbo.sp_InsertRegistration 'Prabhu','Bang',4457890213
create procedure sp_updateEmp(@empno int,
@newsal money)
as
begin
update emp set sal=@newsal where EMPNO=@empno
end
exec dbo.sp_updateEmp 7902,2100
--Cursor
Create procedure sp_selectUpdateData_Cursor
as
begin
set nocount on
declare @empno int,@ename varchar(30),@sal money
declare printEmp cursor read_only for select empno,ename,sal from emp
open printEmp
fetch next from printEmp into @empno,@ename,@sal
while @@FETCH_STATUS=0
begin
select str(@empno) + space(10)+@ename+space(10)+str(@sal) as EmpData
fetch next from printEmp into @empno,@ename,@sal
end
close printEmp
deallocate printEmp
end
exec sp_selectUpdateData_Cursor
--Trigger
create trigger tr_insertNewEmp
on emp
for insert
as
begin
declare @sal money
select @sal=sal from inserted
if @sal<5000
begin
Rollback
print 'Min salary should be 5000'
end
else
begin
print 'New Emp has added successfully'
end
end
insert into emp(empno,ename,sal) values(1224,'Pushpa',5250)
create trigger tr_updateEmp
on emp
for update
as
begin
declare @oldsal money,@newsal money,@empno int
select @oldsal=sal from deleted
select @newsal=sal,@empno=EMPNO from inserted
if @newsal<@oldsal
begin
Rollback
print 'New salary cannot be lesser than old salary'
end
else
begin
print 'Salary updated'
end
end
update emp set sal=3000 where EMPNO=100
select * from emp
--Create table from existing table
select * into resignedemp from emp where 1<>1
select * from resignedemp
alter trigger tr_deleteEmp
on emp
for delete
as
begin
declare @empno int, @ename varchar(30),@job varchar(20),@mgr varchar(20),@hiredate datetime,@sal money,@comm money,@deptno int
select @empno=empno,@ename=ENAME,@job=JOB,@mgr=MGR,@hiredate=HIREDATE,@sal=SAL,@comm=COMM,@deptno=@deptno from deleted
insert into resignedemp values(@empno,@ename,@job,@mgr,@hiredate,@sal,@comm,@deptno)
print 'Deleted data moved to resignedemp table'
end
delete from emp where empno=100
select * from resignedemp
No comments:
Post a Comment