create TABLE Depts (Dno varCHAR(5) PRIMARY KEY, Dname varCHAR(20) NOT NULL )； create TABLE Students (Sno varCHAR(10), Sname varCHAR(20) NOT NULL, Sgender varCHAR(2), birthday date, Dno varCHAR(5), CONSTRAINT PK_Sno PRIMARY KEY(Sno), CONSTRAINT FK_Dno FOREIGN KEY(Dno) REFERENCES Depts ); create TABLE Courses (Cno varCHAR(6) PRIMARY KEY, Cname varCHAR(20), Pre_Cno varCHAR(6), Credits float )； CREATE TABLE Reports (Sno varchar(10), Cno varchar(6), Grade float CHECK(Grade>=0 and Grade<=100), PRIMARY KEY(Sno,Cno), CONSTRAINT Student_Report FOREIGN KEY(Sno) REFERENCES Students, CONSTRAINT Report_Course FOREIGN KEY(Cno) REFERENCES Courses ); 基于以上四个表，完成以下题目： (11) 查询选修全部课程的学生姓名，相当于查询这样的学生，没有一门课程是他不选的。 (12) 查询平均成绩大于等于黄河同学的平均成绩的学生的学号、姓名和平均成绩。 (13)查询每门课程获得最高分的同学的学号、姓名、课程名称和成绩，并按照最高成绩从高到低进行排序。

(11) 查询选修全部课程的学生姓名，相当于查询这样的学生，没有一门课程是他不选的。

SELECT S.Sname
FROM Students S
WHERE NOT EXISTS (
  SELECT C.Cno
  FROM Courses C
  WHERE NOT EXISTS (
    SELECT R.Cno
    FROM Reports R
    WHERE R.Cno = C.Cno AND R.Sno = S.Sno
  )
)

(12) 查询平均成绩大于等于黄河同学的平均成绩的学生的学号、姓名和平均成绩。

SELECT S.Sno, S.Sname, AVG(R.Grade) AS avg_grade
FROM Students S, Reports R
WHERE S.Sno = R.Sno
GROUP BY S.Sno, S.Sname
HAVING AVG(R.Grade) >= (
  SELECT AVG(R1.Grade)
  FROM Students S1, Reports R1
  WHERE S1.Sno = R1.Sno AND S1.Sname = '黄河'
  GROUP BY S1.Sno, S1.Sname
)

(13)查询每门课程获得最高分的同学的学号、姓名、课程名称和成绩，并按照最高成绩从高到低进行排序。

SELECT S.Sno, S.Sname, C.Cname, R.Grade
FROM Students S, Courses C, Reports R
WHERE S.Sno = R.Sno AND C.Cno = R.Cno AND R.Grade = (
  SELECT MAX(R1.Grade)
  FROM Reports R1
  WHERE R1.Cno = R.Cno
)
ORDER BY R.Grade DESC