GROUP FUNCTIONS AND SUBQUERIES

1. Display name, job for employee whose job is same as a given employeenumber and his salary is greater than the given employee

SQL> select ename, job from emp where job=(select job from emp where empno=7876) and sal>(select sal from emp where empno=7876);

ENAME JOB
---------- ---------
MILLER CLERK

2. Display the name , job and salary for a given employee whose salary is equal to the minimum salary.

SQL> select ename, job,sal from emp where sal=(select min(sal) from emp);

ENAME JOB SAL
---------- --------- ----------
SMITH CLERK 800

3. Display name, job, and salary for an employee whose salary is less than the maximum salary of any given job;

SQL> select ename, job,sal from emp where sal select ename, job,sal from emp where sal >any(select sal from emp where job<>'CLERK');

ENAME JOB SAL
---------- --------- ----------
ALLEN SALESMAN 1600
JONES MANAGER 2975
BLAKE MANAGER 2850
CLARK MANAGER 2450
SCOTT ANALYST 3000
KING PRESIDENT 5000
TURNER SALESMAN 1500
FORD ANALYST 3000
MILLER CLERK 1300

9 rows selected.




5. Display name, job, and employeenumber for an employee whose salary is less than the maximum salary of any given job and his job is not equal to the given job;

SQL> select empno,ename,job from emp where sal>any(select sal from emp where job='CLERK') and job<>'CLERK';

EMPNO ENAME JOB
---------- ---------- ---------
7499 ALLEN SALESMAN
7521 WARD SALESMAN
7566 JONES MANAGER
7654 MARTIN SALESMAN
7698 BLAKE MANAGER
7782 CLARK MANAGER
7788 SCOTT ANALYST
7839 KING PRESIDENT
7844 TURNER SALESMAN
7902 FORD ANALYST

10 rows selected.


6.Display name, job, and salary for an employee whose salary is equal to the salary of any job which is not equal to the given job;

SQL> select ename, job,sal from emp where sal =any (select sal from emp where job<>'CLERK');

ENAME JOB SAL
---------- --------- ----------
WARD SALESMAN 1250
MARTIN SALESMAN 1250
TURNER SALESMAN 1500
ALLEN SALESMAN 1600
CLARK MANAGER 2450
BLAKE MANAGER 2850
JONES MANAGER 2975
SCOTT ANALYST 3000
FORD ANALYST 3000
KING PRESIDENT 5000

10 rows selected.

7.Display name, job, and employeenumber for an employee whose salary is more than the maximum salary of their department;

SQL> select empno, ename, job from emp where sal>all (select avg(sal) from emp group by deptno);

EMPNO ENAME JOB
---------- ---------- ---------
7566 JONES MANAGER
7788 SCOTT ANALYST
7839 KING PRESIDENT
7902 FORD ANALYST


8.Display name, job, and employeenumber for an employee whose salary is less than the minimum salary of their department;

SQL> select empno, ename, job from emp where sal select a.ename,a.sal,a.deptno ,b.salavg from emp a,(select deptno,avg(sal) salavg from emp group by deptno) b where a.deptno=b.deptno and a.sal>b.salavg;

ENAME SAL DEPTNO SALAVG
---------- ---------- ---------- ----------
KING 5000 10 2916.66667
FORD 3000 20 2175
SCOTT 3000 20 2175
JONES 2975 20 2175
ALLEN 1600 30 1566.66667
BLAKE 2850 30 1566.66667

6 rows selected.

10. Display name, departmentnumber, salary and commission for all employees whose salary and name is matched with the given department.

SQL> select ename,deptno,sal,comm from emp where (sal,ename) in (select sal,ename from emp where deptno=30);

ENAME DEPTNO SAL COMM
---------- ---------- ---------- ----------
JAMES 30 950
MARTIN 30 1250 1400
WARD 30 1250 500
TURNER 30 1500 0
ALLEN 30 1600 300
BLAKE 30 2850

6 rows selected.

11. Display name, departmentnumber, salary and commission for all employees whose salary and commission is matched with the given department.

SQL> select ename,deptno,sal,comm from emp where (sal,nvl(comm,0)) in ( select sal,nvl(comm,0) from emp where deptno=30);

ENAME DEPTNO SAL COMM
---------- ---------- ---------- ----------
JAMES 30 950 0
WARD 30 1250 500
MARTIN 30 1250 1400
TURNER 30 1500 0
ALLEN 30 1600 300
BLAKE 30 2850 0

6 rows selected.