General SQL Queries

create database empdetails
use empdetails
create table department(deptid char(4),deptname varchar(15),depthead varchar(15))
insert into department values('D01','HR','Anjan')
insert into department values('D02','Sales','Kavitha')
insert into department values('D03','Finance','Suresh')
insert into department values('D04','Admin',null)
insert into department values('D05','System','Ravi')
select * from department
delete from department
create table employ(empid char(4),fname varchar(15),lname varchar(15),salary int,deptid char(4))
insert into employ values('E01','Rahul','Raj',35000,'D02')
insert into employ values('E02','Anil','Babu',24000,'D01')
insert into employ values('E03','Samir','Thahir',23000,'D03')
insert into employ values('E04','Jaya','Krishnan',33000,'D01')
insert into employ values('E05','Suresh','Peters',34500,'D01')
insert into employ values('E06','Karthik','kumaraj',24500,'D01')
insert into employ values('E07','Bhawana','Aravind',28000,'D02')
insert into employ values('E08','Steven','Devassy',22500,'D03')
insert into employ values('E09','Abdul','Rehman',29500,null)
insert into employ values('E010','Manoj','Kumar',30500,null)
--List all the Rows and Columns
select * from employ
-- Retrieve firstname and salary from employ table
--(selected columns)
select fname,salary from employ
--List the employees working in Dept D01 (Selected Rows)
select * from employ where deptid='D01'
--write a query to display employees working in Dept D01 and D02
select * from employ where deptid='D01' or deptid='D02'
--List of employees working in dept other than D01 and D02
select * from employ where deptid not in ('D01','D02')
--List of employees where depts are not allocated
select * from employ where deptid is null
--List of employees where depts not allocated
select * from employ where deptid is not null
select * from employ where deptid !='D01'
select * from employ where deptid <>'D01'
--List of employees with saary above 3000o belonging to dept 01
select * from employ where salary>30000 and deptid='D01'
select * from employ where salary>=28000 and salary<=30000
select * from employ where salary between 25000 and 30000
select * from employ where fname='suresh'
select * from employ where fname like 's%'
select * from employ where fname not like 's%'
select * from employ where fname like '%l'
select * from employ where fname like '%u_'
select * from employ where fname like '_a%'
select * from employ where fname like '[ABCD]%'
select * from employ where fname like '[A-D]%'
select * from employ where fname like '[^A-D]%'
-- list of employee details in the ascending and descending order of salary
select* from employ order by salary asc
select* from employ order by salary desc

select * from employ order by salary asc,deptid asc
select * from employ order by deptid asc,salary asc
select * from employ order by salary asc,deptid desc
-- Aggregate functions
select SUM(salary) from employ
select SUM(salary) from employ where deptid='D01'
select SUM(salary) from employ where deptid='D02'
select MAX(salary) from employ
select Min(salary) from employ
select avg(salary) from employ
select COUNT(empid) from employ
select COUNT(distinct deptid) from employ
select COUNT(*) from employ
select COUNT(deptid) from employ
select distinct deptid from employ

--Write a query to display the details of employee who is getting the maximum salary
select * from employ where salary= (select MAX(salary) from employ)

--Write a query to display the details of employee who is getting more than the average salary
select * from employ where salary> (select avg(salary) from employ)

--Write a query to display the details of employee who is working in the same department of rahul
select * from employ where deptid= (select (deptid) from employ where fname='Rahul')

--group by clause for summarise reports/data
select deptid,sum(salary) 'sum salary to each dept' from employ group by deptid
select deptid,count(empid) 'no of employees to each dept' from employ group by deptid
select deptid,sum(salary) 'sum salary to each dept' from employ groupby deptid having SUM(salary)>10000
select * into copyemploy1 from employ
select * from copyemploy1 update copyemploy1set salary= 29000
delete from copyemploy1
select * into copyemploy2 from employ
select * from copyemploy2 update copyemploy2set salary=30000, deptid='D03'where empid='E06'
sp_help copyemploy2
alter table copyemploy2
alter column lname varchar(30)
alter table copyemploy2
drop column salary
select * from copyemploy2
alter table copyemploy2 add phoneno varchar(15)
drop table copyemploy2
select * from employ
select * into cemploy from employ
select * from department
select * into cdepartment from department
sp_help employ
sp_help department

delete from department where deptid='D01' 
--error: employees are working in this particular dept., hence the dept can't be removed

delete from department where deptid='D05'
--error: no employee is working in this dept.,thus it can be deleted

alter table employ
drop constraint fkempdeptid
alter table employ
add constraint fkempdeptid foreign key (deptid) references department (deptid) on delete set  null  on update cascade
select  * from employ
select * from department
delete from department where deptid='D02'
update department set deptid='D08'where deptid='D03'
delete from department where deptid='D08'
select * from employ
truncate table employ

create table empdetxml(eid char(4),ename xml)
insert into empdetxml values('e1','<fname>sai</fname><lname>kiran</lname>')
insert into empdetxml values('e2','<fname>raj</fname><lname>kumar</lname>')
select * from empdetxml
select * from cemploy for xml auto
selec t* from cemploy for xml auto,elements
select * from cemploy for xml auto,elements,root('empdetails')
drop table employ
select * into employ from cemploy
select * from employ
create view vwemp as select empid,fname,salary from employ
select * from vwemp
sp_help'vwemp'
sp_helptext'vwemp'
select id,name from sysobjects
select Id,TEXT from syscomments
alter view vwemp with encryption as select empid,fname,salary from employ
alter view vwemp as select empid,fname,salary from employ
select * from employ
select * from vwemp
delete from vwemp where empid='E05'
update vwemp set salary=30000where empid='E08'
insert into vwemp values('E11','Mahesh',35000)
select * from employ
select * from vwemp
delete from vwemp where empid='E11'
alter table employ alter column lname varchar(20) not null
insert into vwemp values ('E11','Mahesh',35000)
create view vwemp2 as select empid,fname,salary from employ where salary>25000 with check option
select * from vwemp2
update vwemp2 set salary=22000 where empid='E04'
update vwemp2 set salary=26000 where empid='E04'
update employ set salary=22000 where empid='E04'
select*from employ

--PARTITIONED VIEW
select * from recruitmentagencies union all select * from contractrecruiter

create view vwracr as select * from recruitmentagencies union all select * from contractrecruiter
select * from vwracr
select * from recruitmentagencies
select * from contractrecruiter

-- inline view or derived tables
select * from (select fname,salary,deptid from employ) as e
delete from department
select * from cdepartment
select * from employ
select * from department

--complex view
create view wempdept as
select empid,fname,lname,salary,deptname,depthead from employ e join department d on e.deptid=d.deptid

select * from wempdept
select deptname from wempdept v join employ e on v.empid=e.empid

alter view wempdept as
select empid,fname,lname,salary,deptname,depthead,e.deptid 'empdeptid',d.deptid from employ e join department d on e.deptid=d.deptid
update wempdept set salary=35000, deptname='Admin' where empid='E06'
update wempdeptset deptname='HR'where deptid='D01'
select * from wempdept

create view vwemp9 with schemabinding as
select empid,fname,salary from dbo.employ

create unique clustered index icxvwemp6 on vwemp9(empid)

select * from vwemp9
alter table employ alter column fname varchar(25)

create view vwnestedview as
select fname,deptname from wempdept

select * from vwnestedview

create table emp12(eid char(4),ename varchar(10))

--Bulk Insert
Bulk insert wiproempdetails.dbo.emp12 from 'C:\mydata.txt' with(DATAFILETYPE='char', FIELDTERMINATOR=',', ROWTERMINATOR=';')
select * from emp12

Cheers..!!
https://techiesss.blogspot.in/