目? 錄

一、題目

二、答案（不唯一）

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;