数据库学习之实验二+数据查询

青旅半醒 2022-07-15 02:46 157阅读 0赞

**实验目的：**

1. 通过本实验能够掌握投影、选择条件表达、排序、分组的sql语句表达。

2. 通过本实验能够熟练应用sql语言进行查询，具体包括单表查询，多表连接查询。

**实验要求：**

1. 在进行本实验之前，应熟练课程内容，在上机之前做好实验计划，编写好相应的代码。

2. 认真填写实验报告，记录所有实验用例。

**实验内容：**

**（一） 多表连接查询**

1．查询每个选课学生的学生姓名、课程号和分数。（2表连接）

select sname,cno,grade from student,sc where student.sno=sc.sno;

SNAME CNO GRADE

\-------- ----- -----

李佳 10

李佳 1 92

李佳 2 85

李佳 3 88

刘明 2 90

刘明 3 80

李佳 4 92

李佳 5 85

李佳 6 88

王添 2 90

王添 3 80

李佳 7 92

张力 4 92

王添 1 85

王添 4 88

刘明 1 90

刘明 5 80

17 rows selected

2．求学生的学号、姓名、选修课程的课程名及成绩。（3表连接）

select student.sno,sname,sc.cno,grade from student,sc,course wherestudent.sno=sc.sno and sc.cno=course.cno;

SNO SNAME CNO GRADE

\--------- -------- ----- -----

20070001 李佳 10

20070002 刘明 1 90

20070003 王添 1 85

20070001 李佳 1 92

20070003 王添 2 90

20070002 刘明 2 90

20070001 李佳 2 85

20070003 王添 3 80

20070002 刘明 3 80

20070001 李佳 3 88

20070003 王添 4 88

20070005 张力 4 92

20070001 李佳 4 92

20070002 刘明 5 80

20070001 李佳 5 85

20070001 李佳 6 88

20070001 李佳 7 92

17 rows selected

3．求选修课程号为1或课程号为2的学生姓名和学号。//去重

select sname,student.sno from student,sc where student.sno=sc.sno andcno='1' or student.sno=sc.sno and cno='2';

SNAME SNO

\-------- ---------

李佳 20070001

刘明 20070002

王添 20070003

刘明 20070002

6 rows selected

4．查询每一门课程的间接先行课的课程号。

select a.cno,b.cpno from course a, course b where a.cpno=b.cno;

CNO CPNO

\----- ----

3 5

1 7

10 7

5 6

6 rows selected

5．查询与’刘晨’在同一个系学习的学生。（王添）

select \* from student where sdept in (select sdept from student wheresname='王添');

SNO SNAME SSEX SAGE SDEPT

\--------- -------- ---- ---- --------------------

20070006 张力男 19 MA

20070003 王添男 18 MA

20070001 李佳女 20 MA

6．查询选修了课程名为’C语言’的学生学号和姓名。（操作系统）

select student.sno,sname from student,sc,course where student.sno=sc.snoand sc.cno=course.cno and cname='操作系统';

SNO SNAME

\--------- --------

20070001 李佳

20070005 张力

20070003 王添

7．查询平均成绩在80分以上的学生学号和平均成绩。

select sno,avg(grade) from sc group by sno having avg(grade)>80;

SNO AVG(GRADE)

\--------- ----------

20070003 85.75

20070001 88.8571428

20070002 85

20070005 92

8．查询选修了1门且平均分在80分以下课程的学生的学号。

select student.sno from student,sc where student.sno=sc.sno and cno in(select cno from sc group by cno having avg(grade)<85);

SNO

\---------

20070001

20070002

20070001

20070003

20070002

9．以学生为主体显示学生的信息及其选课的信息。

select \* from student,sc where student.sno=sc.sno(+);

SNO SNAME SSEX SAGE SDEPT SNO CNO GRADE

\--------- -------- ---- ---- -------------------- --------- ----- -----

20070001 李佳女 20 MA 20070001 10

20070001 李佳女 20 MA 20070001 1 92

20070001 李佳女 20 MA 20070001 2 85

20070001 李佳女 20 MA 20070001 3 88

20070002 刘明男 19 IS 20070002 2 90

20070002 刘明男 19 IS 20070002 3 80

20070001 李佳女 20 MA 20070001 4 92

20070001 李佳女 20 MA 20070001 5 85

20070001 李佳女 20 MA 20070001 6 88

20070003 王添男 18 MA 20070003 2 90

20070003 王添男 18 MA 20070003 3 80

20070001 李佳女 20 MA 20070001 7 92

20070005 张力女 19 CS 20070005 4 92

20070003 王添男 18 MA 20070003 1 85

20070003 王添男 18 MA 20070003 4 88

20070002 刘明男 19 IS 20070002 1 90

20070002 刘明男 19 IS 20070002 5 80

20070004 张力女 21 IS

20070006 张力男 19 MA

19 rows selected

10． 对学生表和选课表做自然连接，并输出结果。

select student.\*,cno,grade from student,sc where student.sno=sc.sno;

SNO SNAME SSEX SAGE SDEPT CNO GRADE

\--------- -------- ---- ---- -------------------- ----- -----

20070001 李佳女 20 MA 10

20070001 李佳女 20 MA 1 92

20070001 李佳女 20 MA 2 85

20070001 李佳女 20 MA 3 88

20070002 刘明男 19 IS 2 90

20070002 刘明男 19 IS 3 80

20070001 李佳女 20 MA 4 92

20070001 李佳女 20 MA 5 85

20070001 李佳女 20 MA 6 88

20070003 王添男 18 MA 2 90

20070003 王添男 18 MA 3 80

20070001 李佳女 20 MA 7 92

20070005 张力女 19 CS 4 92

20070003 王添男 18 MA 1 85

20070003 王添男 18 MA 4 88

20070002 刘明男 19 IS 1 90

20070002 刘明男 19 IS 5 80

17 rows selected

11． 输出学号大于’ 刘晨’的学生的姓名和学号。（王添）

select sname,sno from student where sno>(select sno from student wheresname='王添');

SNAME SNO

\-------- ---------

张力 20070004

张力 20070005

张力 20070006

12． 查询数学系的学生每个人所选课程的平均成绩。

select cno,avg(grade) from student,sc where student.sno=sc.sno andsdept='MA' group by sc.cno;

CNO AVG(GRADE)

\----- ----------

1 88.5

6 88

2 87.5

4 90

5 85

3 84

7 92

8 rows selected

变式：查询数学系的学生每个人的平均成绩：

select sno,avg(grade) from sc group by sno having sno in (select sno fromstudent where sdept='MA');

SNO AVG(GRADE)

\--------- ----------

20070003 85.75

20070001 88.8571428

注意：此处为不太规范的写法，特别注意的是：having后面的控制条件必须要是对分组的属性，而不能是其他。在编写命令语句时，要遵循这样的顺序规范：select->from->where->groupby->order by 。还有，虽然在学生表里有三个同学的系别是数学，但是检索出的符合条件的条目只有两条是因为有一个同学并没有进行选课。

规范写法：

select student.sno,avg(grade) from student,sc where student.sno=sc.sno andsdept='MA' group by student.sno;

SNO AVG(GRADE)

\--------- ----------

20070003 85.75

20070001 88.8571428

13． 查询选修2号课程且成绩高于“200215121”号学生成绩（2号课程的成绩）的所有学生记录。（20070001）

select student.\* from student,sc where student.sno=sc.sno and cno='2' andgrade>(select grade from sc where sno='20070001' and cno='2');

SNO SNAME SSEX SAGE SDEPT

\--------- -------- ---- ---- --------------------

20070002 刘明男 19 IS

20070003 王添男 18 MA