#第六章、练习题(一) SELECT * FROM employees; SELECT * FROM departments; SELECT * FROM locations; SELECT * FROM jobs; #1、显示所有员工的姓名、部门号和部门名称 SELECT e.last_name,e.department_id,d.department_name FROM employees e LEFT JOIN departments d ON e.department_id = d.department_id; #2、查询90号部门员工的job_id和90号部门的location_id SELECT e.last_name,e.job_id,d.location_id FROM employees e JOIN departments d ON e.department_id = 90 AND e.department_id = d.department_id; #老师做的 SELECT e.last_name,e.job_id,d.location_id FROM employees e JOIN departments d ON e.department_id = d.department_id WHERE d.department_id = 90; #3、选择所有有奖金的员工的last_name,department_name,location_id,city SELECT e.last_name,e.commission_pct,d.department_name,d.location_id,l.city FROM employees e LEFT JOIN departments d ON e.department_id = d.department_id LEFT JOIN locations l ON d.location_id = l.location_id WHERE e.commission_pct IS NOT null; SELECT * FROM employees WHERE commission_pct IS NOT NULL; #老师做的 SELECT e.last_name,d.department_name,d.location_id,l.city FROM employees e LEFT OUTER JOIN departments d ON e.`department_id` = d.`department_id` LEFT OUTER JOIN locations l ON d.`location_id` = l.`location_id` WHERE commission_pct IS NOT NULL; #4、选择city在Toronto工作的员工的last_name,job_id,department_id,department_name SELECT e.last_name,e.job_id,e.department_id,d.department_name FROM employees e JOIN departments d ON e.department_id = d.department_id JOIN locations l ON d.location_id = l.location_id WHERE l.city = 'Toronto'; SELECT * FROM locations WHERE city = 'Toronto'; #SQL92语法:本题不涉及内外连接,所以可以用SQL92语法实现 SELECT e.last_name,e.job_id,e.department_id,d.department_name FROM employees e,departments d,locations l WHERE e.department_id = d.department_id AND d.location_id = l.location_id AND l.city = 'Toronto'; #5、查询员工所在的部门名称、部门地址、姓名、工作、工资,其中员工所在部门的部门名称为“Executive” SELECT d.department_name,l.street_address,e.last_name,j.job_title,e.salary FROM employees e LEFT JOIN departments d ON e.department_id = d.department_id JOIN locations l ON d.location_id = l.location_id JOIN jobs j ON e.job_id = j.job_id WHERE d.department_name = 'Executive'; #6、选择指定员工的姓名,员工号,以及他的管理者的姓名和员工号,结果类似于下面的格式 #employees Emp# manager Mgr# #kochhar 101 king 100 SELECT e.last_name "employees",e.employee_id "Emp#",m.last_name "manager",m.employee_id "Mgr#" FROM employees e LEFT JOIN employees m ON e.manager_id = m.employee_id; #7、查询哪些部门没有员工 SELECT d.department_name,d.department_id FROM employees e RIGHT JOIN departments d ON e.department_id = d.department_id WHERE d.manager_id IS NULL; #老师做的 SELECT d.department_name,d.department_id FROM departments d LEFT JOIN employees e ON d.department_id = e.department_id WHERE e.department_id IS NULL; #本题也可以使用子查询 #8、查询那个城市没有部门 SELECT l.location_id,l.city,d.department_name FROM departments d RIGHT JOIN locations l ON d.location_id = l.location_id WHERE d.department_name IS NULL; #老师做的 SELECT l.location_id,l.city FROM locations l LEFT JOIN departments d ON l.location_id = d.location_id WHERE d.location_id IS NULL; #9、查询部门名为Sales或IT的员工信息 SELECT e.employee_id,e.last_name,e.department_id,d.department_name FROM employees e JOIN departments d ON e.department_id = d.department_id WHERE d.department_name IN('Sales','IT');