Mysql sql查询语句，多表联查练习

sql查询语句练习

原表：

employee

department

题目

1. 查询出部门编号为D2019060011的所有员工

mysql> select * from employee where department_NO like "D2019060011";

2. 所有财务总监的姓名、编号和部门编号。

mysql> select name,number,department_NO from employee where job like "财务总监";

3. 找出奖金高于工资的员工。

mysql> select name from employee where bonus > salary;

4. 找出奖金高于工资40%的员工。

mysql> select name from employee where bonus > salary * 0.4;

5. 找出部门编号为D2019090011中所有财务总监，和部门编号为D2019060011中所有财务专员的详细资料。

mysql> select * from employee where department_NO like "D2019090011" and job like "财务总监" or department_NO like "D2019060011" and job like "财务专员";

6. 找出部门编号为D2019090001中所有总经理，部门编号为D2019090011中所有财务总监，还有即不是总经理又不是销售总监但其工资大或等于4000的所有员工详细资料。

mysql> select * from employee where department_NO like "D2019090001" and job like "总经理" or department_NO like "D2019090011" and job like "财务总监" or job not in ("总经理","销售总监") and salary >= 4000;

7. 有奖金的工种。

mysql> select job from employee where bonus > 0;

8. 无奖金或奖金低于1000的员工。

mysql> select name from employee where bonus <1000 or bonus like "NULL";

9. 查询名字由两个字组成的员工。

mysql> select name from employee where name like "__";

10. 查询2020年入职的员工。

mysql> select name from employee where hire_date like "2020%";

11.查询所有员工详细信息，用编号升序排序。

mysql> select * from employee order by number asc;

12. 查询所有员工详细信息，用工资降序排序，如果工资相同使用入职日期升序排序。

mysql> select * from employee order by salary desc, hire_date asc;

13. 查询每个部门的平均工资。

mysql> select department_NO,avg(salary) from employee group by job;

14. 求出每个部门的雇员数量。

mysql> select job,count(job) from employee group by job order by count(job) desc;

15. 查询每种工作的最高工资、最低工资、人数。

mysql> select job,max(salary),min(salary),count(job) from employee group by job order by max(salary) desc;

16. 列出最低薪金大于4000的各种工作及从事此工作的员工人数。

mysql> select job,min(salary),count(job) from employee where salary > 4000 group by job;

17. 统计各部门工资总和，显示部门编号和该部门雇员的月工资的总和，并且要满足该部门雇员的月工资合计 大于6000，输出结果按月工资的合计升序排列。

mysql> select department_NO,sum(salary) sum from employee group by department_NO having sum > 6000 order by sum asc;

多表练习

原表：

employee

department

题目：

1. 列出所有员工的姓名及其直接上级的姓名。

mysql> select e1.name 员工性名,e2.name 上级性名 from employee e1 left join employee e2 on e1.leader_NO = e2.number;

2. 列出受雇日期早于直接上级的所有员工的编号、姓名、部门名称。

mysql> select e1.number 员工编号 ,e1.name 员工姓名 ,d.name 部门名称 from employee e1 left join employee e2 on e1.leader_NO = e2.number join department d on e1.department_NO = d.number where e1.hire_date < e2.hire_date;

3. 列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门

mysql> select * from employee e right join department d on e.department_NO = d.number;

4. 列出在财务部工作的员工的姓名，假定不知道财务部的部门编号。

mysql> select name from employee where department_NO in (select number from department where name = "财务部");

5. 列出薪金高于公司平均薪金的所有员工信息，所在部门名称，上级领导。

mysql> select e1.*,d.name,(select e2.name from employee e2 where e2.number = e1.leader_NO) from employee e1 left join department d on e1.department_NO = d.number where e1.salary > (select avg(salary) from employee);

6. 列出与陈超从事相同工作的所有员工及部门名称

mysql> select e.name,d.name from employee e left join department d on e.department_NO = d.number where e.job = (select job from employee where name = "陈超");

7. 查出至少有一个员工的部门。显示部门编号、部门名称、部门位置、部门人数。

mysql> select d.number 部门编号,d.name 部门名称,d.location 部门位置,count(e.id) 部门人数 from department d left join employee e on d.number = e.department_NO group by d.number,d.name,d.location having count(e.id) > "0";

8. 列出薪金高于在财务部工作员工平均薪金的员工姓名和薪金、部门名称

mysql> select e.name 员工姓名,e.salary 员工薪资,d.name 部门名称 from employee e left join department d on e.department_NO = d.number where e.salary > (select avg(salary) from employee where department_NO in (select number from department where name = "财务部"));