根据学生表,课程表,成绩表,班级表出10道多表联查的sql语句题
时间: 2023-07-05 11:28:57 浏览: 106
好的,以下是10道多表联查的 SQL 语句题:
1. 查询班级表中班级名称为“计算机1班”的所有学生的姓名、所在班级和所学专业。
```sql
SELECT s.name, s.class, s.major
FROM Student s
JOIN Class c ON s.class = c.name
WHERE s.class = '计算机1班';
```
2. 查询学生表中姓“张”的所有学生所在班级的班级名称和所学专业。
```sql
SELECT s.name, c.name as class_name, c.major as class_major
FROM Student s
JOIN Class c ON s.class = c.name
WHERE s.name LIKE '张%';
```
3. 查询学生表中出生日期在1990年1月1日之后的学生所选的所有课程名称和成绩。
```sql
SELECT s.name as student_name, c.name as course_name, g.score
FROM Student s
JOIN Grade g ON s.id = g.student_id
JOIN Course c ON g.course_id = c.course_id
WHERE s.birthday >= '1990-01-01';
```
4. 查询每个班级的平均年龄和所学专业以及该班级的任课教师姓名。
```sql
SELECT c.name as class_name, c.major as class_major, AVG(s.age) as avg_age, t.name as teacher_name
FROM Student s
JOIN Class c ON s.class = c.name
JOIN Teacher t ON c.teacher_id = t.id
GROUP BY c.name;
```
5. 查询选修了课程编号为1001和1002的学生姓名和所在班级。
```sql
SELECT s.name as student_name, s.class as student_class
FROM Student s
JOIN Grade g1 ON s.id = g1.student_id AND g1.course_id = 1001
JOIN Grade g2 ON s.id = g2.student_id AND g2.course_id = 1002;
```
6. 查询每门课程的平均成绩和最高成绩以及任课教师姓名和所在学院名称。
```sql
SELECT c.name as course_name, AVG(g.score) as avg_score, MAX(g.score) as max_score, t.name as teacher_name, co.name as college_name
FROM Course c
JOIN Grade g ON c.course_id = g.course_id
JOIN Teacher t ON c.teacher_id = t.id
JOIN College co ON t.college_id = co.id
GROUP BY c.name;
```
7. 查询成绩表中成绩在80分以上的学生姓名和所在班级以及所选课程的名称和成绩。
```sql
SELECT s.name as student_name, s.class as student_class, c.name as course_name, g.score
FROM Student s
JOIN Grade g ON s.id = g.student_id
JOIN Course c ON g.course_id = c.course_id
WHERE g.score >= 80;
```
8. 查询所有学生的姓名、所在班级和所学专业以及每个学生的平均成绩。
```sql
SELECT s.name as student_name, s.class as student_class, c.major as student_major, AVG(g.score) as avg_score
FROM Student s
JOIN Grade g ON s.id = g.student_id
JOIN Course c ON g.course_id = c.course_id
GROUP BY s.id;
```
9. 查询每个学院的平均学生年龄和平均成绩以及该学院的任课教师姓名和所教授的课程名称。
```sql
SELECT co.name as college_name, AVG(s.age) as avg_age, AVG(g.score) as avg_score, t.name as teacher_name, c.name as course_name
FROM College co
JOIN Teacher t ON co.id = t.college_id
JOIN Course c ON t.id = c.teacher_id
JOIN Grade g ON c.course_id = g.course_id
JOIN Student s ON g.student_id = s.id
GROUP BY co.id;
```
10. 查询选修了所有课程的学生姓名和学号以及所在班级和所学专业。
```sql
SELECT s.id as student_id, s.name as student_name, s.class as student_class, s.major as student_major
FROM Student s
WHERE NOT EXISTS (
SELECT c.course_id
FROM Course c
WHERE NOT EXISTS (
SELECT g.student_id
FROM Grade g
WHERE g.course_id = c.course_id AND g.student_id = s.id
)
);
```