疫情未结束，继续带软件测试班级学员复习SQl数据库知识

1：查询员工名和对应的部门名（多表联查）
SELECT last_name,department_name
FROM employees,departments
WHERE employees.department_id=departments.department_id;

2：查询有奖金的员工名和工种名（多表联查）
SELECT last_name,job_title
FROM employees e,jobs j
WHERE e.`job_id`=j.`job_id`
AND  commission_pct IS NOT NULL;

3：查询每个部门的部门名和员工个数（多表联查）
SELECT COUNT(*) 个数,department_name
FROM departments d,employees e
WHERE d.department_id=e.`department_id`
GROUP BY e.`department_id`;

4：查询员工个数>5的部门名称（多表联查）
SELECT COUNT(*) 个数,department_name
FROM departments d,employees e
WHERE d.department_id=e.`department_id`
GROUP BY e.`department_id`
HAVING 个数>5;

5：查询 员工名、部门名和城市名（三表联查）
SELECT last_name,department_name,city
FROM departments d,employees e,locations l
WHERE e.`department_id`=d.`department_id`
AND l.`location_id`=d.`location_id`;

6：查询员工表中，部门编号在80-100之间的last_name,employee_id信息，并且按工资降序排列
select last_name,employee_id
from employees
where department_id BETWEEN 80  and 100
ORDER BY salary desc;

7：查询没有奖金，且工资小于18000的salary,last_name信息
select salary,last_name
from employees
where commission_pct is NULL
and salary<18000;

8：查询员工表中，job_id不为“IT”或者工资为12000的员工信息
select *
from employees
where not (job_id='IT')
or salary=12000;

9：查询各个工种(job_id)的员工工资的最大值，最小值，平均值，总和，并且按job_id升序排列
select max(salary),min(salary),avg(salary),sum(salary),job_id
from employees
GROUP BY job_id
ORDER BY job_id asc;

10：查询各个管理者手下员工的最低工资，其中最低工资不能低于6000，没有管理者的员工不在计算内，
select min(salary),manager_id
from employees
where manager_id is not NULL
group by  manager_id
HAVING min(salary)>=6000

11：查询每个部门的有奖金的员工的最高工资
select department_id,max(salary)
from employees
where commission_pct is not NULL
group by department_id;

12：查询哪个部门有奖金员工最高工资 >10000的部门编号和最高工资
select department_id,max(salary)
from employees
where commission_pct is not NULL
group by department_id
having max(salary)>10000;

13：查询各个工种(job_id)的员工工资的最大值，最小值，平均值，总和，并且按job_id升序排列
select max(salary),min(salary),avg(salary),sum(salary),job_id
from employees
GROUP BY job_id
ORDER BY job_id asc;

14：写出查询员工对应的部门名称（用3种方法）
第1种方法：
select department_name
from departments
where department_id in

(select department_id
from employees)

第2种方法：
select DISTINCT(d.department_name)
from departments d,employees e
where d.department_id=e.department_id;

第3种方法：
select DISTINCT(d.department_name)
from departments d
inner join employees e
on d.department_id=e.department_id