SQL Practise content Start.:---------
--creation of a table start
CREATE TABLE [dbo].[department](
[department_id] [int] NOT NULL,
[name] [nvarchar](50) NOT NULL,
[location_id] [int] NOT NULL
) ON [PRIMARY]
=====================
CREATE TABLE [dbo].[job](
[job_id] [int] NOT NULL,
[Function] [varchar](50) NOT NULL
) ON [PRIMARY]
==========================
CREATE TABLE [dbo].[location](
[location_id] [int] NOT NULL,
[regional_group] [nvarchar](50) NULL
) ON [PRIMARY]
============================
CREATE TABLE [dbo].[Employee](
[employee_id] [int] NOT NULL,
[last_name] [nvarchar](50) NULL,
[first_name] [nvarchar](50) NOT NULL,
[middle_name] [nvarchar](50) NULL,
[job_id] [int] NOT NULL,
[manager_id] [int] NOT NULL,
[hiredate] [datetime] not null,
[salary] [int] NOT NULL,
[comm] [int] NULL,
[department_id] [int] NOT NULL
) ON [PRIMARY]
--creation of a table end
--===============================
--insertion in tables
--===============================
insert into location
select 122,'new york'
union
select 123,'dallas'
union
select 124,'chicago'
union
select 167,'boston'
================================
insert into department
select 10,'accounting',122
union
select 20,'research',124
union
select 30,'sales',123
union
select 40,'operations',167
====================
insert into job
select 667,'clerk'
union
select 668,'staff'
union
select 669,'analyst'
union
select 670,'salesperson'
union
select 671,'manager'
union
select 672,'president'
==============================
insert into employee
select 7369,'Smith','John','Q','667','7902','17-DEC-84','800',null,'20'
union
select 7499,'Allen','Kevin','J','670','7698','20-FEB-85','1600','300','30'
union
select 7505,'Doyle','Jean','K','671','7839','04-APR-85','2850',null,'30'
union
select 7506,'Dennis','Lynn','S','671','7839','15-MAY-85','2750',null,'30'
union
select 7507,'Baker','Leslie','D','671','7839','10-JUN-85','2200',null,'40'
union
select 7521,'Wark','Cynthia','D','670','7698','22-FEB-85','1250','500','30'
--insertion in tables end
NOW Practise Session start
--list all the location detail
select * from location
--list all the dempartment detail
select * from department
--list all the job detail
select * from job
--list all the employee detail
select * from employee
--list out the firstname ,lastname salary commission of employees
select first_name, last_name,salary,comm from employee
select employee_id as [ID of the employee],last_name as [name of the employee],department_id as [department Id]
from employee
--list out the employee anual salary of all employee
select last_name ,(12*salary) as [anuual salary] from employee
--where conditions
--list the detail about smith
select * from employee where last_name='smith'
--list the department 20 detail
select * from employee where department_id=20
--list out the detail of the employee whose salary between 800 and 2500
select * from employee where salary between 800 and 2500
--------------
--list out the detail of the employee whose department_id is 10 and 20
select * from employee where department_id=10 or department_id=20
select * from employee where department_id in(10,20)
-------------
--list out the detail of the employee who not working is 10,30
select * from employee where department_id not in(10,30)
--list out the employee detail whose name start form s
select * from employee where last_name like 's%'
--list out the empoloyee whose last_name start with s and end with h
select * from employee where last_name like 's%h'
----------
--list out the employee whose name is of five char and start with s
select * from employee where Len(last_name)=5 and last_name like 's%'
select * from employee where last_name like 's____'
-----------
--list out the employee whose department is 30 and salary is grater then 1700
select * from employee where department_id=30 and salary>1700
--whose not geting commission
select * from employee where comm is NULL or comm=0
--ascending order based on employee id
select employee_id,last_name from employee order by employee_id asc
--descending order based on salary
select employee_id,last_name from employee order by salary desc
--base on name and salary
select * from employee order by last_name asc, salary desc
--based on lastname asc department desc
select * from employee order by last_name asc , department_id desc
--group by and having clause
--how many employee working in diffrent department wise
select count(employee_id) as [number of employee],department_id from employee group by department_id
--list out department wise min,max,avg salary
select count(*), max(salary) as [max sal],avg(salary) as [avg salary], min(salary) as [min sal], department_id
from employee group by department_id
--list out num of employee joind in every month
select count(*) as [num of employee] ,datename(month,hiredate) as [month] from employee group by datename(month,hiredate) order by [month]
select count(*) as [num of employee] ,datename(month,hiredate) as [month] from employee group by datename(month,hiredate),datepart(month,hiredate) order by datepart(month,hiredate)
--list out num of employee joined in each month of the year
select count(*) as [num of employee], (datename(month,hiredate)+'/'+datename(year,hiredate))as [month-year] from employee group by (datename(month,hiredate)+'/'+datename(year,hiredate))
select count(*) as [num of employee], (datename(month,hiredate)+'/'+datename(year,hiredate))as [month-year] from employee group by (datename(month,hiredate)+'/'+datename(year,hiredate)),datename(year,hiredate) order by datename(year,hiredate)
select count(*) as [num of employee], datename(month,hiredate)+'/'+datename(year,hiredate)as [month-year] from employee group by datename(month,hiredate),datename(year,hiredate)
--list out department id having more then 3 employee
select count(*)as [num of employee],department_id from employee group by department_id having count(*)>3
--------------------
--how many employee in february month
select count(*) as [num of employee],datename(month,hiredate) month_char, datepart(month,hiredate) month_num from employee group by datepart(month,hiredate),datename(month,hiredate) having datepart(month,hiredate)=2
select count(*) as [num of employee],datename(month,hiredate) month_char, datepart(month,hiredate) month_num from employee where datepart(month,hiredate)=2 group by datepart(month,hiredate),datename(month,hiredate)
--------------------
--join in feb and jun
select count(*) as [num of employee],datename(month,hiredate),datepart(month,hiredate) from employee group by datepart(month,hiredate),datename(month,hiredate) having datepart(month,hiredate) in(2,6)
--joined in 1985
select count(*)as [num of employee],datename(year,hiredate)as [year] from employee group by datename(year,hiredate) having datename(year,hiredate)=1985
--each month in 1985
select count(*) as [num of employee], datename(month,hiredate) as month_char, datename(year,hiredate) from employee group by datename(month,hiredate),datename(year,hiredate) having datename(year,hiredate)=1985
--feb 1985
select count(*)as [num of employee], datename(month,hiredate)as month_char,datepart(month,hiredate) as month_num, datename(year,hiredate) as [year] from employee group by datename(year,hiredate),datepart(month,hiredate),datename(month,hiredate) having datepart(month,hiredate)=2 and datename(year,hiredate)=1985
--grater then 2 employee joined in feb 1985
select count(*)as [num of employee],department_id from employee group by datename(year,hiredate),datepart(month,hiredate),datename(month,hiredate),department_id having datepart(month,hiredate)=2 and datename(year,hiredate)=1985 and count(*)>=2
----------
--=============
--subquery
--display the employee who get the max salary
select * from employee where salary=(select max(salary) from employee)
--working in sales department
select * from employee where department_id=(select department_id from department where name='sales')
--working in clark department
select * from employee where job_id=(select job_id from job where [function]='clerk')
--working in dallas
select * from employee where department_id=( select department_id from department where location_id=(select location_id from location where regional_group='dallas'))
----------
--num of employee working in sales
select count(*) from employee group by department_id having department_id=(select department_id from department where name='sales')
select count(*) from employee where department_id=(select department_id from department where name='sales')group by department_id
-------------
------
--update employee salary working as clerk with 10%
select * from employee where job_id=(select job_id from job where [function]='clerk')
update employee set salary=(salary*10/100)+salary where job_id=(select job_id from job where [function]='clerk')
-------------
--delete the employee working in opertations department
select * from employee where department_id=(select department_id from department where name='operations')
delete from employee where department_id=(select department_id from department where name='operations')
--------------
--==============================
--display second highest salary
select top 1 * from employee where salary in(select top 2 salary from employee order by salary desc) order by salary asc
select * from employee where salary=(select max(salary) from employee where salary <(select max(salary) from employee))
--display the nth salary
WITH Salaries AS
(
select row_number() over (order by salary desc)as row,salary from employee
)
SELECT
Salary
FROM
Salaries
WHERE
row = 6
----
--list out the employee who earn more then every employee employee in 30
select * from employee where salary>(select max(salary) from employee where department_id=30)
select * from employee where salary> all(select salary from employee where department_id=30)
------
--list out the employee who earn more then lowest every employee employee in 30
select * from employee where salary>(select min(salary) from employee where department_id=30)
select * from employee where salary> any(select salary from employee)
-------------------------------
-- select last_name from department,employee e where department.department_id=e.department_id
----------------
--find out whose department has not employee
select last_name,department_id from employee e where not exists(select department_id from department d where d.department_id=e.department_id )--???
select name from department d where not exists(select d.name from employee e where d.department_id=e.department_id)--???
--co related sub query\
--find out the employee who earn grater then the avr salary of there department
select * from employee e where salary>(select avg(salary)from employee where department_id=e.department_id)
---------------
--joins
--list out employee with there department name
select e.employee_id,last_name, e.department_id, d.name from employee e, department d where e.department_id=d.department_id
select e.employee_id,e.last_name,e.department_id,d.name from employee e inner join department d on e.department_id=d.department_id
select * from employee e inner join department d on e.department_id=d.department_id
--list out employee with designation
select employee_id,last_name,job.job_id,[function] from employee inner join job on employee.job_id=job.job_id
--===============
---------------
--display employee with there department and regional group
select employee_id,last_name,name,regional_group from employee e
inner join department d on e.department_id=d.department_id
inner join location l on d.location_id=l.location_id
---------------
select e.employee_id,e.last_name,e.name,l.regional_group from
(select e.employee_id,last_name,d.name,e.department_id,d.location_id from employee e inner join department d on e.department_id=d.department_id) e
inner join location l on e.location_id=l.location_id
select employee_id,last_name,name,regional_group from employee e,department d, location l where
e.department_id=d.department_id and l.location_id=d.location_id
--============
--how many employee who working in diffrent department and display departmentname
select count(*)[num of emploee],employee.department_id,name from employee
inner join department on employee.department_id=department.department_id
group by employee.department_id,department.name
-----
select count(*)[num of employee],name from employee,department
where employee.department_id=department.department_id
group by name
--how many employee working in sales department
select count(*)[num of employee],name from employee inner join department
on employee.department_id=department.department_id
where name='sales' group by name
--which department having more then 3 employee and display the department name
select name,count(*) from employee e inner join department d
on e.department_id=d.department_id
group by name
having count(*)>3 order by name
select * from location
select * from department
how many jobs in the orgnigation with designations
select [function],count(*) [num of employee] from employee e,job j
where e.job_id=j.job_id
group by j.[function]
--how many employee working in chicago
select count(*),regional_group from employee e
inner join department d on e.department_id=d.department_id
inner join location l on l.location_id=d.location_id
where l.regional_group='chicago'
group by l.regional_group
--non equi join
?
--self join
select * from employee
select * from employee
--display the employee detail with manager name
select e.employee_id,e.last_name,e.manager_id,m.last_name from employee e,employee m
where e.manager_id=m.employee_id
select e.employee_id,e.last_name,e.manager_id ,f.last_name from employee e inner join
employee f on e.manager_id =f.employee_id
--display employee salary who earn more salary then his mangager salary
select e.employee_id,e.last_name,e.manager_id ,m.last_name from employee e inner join
employee m on e.manager_id =m.employee_id
where e.salary>m.salary
--show the number of employee working under every manager
select count (e.employee_id),m.last_name as [manager name] from employee e
inner join employee m
on e.manager_id=m.employee_id
group by m.last_name
--outer join
select * from employee
insert into employee values(
7371,'singh','anish','kumar',667,7370,'2/2/1990',15000,null,50
)
select last_name,d.department_id,d.name from employee e
right outer join department d
on e.department_id=d.department_id
select last_name,d.department_id,d.name from employee e
left outer join department d
on e.department_id=d.department_id
select last_name,d.department_id,d.name from employee e
right outer join department d
on e.department_id=d.department_id
where d.name in('sales','operation')
select * from job
select distinct j.[function],d.department_id from employee e
inner join department d
on e.department_id=d.department_id
inner join job j
on j.job_id=e.job_id
select j.[function],d.department_id from employee e
inner join department d
on e.department_id=d.department_id
inner join job j
on j.job_id=e.job_id
--=========================
No comments:
Post a Comment