使用子查询检索数据

Lesson使用EXISTS和NOTEXISTSWHERE(MANAGER_ID,(SELECTmanager_id,(SELECTmanager_id,department_idFROMempl_demoWHEREfirstname=SELECTSELECTemployee_id,manager_id,department_id WHERE(manager_id,department_id)ANDfirst_name<>(SELECTmanager_idFROMempl_demoWHEREfirst_name=(SELECTmanager_idFROMempl_demoWHEREfirst_name=(SELECTdepartment_idFROMempl_demoWHEREfirst_name=ANDdepartmentidANDfirst_name<>employee_id,manageridLesson使用EXISTS和NOTEXISTSDECODECASESELECTGROUPBYSELECTSELECTemployee_id,last_name,WHENdepartment_idTHEN'Canada'ELSE'USA'END)employees--查询所有部⻔号为20的员工信息并且location标为Canada其它记为(SELECTFROMWHERElocation_id= employee_id,last_name,department_id employeeseORDER(SELECTFROMdepartmentsWHEREe.department_id=Lesson使用EXISTS和NOTEXISTSSELECTSELECTcolumn1,column2,... WHEREcolumn1(SELECTcolumn1, expr1SELECTSELECTlast_name,salary, employeesouter_tableWHEREsalary>(SELECT employeesinner_tableWHEREinner_table.department_id=SELECTSELECTe.employee_id,last_name,e.job_id employeeseWHERE2<=(SELECT WHEREemployee_id=Lesson使用EXISTSNOTEXISTS使用EXISTS使用EXISTS操作符SELECTSELECTemployee_id,last_name,job_id,department_id employeesouter (SELECT WHEREmanager_id=SELECTSELECTdepartment_id,department_nameFROMdepartmentsd (SELECTdepartment_id=NOT…相关UPDATEUPDATEtable1 column=(SELECT table2WHEREalias1.columnUPDATEempl6UPDATEempl6department_name(SELECT departmentsWHEREe.department_id= 范式的表EMPL6添加字段 ALTERALTERTABLE相关DELETEDELETEFROMtable1WHEREcolumn(SELECTtable2alias1.column=使用相关DELETEDELETEFROMempl6WHEREemployee_id(SELECTemployee_id=Lesson使用EXISTSNOTEXISTS ASSELECTd.department_name,SUM(e.salary)ASdept_total employeeseJOINdepartmentsd e.department_id=d.department_idGROUPBYd.department_name), ASSELECTSUM(dept_total)/COUNT(*)ASdept_avg SELECT WHEREdept_total>(SELECTdept_avgFROMavg_cost)ORDERBY递归WITHEnablesformulationofrecursiveCreatesquerywithaname,calledtheRecursiveWITHelementnameContainstwotypesofqueryblocksmember:anchorandaIs递归WITHFLIGHTS WITH(Source,Destin,TotalFlightTime)WITH(SELECTSource,Destin,Flight_timeFROMFlightsUNIONSELECTing.Source,outgoing.Destin,FROMReachable_Froming,Flights WHEREing.Destin=)SELECTSource,Destin,TotalFlightTimeFROMReachable_From;3Inthislesson,youshouldhavelearnedAmultiple-columnsubqueryreturnsmorethanoneMultiple-columncomparisonscanbepairwiseorAmultiple-columnsubquerycanalsobeusedintheclauseofaSELECTCorrelatedsubqueriesareusefulwheneverasubquerymustreturnadifferentresultforeachcandidaterowTheEXISTSoperatorisaBooleanoperatorthatteststhepresenceofavalueCorrelatedsubqueriescanbeusedwithSELECT,UPDATE,andDELETEstatementsYoucanusetheWITHclausetousethesamequeryblockinaSELECTstatementwhen