山东大学数据库实验答案七、八、九、十

实验七

1． 在学生表pub.student中统计名字（姓名的第一位是姓氏，其余为名字，不考虑复姓）的使用的频率，将统计结果放入test7_01中，表结构如下。
First_name varchar(4) frequency numeric(4)
国强 1034
红 1232
卫东 2323
create table test7_01 as

select First_name,(count(*)) frequency

from (select substr(name,2) First_name from pub.student)

group by First_name

2． 在学生表pub.student中统计名字（姓名的第一位是姓氏，不作统计，名字指姓名的第二个之后的汉字）的每个字使用的频率，将统计结果放入test7_02中（特别提示：需要区别union和union all的不同），表结构如下。

letter varchar(2) frequency numeric(4)

锋 1034

红 1232

鹏 2323

create table test7_02 as

select letter,(count(*)) frequency

from ((select substr(name,2,1) letter from pub.student) union all (select substr(name,3) letter from pub.student))

where letter is not null

group by letter

3． 创建"学院班级学分达标情况统计表1"test7_03，依据pub.student， pub.course，pub.student_course统计形成表中各项数据，成绩>=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
计算机学院 2006 
计算机学院 2007 
软件学院 2006 

create table test7_031 as

select *from

(

(select dname,class,count(sid)  p_count1

from (select dname,class,sid,sum(credit) sum_credit

from ((select sid ,dname,class from pub.student where dname is not null) natural join

(select sid ,cid,score from pub.student_course where score>=60) natural join pub.course)

group by dname,class,sid)

where sum_credit>=10

group by dname,class)

)

 

create table test7_032 as

select dname,class,sum(p2) p_count2

from ((select dname,class,count(sid)  p2

from (select dname,class,sid,sum(credit) sum_credit

from ((select sid ,dname,class from pub.student where dname is not null) natural join

(select sid ,cid,score from pub.student_course where score>=60) natural join pub.course)

group by dname,class,sid)

where sum_credit<10

group by dname,class)

union all (马克思主义学院 2009级 上下两部分p2均为8)

(select dname,class,count(sid) p2

  from pub.student

  where dname is not null and sid not in(

                             select sid

                                from pub.student_course

                             )

   group by dname,class

))

group by dname,class

 

create table test7_033 as

select dname,class,count(sid) p_count

from pub.student

where dname is not null

group by dname,class

 

create table test7_03 as

select * from

test7_031 natural full outer join test7_032 natural full outer join test7_033

 

update test7_03

set p_count1 =0

where p_count1 is null

update test7_03

set p_count2 =0

where p_count2 is null

4． 创建"学院班级学分达标情况统计表2"test7_04，依据pub.student， pub.course，pub.student_course统计形成表中各项数据，成绩>=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
计算机学院 2006
计算机学院 2007
软件学院 2006

create table test7_041 as

select *from

(

(select dname,class,count(sid)  p_count1

from (select dname,class,sid,sum(credit) sum_credit

from ((select sid ,dname,class from pub.student where dname is not null) natural join

(select sid ,cid,score from pub.student_course where score>=60) natural join pub.course)

group by dname,class,sid)

where (sum_credit>=10 and class >2008) or (sum_credit >=8 and class<=2008)

group by dname,class)

)

 

create table test7_042 as

select dname,class,sum(p2) p_count2

from ((select dname,class,count(sid)  p2

from (select dname,class,sid,sum(credit) sum_credit

from ((select sid ,dname,class from pub.student where dname is not null) natural join

(select sid ,cid,score from pub.student_course where score>=60) natural join pub.course)

group by dname,class,sid)

where (sum_credit<10 and class >2008) or (sum_credit <8 and class<=2008)

group by dname,class)

union all

(select dname,class,count(sid) p2

  from pub.student

  where dname is not null and sid not in(

                             select sid

                                from pub.student_course

                             )

   group by dname,class

))

group by dname,class

 

create table test7_043 as

select dname,class,count(sid) p_count

from pub.student

where dname is not null

group by dname,class

实验八

1． 查询各院系（不包括院系名称为空的）的数据结构平均成绩avg_ds_score、操作系统平均成绩avg_os_score，平均成绩四舍五入到个位，创建表test8_01，表结构及格式如下：
Dname Avg_ds_score Avg_os_score
马克思主义学院 72 70
软件学院 77 74
艺术学院 77 76
医学院 74 73

 

create table test8_01 as

select * from

(select dname, (round(avg(s1),0)) Avg_ds_score

from (select pub.student.sid, max(score) s1

from pub.student, pub.student_course, pub.course

where pub.student.sid= pub.student_course.sid

and pub.student_course.cid= pub.course.cid

and pub.course.name = '数据结构'

group by pub.student.sid) A,

pub.student B

where A.sid= B.sid

and dname is not null

group by dname)

natural  join

(select dname, (round(avg(s2),0)) Avg_os_score

from (select pub.student.sid, max(score) s2

from pub.student, pub.student_course, pub.course

where pub.student.sid= pub.student_course.sid

and pub.student_course.cid = pub.course.cid

and pub.course.name = '操作系统'

group by pub.student.sid) A,

pub.student B

where A.sid = B.sid

and dname is not null

group by dname)

2.查询”计算机科学与技术学院”的同时选修了数据结构、操作系统两门课的学生的学号sid、姓名name、院系名称dname、数据结构成绩ds_score、操作系统成绩os_score，创建表test8_02，表结构及格式如下：

create table test8_02 as

select *

from ((select pub.student.sid,pub.student.name,pub.student.dname, max(score) ds_score

from pub.student, pub.student_course, pub.course

where pub.student.sid= pub.student_course.sid

and pub.student_course.cid= pub.course.cid

and pub.course.name= '数据结构'

and pub.student.dname='计算机科学与技术学院'

group by pub.student.sid ,pub.student.dname,pub.student.name)

natural join

(select pub.student.sid,pub.student.name,pub.student.dname, max(score) os_score

from pub.student, pub.student_course, pub.course

where pub.student.sid= pub.student_course.sid

and pub.student_course.cid= pub.course.cid

and pub.course.name= '操作系统'

and pub.student.dname='计算机科学与技术学院'

group by pub.student.sid,pub.student.name,pub.student.dname))

查询计算机科学与技术学院的选修了数据结构或者操作系统的学生的学号sid、姓名name、院系名称dname、数据结构成绩ds_score、操作系统成绩os_score，创建表test8_03，表结构及格式如下：

create table test8_03 as

select *

from ((select pub.student.sid,pub.student.name,pub.student.dname, max(score) ds_score

from pub.student, pub.student_course, pub.course

where pub.student.sid= pub.student_course.sid

and pub.student_course.cid= pub.course.cid

and pub.course.name= '数据结构'

and pub.student.dname='计算机科学与技术学院'

group by pub.student.sid ,pub.student.dname,pub.student.name)

natural full outer join

(select pub.student.sid,pub.student.name,pub.student.dname, max(score) os_score

from pub.student, pub.student_course, pub.course

where pub.student.sid= pub.student_course.sid

and pub.student_course.cid= pub.course.cid

and pub.course.name= '操作系统'

and pub.student.dname='计算机科学与技术学院'

group by pub.student.sid,pub.student.name,pub.student.dname))

查询计算机科学与技术学院所有学生的学号sid、姓名name、院系名称dname、数据结构成绩ds_score、操作系统成绩os_score，创建表test8_04，表结构及格式如下

create table test8_04 as

select * from

(select pub.student.sid,pub.student.name,pub.student.dname, max(score) ds_score

from pub.student, pub.student_course, pub.course

where pub.student.sid= pub.student_course.sid

and pub.student_course.cid= pub.course.cid

and pub.course.name= '数据结构'

and pub.student.dname='计算机科学与技术学院'

group by pub.student.sid ,pub.student.dname,pub.student.name)

natural full outer join

(select pub.student.sid,pub.student.name,pub.student.dname, max(score) os_score

from pub.student, pub.student_course, pub.course

where pub.student.sid= pub.student_course.sid

and pub.student_course.cid= pub.course.cid

and pub.course.name= '操作系统'

and pub.student.dname='计算机科学与技术学院'

group by pub.student.sid,pub.student.name,pub.student.dname)

natural full outer join

(select sid,name,dname

from pub.student

where dname='计算机科学与技术学院' and sid not in

(

select sid

from pub.course c, pub.student_course sc

where (c.name='操作系统' or c.name='数据结构') and c.cid=sc.cid

)

)

实验九

实验题目1
1． 创建表test9_01，表的结构同pub.student_11_1一样。
2． 为test9_01的sid创建唯一不重复索引。
3． 将pub用户下的Student中性别是""女""的数据添加到test9_01中。
4． 将pub用户下的Student_11_1中性别是""女""的数据添加到test9_01中,如果某个学号已经包含在test9_01中，这个记录就不要再插入了（即不要插入重复学号的数据）。
5． 将pub用户下的Student_11_2中性别是""女""的数据添加到test9_01中,如果某个学号已经包含在test9_01中，这个记录就不要再插入了（即不要插入重复学号的数据）。
6． 要求完成上述功能，请采用1条create、3条insert共4条sql方式完成。

create table test9_01 (

 sid char(12)  unique  ,name varchar(10),sex char(2),age int,birthday date,dname varchar(30),class varchar(10)

)

insert into test9_01 (sid,name,sex,age,birthday,dname,class)

(select sid,name,sex,age,birthday,dname,class

from pub.student

where sex ='女')

insert into test9_01 (sid,name,sex,age,birthday,dname,class)

(select sid,name,sex,age,birthday,dname,class

from pub.student_11_1

where sex ='女' and sid not in(select sid from test9_01))

insert into test9_01 (sid,name,sex,age,birthday,dname,class)

(select sid,name,sex,age,birthday,dname,class

from pub.student_11_2

where sex ='女' and sid not in(select sid from test9_01))

 实验题目2
7． 创建表test9_02，表的结构同pub.student_11_1一样。
8． 为test9_02的sid创建唯一不重复索引。
9． 将pub用户下的Student中性别是""女""的且pub.stuent_score中存在一次不及格成绩的同学添加到test9_02中。
10． 将pub用户下的Student_11_1中性别是""女""的且pub.stuent_score中存在一次不及格成绩的同学数据添加到test9_02中,如果某个学号已经包含在test9_02中，这个记录就不要再插入了（即不要插入重复学号的数据）。
11． 将pub用户下的Student_11_2中性别是""女""的且pub.stuent_score中存在一次不及格成绩的同学数据添加到test9_02中,如果某个学号已经包含在test9_02中，这个记录就不要再插入了（即不要插入重复学号的数据）。
12． 要求完成上述功能，请采用1条create、3条insert共4条sql方式完成

create table test9_02 (

 sid char(12)  unique  ,name varchar(10),sex char(2),age int,birthday date,dname varchar(30),class varchar(10)

)

insert into test9_02 (sid,name,sex,age,birthday,dname,class)

(select sid,name,sex,age,birthday,dname,class

from pub.student

where sex ='女' and sid in(select sid from pub.student_course where score<60))

insert into test9_02 (sid,name,sex,age,birthday,dname,class)

(select sid,name,sex,age,birthday,dname,class

from pub.student_11_1

where sex ='女' and sid in(select sid from pub.student_course where score<60) and sid not in (select sid from test9_02) )

insert into test9_02 (sid,name,sex,age,birthday,dname,class)

(select sid,name,sex,age,birthday,dname,class

from pub.student_11_2

where sex ='女' and sid in(select sid from pub.student_course where score<60) and sid not in (select sid from test9_02) )

实验十

1． 将pub用户下表student的3个列sid,name,birthday复制到表test10_01中。
2． 执行如下查询，观察运行速度（5秒以上）。
查询Samefirstname相同姓氏的人数。
select * from 
(select sid,name,birthday,
(select count(*) from test10_01  where substr(name,1,1)=substr(t1.name,1,1)) samefirstname 
from pub.student_testindex t1)
where samefirstname=7
3． 为test10_01创建一个仅仅一个索引，保证上面SQL耗时在1秒内。
4． 交卷验证

create table test10_01 as

select sid ,name,birthday

from pub.student

create index test10_01_name on test10_01 (substr(name,1,1))

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

create table test10_02 as

select sid ,name,birthday

from pub.student;

Update test10_02 set birthday=to_date('19881018','yyyymmdd');

create index test10_02_name on test10_02 (birthday,name)

1． pub用户下表student已经用下面两句SQL创建了两索引。
Create index student_birthday on student(birthday);
Create index student_name on student(name);
2． 下面SQL不能用索引耗时超过1秒，在逻辑不变下修改SQL条件，使其能使用索引。
查询samefirstname同姓氏的人数、samename同姓名的人数。
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 test10_3 as select ……。
4． 交卷验证

create or replace view test10_03 as

select * from

(select sid,name,birthday,

(select count(name) from pub.student  where name like concat(substr(t1.name,1,1),'%')) samefirstname

from pub.student_testindex t1)

where samefirstname=7

1． pub用户下表student已经用下面两句SQL创建了两索引。
Create index student_birthday on student(birthday);
Create index student_name on student(name);
2． 下面SQL不能用索引耗时超过1秒，在逻辑不变下修改SQL条件，使其能使用索引。
查询sameyearmonth相同年月的人数、查询sameyear相同年的人数。
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 test10_4 as select ……。
4． 交卷验证

create or replace view test10_04 as

select * from

(select sid,name,birthday,

(select count(*) from pub.student  where birthday >= trunc(t1.birthday,'mm') and birthday<=last_day(t1.birthday) ) sameyearmonth,

(select count(*) from pub.student  where  birthday >= trunc(t1.birthday,'yyyy') and birthday<=last_day(add_months(trunc(t1.birthday,'y'),11)) ) sameyear

 from pub.student_testindex t1)

where sameyearmonth=35

1． pub用户下表student已经用下面两句SQL创建了两索引。
Create index student_birthday on student(birthday);
Create index student_name on student(name);
2． 下面SQL不能用索引耗时超过1秒，在逻辑不变下修改SQL条件，使其能使用索引。
查询nextbirthday晚一天出生的人数
select * from 
(select sid,name,birthday,
(select count(*) from pub.student  where birthday-1=t1.birthday)  nextbirthday
from pub.student_testindex t1)
where nextbirthday=7
3． 修改以后验证耗时在1秒之内，将修改以后语句创建成视图create view test10_5 as select ……。
4． 交卷验证

create view test10_05 as

select * from

(select sid,name,birthday,

(select count(*) from pub.student  where birthday=t1.birthday+1)  nextbirthday

from pub.student_testindex t1)

where nextbirthday=7