多表查询练习题

1、创建好数据库

create database text
use text
--学生表 (students)
CREATE TABLE students (  
    student_id INT PRIMARY KEY,  
    name VARCHAR(50),  
    age INT,  
    major VARCHAR(50)  
);

--课程表 (courses)
CREATE TABLE courses (  
    course_id INT PRIMARY KEY,  
    course_name VARCHAR(50),  
    teacher_name VARCHAR(50)  
);

--选课表 (enrollments)
CREATE TABLE enrollments (  
    enrollment_id INT PRIMARY KEY,  
    student_id INT,  
    course_id INT,  
    grade DECIMAL(4, 1),  
    FOREIGN KEY (student_id) REFERENCES students(student_id),  
    FOREIGN KEY (course_id) REFERENCES courses(course_id)  
);

--插入学生数据
INSERT INTO students (student_id, name, age, major) VALUES  
(1, '张三', 20, '计算机科学'),  
(2, '李四', 21, '数学'),  
(3, '王五', 19, '物理');

--插入课程数据
INSERT INTO courses (course_id, course_name, teacher_name) VALUES  
(1, '数据库原理', '赵老师'),  
(2, '计算机网络', '钱老师'),  
(3, '高等数学', '孙老师');


--插入选课数据
INSERT INTO enrollments (enrollment_id, student_id, course_id, grade) VALUES  
(1, 1, 1, 85.5),  
(2, 1, 2, 90.0),  
(3, 2, 1, 78.0),  
(4, 2, 3, 92.0),  
(5, 3, 2, 88.5);

所有表查询结果如图：

题目1：查询选修了“数据库原理”课程的学生姓名。

select name
from students s
inner join enrollments e
on s.student_id=e.student_id
inner join courses c
on e.course_id=c.course_id and course_name='数据库原理'

结果：

题目2：查询张三的所有课程成绩。

select grade
from students s 
inner join enrollments e
on s.student_id=e.student_id and name='张三'

结果：

题目3：查询每位学生的姓名、年龄和平均成绩。

select name,age,AVG(grade)平均成绩
from students s
inner join enrollments e
on s.student_id=e.student_id
group by name,age

结果：

题目4：查询年龄大于20岁的学生所选修的课程名称。

select course_name
from courses c
inner join enrollments e
on c.course_id=e.course_id
inner join students s
on s.student_id=e.student_id and age>20

结果：

题目5：查询“赵老师”所教课程的名称以及选修这些课程的学生姓名。

select  course_name,name
from students s
inner join enrollments e
on s.student_id=e.student_id
inner join courses c
on c.course_id=e.course_id and teacher_name='赵老师'

结果：

题目6：查询选修了至少两门课的学生姓名

select name
from  students s
inner join enrollments e
on s.student_id=e.student_id
group by e.student_id,name
having COUNT(e.student_id)>=2

结果：

题目7：查询成绩高于90分的学生姓名和课程名称。

select name,course_name
from students s
inner join enrollments e
on s.student_id=e.student_id
inner join courses c
on e.course_id=c.course_id and grade>90

结果：

题目8：查询每位老师所教课程的平均成绩

select AVG(grade) 平均成绩,teacher_name
from enrollments e
inner join courses c
on e.course_id=c.course_id
group by c.teacher_name

结果：

题目9：查询成绩在80分以上且选课数量不少于2门的学生姓名和平均成绩

select name,AVG(grade) 平均成绩
from students s
inner join enrollments e
on s.student_id=e.student_id and grade>=80
group by e.student_id,name
having  COUNT(e.student_id)>=2

结果：

题目10：查询每个专业选修了最多课程的学生姓名。

select name,major
from students s
inner join enrollments e
on s.student_id=e.student_id
group by major,name
having  COUNT(e.student_id)

结果：