oracle10g ocp认证中文与真题精解les app join

SELECT使用self-join …… 乘积形成条件 EMPLOYEES(20 DEPARTMENTS(8…Cartesianproduct:20x8=160… Outerjoin(外连接

table1.column,table2.columntable1,table2table1.column1= SQL代码精短, Primary…Foreign SELECTe.employee_id,e.last_name,d.department_id,d.location_id employeese,departmentsdWHEREe.departmentid=d.department… SELECTSELECTd.department_id,d.department_name,d.location_id,l.city departmentsd,locationsWHEREd.location_id=

d.department_id,d.department_name,l.citydepartmentsd,locationsld.location_id=ANDd.departmentidIN(20,

…Tojoinntablestogether,youneedaminimumofn–1joinconditions.Forexample,tojointables,minimumoftwojoinsis JOB_GRADEStabledefines andHIGHEST_SALrangeofvaluesforeachGRADE_LEVEL.Hence,theGRADE_LEVELcolumncanbeusedassigngradestoeach SELECTSELECTe.last_name,e.salary, employeese,job_gradesjWHEREe.salaryBETWEENj.lowest_salAND… …Therearenoemployees…department 外连接SELECTSELECTtable1.column, table1,WHEREtable1.column(+)=SELECTSELECTtable1.column, table1,WHEREtable1.column= SELECTSELECTe.last_name,e.department_id, employeese,departmentsde.department_id(+)=d.department_id… 外连接SELECTSELECTe.last_name,e.department_id,d.department_name employeese,departmentsdWHEREe.department_id=d.department_id(+)… MANAGER_IDintheWORKERtableisequalEMPLOYEE_IDintheMANAGER SELECTworker.last_name||'SELECTworker.last_name||'works