子查詢的典型例子

幾個子查詢的典型例子[@more@]

顯示那些比平均工資低的僱員資訊：
SQL> SELECT last_name, title, salary FROM s_emp WHERE salary <
2 ( SELECT AVG(salary) FROM s_emp );

顯示平均工資比“部門號是32”的平均工資大的所有部門：
SQL> SELECT dept_id, AVG(salary) FROM s_emp
2 GROUP BY dept_id HAVING AVG(salary) >
3 (SELECT AVG(salary) FROM s_emp WHERE dept_id = 32);

DEPT_ID AVG(SALARY)
------- -----------
33 1515
50 2025

找出最低平均工資的的工作：
SQL> SELECT title, AVG(salary)
2 FROM s_emp
3 GROUP BY title
4 HAVING AVG(salary) =
5 (SELECT MIN(AVG(salary))
6 FROM s_emp
7 GROUP BY title);

TITLE AVG(SALARY)
--------------------- -----------
Stock Clerk 955