设为首页收藏本站
开启辅助访问

数据库系统概念 实验1~实验9

 您所在的位置：网站首页 › 姓名性别年龄表格 › 数据库系统概念 实验1~实验9

数据库系统概念 实验1~实验9

2023-09-30 21:06| 来源: 网络整理| 查看: 265

数据库系统概念实验1~实验9

本文仅为某咸鱼的实验报告记录，仅供参考，不保证一定正确，也不保证sql语句最优化。代码前斜体部分是本人对实验题目的思路，不保证方法一定最简。部分实验创建表或创建视图的语句已省略。

实验1

1.创建学生信息表（学生编号、姓名、性别、年龄、出生日期、院系名称、班级）：test1_student：sid char 12 not null、name varchar 10 not null、sex char 2、age int、birthday date、dname varchar 30、class varchar 10。

create table test1_student ( sid char(12) not null, name varchar(10) not null, sex char(2), age int, birthday date , dname varchar(30) , class varchar(10) );

2.创建课程信息表(仅考虑一门课程最多一个先行课的情况):课程编号、课程名称、先行课编号、学分：test1_course：cid char 6 not null、name varchar 40 not null、fcid char 6、credit numeric 4，1（其中4代表总长度，1代表小数点后面长度）。

create table test1_course ( cid char(6) not null, name varchar(40) not null, fcid char(6), credit numeric(4,1) );

3.创建学生选课信息表（学号、课程号、成绩、教师编号、选课时间） test1_student_course：sid char 12 not null、cid char 6 not null、score numeric 5，1（其中5代表总长度，1代表小数点后面长度）、tid char 6, sctime date

create table test1_course ( cid char(6) not null, name varchar(40) not null, fcid char(6), credit numeric(4,1) );

4.给表test1_student插入如下2行数据。 学号 姓名 性别 年龄 出生日期 院系名称 班级 200800020101 王欣 女 21 1994/2/2 计算机学院 2010 200800020102 李华 女 20 1995/3/3 软件学院 2009

insert into test1_student values(‘200800020101’,‘王欣’,‘女’,‘21’,date‘1994-02-02’,‘计算机学院’,‘2010’);

5.给表test1_course插入如下2行数据。 课程号 课程名 先行课程号 学分 300001 数据结构 2 300002 数据库 300001 2.5

insert into test1_course values (‘300001’,‘数据结构’,null,‘2’); insert into test1_course values (‘300002’,‘数据库’,‘300001’,‘2.5’);

6.给表test1_student_course插入如下2行数据。 学号 课程号 成绩 教师编号 选课时间 200800020101 300001 91.5 100101 2009-7-15 09:09:09 200800020101 300002 92.6 100102 2009-7-15 10:10:10

insert into test1_student_course values(‘200800020101’,‘300001’,91.5,‘100101’,to_date(‘20090715090909’, ‘yyyymmddhh24miss’)); insert into test1_student_course values(‘200800020101’,‘300002’,92.6,‘100102’,to_date(‘20090715101010’, ‘yyyymmddhh24miss’)); 实验2 找出没有选修任何课程的学生的学号、姓名(即没有选课记录的学生）。 找出学号只存在于pub.student而不存在于pub.student_course的学生 Select sid, name from pub.student Where sid not in (select sid from pub.student_course) 找出至少选修了学号为“200900130417”的学生所选修的一门课的学生的学号、姓名(不包含这名同学)。 从pub.student_course找到该学生所修所有课程的cid→根据cid找出对应的sid→利用sid在pub.student中找出对应的学生→去除学号为“200900130417”的学生 Select sid, name from pub.student Where sid !=’ 200900130417’ And sid in (select sid from pub.student_course Where cid in (select cid from pub.student_course Where sid=’ 200900130417’)) 找出至少选修了一门其先行课程号为"300002"号课程的学生的学号、姓名。 从pub.course中找出fcid=’300002’课程的cid→从pub.student_course中找出cid对应的sid→利用sid在pub.student中找出对应的学生 Select sid, name from pub.student Where sid in (select sid from pub.student_course Where cid in (select cid from pub.course Where fcid=’300002’)) 找出选修了“操作系统”并且也选修了“数据结构”的学生的学号、姓名。 分别找出选修了“操作系统”和“数据结构”的学生（方法同第3题），使用intersect对两个结果进行交运算 (Select sid, name from pub.student Where sid in (select sid from pub.student_course Where cid in (Select cid from pub.course Where name=’操作系统’))) Intersect (Select sid, name from pub.student Where sid in (select sid from pub.student_course Where cid in (Select cid from pub.course Where name=’数据结构’))) 查询20岁的所有有选课的学生的学号、姓名、平均成绩(avg_score，此为列名，下同)（平均成绩四舍五入到个位）、总成绩(sum_score)。 通过sid将pub.student和pub.student_course两表合并，再利用sid和name进行分组，然后使用聚集函数avg和sum计算平均值与总和 Select sid, name, round(avg(score),0)avg_score, sum(score)sum_score From pub.student join pub.student_course using (sid) where age=20 group by sid,name 查询所有课的最高成绩、最高成绩人数，test2_06 有四个列：课程号 cid、课程名称 name、最高成绩max_score、最高成绩人数 max_score_count（一个学生同一门课成绩都是第一，只计一次,需要考虑刷成绩情况，一个同学选了一个课程多次，两次都是最高分）。如果没有学生选课，则最高成绩为空值, 最高成绩人数为零。 1)连接pub.course和pub.student_course两表，通过cid分组，统计每个cid下的最高分，得到由cid, name, max_score组成的表 2)筛选pub.student_course，使之只剩下取得每门课最高分的学生的数据，通过cid分组，以sid作区分(distinct)，统计每个cid下学生的人数(count)，得到由cid, max_score_count组成的表 3)连接以上两表 Select cid, name, max_score, max_score_count from (Select cid, name, max(score)max_score From pub.course join pub.student_course using (cid) Group by cid, name) join (select psc.cid, count(distinct sid) max_score_count From pub.student_course psc, (select cid,max(score) max_score from pub.student_course group by cid ) max where psc.cid=max.cid and psc.score=max.max_score group by psc.cid) using (cid) 查询所有不姓张、不姓李、也不姓王的学生的学号 sid、姓名 name。 使用not…and…排除三种情况即可 Select sid, name from pub.student Where name not like ‘张%’ and name not like ‘李%’ and name not like ‘王%’ 查询学生表中每一个姓氏及其人数（不考虑复姓），test2_08 有两个列：second_name、p_count。 通过substr(name,1,1)提取姓氏，然后按照姓氏对pub.student进行分组，之后使用count统计人数 Select substr(name,1,1)second_name,count(sid)p_count From pub.student Group by substr(name,1,1) 查询选修了 300003 号课程的学生的 sid、name、score。 连接pub.student和pub.student_course两表进行查询即可 Select sid,name,score From pub.student join pub.student_course using (sid) Where cid=’300003’ 找出同一个同学同一门课程有两次或以上不及格的所有学生的学号、姓名（即一门课程需要补考两次或以上的学生的学号、姓名）。 1)筛选pub.student_course，使之只剩下score=60为及格计入学分，总学分>=10算作达标，院系为空值的数据不统计在下表中，表结构：院系名称dname、班级class、学分达标人数p_count1、学分未达标人数p_count2、总人数p_count。 Dname varchar(30) class varchar(10) P_count1 Int P_count2 int P_count int 1)创建表test5_03，所有count都默认初值为0(经考察发现，dname=‘生命科学学院’，class=‘2008’时有P_count1=0，这是本表中唯一一个零项，如果此处不设初值，后期合并数据表时会比较麻烦) create table test5_03 (dname varchar(30), class varchar(10), P_count1 int default 0 not null, P_count2 int default 0 not null, p_count int default 0 not null)

2)从pub.student中提取sid，dname(非空），class制成test5_03_01

create table test5_03_01 as select sid,dname,class from pub.student where dname is not null

3)给test5_03_01添加一列sum_credit表示这名学生的学分之和 (经考察发现，并不是所有dname非空的sid都出现在pub.student_course中，即有些同学未参加任何考试，这些学生的学分之和为0，但这些学生的sid并未出现在pub.student_course中，于是无法通过pub.student_course和pub.course获取这些同学的学分。因此此表设定每名学生学分之和初始值为0。)

alter table test5_03_01 add sum_credit int default 0 not null

4)创建表test5_03_02，连接pub.student，pub.course，pub.student_course三表，此表记录sid同时存在于pub.student和pub.student_course的每名学生每门课程成绩及格之后的最高分(也就是说某学生的某门课程最高分如果不及格，则不会出现在此表中)，以及他所在的学院、年级和这门课程的学分

create table test5_03_02 as select sid,cid,dname,class,credit,max_score from (select sid,cid,dname,class,credit,max(score)max_score from ((select cid,credit from pub.course) join ((select sid,dname,class from pub.student where dname is not null) join (select sid,cid,score from pub.student_course) using(sid)) using(cid)) group by sid,cid,dname,class,credit) where max_score>=60

5)统计出现在test5_03_02中的每名学生的总学分，制成test5_03_03

create table test5_03_03 as select sid,dname,class,sum(credit)max_credit from test5_03_02 group by sid,dname,class

6)比较test5_03_01和test5_03_03两表，发现test5_03_03不包含所有课程都没成绩的学生，以及所有课程成绩都不及格的学生。将test5_03_03的所有学生学分之和更新到test5_03_01中，则test5_03_01已完备记录所有学院非空值的学生的学分之和(包含所有课程都没成绩的学生，所有课程成绩都不及格的学生和获得学分的学生)。

update test5_03_01 set sum_credit= (select sum_credit from test5_03_03 where test5_03_01.sid=test5_03_03.sid) where test5_03_01.sid in (select sid from test5_03_03)

7)统计test5_03_01中不同学院不同年级学生人数，插入进表test5_03

insert into test5_03(dname,class,P_count) select dname,class,count(sid)P_count from test5_03_01 group by dname,class

8) 统计test5_03_01中不同学院不同年级学分合格人数，制成test5_03_031; 统计test5_03_01中不同学院不同年级学分不合格人数，制成test5_03_032。

create table test5_03_031 as select dname,class,count(sid)p_count1 from test5_03_01 where sum_credit>=10 group by dname,class create table test5_03_032 as select dname,class,count(sid)p_count2 from test5_03_01 where sum_credit=60为及格计入学分，2008级及之前的班级总学分>=8算作达标，2008级之后的班级学分>=10算作达标，院系为空值的数据不统计在下表中，表结构：院系名称dname、班级class、学分达标人数p_count1、学分未达标人数p_count2、总人数p_count。 Dname varchar(30) class varchar(10) P_count1 int P_count2 int P_count int 本题可以套用第3题模式和中间表，利用test5_03_01完成。 1)创建test5_04

create table test5_04 (dname varchar(30), class varchar(10), P_count1 int default 0 not null, P_count2 int default 0 not null, p_count int default 0 not null)

2) 统计test5_03_01中不同学院不同年级学生人数，插入进表test5_04

insert into test5_04(dname,class,P_count) select dname,class,count(sid)P_count from test5_03_01 group by dname,class

3) 统计test5_03_01中不同学院不同年级学分合格人数，制成test5_04_01; 统计test5_03_01中不同学院不同年级学分不合格人数，制成test5_04_02。 (只需修改第3题的条件)

create table test5_04_01 as select dname,class,count(sid)p_count1 from test5_03_01 where (sum_credit>=10 and class>2008) or (sum_credit>=8 and class0 找出有间接先行课的所有课程的课程号、课程名称。 create or replace view test6_10 as select cid,name from pub.course where fcid in (select cid from pub.course where fcid is not null) 实验7 (1)将pub用户下表student的3个列sid,name,birthday复制到表test7_01中。 (2)执行如下查询，观察运行速度（5秒以上）。 查询Samefirstname相同姓氏的人数。 select * from (select sid,name,birthday, (select count(*) from test7_01 where substr(name,1,1) = substr(t1.name,1,1)) samefirstname from pub.student_testindex t1) where samefirstname=7 (3)为test7_01创建一个仅仅一个索引，保证上面SQL耗时在1秒内。 (4)交卷验证

可以以substr(name,1,1)为索引进行查询 添加索引之前，查询速度约为13s；添加索引之后，查询速度约为0.14s

create index test7_01_firstname on test7_01 (substr(name,1,1)) (1)将pub用户下表student的3个列sid,name,birthday复制到表test7_02中。 (2)将出生日期全部修改成一天： Update test7_02 set birthday=to_date(‘19881018’,‘yyyymmdd’) where substr(sid,12,1)=‘0’; (3)为test7_02创建一个仅仅一个索引，保证下面SQL耗时在1秒内。 Samenamebirthday同名同生日的人数，Samebirthday相同出生日期的人数 select * from (select sid,name,birthday, (select count() from test7_02 where name=t1.name and birthday=t1.birthday) samenamebirthday, (select count() from test7_02 where birthday=t1.birthday) samebirthday from pub.student_testindex t1) where samebirthday=403 (4)交卷验证

添加索引：(birthday,name) 用时0.14s

create index test7_02_birthday_name on test7_02 (birthday,name) (1)pub用户下表student已经用下面两句SQL创建了两索引。 Create index student_birthday on student(birthday); Create index student_name on student(name); (2)下面SQL不能用索引耗时超过2秒，在逻辑不变情况下，修改SQL中标为记红色的子查询的where条件部分，不要修改其它地方，使其能使用索引。 说明：因为pub.student_testindex数据行数太少，不能通过修改主句where绕过问题。 查询samefirstname同姓氏的人数。 select * from (select sid,name,birthday, (select count(*) from pub.student where substr(name,1,1)=substr(t1.name,1,1)) samefirstname from pub.student_testindex t1) where samefirstname=7 (3)修改以后验证耗时在2秒之内，将修改以后语句创建成视图create view test7_03 as select ……。 (4)交卷验证

由于对索引列进行 Like 运算时，表达式前面没有%等通配符时可以使用索引，所以为了使用索引，应当改写成name like ‘x%’的形式，而函数concat(str1,str2)可以实现两个字符串的拼接。 修改语句为：

where name like concat(substr(t1.name,1,1),'%'）

用时：0.015s

(1)pub用户下表student已经用下面两句SQL创建了两索引。 Create index student_birthday on student(birthday); Create index student_name on student(name); (2)下面SQL不能用索引耗时超过1秒，在逻辑不变情况下，修改SQL中标为记红色的子查询的where条件部分，不要修改其它地方，使其能使用索引。 说明：因为pub.student_testindex数据行数太少，不能通过修改主句where绕过问题。 select * from (select sid,name,birthday, (select count() from pub.student where to_char(birthday,‘yyyymm’)=to_char(t1.birthday,‘yyyymm’) ) sameyearmonth, (select count() from pub.student where extract (year from birthday) =extract (year from t1.birthday) ) sameyear from pub.student_testindex t1) where sameyearmonth=35 (3)修改以后验证耗时在1秒之内，将修改以后语句创建成视图create view test7_04 as select ……。 (4)交卷验证

比较同年同月的时候，应当为birthday>=本月第一天且birthday=本年第一天且birthday= trunc(t1.birthday,'mm') and birthday= trunc(t1.birthday,'yyyy') and birthday



【本文地址】


今日新闻

推荐新闻

CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3