Oracle Database 11g Fundamental SQL (Chapter 7)

SubQuery

Penggunaan subquery single row

SELECT last_name, salary
FROM employees
WHERE salary > (SELECT salary FROM employees WHERE last_name = 'Abel');

Penggunaan subquery multiple row

SELECT last_name, job_id
FROM employees
WHERE job_id = (SELECT job_id FROM employees WHERE employee_id = 141);

Penggunaan single row sub query
SELECT last_name, job_id, salary
FROM employees
WHERE job_id = (SELECT job_id FROM employees WHERE last_name = ‘Taylor’)
AND salary > (SELECT salary FROM employees WHERE last_name = ‘Taylor’);

Penggunaaan subquery dengan function

SELECT last_name, job_id, salary
FROM employees WHERE salary = (SELECT MIN(salary) FROM employees);

SELECT department_id, MIN(salary) FROM employees GROUP BY department_id
HAVING MIN(salary) > (SELECT MIN(salary) FROM employees WHERE department_id = 50);

SELECT job_id, AVG(salary) FROM employees GROUP BY job_id
HAVING AVG(salary) = (SELECT MIN(AVG(salary))
FROM employees
GROUP BY job_id);

Kasus jika tidak ada data yang dihasilkan dari inner query

SELECT last_name, job_id
FROM employees
WHERE job_id = (SELECT job_id FROM employees WHERE last_name = 'Haas');


Multiple row subquery
SELECT last_name, salary, department_id
FROM employees
WHERE salary IN (SELECT MIN(salary)
FROM employees
GROUP BY department_id);

SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary < ANY (SELECT salary FROM employees WHERE job_id = 'IT_PROG') AND job_id <> 'IT_PROG';


SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary < ALL (SELECT salary FROM employees WHERE job_id = 'IT_PROG') AND job_id <> 'IT_PROG';

SELECT emp.last_name
FROM employees emp
WHERE emp.employee_id NOT IN
(SELECT mgr.manager_id
FROM employees mgr);

SELECT emp.last_name
FROM employees emp
WHERE emp.employee_id IN
(SELECT mgr.manager_id
FROM employees mgr);

SELECT last_name FROM employees
WHERE employee_id NOT IN
(SELECT manager_id
FROM employees
WHERE manager_id IS NOT NULL);