Scribd
Upload
4 views2 pages

SQL Queries for Employee Salary Analysis

The document contains a series of SQL queries aimed at extracting specific employee and department information from a database. Key queries include identifying sales managers' departments, employees with salary hikes, and those with the same reporting manager, as well as various salary comparisons. The queries also focus on conditions such as minimum salary thresholds and relationships between employees and their managers.

Uploaded by

Accust Here
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
4 views2 pages

SQL Queries for Employee Salary Analysis

The document contains a series of SQL queries aimed at extracting specific employee and department information from a database. Key queries include identifying sales managers' departments, employees with salary hikes, and those with the same reporting manager, as well as various salary comparisons. The queries also focus on conditions such as minimum salary thresholds and relationships between employees and their managers.

Uploaded by

Accust Here
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd

List all the department and location of all the salesman manager-managers.

select dname, loc


from dept
where deptno in (select deptno
from emp
where empno in (select mgr
from emp
where empno in (select mgr
from emp
where job = 'SALESMAN')));

Display the last employee record (hired last) with a 25K hike in salary.
select EMP.*, hiredate, 1.25*sal as Hikedsal
from emp
where hiredate=(select max(hiredate)
from emp);

Display the dname, loc, deptno of employees who have the same reporting manager.

select dname, loc, deptno


from dept
where deptno in (select deptno
from emp
where mgr in (select mgr
from emp
group by mgr
having count(*)>1));

Display the dname of the employees whose manager earns more than 2000.
select dname
from dept
where deptno in (select deptno
from emp
where empno in (select empno
from emp
where mgr in (select empno
from emp
where sal>2000)));

Display all the employees whose salary is greater than the average salary of
department 20.

select *
from emp
where sal > (select avg(sal)
from emp
where deptno=20);

Display the number of employees who work for the research dept and their salary is
lesser than one of the salaries in department 10.
select count(*)
from emp
where (sal<any(select sal
from emp
where deptno=10) and deptno in (select deptno
from dept
where dname='RESEARCH'));

Display the dname if the employee's manager earns 2nd max salary.
select dname
from dept
where deptno in (select deptno
from emp
where mgr in (select empno
from emp
where sal = (select max(sal)
from emp
where sal < (select max(sal)
from emp))));

Display the names of the employees who have the same reporting manager and earn
more than the first hired employee.
select ename
from emp
where (sal>all(select sal
from emp
where hiredate=(select min(hiredate)
from emp)))
and mgr in (select mgr
from emp
group by mgr
having count(*)>1);

Display the details of the employee working as a manager and reports to Blake and
earns sal more than scott who is working in Accounting dept.
select *
from emp
where job='MANAGER and MGR = (select empno
from emp
where ename = 'BLAKE') and sal>(select sal
from emp
where ename = 'SCOTT' and deptno in (select deptno
from emp
where dname ='ACCOUNTING'));

Find the employees who earn the highest salary in the department where the minimum
salary is greater than 1500.
select *
from emp
where sal in ( select max(sal0
from emp
group by deptno
having min(sal)>1500);

Find departments where the highest salary is less than the overall average salary.
select *
from dept
where deptno in (select deptno
from emp
group by deptno
having max(sal)<(select avg(sal)
from emp));

You might also like