对于教学数据库的三个基本表: Stdent(SNO,SNAME,AGE,SEX) SC(SNO,CNO,GRADE) Course(CNO,CNAME,TEACHER) 1.求刘老师所授课程的每门课程的成绩的最高分和最低分。(再换一种语句)
时间: 2024-03-20 16:45:35 浏览: 85
可以使用 WITH 子句和窗口函数的方式,进一步简化 SQL 语句,如下所示:
```
WITH T AS (
SELECT Course.CNAME, SC.GRADE, ROW_NUMBER() OVER (PARTITION BY Course.CNAME ORDER BY SC.GRADE DESC) AS RN
FROM Course
INNER JOIN SC ON Course.CNO = SC.CNO
INNER JOIN Stdent ON Stdent.SNO = SC.SNO
WHERE Course.TEACHER = '刘老师'
)
SELECT CNAME, MAX(GRADE) AS MAX_GRADE, MIN(GRADE) AS MIN_GRADE
FROM T
WHERE RN = 1
GROUP BY CNAME;
```
这个 SQL 语句使用了 WITH 子句和窗口函数的方式,先将查询结果和计算最高分、最低分分别放在一个子查询中,并使用窗口函数 ROW_NUMBER() 对每门课程的成绩进行排序并编号,然后在外层查询中筛选出每门课程的最高分(即编号为 1 的记录),最后使用 GROUP BY 子句进行聚合计算。这种方式的好处是可以将多个查询步骤放在一起,使 SQL 语句更加简洁。
相关问题
对于教学数据库的三个基本表: Stdent(SNO,SNAME,AGE,SEX) SC(SNO,CNO,GRADE) Course(CNO,CNAME,TEACHER) 1.求刘老师所授课程的每门课程的成绩的最高分和最低分。
根据提供的三个基本表,我们可以使用下面的 SQL 语句来查询刘老师所授课程的每门课程的成绩的最高分和最低分:
```
SELECT Course.CNAME, MAX(SC.GRADE) AS MAX_GRADE, MIN(SC.GRADE) AS MIN_GRADE
FROM Course
INNER JOIN SC ON Course.CNO = SC.CNO
INNER JOIN Stdent ON Stdent.SNO = SC.SNO
WHERE Course.TEACHER = '刘老师'
GROUP BY Course.CNAME;
```
解释一下这个 SQL 语句:
- 首先我们使用 INNER JOIN 将 Course 表、SC 表和 Stdent 表连接起来。
- 然后我们使用 WHERE 子句来筛选出刘老师所授课程的记录。
- 最后我们使用 GROUP BY 子句将结果按照课程名进行分组,并使用 MAX 和 MIN 聚合函数来计算每门课程的最高分和最低分。
程序设计题(50) 对于教学数据库的三个基本表: Stdent(SNO,SNAME, AGE,SEX) SC(SNO,CNO,GRADE) HERE Course(CNO,CNAME, TEACHER)试用SQL的查询语句表达下列查询: () 1)检索学号为S3的学生所学课程的课程号与成绩。(5分) 2)检索年龄大于20岁的女学生的姓名和性别。(5分) 3)检索“数据库”课程的课程号。(10分) L 4)检索王敏同学没有选修的课程的课程号。(10分) 5)统计每门课程的学生选修人数(超过15人的课程才统计)。要求显示课程 号,课程名和人数,查询结果按人数降序排列,若人数相同,按课程号升序排列。(10分) 6)求刘老师所授课程的每门课程的成绩的最高分和最低分。(10分)
1) SELECT CNO, GRADE FROM SC WHERE SNO = 'S3';
2) SELECT SNAME, SEX FROM Student WHERE AGE > 20 AND SEX = '女';
3) SELECT CNO FROM Course WHERE CNAME = '数据库';
4) SELECT CNO FROM Course WHERE CNO NOT IN (SELECT CNO FROM SC WHERE SNO = '王敏');
5) SELECT CNO, CNAME, COUNT(*) AS NUM FROM Course JOIN SC ON Course.CNO = SC.CNO GROUP BY Course.CNO, CNAME HAVING COUNT(*) > 15 ORDER BY NUM DESC, CNO ASC;
6) SELECT SC.CNO, MAX(GRADE), MIN(GRADE) FROM SC JOIN Course ON SC.CNO = Course.CNO WHERE Course.TEACHER = '刘老师' GROUP BY SC.CNO;