华珠云计算第二次作业

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

select * from employee where department_NO = "D2019060011";

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

select name,number,department_NO from employee where job = "财务总监";

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

select * from employee where bonus > salary;

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

select * from employee where bonus > salary * 0.4;

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

料。

   select * from employee
-> where
-> (department_NO = "D2019090011" and job = "财务总监")
-> or
-> (department_NO = "D2019060011" and job = "财务专员");

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

> select * from employee
-> where
-> (department_NO = "D2019090001" and job = "总经理")
-> or
-> (department_NO = "D2019090011" and job = "财务总监")
-> or
-> (job <> "总经理" and job <> "销售总监" and salary >= 4000);

7.有奖金的工种。

select job from employee where bonus is not null;

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

 select * from employee where bonus is null or bonus < 1000;

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

select * from employee where name like "__";

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

select * from employee where hire_date like "2020%";

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

select * from employee order by number asc;

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

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

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

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

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

select department_NO,count(id) from employee group by department_NO;

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

select max(salary),min(salary),count(id) from employee group by job;

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

select job,count(id) from employee group by job having min(salary) >
4000;

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

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

二、多表查询

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

select e1.name "上级姓名",e2.name "员工姓名" from employee e1,employee
e2 where e1.number = e2.leader_NO;

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

mysql> select e1.hire_date "上级入职日期",e2.hire_date "员工入职日期" from
employee e1
-> join employee e2
-> on e1.number = e2.leader_NO;

mysql> select e1.hire_date "上级入职日期",e2.hire_date "员工入职日期" from
employee e1
-> join employee e2
-> on e1.number = e2.leader_NO
-> where e1.hire_date > e2.hire_date;

mysql> select e2.number,e2.name,d.name from employee e1
-> join employee e2
-> on e1.number = e2.leader_NO
-> join department d
-> on e2.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;

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

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

select e.name from employee e
join department d
on e.department_NO = d.number
where d.name = "财务部";
mysql> select e.name from employee e
-> join department d
-> on e.department_NO = d.number
-> where d.name = "财务部";

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

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

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

select e.*,d.name
from employee e,department d
where e.department_NO = d.number
and e.job = (select job from employee
where name = "陈超");

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

select res.department_NO,d.name,d.location,res.count
from (select department_NO,count(*) count from employee group by
department_NO) res,
department d
where res.department_NO = d.number;

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

select number from department where name = "财务部";

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

select e.name,e.salary,d.name from employee e
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 = "财务部"));