✏️ 多表查询课堂练习 2
第 5 章 · 数据查询 · 共 13 题 · emp / dept / salgrade 表
🛠 建表与测试数据（练习前先执行）
本练习在练习 1 创建的 emp、dept 表基础上操作，请先执行练习 1 的建表语句。
sql
-- ---------------------------------------> 多表查询案例 <----------------------------------
create table salgrade(
grade int,
losal int,
hisal int
) comment '薪资等级表';
insert into salgrade values (1,0,3000);
insert into salgrade values (2,3001,5000);
insert into salgrade values (3,5001,8000);
insert into salgrade values (4,8001,10000);
insert into salgrade values (5,10001,15000);
insert into salgrade values (6,15001,20000);
insert into salgrade values (7,20001,25000);
insert into salgrade values (8,25001,30000);1. 查询员工的姓名、年龄、职位、部门信息 （隐式内连接）
查看参考答案（可复制）
sql
-- 表: emp , dept
-- 连接条件: emp.dept_id = dept.id
select e.name , e.age , e.job , d.name from emp e , dept d where e.dept_id = d.id;2. 查询年龄小于30岁的员工的姓名、年龄、职位、部门信息（显式内连接）
查看参考答案（可复制）
sql
-- 表: emp , dept
-- 连接条件: emp.dept_id = dept.id
select e.name , e.age , e.job , d.name from emp e inner join dept d on e.dept_id = d.id where e.age < 30;3. 查询拥有员工的部门ID、部门名称
查看参考答案（可复制）
sql
-- 表: emp , dept
-- 连接条件: emp.dept_id = dept.id
select distinct d.id , d.name from emp e , dept d where e.dept_id = d.id;4. 查询所有年龄大于40岁的员工, 及其归属的部门名称; 如果员工没有分配部门, 也需要展示出来
查看参考答案（可复制）
sql
-- 表: emp , dept
-- 连接条件: emp.dept_id = dept.id
-- 外连接
select e.*, d.name from emp e left join dept d on e.dept_id = d.id where e.age > 40 ;5. 查询所有员工的工资等级
查看参考答案（可复制）
sql
-- 表: emp , salgrade
-- 连接条件 : emp.salary >= salgrade.losal and emp.salary <= salgrade.hisal
select e.* , s.grade , s.losal, s.hisal from emp e , salgrade s where e.salary >= s.losal and e.salary <= s.hisal;
select e.* , s.grade , s.losal, s.hisal from emp e , salgrade s where e.salary between s.losal and s.hisal;6. 查询 "研发部" 所有员工的信息及 工资等级
查看参考答案（可复制）
sql
-- 表: emp , salgrade , dept
-- 连接条件 : emp.salary between salgrade.losal and salgrade.hisal , emp.dept_id = dept.id
-- 查询条件 : dept.name = '研发部'
select e.* , s.grade from emp e , dept d , salgrade s where e.dept_id = d.id and ( e.salary between s.losal and s.hisal ) and d.name = '研发部';7. 查询 "研发部" 员工的平均工资
查看参考答案（可复制）
sql
-- 表: emp , dept
-- 连接条件 : emp.dept_id = dept.id
select avg(e.salary) from emp e, dept d where e.dept_id = d.id and d.name = '研发部';8. 查询工资比 "灭绝" 高的员工信息。
查看参考答案（可复制）
sql
-- a. 查询 "灭绝" 的薪资.
select salary from emp where name = '灭绝';
-- b. 查询比她工资高的员工数据
select * from emp where salary > ( select salary from emp where name = '灭绝' );9. 查询比平均薪资高的员工信息
查看参考答案（可复制）
sql
-- a. 查询员工的平均薪资
select avg(salary) from emp;
-- b. 查询比平均薪资高的员工信息
select * from emp where salary > ( select avg(salary) from emp );10. 查询低于本部门平均工资的员工信息
查看参考答案（可复制）
sql
-- a. 查询指定部门平均薪资
select avg(e1.salary) from emp e1 where e1.dept_id = 1;
select avg(e1.salary),dept_id from emp e1 GROUP BY dept_id;
-- b. 查询低于本部门平均工资的员工信息
select * from emp e2 where e2.salary < ( select avg(e1.salary) from emp e1 where e1.dept_id = e2.dept_id );11. 查询所有的部门信息, 并统计部门的员工人数
查看参考答案（可复制）
sql
select d.id, d.name , ( select count(*) from emp e where e.dept_id = d.id ) '人数' from dept d;
select count(*) from emp where dept_id = 1;12. 查询所有学生的选课情况, 展示出学生名称, 学号, 课程名称,成绩
查看参考答案（可复制）
sql
-- 表: student , course , student_course
-- 连接条件: student.id = student_course.studentid , course.id = student_course.courseid
SELECT
student.Sname, student.Sno,course.Cname,sc.score
FROM student
INNER JOIN sc ON student.Sno = sc.Sno INNER JOIN course
ON sc.Cno = course.Cno13. 统计每名学生的总成绩和平均成绩
查看参考答案（可复制）
sql
SELECT
student.sno,student.Sname,sum(sc.score) '总成绩',avg(sc.score) '平均成绩'
FROM student
INNER JOIN sc ON student.Sno = sc.Sno INNER JOIN course
ON sc.Cno = course.Cno
GROUP BY sno,sname;