数据库原理实验（数据的查询）

一、实验目的

掌握SQL语句中的各种查询方法，如单表查询，多表的连接查询、嵌套查询，熟练掌握数据查询中分组、统计、计算和组合的操作方法。 

二、实验要求

在现有的数据库上进行各种查询操作。 

三、实验内容

设有教学数据库，包含表：

学生表：Student(Sno,Sname,Ssex,Sage,Sdept)

课程表：Course(Cno,Cname,Cpno,Ccredit)

学生选课表：SC(Sno,Cno,Grade)

数据如下：

实验数据如下：

在数据库中建立三个关系表Students，Course，SC。

学生表：Students(Sno,Sname,Ssex,Sage,Sdept)

    课程表：Course(Cno,Cname,Cpno,Ccredit)

    学生选课表：SC(Sno,Cno,Grade)

表说明如下：

如下表1建立表students：

                          表 1 students 说明

	列名	数据类型	允许空	主键	说明
1	Sno	Char(9)	否	是	学号
2	Sname	Char(20)	是	否	姓名
3	Ssex	Char(2)	是	否	性别
4	Sage	SMALLINT	是	否	年龄
5	Sdept	Varchar(20)	是	否	所在系

如下表2建立表：Course

                          表 2 Course表说明

	列名	数据类型	允许空	主键	说明
1	Cno	Char(4)	否	是	课程号
2	Cname	Char(40)	是	否	课程名
3	Cpno	Char(4)	是	否	先修课程
4	Ccredit	SMALLINT	是	否	学分

如下图建立表SC：（注：包括两个外键，sno和cno共同组成主键）

                          表 3 SC表说明

	列名	数据类型	允许空	主键	说明
1	Sno	Char(9)	否	是	学号
2	Cno	Char(4)	否	是	课程号
3	Grade	Int	是	否	成绩

4.在Students表中插入以下4条数据。

学  号 Sno	姓  名 Sname	性  别  Ssex	年  龄  Sage	所 在 系  Sdept
200215121 200215122 200215123 200515125	李勇 刘晨 王敏 张立	男 女 女 男	20 19 18 19	CS CS MA IS

5.在Course表中插入以下数据。

课程号 Cno	课程名 Cname	先行课 Cpno	学分 Ccredit
1 2 3 4 5 6 7	数据库 数学 信息系统 操作系统 数据结构 数据处理 PASCAL语言	5 1 6 7 6	4 2 4 3 4 2 4

6.在SC表中插入以下数据。

学 号 Sno	课程号   Cno	成绩     Grade
200215121    200215121    200215121    200215122    200215122 200215122	1   2   3   2   3   1	92  85  88  90  80  90

在上述数据库的基础上进行以下实验。将所用SQL语句写入实验报告。

注意：1）在实验报告中写清楚每道题目（包括题号及题目）；2）在每道题目下写清楚查询代码；3）每道题目都要附上查询代码和执行代码的结果截图；4）程序调试过程需要写明实验中遇到的问题或错误，及解决的方法；5）实验小结部分请写明通过实验得到的结论及心得体会；6）所有的题目同样要求。

1、在SC表中查询选修了课程的学生学号。注意去掉重复的行。

2、查询既不是信息系、数学系，也不是计算机科学系的学生的姓名和性别。

3、查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列。

4、查询选修了3号课程的学生的学号及其成绩，查询结果按分数降序排列。

5、、检索至少选修课程号为C2和C4的学生学号

6、查询每个学生的学号、姓名、选修的课程名及成绩

7、查询总学分超过6分的同学学号，姓名，选修课的门数，总学分

8、查询选修1号课程的男生和女生人数，显示课号，性别，人数。

9、检索学C2课程的学号与姓名。

10、检索平均成绩最高的学生学号。

11、查询没有选修1号课程的学生姓名。

12、查询选修了全部课程的学生姓名。

13、检索刘晨同学不学的课程的课程号。

14、检索学号比刘晨同学大，而年龄比他小的学生姓名。

15、求年龄大于女同学平均年龄的男学生姓名和年龄。

四、实验小结

【实验报告】

实验时间：              实验地点：             

具 体 实 验 内 容

1、在SC表中查询选修了课程的学生学号。注意去掉重复的行。 select sno from SC group by sno;                                2、查询既不是信息系、数学系，也不是计算机科学系的学生的姓名和性别。 select sname,ssex from students where sdept not in('cs','md','is');  3、查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列 select * from students order by sdept asc,sage desc; 4、查询选修了3号课程的学生的学号及其成绩，查询结果按分数降序排列。 select sno,grade from sc where cno=3 order by grade desc; 5、检索至少选修课程号为C2和C4的学生学号 select distinct sno from sc  where cno not IN('2','4'); 6、查询每个学生的学号、姓名、选修的课程名及成绩 select students.sno,students.sname,cname,grade from students,sc,course where students.sno=sc.sno and sc.cno=course.cno; 7、查询总学分超过6分的同学学号，姓名，选修课的门数，总学分 select students.sno,sname,COUNT(course.cno),SUM(ccredit) from course,students,sc where sc.sno=students.sno and course.cno=sc.cno group by students.sno,snamehaving SUM(ccredit)>6 8、查询选修1号课程的男生和女生人数，显示课号，性别，人数。 select ssex,cno,COUNT(students.sno) from students,sc where cno='1' and Students.sno=sc.sno group by cno,ssex 9、检索学C2课程的学号与姓名。 select sno,sname from students where sno in(select sno from sc where cno=2); 10、检索平均成绩最高的学生学号。 select sno from sc group by sno having AVG(grade)>=all(select AVG(grade)  from sc  group by sno); 11、查询没有选修1号课程的学生姓名。 select sname from students where students.sno not in(select sno from sc where cno=1) 12、查询选修了全部课程的学生姓名。 select sname from students where sno in(select sno from sc where exists (select cno from sc where cno in(1,2,3))) 13、检索刘晨同学不学的课程的课程号。 select cno from course where cno not in(select cno from sc where sno in(select sno from students where sname='刘晨')) 14、检索学号比刘晨同学大，而年龄比他小的学生姓名。 select sname from students where sno>(select sno from students where sname='刘晨')and sage<(select sage from students where sname='刘晨') 15、求年龄大于女同学平均年龄的男学生姓名和年龄。 select sname,sage from students where sage>(select avg(sage) from students group by ssex having ssex='女')and ssex='男';