学习数据库之查询综合练习

(需要注意的是orcle和mysql有些语法是不通用的，比如mysql不支持使用INTERSECT和MINUS)

题目：

某校学生选课管理数据库各表情况说明如下：
Student( Sno CHAR(7) primary key，
Sname CHAR(10) ，
Ssex CHAR(2) ，
Sage int DEFAULT 20，
Sdept CHAR(20) )

Course(
Cno Char(10) PRIMARY KEY,
Cname Char(20) NOT NULL,
Ccredit int CHECK(Ccredit>0),
Semester int CHECK(Semester>0),
Period int CHECK(Period>0),
)

SC(
Sno CHAR(7) NOT NULL,
Cno CHAR(10) NOT NULL,
Grade int,
PRIMARY KEY (Sno,Cno),
FOREIGN KEY(Sno) REFERENCES Student(Sno),
FOREIGN KEY(Cno) REFERENCES Course(Cno)
)

针对该课数据库进行以下操作
1、 查询Student表中的所有记录的Sname和Sage列。
2、 查询学生所有的系别即不重复的Sdept列。
3、 查询Student表的所有记录。
4、 查询全体学生的出生年份和性别，并给出生年份列命名为“BIRTHDAY”。
5、 查询SC表中分数大于85分的学生学号。
6、 查询SC表中成绩在60到80之间的所有记录。
7、 查询SC表中成绩为85，86或88的记录。
8、 查询Student表中不姓“王”的同学记录。
9、 查询全校同学名字中第二个字为“小”的同学的具体情况。
10、查询课程名中含有“_”的课程的名字和学分。
11、查询学生表中姓赵、钱、孙、李的同学。
12、查询Sc表中没有成绩的同学的学号。
13、查询Student表中“信息系”班或性别为“女”的同学记录。（复合条件查询，集合查询）
14、查询计算机系男生的具体情况。
15、以年龄降序查询Student表的所有记录。
16、以Cno升序、Grade降序查询Sc表的所有记录。
17、查询所有学生的Sname、Cname和Grade列。（连接查询，嵌套查询）
18、查询所有选修“计算机导论”课程的同学的成绩。（连接查询，嵌套查询）
19、查询和“李军”同性别的同学Sname. （自身连接查询，嵌套查询）
20、查询所有同学的基本情况和选课情况，包括未选课的同学。（外连接查询）
21、查询选修13号课程且成绩高于80分的同学的名字。（连接查询，嵌套查询，集合查询）
22、查询和学号为0608002的同学同年出生的所有学生的Sno、Sname列。（自身连接查询，嵌套查询）
23、查询王位同学所有的成绩。（连接查询，嵌套查询）
24、查询非计算机系的不超过计算机系所有学生的年龄的学生姓名。（用ANY,ALL）
25、查询存在有85分以上成绩的课程Cno.（用exists）
26、查询计算机系同学的人数。
27、查询数学系统学所选课程的平均分。
28、查询Student表中年龄最大和最小的同学的具体情况。
29、查询最高分同学的Sno、Cno列。
30、查询13号课程的平均分。
31、查询选修了13号课程且成绩比该课程平均成绩低的同学的学号和成绩。
32、查询选修了13号课程或1号课程的同学的学号。（复合条件查询，集合查询）
33、查询各个课程号及相应的选课人数。
34、查询选修了3门以上课程且总分大于200分的同学的学号。

数据库模型：

student表：

sc表：

course表：

操作：

CREATE TABLE student (

Sno CHAR(7) primary key，

Sname CHAR(10) ，

Ssex CHAR(2) ，

Sage int DEFAULT 20，

Sdept CHAR(20) )

 

CREATE TABLE sc (

Sno CHAR(7) NOT NULL,

Cno CHAR(10) NOT NULL,

Grade int,

PRIMARY KEY (Sno,Cno),

FOREIGN KEY(Sno) REFERENCES Student(Sno),

FOREIGN KEY(Cno) REFERENCES Course(Cno)


)

 

CREATE TABLE course(

Cno Char(10) PRIMARY KEY,

Cname Char(20) NOT NULL,

Ccredit int CHECK(Ccredit>0),

Semester int CHECK(Semester>0),

Period int CHECK(Period>0),

)

SELECT Sname,Sage FROM student
SELECT DISTINCT Sdept FROM student
SELECT * FROM student
SELECT Sage AS BIRTHDAY,Ssex FROM student 
SELECT Sno FROM sc WHERE  sc.Grade >= 85 GROUP BY Sno
SELECT * FROM sc WHERE Grade BETWEEN 60 AND 80
SELECT * FROM sc WHERE Grade IN (85,86,88)
SELECT * FROM student WHERE Sname NOT LIKE '王%' 
SELECT * FROM sc,student WHERE Sname LIKE '_小%' AND sc.Sno=student.Sno
SELECT Cname,Ccredit FROM course WHERE Cname LIKE '%/_%' ESCAPE '/'
SELECT * FROM student WHERE Sname LIKE '赵%' OR Sname LIKE '钱%' OR Sname LIKE '孙%' OR Sname LIKE '李%'
SELECT Sno FROM sc WHERE Grade IS NULL
SELECT * FROM student WHERE Sdept='信息系' OR Ssex='女'
SELECT * FROM student WHERE Sdept='计算机系' AND Ssex='男'
SELECT * FROM student ORDER BY Sage DESC
SELECT * FROM sc ORDER BY Cno,Grade DESC
SELECT Sname,Cname,Grade FROM sc,course,student WHERE sc.Sno=student.Sno AND sc.Cno=course.Cno
SELECT Grade FROM sc WHERE Cno IN (SELECT Cno FROM course WHERE Cname='计算机导论')
SELECT Sname FROM student WHERE Ssex IN (SELECT Ssex FROM student WHERE Sname='李军')
SELECT student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade FROM student LEFT OUTER JOIN sc ON (student.Sno=sc.Sno)
SELECT Sname FROM sc,student WHERE sc.Sno=student.Sno AND sc.Cno=13 AND Grade>=80 # 21 连接查询
SELECT Sname FROM student WHERE Sno IN (SELECT Sno FROM sc WHERE sc.Cno=13 AND sc.Grade>=80) # 21 嵌套查询
SELECT Sno,Sname FROM student WHERE Sage IN (SELECT Sage FROM student WHERE Sno = 0608002)
SELECT Cno,Grade FROM student,sc WHERE student.Sno=sc.Sno AND student.Sname='王位' # 23 连接查询
SELECT Cno,Grade FROM sc WHERE Sno IN (SELECT Sno FROM student WHERE Sname='王位') # 23 嵌套查询
SELECT Sname FROM student WHERE Sage < ANY(SELECT Sage FROM student WHERE Sdept='计算机系') AND Sdept <> '计算机系'
SELECT Cno FROM sc WHERE Grade > 85
SELECT COUNT(*) AS 总人数 FROM student GROUP BY Sdept HAVING Sdept='计算机系'
SELECT AVG(Grade) FROM sc GROUP BY Cno HAVING Cno IN (SELECT Cno FROM course WHERE Cname='数学系统学')
SELECT * FROM student WHERE Sage=(SELECT MAX(Sage) FROM student) OR Sage=(SELECT MIN(Sage) FROM student)
SELECT Sno,Cno FROM sc WHERE Grade=(SELECT MAX(Grade) FROM sc)
SELECT AVG(Grade) FROM sc GROUP BY Cno HAVING Cno=13
SELECT Sno,Grade,Cno FROM sc WHERE Grade<(SELECT AVG(Grade) FROM sc WHERE Cno=13) AND Cno=13
SELECT Sno FROM sc WHERE Cno=1 OR Cno=13
SELECT Cno,COUNT(*) AS 选课人数 FROM sc GROUP BY Cno
SELECT Sno FROM sc GROUP BY Sno HAVING COUNT(*)>=3 AND SUM(Grade)>200