查询注册所有课程的学生姓名

Question

我目前在一个数据库类，在这个问题上被困在第3个。

问题2一个大学数据库有以下关系:

STUDENTS (Sno: int, Sname: varchar(64)，性别:' F ' or ' M '，年龄:int)，
COURSES (Cno: int, Cname: varchar(32))，
student (student:int, student:int, Grade: int).

编写SQL语句执行以下任务:

1. 查找最年轻学生的名字
2. 查找至少参加Cno = 1和Cno = 3课程的学生的Sno。
3. 查看所有课程的注册学生名单
4. 查询注册三门以上课程的学生名单
5. 查找每门课程的名称和平均成绩
6. 查找在"DBMS"课程中成绩高于平均成绩的学生的名字。

Answer 1

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

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

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

Answer 2

4。select count (*)

足够地说

。

相同的想法，不同的聚合第5。