IMP Queries
1> nth highest salary
select * from (select employees.*, dense_rank() over(order by salary desc) rn from employees) where rn<4;
select max(salary) from employees where salary<(select max(salary) from employees where salary<(select
max(salary) from employees));
select * from employees e1 where 1 = (select count(distinct salary) from employees e2 where
e1.salary<e2.salary;
2> nth lowest salary
select * from (select employees.*, dense_rank() over(order by salary ) rn from employees) where rn<4;
select min(salary) from employees where salary>(select min(salary) from employees where salary>(select
min(salary) from employees));
select * from employees e1 where 3 = (select count(distinct salary) from employees e2 where
e1.salary>e2.salary;
3> Even records
select * from employee where mod(emp_id,2)=0;
4> Odd records
select * from employee where mod(emp_id,2)=1;
5> First n records
select * from employees where rownum=<30;
6> Last n records
Select rownum, name from employee order by rownum desc;
7> First record
select * from employees where rownum=1;
select * from employees where rowid=(select min(rowid) from employees);
8> Last record
select * from (select employees.*,rownum rn from employees) where rn = (select count(*) from
employees);
select * from employees where rowid=(select max(rowid) from employees);
9> Department_name which doesn't have employees
select * from departments d where d.department_id not in(select e.department_id from employees e where
d.department_id=e.department_id );
select * from departments d where d,department_id not exists (select e.department_id from employees e
where d.department_id=e.department_id );
10> Distinct records
select distinct employees.* from employees
select unique employees.* from employees
11> Delete Duplicate records
delete from employees where rowid not in (select max(rowid) from employees group by emp_id;
12> Department wise count of employees
select d.department_id,count(e.employee_id) from departments d left outer join employees e on
d.department_id = e.department_id group by d.department_id;
select d.department_id,count(e.employee_id) from departments d , employees e where d.department_id =
e.department_id(+) group by d.department_id;
13> Create empty table
create table emp as select * from employees where 1=2
14> Retrieving records where salary between 10000 to 20000
select * from employees where salary >=10000 and salary<=20000;
select * from employees where salary between 10000 and 20000;
15> All records but common records from two tables only once
select * from employees union select * from emp;
16> Fetch common records from both tables
select * from employees intersect select * from emp;
17> All records from emp1 which is not present in emp2
select * from employees minus select * from emp;
18> Department wise total salary provided that department must have more than 2 employees
select department_id, sum(salary) from employees group by department_id having count(employee_id)>2;
19> Count employee under each manager
select manager_id, count(employee_id) from employees where manager_id is not null group by
manager_id;
20> Employees with his manager name
select e.employee_id, e.first_name, m.first_name,m.manager_id from employees e join (select
e.first_name,temp.manager_id from employees e join (select manager_id from employees group by
manager_id ) temp on e.employee_id=temp.manager_id) m on e.manager_id=m.manager_id order by
manager_id
select e.first_name,m.first_name from employees e left outer join (select * from employees where
employee_id in(select distinct manager_id from employees)) m on m.employee_id=e.manager_id.
21> Max salary department wise.
Select dept_id, max(salary) from employee group by dept_id;
22> 2nd highest salary
Select max(salary) from employee where salary not in (select max(salary) from employee);
Select max(salary) from employee where salary > (select max(salary) from employee);
select employees.*, dense rank() over(order by salary desc) from employees) where salary<3;
23> 3rd highest salary
Select max(salary) from employee where salary > (select max(salary) from employee) > (select
max(salary) from employee);
select employees.*, dense rank() over(order by salary desc) from employees) where salary<4;
24> to fetch duplicate records / to get duplicate records
Select emp_no, count(*) from employees group by emp_no having count(*) >1;
25> 2nd highest salary department wise
Select dept_id, max(salary) from employee where salary not in (select max(salary) from employee) group
by dept_id;
