MySQL/第六章、练习题一.sql

#第六章、练习题（一）

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');