山东大学数据库实验答案四、五、六

实验四

1． 将pub用户下表student_41及数据复制到主用户的表test4_01中，使用alter table语句为表增加列："总成绩:sum_score"。
使用update语句,利用pub.student_course、pub.course，统计 "总成绩"；

SQL：

create table test4_01 as(

select *

from pub.student_41)

 

alter table test4_01 add sum_score int

 

update test4_01

set sum_score=(

select sum(score)

from pub.student_course a

where a.sid=test4_01.sid)

2． 将pub用户下表student_41及数据复制到主用户的表test4_02中，使用alter table语句为表增加列"平均成绩:avg_score" （小数点后保留1位）。
利用pub.student_course、pub.course，统计"平均成绩"，四舍五入到小数点后1位

主要SQL：

update test4_02

set avg_score=(

select avg(score)

from pub.student_course a

where a.sid=test4_02.sid)

3． 将pub用户下表student_41及数据复制到主用户的表test4_03中，使用alter table语句为表增加列："总学分:sum_credit"。
使用update语句,利用pub.student_course、pub.course，统计 "总学分"；
这是需要注意：成绩及格才能够计算所得学分。

update test4_03

set sum_credit=(

select sum(credit)

from (select sid ,cid ,max(score) score from pub.student_course group by sid,cid) a natural join pub.course b

where a.sid=test4_03.sid and a.score>=60)

4． 将pub用户下表student_41及数据复制到主用户的表test4_04中。
根据列院系名称dname到pub.department找到对应院系编号did，将对应的院系编号回填到院系名称列dname中，如果表中没有对应的院系名称，则列dname中内容不变仍然是原来的内容。

主要SQL：

update test4_04

set dname=(select  did

from pub.department a

where test4_04.dname=a.dname )

where dname in (select dname from pub.department)

5． 将pub用户下表student_41及数据复制到主用户的表test4_05中，使用alter table语句为表增加4个列："总成绩:sum_score"、 "平均成绩:avg_score"、"总学分:sum_credit"、"院系编号:did varchar(2) "。
（1） 利用pub.student_course、pub.course，统计 "总成绩"；
（2） 利用pub.student_course、pub.course，统计"平均成绩"，四舍五入到小数点后1位；
（3） 利用pub.student_course、pub.course，统计 "总学分"；
（4） 根据院系名称到pub.department或者pub.department_41中，找到对应编号，填写到院系编号中，如果都没有对应的院系，则填写为00。

主要SQL：

update test4_05

set did=(select  did

from pub.department a

where test4_05.dname=a.dname

)

where dname in (

select dname from pub.department

)

 

update test4_05

set did=(select  did

from pub.department_41 a

where test4_05.dname=a.dname

)

where dname in (

select dname from pub.department_41

)

 

update test4_05

set did='00'

where dname not in (

select dname from pub.department

) or dname is null

6． 将pub用户下的Student_42及数据复制到主用户的表test4_06中,对表中的数据进行整理，修复那些不规范的数据：
剔除姓名列中的所有空格；

主要SQL：

create table test4_06 as(

select *

from pub.student_42)

update test4_06

set name=translate(name,'/ ','/')

7． 将pub用户下的Student_42及数据复制到主用户的表test4_07中,对表中的数据进行整理，修复那些不规范的数据：
对性别列进行规范（需要先确定哪些性别数据不规范，也就是那些和大多数不一样的就是不规范的）；

SQL：（区别为多了性字和空格）

update test4_07

set sex=translate(sex,'/性 ','/')

8． 将pub用户下的Student_42及数据复制到主用户的表test4_08中,对表中的数据进行整理，修复那些不规范的数据：
对班级列进行规范（需要先确定哪些班级不规范）。

create table test4_08 as(

select *

from pub.Student_42)

update test4_08

set class=translate(class,'/级 ','/')

9． 将pub用户下的Student_42及数据复制到主用户的表test4_09中,对表中的数据进行整理，修复那些不规范的数据：
年龄为空值的根据出生日期设置学生年龄（截止到2012年的年龄，即年龄=2012-出生年份），年龄不为空值的不要改变。

create table test4_09 as(

select *

from pub.Student_42)

update test4_09

set age=2012-extract(year from birthday)

where age is null

10． 将pub用户下的Student_42及数据复制到主用户的表test4_10中,对表中的数据进行整理，修复那些不规范的数据：
（1） 剔除姓名列中的所有空格；
（2） 剔除院系名称列中的所有空格； 
（3） 对性别列进行规范（需要先确定哪些性别数据不规范，也就是那些和大多数不一样的就是不规范的）；
（4） 对班级列进行规范（需要先确定哪些班级不规范）。
（5） 年龄为空值的根据出生日期设置学生年龄（截止到2012年的年龄，即年龄=2012-出生年份），年龄不为空值的不要改变。

create table test4_10 as(

select *

from pub.Student_42)

update test4_10

set name=translate(name,'/ ','/')

update test4_10

set dname=translate(dname,'/ ','/')

update test4_10

set  sex=translate(sex,'/性 ','/')

update test4_10

set   class=translate(class,'/级 ','/')

update test4_10

set age=2012-extract(year from birthday)

where age is null

实验五

1. 按表中序号在相应窗口执行对应的命令（主用户访问备用用户表需要授权）。

序号	窗口	题号	执行语句	结果
1	备用窗口		Update test5_00 set age=88
2	备用窗口	结果1	select * from test5_00	88
3	备用窗口		Commit
4	备用窗口		Rollback
5	备用窗口		Update test5_00 set age=age+1
6	备用窗口		Rollback
7	备用窗口		Commit
9	备用窗口		Update test5_00 set age=age+2
10	备用窗口		commit
11	备用窗口	结果2	select * from test5_00	90
12	备用窗口		rollback
13	主窗口	结果3	select *  from userbID.test5_00	90
14	备用窗口		Update test5_00 set age=age-2
15	备用窗口		Update test5_00 set age=age-2
16	备用窗口	结果4	select * from test5_00	86
17	主窗口	结果5	select *  from userbID.test5_00	90
18	主窗口		Commit
19	主窗口	结果6	select *  from userbID.test5_00	90
20	主窗口		Rollback
21	主窗口		Update userbID.test5_00 set age=age-10	注意锁等待现象,可以继续后面步骤
22	备用窗口	结果7	select * from test5_00	86
23	备用窗口		Create table test5_01 as select * from test5_00
24	备用窗口		rollback
25	备用窗口	结果8	select *  from userbID.test5_00	86
26	主窗口	结果9	select *  from userbID.test5_00	76
27	主窗口		rollback
28	主窗口	结果10	select *  from userbID.test5_00	86

主要SQL：

create table test5_00 as select * from pub.teacher

grant all on test5_00 to user201700301175

update test5_00 set age = 88                         

select * from test5_00                                 

Commit

Rollback

update test5_00 set age = age + 1

Rollback

Commit

update test5_00 set age = age + 2

Commit

select * from test5_00                                 

Rollback

select * from userb201700301175.test5_00              

update test5_00 set age = age - 2

update test5_00 set age = age - 2

select * from test5_00                                 

select * from userb201700301175.test5_00             

Commit

select * from userb201700301175.test5_00              

Rollback

update userb201700301175.test5_00 set age = age – 10

Select * from test5_00                                 

create table test5_01 as select * from test5_00

Rollback

select * from userb201700301175.test5_00            

select * from userb201700301175.test5_00             

Rollback

select * from userb201700301175.test5_00          

create table test5_10(test varchar(20),age numeric (3))

insert into test5_10 values ('结果1','88')

依次插入结果：88 90 90 86 90 90 86 86 76 86

实验六

1.找出年龄小于20岁且是"物理学院"的学生的学号、姓名、院系名称,按学号排序。

create or replace view test6_01 as

select sid ,name,dname

from pub.student

where age<20 and dname='物理学院'

order by sid

2． 查询统计2009级、软件学院每个学生的学号、姓名、总成绩（列名sum_score）。

create or replace view test6_02 as

select sid ,name,sum(score) sum_score

from pub.student natural join pub.student_course

where class=2009 and dname='软件学院'

group by sid,name

3． 查询2010级、计算机科学与技术学院、操作系统的学生成绩表，内容有学号、姓名、成绩。

create or replace view test6_03 as

select a.sid ,a.name,b.score

from pub.student a, pub.student_course b,pub.course c

where a.class=2010 and a.dname='计算机科学与技术学院' and c.name='操作系统' and a.sid=b.sid and b.cid=c.cid

4． 找出选修"数据库系统"课程，且成绩大于90的学生的学号、姓名

create or replace view test6_04 as

select a.sid ,b.name

from (select sid from pub.student_course natural join pub.course where name ='数据库系统' and score >90) a,pub.student b

where a.sid =b.sid

5． 找出姓名叫"李龙"的学生的学号及其选修全部课程的课程号、课程名和成绩。

create or replace view test6_05 as

select sid,cid,name,score

from (select sid from pub.student where name='李龙')natural join pub.student_course natural join pub.course

6． 找出选修了所有课程的学生的学号、姓名

create or replace view test6_06 as

select a.sid ,a.name

from pub.student a

where not exists(

(select cid from pub.course) minus

(select cid from pub.student_course natural join pub.student where a.sid =sid)

)

7． 找出选修了所有课程并且所有课程全部通过的学生的学号、姓名

create or replace view test6_07 as

select a.sid ,a.name

from pub.student a

where not exists(

(select cid from pub.course) minus

(select cid from pub.student_course natural join pub.student where a.sid =sid and score>=60 )

)

8． 检索先行课的学分为2的课程号、课程名。

create or replace view test6_08 as

select a.cid,a.name

from pub.course a

where exists(

select cid

from pub.course

where cid=a.fcid and credit=2 )

9． 查询统计2010级、化学与化工学院的学生总学分表，内容有学号、姓名、总学分sum_credit。

create or replace view test6_09 as

select sid ,name,sum(credit) sum_credit

from(select sid,name,cid from pub.student natural join pub.student_course where class=2010 and dname='化学与化工学院' and score>=60) natural join (select cid ,credit from pub.course)

group by sid ,name

10． 找出有间接先行课的所有课程的课程号、课程名称。

create or replace view test6_10 as

select a.cid ,a.name

from pub.course a,pub.course b,pub.course c

where a.fcid=b.cid and b.fcid=c.cid