中国大学MOOC数据库系统概论中国人民大学实验二 多表查询

数据库系统概论实验评分系统链接

实验题基于学生选课数据库，其包含三个关系模式：
（1）学生关系模式Student(Sno, Sname, Ssex, Sage,Sdept)
（2）课程关系模式Course(Cno, Cname, Cpno, Ccredit)
（3）选课关系模式SC(Sno, Cno, Grade)其中，
学生关系模式各字段的含义为Sno：学号，Sname：姓名，Ssex：性别，Sage：年龄，Sdept：所在系。
课程关系模式各字段的定义为Cno：课程号，Cname：课程名，Cpno：先行课，Ccredit：学分。
选课关系模式各字段的定义为Sno：学号，Cno：课程号，Grade：成绩。
其中，SC表中学号Sno和课程号Cno分别对应于Student表的主码和Course表的主码，即学号Sno和课程号Cno是SC表的外码。

1.查询学生姓名，对应的课程名和成绩（输出列为Sname,Cname,Grade）

SELECT Sname SNAME,Cname CNAME,Grade GRADE
FROM Student,SC,Course
WHERE Student.Sno=SC.Sno AND SC.Cno=Course.Cno;

2.查询每个学生的学号、姓名及其选修课程的名称和成绩(包括没有选修课程的学生情况（输出列为：Sno, Sname, Cname, Grade)。

SELECT Student.Sno,Sname,Cname,Grade
FROM student LEFT OUTER JOIN 
	(SELECT Sno,Cname,Grade 
	 FROM Course,SC 
	 WHERE Course.Cno=SC.Cno) AS sc_cou ON student.sno=sc_cou.sno;

3.查询其他系中比“信息学院”某一学生年龄小的学生姓名和年龄（输出列为：Sname, Sage）。

SELECT Sname SNAME, Sage SAGE
FROM Student
WHERE Sage < ANY(SELECT Sage
                 FROM Student
                 WHERE Sdept='信息学院'
                 )
             AND Sdept <> '信息学院';

4.查询选修了全部课程的学生姓名（输出列为：Sname）

SELECT Sname
FROM Student
WHERE NOT EXISTS
          (SELECT *
           FROM Course
           WHERE NOT EXISTS
                     (SELECT *
                      FROM SC
                      WHERE SC.Sno=Student.Sno AND SC.Cno=Course.Cno
                     )
            );

5.查询选了经济控制论的学生中，成绩比名字为赵古韵的学生好的学生信息（输出列为student表全部列）

SELECT Student.*
FROM Student, SC, Course
WHERE Grade > (SELECT Grade
               FROM Student S1, SC SC1, Course C1
               WHERE S1.Sname = '赵古韵'
                     AND S1.Sno = SC1.Sno
                     AND SC1.Cno = C1.Cno 
                     AND C1.Cname = '经济控制论'
               )
               AND Student.Sno = SC.Sno
               AND SC.Cno = Course.Cno
               AND Cname = '经济控制论';

6.统计每个学生所选课程的平均成绩（输出列为：Sname，Avgscore）

SELECT Sname SNAME, AVGSCORE
FROM Student, (SELECT DISTINCT Sno, AVG(Grade)
               FROM SC
               GROUP BY Sno
               ) AS Stu_avg(Sno, AVGSCORE)
WHERE Student.Sno = Stu_avg.Sno;

7.查询信息学院年龄在21岁以下的男生每个人所修课程的总学分，并按总学分进行升序排序（输出列为学号sno、总学分creditsum）

SELECT Student.Sno SNO, CREDITSUM
FROM Student, (SELECT Sno, SUM(Ccredit)
               FROM SC,Course 
               WHERE SC.Cno=Course.Cno
               GROUP BY Sno
               ) AS Stu_credit(Sno, CREDITSUM)
WHERE Student.Sno = Stu_credit.Sno
      AND Sdept = '信息学院'
      AND Sage < 21
      AND Ssex = '男'
ORDERT BY CREDITSUM ASC;

8.查询各门课程的最高成绩的学生的姓名及其成绩（输出列为cno,sname,grade）

SELECT Cno CNO, Sname SNAME, Grade GRADE
FROM Student, (SELECT *
               FROM SC
               WHERE (Cno,Grade) IN (SELECT Cno, MAX(Grade)
                                     FROM SC
                                     GROUP BY Cno
                                     )
               ) AS Stu_max(Sno, Cno, Grade)
WHERE Student.Sno = Stu_max.Sno;

9.查询选修了2012001001号学生所选修的全部课程的学生的姓名（输出列sname）

SELECT DISTINCT Sname SNAME
FROM Student, SC SCX
WHERE Student.Sno = SCX.Sno 
            AND NOT EXISTS 
            (SELECT *
             FROM SC SCY
             WHERE SCY.Sno = '2012001001' 
                   AND NOT EXISTS 
                           (SELECT *
                            FROM SC SCZ
                            WHERE SCZ.Sno=SCX.Sno AND SCZ.Cno=SCY.Cno
                           )
            );