MySQL以数据库user_db及数据表student、teacher、class、course、sc为基础(8)查询每门课程最低分的学生的学号、姓名、院系、课程名及成绩; 3、SQL连接查询的应用; (1)查询选修“数据库原理”不及格的学生的学号、姓名、成绩; (2)查询和“崔晓”一个院系的其他学生的学号、姓名、院系; (3)查询信息技术学院年龄最大的学生的学号、姓名、性别; (4)查询信息技术学院成绩在60分以下之间的学生的学号、课程号、成绩,其中查询结果先按照课程号升序排列,再按照成绩降序排列;
时间: 2023-06-13 21:07:22 浏览: 121
1、查询每门课程最低分的学生的学号、姓名、院系、课程名及成绩
```
SELECT
sc.s_id AS 学号,
student.s_name AS 姓名,
student.s_dept AS 院系,
course.c_name AS 课程名,
MIN(sc.score) AS 最低分
FROM
sc
INNER JOIN student ON sc.s_id = student.s_id
INNER JOIN course ON sc.c_id = course.c_id
GROUP BY
sc.c_id;
```
2、查询选修“数据库原理”不及格的学生的学号、姓名、成绩
```
SELECT
sc.s_id AS 学号,
student.s_name AS 姓名,
sc.score AS 成绩
FROM
sc
INNER JOIN student ON sc.s_id = student.s_id
INNER JOIN course ON sc.c_id = course.c_id
WHERE
course.c_name = '数据库原理' AND
sc.score < 60;
```
3、查询和“崔晓”一个院系的其他学生的学号、姓名、院系
```
SELECT
s_id AS 学号,
s_name AS 姓名,
s_dept AS 院系
FROM
student
WHERE
s_dept = (SELECT s_dept FROM student WHERE s_name = '崔晓') AND
s_name != '崔晓';
```
4、查询信息技术学院年龄最大的学生的学号、姓名、性别
```
SELECT
s_id AS 学号,
s_name AS 姓名,
s_sex AS 性别
FROM
student
WHERE
s_dept = '信息技术学院'
ORDER BY
s_age DESC
LIMIT 1;
```
5、查询信息技术学院成绩在60分以下之间的学生的学号、课程号、成绩,其中查询结果先按照课程号升序排列,再按照成绩降序排列
```
SELECT
sc.s_id AS 学号,
sc.c_id AS 课程号,
sc.score AS 成绩
FROM
sc
INNER JOIN course ON sc.c_id = course.c_id
INNER JOIN student ON sc.s_id = student.s_id
WHERE
student.s_dept = '信息技术学院' AND
sc.score < 60
ORDER BY
sc.c_id ASC, sc.score DESC;
```
阅读全文