SQl 查找注册所有课程的学生姓名
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7971950/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQl Find names of students who enroll in all courses
提问by Marty Griffin
I am in a database class currently and am stuck on number 3 on this problem.
我目前在一个数据库课程中，并且在这个问题上停留在第 3 位。
Problem 2 A university database has the following relations:
问题2 某大学数据库有如下关系：
STUDENTS (Sno: int, Sname: varchar(64), Gender: ‘F' or ‘M', Age: int),
COURSES (Cno: int, Cname: varchar(32)),
ENROLLMENT (Sno: int, Cno:int, Grade: int).
学生 (Sno: int, Sname: varchar(64), Gender: 'F' or 'M', Age: int),
COURSES (Cno: int, Cname: varchar(32)),
ENROLLMENT (Sno: int, Cno: int，等级：int）。
Write SQL statements to perform the following tasks:
编写 SQL 语句来执行以下任务：
- Find the names of the youngest students.
- Find the Sno for students who enroll at least in courses with Cno = 1 and Cno = 3.
- Find the names of the students who enroll in all the courses.
- Find the names of the students who enroll more than 3 courses.
- Find the name and the average grade for each course.
- Find the names of students whose grades in the course “DBMS” is above the average grade.
- 找出年龄最小的学生的名字。
- 为至少注册了 Cno = 1 和 Cno = 3 的课程的学生找出 Sno。
- 查找注册所有课程的学生姓名。
- 查找注册超过 3 门课程的学生的姓名。
- 找出每门课程的名称和平均成绩。
- 找出在“DBMS”课程中成绩高于平均成绩的学生姓名。
回答by Dylan Smith
3
3
SELECT S.Sname
FROM Students AS S INNER JOIN Enrollment AS E ON S.Sno = E.Sno
GROUP BY S.Sno, S.Sname
HAVING COUNT(*) = (SELECT COUNT(*) FROM Courses)
4
4
SELECT S.Sname
FROM Students AS S INNER JOIN Enrollment AS E ON E.Sno = S.Sno
GROUP BY S.Sno, S.Sname
HAVING COUNT(*) > 3
5
5
SELECT C.CName, AVG(E.Grade) AS AvgGrade
FROM Courses AS C INNER JOIN Enrollment AS E ON C.CNo = E.CNo
GROUP BY C.Cno, C.CName
回答by drdwilcox
Number 4. select count(*)
数字 4. 选择计数(*)
'nuff said.
'纳夫说。
Same idea, different aggregate for number 5.
同样的想法，数字 5 的聚合不同。
