For praccticing this we will create two tables tblDepartment and tblEmployee as follows:
-------------------------------------------------------------------------------------------
Create table tbl_deprtment(dept_id int ,dept_name varchar(50))
create table tblEmployee(emp_id int, emp_name varchar(50),emp_salary int,department_id int foreign key references tbl_employee(emp_id))
now insert records in tbl_Department:
insert into tbl_department('10','IT')
insert into tbl_department('20','Mechanical')
insert into tbl_department('30','Computer Science')
insert into tbl_department('40','civil')
Now add records in tbl_employee for different departments
insert into tbl_department('10','Satish',15000)
insert into tbl_department('10','Sachin',20000)
insert into tbl_department('20','Akash',25000)
insert into tbl_department('20','Manoj',30000)
insert into tbl_department('30','Suraj'24000)
insert into tbl_department('40','Nikhil'24000)
you can see that:
in tbl_Department there are 4 records
in tbl_Employee there are 6 records with different number that means they belong to different departments.
Operations on tblDepartment and tblEmployee table:
How to select records with department id = 10
select * from tbl_employee where fkey_dept_id=10
You can see that we got 2 records but the depaertment id is 10.We want the name of department in that records.
select e.emp_id,e.emp_name,e.emp_salary,d.dept_name
from tbl_employee e,tbl_department d
where e.fkey_dept_id=d.dept_id and e.fkey_dept_id=10
Now you can see that we got the same records but in last column we also got department name.
---------------------------------------------------------------------------------
How to select records for 2 departments at a time
select e.emp_id,e.emp_name,e.emp_salary,d.dept_name
from tbl_employee e,tbl_department d
where e.fkey_dept_id=d.dept_id and e.fkey_dept_id in(10,20)
---------------------------------------------------------------------------------
How to count number of employees for each departments
select d.dept_name,count(e.*) as [Total of employee]
from tbl_employee e,tbl_department d
where e.fkey_dept_id=d.dept_id and
group by d.dept_name
---------------------------------------------------------------------------------
How to select records having maximum salary in each department
select d.dept_name,max(e.emp_salary) as [Employee salary]
from tbl_employee e,tbl_department d
where e.fkey_dept_id=d.dept_id and
group by d.dept_name
---------------------------------------------------------------------------------
How to get highest salary its employee name and department dane of that employee details
top 1 d.dept_name,e.emp_name,max(e.emp_salary) as [Employee salary]
from tbl_employee e,tbl_department d
where e.fkey_dept_id=d.dept_id and
group by d.dept_name,e.emp_name
order by [Employee salary] desc
---------------------------------------------------------------------------------
How to get secod highest salary from tbl_employee
select max(esal) as [Employee salary]
from employee
where esal not in(select max(esal) from employee)
---------------------------------------------------------------------------------
How to get third highest salary from tbl_employee
select distinct emp_salary from tblEmployee e1
where 3=(select count(distinct emp_salary) from tblEmployee e2 where e1.emp_salary<=e2.emp_salary)
order by emp_salary desc
---------------------------------------------------------------------------------
How to get third lowest salary from tbl_employee
select distinct emp_salary from tblEmployee e1
where 3=(select count(distinct emp_salary) from tblEmployee e2 where e1.emp_salary>=e2.emp_salary)
order by emp_salary desc
---------------------------------------------------------------------------------
How to get 3 highest salary from tbl_employee
select distinct emp_salary from tblEmployee e1
where 3>=(select count(distinct emp_salary) from tblEmployee e2 where e1.emp_salary<=e2.emp_salary)
order by emp_salary desc
---------------------------------------------------------------------------------
How to get 3 lowest salary from tbl_employee
select distinct emp_salary from tblEmployee e1
where 3>=(select count(distinct emp_salary) from tblEmployee e2 where e1.emp_salary>=e2.emp_salary)
order by emp_salary desc
---------------------------------------------------------------------------------