【练习】子查询

1.子查询：子查询要包含在括号内，将子查询放在比较条件的右侧增强可读性（子查询可以出现在比较运算符的两侧），单行操作符对应单行子查询，多行操作符对应多行子查询

SQL> select last_name,salary from employees where salary > (select salary from employees where last_name = 'Abel');

LAST_NAME                     SALARY
------------------------- ----------
Hartstein                      13000
Higgins                        12008
King                           24000
Kochhar                        17000
De Haan                        17000
Greenberg                      12008
Russell                        14000
Partners                       13500
Errazuriz                      12000
Ozer                           11500

10 rows selected.

2.在子查询中使用组函数：

SQL> SELECT last_name, job_id, salary
FROM   employees
WHERE  salary = 
               2    3    4     (SELECT MIN(salary)
                 FROM   employees);
  5  
LAST_NAME                 JOB_ID         SALARY
------------------------- ---------- ----------
Olson                     ST_CLERK         2100

3.子查询中的 HAVING 子句：首先执行子查询，向主查询中的 HAVING 子句返回结果：

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

DEPARTMENT_ID MIN(SALARY)
------------- -----------
          100        6900
           30        2500
                     7000
           20        6000
           70       10000
           90       17000
          110        8300
           40        6500
           80        6100
           10        4400
           60        4200

11 rows selected.

4.子查询中的空值问题，子查询不返回任何行，因为员工表没有“Haas”：

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

no rows selected