test-lesson04-answer

测试1.显示所有员工的姓名，部门号和部门名称。SELECTe.first_name,e.department_id,d.department_nameFROMemployeese,departmentsdWHEREe.department_id=d.department_id;2.查询90号部门员工的job_id和90号部门的location_idSELECTDISTINCTemployees.job_id,departments.location_idFROMemployees,departmentsWHEREemployees.department_id=departments.department_idANDemployees.department_id=90;3.选择所有有奖金的员工的last_name,department_name,location_id,citySELECTe.last_name,d.department_name,d.location_id,l.cityFROMemployeese,departmentsd,locationslWHEREe.department_id=d.department_idANDd.location_id=l.location_idANDe.commission_pctISNOTNULL4.选择在Toronto工作的员工的last_name,job_id,department_id,department_nameSELECTe.last_name,e.job_id,e.department_id,d.department_nameFROMemployeeseJOINdepartmentsdON(e.department_id=d.department_id)JOINlocationslON(d.location_id=l.location_id)WHERELOWER(l.city)='toronto'Selecte.last_name,e.job_id,d.department_id,d.department_nameFromemployeese,departmentsd,locationslWheree.department_id=d.department_idAndd.location_id=l.location_idAndLOWER(l.city)='toronto'5.选择所有员工的姓名，员工号，以及他的管理者的姓名和员工号，结果类似于下面的格式employeesEmp#managerMgr#kochhar101king100SELECTw.last_nameEmployee,w.employee_idEMP#,m.last_nameManager,m.employee_idMgr#FROMemployeeswjoinemployeesmON(w.manager_id=m.employee_id)6.查询各部门员工姓名和他们的同事姓名，结果类似于下面的格式Department_idLast_namecolleague20fayhartsteinSELECTe.department_iddepartment,e.last_nameemployee,c.last_namecolleagueFROMemployeeseJOINemployeescON(e.department_id=c.department_id)WHEREe.employee_idc.employee_idORDERBYe.department_id,e.last_name,c.last_name