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

高级子查询书写多列子查询在FROM子句中使用子查询在SQL中使用单列子查询书写相关子查询使用EXISTS和NOTEXISTS操作符使用子查询更新和删除数据使用WITH子句子查询子查询是嵌套在SQL语句中的另一个SELECT语句SELECT...FROM...WHERE...(SELECT...FROM...WHERE...)主查询子查询子查询子查询(内查询)在主查询执行之前执行主查询(外查询)使用子查询的结果SELECT select_listFROM tableWHERE exproperator(SELECTselect_list FROM table);SELECTlast_nameFROMemployeesWHEREsalary(SELECTsalaryFROMemployeesWHEREemployee_id=149);子查询应用举例问题：查询工资大于149号员工工资的员工的信息一、多列子查询MainqueryWHERE(MANAGER_ID,DEPARTMENT_ID)INSubquery100901026012450主查询与子查询返回的多个列进行比较列比较多列子查询中的比较分为两种:成对比较不成对比较不成对比较举例SELECTemployee_id,manager_id,department_idFROMemployeesWHEREmanager_idIN(SELECTmanager_idFROMemployeesWHEREemployee_idIN(174,141))ANDdepartment_idIN(SELECTdepartment_idFROMemployeesWHEREemployee_idIN(174,141))AND employee_idNOTIN(174,141);问题：查询与141号或174号员工的manager_id和department_id相同的其他员工的employee_id,manager_id,department_id成对比较举例SELECT employee_id,manager_id,department_idFROM employeesWHERE(manager_id,department_id)IN(SELECTmanager_id,department_idFROMemployeesWHEREemployee_idIN(141,174))AND employee_idNOTIN(141,174);问题：查询与141号或174号员工的manager_id和department_id相同的其他员工的employee_id,manager_id,department_idselectlast_name,department_id,salary,(selectavg(salary)fromemployeese3wheree1.department_id=e3.department_idgroupbydepartment_id)avg_salaryfromemployeese1wheresalary(selectavg(salary)fromemployeese2wheree1.department_id=e2.department_id--groupbydepartment_id)二、在FROM子句中使用子查询问题：返回比本部门平均工资高的员工的last_name,department_id,salary及平均工资方法一SELECTa.last_name,a.salary,a.department_id,b.salavgFROMemployeesa,(SELECTdepartment_id,AVG(salary)salavgFROMemployeesGROUPBYdepartment_id)bWHEREa.department_id=b.department_idANDa.salaryb.salavg;二、在FROM子句中使用子查询问题：返回比本部门平均工资高的员工的last_name,department_id,salary及平均工资方法二三、单列子查询表达式单列子查询表达式是在一行中只返回一列的子查询Oracle8i只在下列情况下可以使用,例如:SELECT语句(FROM和WHERE子句)INSERT语句中的VALUES列表中Oracle9i中单列子查询表达式可在下列情况下使用:DECODE和CASESELECT中除GROUPBY子句以外的所有子句中单列子查询应用举例在CASE表达式中使用单列子查询SELECTemployee_id,last_name,(CASEdepartment_idWHENTHENCanadaELSEUSAEND)locationFROM