/*第一问*/
SELECT * FROM SC
	WHERE SCORE>80 AND SCORE<90
GO

2.查询至少有 4 个同学选修的课程名。

/*第二问*/
select CN from Course
where CNO in (select CNO from SC
group by CNO having count(SNO)>=4)
go

3.查询其他系中比“信息系”所有学生年龄都大的学生名单及年龄, 并按年龄降序输出。

/*第三问*/
SELECT SNO,SN,AGE from Student
WHERE SNO IN(SELECT SNO FROM Student
WHERE  AGE >  (SELECT MAX (AGE) FROM Student GROUP BY DEPT HAVING DEPT='信息系'))
ORDER BY AGE DESC
GO

4.查询与学生张建国同岁的所有学生的学号、姓名和系别。

/*第四问*/
select SNO,SN,DEPT from Student
WHERE AGE IN (SELECT AGE from Student where SN='张建国')
go

5.查询选修了两门以上课程的学生名单。

/*第五问*/
SELECT * FROM Student
WHERE SNO IN
(SELECT SNO FROM SC GROUP BY SNO HAVING COUNT(CNO)>2)
GO

6.查询至少有一门课程与“张建国”选课相同的学生的姓名、课程名和系别。

/*第六问*/
SELECT CN,SN,DEPT FROM Course,Student
WHERE CNO IN (
SELECT CNO FROM SC
WHERE
SNO IN (SELECT SNO FROM Student WHERE SN='张建国'))
GO

7.查询成绩比该课程平均成绩高的学生的成绩表。

/*第七问*/
SELECT SNO,CNO,SCORE
FROM SC x
WHERE SCORE>(SELECT AVG(SCORE)FROM SC y WHERE y.CNO=x.CNO)
GO

8.查询选修了课号为 01001 的课程且成绩高于课程号为 01002 的课程的学生的姓名、此两门课程的课程名和成绩。

/*第八问*/
SELECT SN,CN,SCORE FROM Student,Course,SC
WHERE Student.SNO =
(SELECT A.SNO FROM SC A,SC B 
 WHERE A.CNO='01001' AND B.CNO='01001' AND A.SCORE >B.SCORE AND A.SNO=B.SNO)
AND SC.Sno=Student.Sno
AND Course.Cno=SC.Cno
GO

 9.查询所有末修 01001 号课程的学生名单。

/*第九问*/
SELECT * FROM Student 
WHERE SNO NOT IN (SELECT SNO FROM SC WHERE CNO='01001')

10. 查询每个学生各门课程的平均成绩和最高成绩,按降序排列输出姓名、平均成绩和最高成绩。

/*第十问*/
SELECT SN,AVG(SCORE) AS 平均成绩, MAX(SCORE) AS 最高成绩 FROM Student ,SC
WHERE Student.SNO=SC.SNO
GROUP BY SN
ORDER BY SN DESC
GO

11.查询所有学生都选修了的课程的课程号和课程名。

/*第十一问*/
SELECT
	Course.CNO,
	Course.CN 
FROM
	Course
WHERE
	NOT EXISTS (
	SELECT
		* 
	FROM
		Student
	WHERE
	NOT EXISTS ( SELECT * FROM SC WHERE SC.SNO= Student.SNO AND CNO=course.CNO)
	)
GO

12.查询选修了 991102 号学生选修了的课程的学生的学号和姓名。

/*第十二问*/
SELECT SN
FROM Student
WHERE NOT EXISTS 
    (SELECT * 
    FROM SC AS sc_1
    WHERE SNO='S2' AND NOT EXISTS
　　　　(SELECT * 
　　　　FROM SC AS sc_2
　　　　WHERE sc_2.SNO=Student.SNO AND sc_2.CNO=sc_1.CNO))