数据库学习(二)——SQL查询语句

数据库实验课课后习题的SQL语句，题目如下： 用到的表的结构如下： 各题具体代码如下： 1.查询成绩在 80~90分之间的记录。

/*第一问*/ 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))