SQL面试问题汇总_高级子查询

1、高级子查询高级子查询 书写多列子查询 在 FROM 子句中使用子查询 在SQL中使用单列子查询 书写相关子查询 使用 EXISTS 和 NOT EXISTS 操作符 使用子查询更新和删除数据 使用 WITH 子句子查询子查询子查询是嵌套在 SQL 语句中的另一个SELECT 语句SELECT . FROM .WHERE .(SELECT . FROM . WHERE .)主查询主查询子查询子查询子查询子查询 子查询 (内查询) 在主查询执行之前执行 主查询(外查询)使用子查询的结果SELECT select_listFROMtableWHEREexpr operator (SELECT sel

2、ect_list FROM table);SELECT last_nameFROM employeesWHERE salary (SELECT salary FROM employees WHERE employee_id = 149) ;子查询应用举例子查询应用举例10500问题：查询工资大于问题：查询工资大于149号员工工资的员工的信息号员工工资的员工的信息一、多一、多列子查询列子查询Main queryWHERE (MANAGER_ID, DEPARTMENT_ID) INSubquery100 90102 60124 50主查询与子查询返回的主查询与子查询返回的多个列多个列进行比较进行

3、比较列比较列比较多列子查询中的比较分为两种: 成对比较 不成对比较不成对比较举例不成对比较举例SELECT employee_id, manager_id, department_idFROM employeesWHERE manager_id IN (SELECT manager_id FROM employees WHERE employee_id IN (174,141)AND department_id IN (SELECT department_id FROM employees WHERE employee_id IN (174,141)ANDemployee_id NOT IN(

4、174,141);问题：查询与问题：查询与141号或号或174号员工的号员工的manager_id和和department_id相同的其他员工的相同的其他员工的employee_id, manager_id, department_id 成对比较举例成对比较举例SELECT employee_id, manager_id, department_idFROMemployeesWHERE (manager_id, department_id) IN (SELECT manager_id, department_id FROM employees WHERE employee_id IN (141

5、,174)ANDemployee_id NOT IN (141,174);问题：查询与问题：查询与141号或号或174号员工的号员工的manager_id和和department_id相同的其他员工的相同的其他员工的employee_id, manager_id, department_id select last_name,department_id,salary,(select avg(salary)from employees e3 where e1.department_id = e3.department_id group by department_id) avg_salaryfr

6、om employees e1where salary (select avg(salary) from employees e2 where e1.department_id = e2.department_id -group by department_id )二、在二、在 FROM 子句中使用子查询子句中使用子查询问题：返回比本部门平均工资高的员工的问题：返回比本部门平均工资高的员工的last_name, department_id, salary及平均工资及平均工资方方法法一一SELECT a.last_name, a.salary, a.department_id, b.salavg

7、FROM employees a, (SELECT department_id, AVG(salary) salavg FROM employees GROUP BY department_id) bWHERE a.department_id = b.department_idAND a.salary b.salavg;二、在二、在 FROM 子句中使用子查询子句中使用子查询问题：返回比本部门平均工资高的员工的问题：返回比本部门平均工资高的员工的last_name, department_id, salary及平均工资及平均工资方方法法二二三、单列三、单列子查询表达式子查询表达式 单列子查询表

8、达式是在一行中只返回一列的子查询 Oracle8i 只在下列情况下可以使用, 例如: SELECT 语句 (FROM 和 WHERE 子句) INSERT 语句中的VALUES列表中 Oracle9i中单列子查询表达式可在下列情况下使用: DECODE 和 CASE SELECT 中除 GROUP BY 子句以外的所有子句中单列子查询应用举例单列子查询应用举例在在 CASE 表达式中使用单列子查询表达式中使用单列子查询SELECT employee_id, last_name, (CASE department_id WHEN THEN Canada ELSE USA END) locatio

9、nFROM employees; (SELECT department_id FROM departmentsWHERE location_id = 1800)20问题：显式员工的问题：显式员工的employee_id,last_name和和location。其中。其中，若员若员工工department_id与与location_id为为1800的的department_id相同，则相同，则location为为Canada,其余则为其余则为USA。单列子查询应用举例单列子查询应用举例SELECT employee_id, last_nameFROM employees eORDER BY (S

10、ELECT department_name FROM departments d WHERE e.department_id = d.department_id);在在 ORDER BY 子句中子句中使用单列子查询使用单列子查询问题：查询员工的问题：查询员工的employee_id,last_name,要求按照员工的要求按照员工的department_name排序排序四、相关四、相关子查询子查询相关子查询按照一行接一行的顺序执行，主查询的每一行都执行一次子查询GET从主查询中获取候选列从主查询中获取候选列EXECUTE子查询使用主查询的数据子查询使用主查询的数据USE如果满足内查询的条件则返回

11、该行如果满足内查询的条件则返回该行相关子查询相关子查询 SELECT column1, column2, . FROM table1 WHERE column1 operator (SELECT colum1, column2 FROM table2 WHERE expr1 = .expr2);outerouter子查询中使用主查询中的子查询中使用主查询中的列列SELECT last_name, salary, department_idFROM employees outerWHERE salary 相关子查询举例相关子查询举例(SELECT AVG(salary) FROM employees WHERE department_id = outer.department_id) ;问题：查询员工中工资大于本部门平均工资的员工的问题：查询员工中工资大于本部门平均工资的员工的last_name,salary和其和其department_id相关子查询举例相关子查询举例SELECT e.employee_id, last_name,e.job_idFROM employees e WHERE 2 (SELECT dept_avg FROM avg_cost)