MySQL基础关键_007_DQL 练习

目  录

一、题目

二、答案（不唯一）

1.查询每个部门薪资最高的员工信息

2.查询每个部门高于平均薪水的员工信息

3. 查询每个部门平均薪资等级

4.查询部门中所有员工薪资等级的平均等级

5.不用分组函数 max 查询最高薪资

6.查询平均薪资最高的部门编号

7.查询平均薪资最高的部门名称

8. 查询平均薪资等级最低的部门名称

9.查询比普通员工中最高薪资更高的领导信息

10. 查询薪资排名前五的员工信息

11.查询薪资排名第六到第十的员工信息

12.查询最后三名入职的员工信息

13.查询每个薪资等级各有多少人

14.查询所有员工及其领导姓名

15.查询入职日期早于其直属领导的员工信息

16. 查询每个部门内有哪些员工

17.查询至少有五个员工的部门

18. 查询薪资比“SMITH”多的员工

19.查询所有职位是“CLERK”的员工姓名、部门名称、部门人数

20.查询最低薪资大于1500的职位的员工人数

21.查询在“SALES”部门工作的员工

22.查询薪资高于平均薪资的员工及其所属部门、直属领导、薪资等级

23.查询与“SCOTT”相同职位的员工及所属部门名称

24.查询薪资与部门“30”薪资相同的其他员工信息

25. 查询薪资高于部门“30”薪资的其他员工及所属部门名称

26. 查询每个部门的员工数量、平均薪资、平均入职时长

27.查询全体员工的姓名、薪资、部门名称

28.查询所有部门详细信息及其人数

29.查询各职位最低薪资及其员工姓名

30.查询各部门职位为“MANAGER”的最低薪资

31.查询全体员工的年收入，升序排列

32.查询薪资高于3000的领导及其下属

33.查询名称中包含“s” 的部门及其总员工薪资、人数

34.查询入职40年以上的员工，并计算为其加薪10%后的薪资

35.综合题

（1） 查询未选过“黎明”老师的所有学生的姓名

（2）列出 2 门以上（含2门）不及格学生姓名及平均成绩

（3）既选过 1 号课程又选过 2 号课所有学生的姓名

---

一、题目

1. 查询每个部门薪资最高的员工信息

2. 查询每个部门高于平均薪水的员工信息

3. 查询每个部门平均薪资等级

4. 查询部门中所有员工薪资等级的平均等级

5. 不用分组函数 max 查询最高薪资

6. 查询平均薪资最高的部门编号

7. 查询平均薪资最高的部门名称

8. 查询平均薪资等级最低的部门名称

9. 查询比普通员工中最高薪资更高的领导信息

10. 查询薪资排名前五的员工信息

11. 查询薪资排名第六到第十的员工信息

12. 查询最后三名入职的员工信息

13. 查询每个薪资等级各有多少人

14. 查询所有员工及其领导姓名

15. 查询入职日期早于其直属领导的员工信息

16. 查询每个部门内有哪些员工

17. 查询至少有五个员工的部门

18. 查询薪资比“SMITH”多的员工

19. 查询所有职位是“CLERK”的员工姓名、部门名称、部门人数

20. 查询最低薪资大于1500的职位的员工人数

21. 查询在“SALES”部门工作的员工

22. 查询薪资高于平均薪资的员工及其所属部门、直属领导、薪资等级

23. 查询与“SCOTT”相同职位的员工及所属部门名称

24. 查询薪资与部门“30”薪资相同的其他员工信息

25. 查询薪资高于部门“30”薪资的其他员工及所属部门名称

26. 查询每个部门的员工数量、平均薪资、平均入职时长

27. 查询全体员工的姓名、薪资、部门名称

28. 查询所有部门详细信息及其人数

29. 查询各职位最低薪资及其员工姓名

30. 查询各部门职位为“MANAGER”的最低薪资

31. 查询全体员工的年收入，升序排列

32. 查询薪资高于3000的领导及其下属

33. 查询名称中包含“s” 的部门及其总员工薪资、人数

34. 查询入职40年以上的员工，并计算为其加薪10%后的薪资

35. 综合题

    1. 查询未选过“黎明”老师的所有学生的姓名

    2. 列出 2 门以上（含2门）不及格学生姓名及平均成绩

    3. 既选过 1 号课程又选过 2 号课所有学生的姓名

---

二、答案（不唯一）

1.查询每个部门薪资最高的员工信息

-- 1
select dept_no, max(salary) max_salary from employees group by dept_no-- sum
select m.*, e.emp_name, e.salary from (select dept_no, max(salary) max_salary from employees group by dept_no) m join employees e where m.dept_no = e.dept_no and m.max_salary = e.salary;

---

2.查询每个部门高于平均薪水的员工信息

-- 1
select dept_no, avg(salary) average_salary from employees group by dept_no;-- sum 
select a.*, e.emp_name, e.salary from (select dept_no, avg(salary) average_salary from employees group by dept_no) a join employees e on e.dept_no = a.dept_no and e.salary > a.average_salary;

---

3. 查询每个部门平均薪资等级

-- 1
select dept_no, avg(salary) as average_salary from employees group by dept_no;-- sum
select a.*, s.grade from (select dept_no, avg(salary) as average_salary from employees group by dept_no) a join salary_grades s on a.average_salary between s.min_salary and s.max_salary;

---

4.查询部门中所有员工薪资等级的平均等级

select e.dept_no, avg(s.grade) from employees e join salary_grades s on e.salary between s.min_salary and s.max_salary group by e.dept_no;

---

5.不用分组函数 max 查询最高薪资

# 方法一
select salary from employees order by salary desc limit 1;# 方法二-- 1
select distinct low.salary from employees low join employees hig on low.salary < hig.salary;-- sum 
select salary from employees where salary not in(select distinct low.salary from employees low join employees hig on low.salary < hig.salary);

---

6.查询平均薪资最高的部门编号

# 方法一-- 1
select dept_no, avg(salary) as average_salary from employees group by dept_no;-- 2
select avg(salary) as average_salary from employees group by dept_no order by average_salary desc limit 1 ;-- sum
select dept_no, round(avg(salary), 4) as average_salary from employees group by dept_no having round(avg(salary), 4) = (select round(avg(salary), 4) as average_salary from employees group by dept_no order by average_salary desc limit 1);# 方法二-- 1
select dept_no, avg(salary) as average_salary from employees group by dept_no;-- 2
select max(average_salary) from (select dept_no, avg(salary) as average_salary from employees group by dept_no) h;-- sum
select dept_no, round(avg(salary), 4) as average_salary from employees group by dept_no having round(avg(salary), 4) = (select max(average_salary) from (select dept_no, round(avg(salary), 4) as average_salary from employees group by dept_no) h);

---

7.查询平均薪资最高的部门名称

select d.dept_name, round(avg(e.salary), 4) as average_salary 
from employees e
join departments d
one.dept_no = d.dept_no
group by d.dept_name
having round(avg(salary), 4) = (
select round(avg(salary), 4) as average_salary 
from employees 
group by dept_no 
order by average_salary desc 
limit 1);

---

8. 查询平均薪资等级最低的部门名称

-- 1.按照部门名称查询各部门平均薪水
select d.dept_name, avg(e.salary) as average_salary from employees e join departments d on e.dept_no = d.dept_no group by dept_name;-- 2.查询对应的薪资等级
select t.*, s.grade from (select d.dept_name, avg(e.salary) as average_salary from employees e join departments d on e.dept_no = d.dept_no group by dept_name) t join salary_grades s on t.average_salary between s.min_salary and s.max_salary;-- 3.查询最低部门平均薪资
select avg(salary) as average_salary from employees group by dept_no order by average_salary asc limit 1;-- 4.查询最低部门平均薪资等级
select grade from salary_grades where (select avg(salary) as average_salary from employees group by dept_no order by average_salary asc limit 1) between min_salary and max_salary;-- 5.sum
select t.*, s.grade 
from (select d.dept_name, avg(e.salary) as average_salary from employees e join departments d on e.dept_no = d.dept_no group by dept_name) t 
join salary_grades s 
on t.average_salary 
between s.min_salary 
and s.max_salary
wheres.grade = (select grade from salary_grades where (select avg(salary) as average_salary from employees group by dept_no order by average_salary asc limit 1) between min_salary and max_salary); 

---

9.查询比普通员工中最高薪资更高的领导信息

-- 1
select max(salary) from employees where emp_no not in(select distinct manager_id from employees where manager_id is not null);-- sum
select emp_name, salary from employees where salary > (select max(salary) from employees where emp_no not in(select distinct manager_id from employees where manager_id is not null));

---

10. 查询薪资排名前五的员工信息

select emp_name, salary from employees order by salary desc limit 5;

---

11.查询薪资排名第六到第十的员工信息

select emp_name, salary from employees order by salary desc limit 5, 5;

---

12.查询最后三名入职的员工信息

select emp_name, hire_date from employees order by hire_date desc limit 3;

---

13.查询每个薪资等级各有多少人

select s.grade, count(*) from employees e join salary_grades s on e.salary between s.min_salary and s.max_salary group by s.grade;

---

14.查询所有员工及其领导姓名

select ee.emp_name as employee, er.emp_name as employer from employees ee left join employees er on ee.manager_id = er.emp_no;

---

15.查询入职日期早于其直属领导的员工信息

select ee.emp_name as employee, ee.hire_date, er.emp_name as employer, er.hire_date from employees ee join employees er on ee.manager_id = er.emp_no where ee.hire_date < er.hire_date;

---

16. 查询每个部门内有哪些员工

select d.dept_name, e.emp_name from departments d left join employees e on d.dept_no = e.dept_no;

---

17.查询至少有五个员工的部门

select d.dept_name, count(*) from departments d join employees e on d.dept_no = e.dept_no group by d.dept_name having count(*) >= 5;

---

18. 查询薪资比“SMITH”多的员工

select emp_name, salary from employees where salary > (select salary from employees where emp_name = 'SMITH');

---

19.查询所有职位是“CLERK”的员工姓名、部门名称、部门人数

-- 1
select e.emp_name, d.dept_name from employees e join departments d on e.dept_no = d.dept_no where job_title = 'CLERK';-- 2
select dept_no, count(*) as total from employees group by dept_no;-- sum
select e.emp_name, d.dept_name, t.total from employees e join departments d on e.dept_no = d.dept_no join (select dept_no, count(*) as total from employees group by dept_no) t on d.dept_no = t.dept_no where job_title = 'CLERK';

---

20.查询最低薪资大于1500的职位的员工人数

select job_title, count(*) from employees group by job_title having min(salary) > 1500;

---

21.查询在“SALES”部门工作的员工

-- 1
select dept_no from departments where dept_name = 'SALES';-- 2
select emp_name from employees where dept_no = (select dept_no from departments where dept_name = 'SALES');

---

22.查询薪资高于平均薪资的员工及其所属部门、直属领导、薪资等级

-- 1.员工所属部门
select e.emp_name, d.dept_name from employees e join departments d on e.dept_no = d.dept_no;-- 2.员工直属领导
select ee.emp_name, er.emp_name from employees ee left join employees er on ee.manager_id = er.emp_no;-- 3.员工薪资等级
select e.emp_name, s.grade from employees e join salary_grades s on e.salary between s.min_salary and s.max_salary;-- 4.汇总上述
select d.dept_name, ee.emp_name as employee, er.emp_name as employer, s.grade as employee_salary_grade from employees ee join departments d on ee.dept_no = d.dept_no left join employees er on ee.manager_id = er.emp_no join salary_grades s on ee.salary between s.min_salary and s.max_salary;-- 5.平均薪资
select avg(salary) from employees;-- 5.高于平均薪资的
select d.dept_name, ee.emp_name as employee, er.emp_name as employer, s.grade as employee_salary_grade 
from employees ee 
join departments d 
on ee.dept_no = d.dept_no 
left join employees er 
on ee.manager_id = er.emp_no 
join salary_grades s 
on ee.salary 
between s.min_salary and s.max_salary 
where ee.salary > (select avg(salary) from employees);

---

23.查询与“SCOTT”相同职位的员工及所属部门名称

-- 1.查询“SCOTT”的职位
select job_title from employees where emp_name = 'SCOTT';-- 2.查询同职位员工信息
select emp_name, dept_no from employees where job_title = (select job_title from employees where emp_name = 'SCOTT');-- 3.查询对应部门名称并排除“SCOTT”
select t.emp_name, d.dept_name from (select emp_name, dept_no from employees where job_title = (select job_title from employees where emp_name = 'SCOTT')) t join departments d on t.dept_no = d.dept_no where t.emp_name != 'SCOTT';

---

24.查询薪资与部门“30”薪资相同的其他员工信息

-- 1.查询部门“30”薪资有哪些
select distinct salary from employees where dept_no = '30';-- 3.查询薪资相同的其他员工
select emp_name, salary from employees where salary in(select distinct salary from employees where dept_no = '30') and dept_no != '30';

---

25. 查询薪资高于部门“30”薪资的其他员工及所属部门名称

-- 1
select max(salary) from employees where dept_no = '30';-- 2
select d.dept_name, e.emp_name, e.salary from employees e join departments d on e.dept_no = d.dept_no where e.salary > (select max(salary) from employees where dept_no = '30') and d.dept_no != '30';

---

26. 查询每个部门的员工数量、平均薪资、平均入职时长

select d.dept_name, count(e.emp_name) as total_person, ifnull(avg(salary), 0) as average_salary, ifnull(avg(datediff(now(), e.hire_date)), 0) as average_date 
from departments d 
left join employees e 
on d.dept_no = e.dept_no 
group by d.dept_name;

---

27.查询全体员工的姓名、薪资、部门名称

select e.emp_name, e.salary, d.dept_name from employees e join departments d on e.dept_no = d.dept_no;

---

28.查询所有部门详细信息及其人数

select d.*, count(e.emp_name) from employees e right join departments d on e.dept_no = d.dept_no group by d.dept_no;

---

29.查询各职位最低薪资及其员工姓名

-- 1
select job_title, min(salary) as min_salary from employees group by job_title;-- 2
select e.emp_name, t.* from employees e join (select job_title, min(salary) as min_salary from employees group by job_title) t on t.job_title = e.job_title and t.min_salary = e.salary;

---

30.查询各部门职位为“MANAGER”的最低薪资

select dept_no, min(salary) from employees where job_title = 'MANAGER' group by dept_no;

---

31.查询全体员工的年收入，升序排列

select emp_name, (salary + ifnull(commission, 0)) * 12 as year_income from employees order by year_income asc;

---

32.查询薪资高于3000的领导及其下属

select ee.emp_name as employee, ee.salary, er.emp_name as employer, er.salary from employees ee join employees er on ee.manager_id = er.emp_no where er.salary > 3000;

---

33.查询名称中包含“s” 的部门及其总员工薪资、人数

select d.dept_name, ifnull(sum(e.salary), 0) as sum_salary, count(e.emp_name) from employees e right join departments d on d.dept_no = e.dept_no where d.dept_name like '%S%' group by d.dept_name;

---

34.查询入职40年以上的员工，并计算为其加薪10%后的薪资

select emp_name, salary, datediff(now(), hire_date) / 365 as work_date, if(datediff(now(), hire_date) / 365 > 40, salary * 1.1, salary) as new_salary from employees;

---

35.综合题

        有 3 张表：students（学生表），courses（课程表），student_course（学生选课表）。

        students（student_id，student_name）代表（学号，姓名），

        courses（course_id，course_name，teacher）代表（课号，课名，教师），        

        student_course（student_id，course_id，grade）代表（学号，课号，成绩）。

        sql 脚本如下。

-- 创建学生表
CREATE TABLE students (student_id VARCHAR(200) PRIMARY KEY,student_name VARCHAR(200) NOT NULL
);-- 创建课程表
CREATE TABLE courses (course_id VARCHAR(200) PRIMARY KEY,course_name VARCHAR(200) NOT NULL,teacher VARCHAR(200) NOT NULL
);-- 创建选课成绩表（含外键约束）
CREATE TABLE student_course (student_id VARCHAR(200) NOT NULL,course_id VARCHAR(200) NOT NULL,grade VARCHAR(200) NOT NULL,PRIMARY KEY (student_id, course_id),FOREIGN KEY (student_id) REFERENCES students(student_id),FOREIGN KEY (course_id) REFERENCES courses(course_id)
);-- 插入课程数据
INSERT INTO courses (course_id, course_name, teacher) VALUES
('1', '语文', '张叶'),
('2', '政治', '王强'),
('3', '英语', '宋刚'),
('4', '数学', '赵红'),
('5', '物理', '黎明');-- 插入学生数据
INSERT INTO students (student_id, student_name) VALUES
('1', '花花'),
('2', '苗苗'),
('3', '黄黄'),
('4', '球球');-- 插入选课成绩数据
INSERT INTO student_course (student_id, course_id, grade) VALUES
('1', '1', '40'),
('1', '2', '30'),
('1', '3', '20'),
('1', '4', '80'),
('1', '5', '60'),
('2', '1', '60'),
('2', '2', '60'),
('2', '3', '60'),
('2', '4', '60'),
('2', '5', '40'),
('3', '1', '60'),
('3', '3', '80');COMMIT;

（1） 查询未选过“黎明”老师的所有学生的姓名

-- 1.查询 “黎明” 老师所授课程id
select course_id from courses where teacher = '黎明';-- 2.选“黎明”老师的学生id
select student_id from student_course where course_id = (select course_id from courses where teacher = '黎明');-- 3.未选“黎明”老师的学生姓名
select student_name from students where student_id not in(select student_id from student_course where course_id = (select course_id from courses where teacher = '黎明'));

---

（2）列出 2 门以上（含2门）不及格学生姓名及平均成绩

-- 1.查询两门及以上成绩不及格的学生id
select student_id from student_course where grade < 60 group by student_id having count(*) >= 2;-- 2.查询该学生的id, 姓名
select s.student_id, s.student_name from students s join student_course sc on s.student_id = sc.student_id where sc.grade < 60 group by s.student_id, s.student_name having count(*) >= 2;-- 3.计算平均成绩
select student_id, avg(grade) as average_grade from student_course group by student_id;-- 4.查询不及格学生的平均成绩
select a.student_name, b.average_grade 
from (select s.student_id, s.student_name from students s join student_course sc on s.student_id = sc.student_id where sc.grade < 60 group by s.student_id, s.student_name having count(*) >= 2) a 
join (select student_id, avg(grade) as average_grade from student_course group by student_id) b 
on a.student_id = b.student_id;

---

（3）既选过 1 号课程又选过 2 号课所有学生的姓名

-- 1.选择“1”或“2”两门课的学生id
select student_id from student_course where course_id ='1';
select student_id from student_course where course_id ='2';-- 2.选择“1”和“2”两门课的学生id
select student_id from student_course where course_id = '1' and student_id in(select student_id from student_course where course_id ='2');-- 3.查询对应的学生姓名
select s.student_name from students s join (select student_id from student_course where course_id = '1' and student_id in(select student_id from student_course where course_id ='2')) t on s.student_id = t.student_id;