MYSQL 课后练习
#select 查询列
#from 表1 别名 【连接类型】
#join 表2 别名
#on 连接条件
#【where 筛选条件】
#【group by 分组】
#【having 分组后筛选】
1.查询部门个数>3的城市名和部门个数，（添加分组+筛选）
SELECT city,COUNT(*)
FROM departments d
INNER JOIN locations l
ON d.location_id = l.location_id
GROUP BY city
HAVING COUNT(*)>3;
2.案例4.查询哪个部门的员工个数>3的部门名和员工个数，并按个数降序（添加排序）
SELECT COUNT(*),department_name
FROM employees e
INNER JOIN departments d
ON e.department_id=d.department_id
GROUP BY department_name
HAVING COUNT(*)>3
ORDER BY COUNT(*) DESC;
3..查询员工名、部门名、工种名，并按部门名降序（添加三表连接）
SELECT last_name,department_name,job_title
FROM employees e
INNER JOIN departments d
ON e.department_id=d.department_id
INNER JOIN jobs j
ON e.job_id=j.job_id
ORDER BY department_name DESC
4.查询员工的名字、上级的名字
SELECT e.last_name,m.last_name
FROM employees e
INNER JOIN employees m
ON e.manager_id=m.employee_id
5.查询每个城市的部门个数
SELECT COUNT(*) ,city
FROM departments d,locations l
WHERE d.location_id = l.location_id
GROUP BY city;
6．查询每个工种的工种名和员工的个数，并且按员工个数降序
SELECT job_title,COUNT(*)
FROM jobs j,employees e
WHERE e.job_id = j.job_id
GROUP BY job_title
ORDER BY COUNT(*) DESC;
7.查询城市名中第二个字符为o的部门名和城市名
SELECT d.department_name,l.city
FROM departments d,locations l
WHERE d.location_id = l.location_id
AND city LIKE '_o%';
8.选择所有有奖金的员工的 e.last_name,d.department_name,commission_pct
SELECT e.last_name,d.department_name,commission_pct
FROM employees e,departments d
WHERE e.department_id = d.department_id
AND commission_pct IS NOT NULL;
9.查询90号部门员工的job_id和90号部门的location_id
SELECT e.job_id job_id,d.location_id location_id
FROM employees e
JOIN departments d
ON e.department_id = d.department_id
WHERE e.department_id = 90;
10.查询每个工种、每个部门的部门名、工种名和最低工资
SELECT
e.department_id,department_name,job_id,MIN(salary)
FROM employees e
INNER JOIN departments d
ON e.department_id=d.department_id
GROUP BY department_id
