Oracle classical syntax (5)

1. Which departments have more people than department 20.
Select deptno, COUNT (*) FROM EMP
GROUP BY DEPTNO
Having count (*)>
(Select count (*) FROM EMP
Where deptno = 20
);

2. Who is SMITH's leader (non-associated subquery ).
SELECT ENAME
FROM EMP
Where empno =
(SELECT MGR FROM EMP
Where ename = 'Smith'
);

3. FORD leads (non-associated subqueries ).
SELECT ENAME
FROM EMP
WHERE MGR IN
(SELECT EMPNO FROM EMP
Where ename = 'Ford'
);

4. Who is SMITH's leader (Associate subquery ).
SELECT ENAME
From emp E1
Where exists (
SELECT 1 from emp E2
Where ename = 'Smith'
AND E2.MGR = E1.EMPNO );

5. FORD leads (Associate subqueries ).
SELECT ENAME
From emp E1
Where exists (
SELECT 1 from emp E2
Where ename = 'Ford'
AND E2.EMPNO = E1.MGR );

6. List employees who work in the same department on a late employment date but whose salaries are higher than those of other colleagues: name, salary, and employment date
(Associate subquery ).
SELECT ename name,
Sal as salary, hiredate as employment date
From emp E1
Where exists (
SELECT 1 from emp E2
WHERE E2.DEPTNO = E1.DEPTNO
AND E1.HIREDATE> E2.HIREDATE
AND E1.SAL> E2.SAL
);

7. which employees are not in the same department as SMITH (non-associated subquery ).
SELECT ENAME
FROM EMP
Where deptno <>
(SELECT DEPTNO FROM EMP
Where ename = 'Smith'
);

8. which employees are not in the same department as SMITH (associated with subquery ).
SELECT ENAME
From emp E1
Where not exists (
SELECT 1 from emp E2
WHERE E1.DEPTNO = E2.DEPTNO
AND E2.ENAME = 'Smith'
);

9. Positions in the RESEARCH Department (non-associated subquery ).
SELECT DISTINCT JOB FROM EMP
Where deptno = (
SELECT DEPTNO FROM DEPT
Where dname = 'Research ');

10. Positions in the RESEARCH Department (associated subquery ).
SELECT DISTINCT JOB FROM EMP
Where exists (
SELECT 1 FROM DEPT
Where emp. DEPTNO = DEPT. DEPTNO
And dept. DNAME = 'Research ');