山東大學軟體學院2017-18學年資料庫實驗五、六、七、八

實驗步驟：（不要求羅列完整原始碼）

create table test5_00 as select * from pub.TEACHER

grant all on test5_00 to user201600301315

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 userb201600301315.test5_00              

update test5_00 set age = age - 2

update test5_00 set age = age - 2

select * from test5_00                                 

select * from userb201600301315.test5_00             

Commit

select * from userb201600301315.test5_00              

Rollback

update userb201600301315.test5_00 set age = age – 10

Select * from test5_00                                 

create table test5_01 as select * from test5_00

Rollback

select * from userb201600301315.test5_00            

select * from userb201600301315.test5_00             

Rollback

select * from userb201600301315.test5_00            

結果為：88 90 90 86 90 90 86 86 76 86

實驗步驟：（不要求羅列完整原始碼）

1. 找出年齡小於20歲且是“物理學院”的學生的學號、姓名、院系名稱，按學號排序

create view test6_01

as select sid,name,dname

from pub.STUDENT

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

order by sid

1. 查詢統計2009級、軟體學院的每個學生的學號、姓名、總成績（sum_score）

create view test6_02 as

select pub.STUDENT.SID sid,pub.STUDENT.NAME name,sum(score) sum_score

from pub.STUDENT,pub.STUDENT_COURSE

where pub.STUDENT.SID=pub.STUDENT_COURSE.SID

and pub.STUDENT.CLASS=2009

and pub.STUDENT.DNAME='軟體學院'

group by pub.STUDENT.SID,pub.STUDENT.NAME

1. 查詢統計2010級、電腦科學與技術學院、作業系統的學生成績表，內容有學號姓名成績。

create view test6_03 as

select pub.STUDENT.SID sid,pub.STUDENT.NAME name,pub.STUDENT_COURSE.SCORE score

from pub.STUDENT,pub.STUDENT_COURSE,pub.COURSE

where pub.STUDENT_COURSE.CID=pub.COURSE.CID

and pub.STUDENT.SID=pub.STUDENT_COURSE.SID

and pub.STUDENT.CLASS=2010

and pub.STUDENT.DNAME='電腦科學與技術學院'

and pub.COURSE.NAME = '作業系統'

1. 找出選修“資料庫系統”課程且成績大於90學生的學號、姓名

create view test6_04 as select pub.STUDENT.SID sid,pub.STUDENT.NAME name

from pub.STUDENT,pub.STUDENT_COURSE,pub.COURSE

where pub.STUDENT_COURSE.CID=pub.COURSE.CID

and pub.STUDENT.SID=pub.STUDENT_COURSE.SID

and pub.COURSE.NAME = '資料庫系統'

and pub.STUDENT_COURSE.SCORE>90

1. 找出姓名叫“李龍”的學生的學號及其選修全部課程的課程號、課程名和成績

create view test6_05 as select pub.STUDENT.SID sid,pub.STUDENT_COURSE.CID cid,pub.COURSE.NAME,pub.STUDENT_COURSE.SCORE

from pub.STUDENT,pub.STUDENT_COURSE,pub.COURSE

where pub.STUDENT_COURSE.CID=pub.COURSE.CID

and pub.STUDENT.SID=pub.STUDENT_COURSE.SID

and pub.STUDENT.NAME='李龍'

1. 找出選修了所有課程的學生的學號、姓名

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 natural join pub.STUDENT_COURSE

where a.sid = sid)）

1. 找出選修了所有課程並且所有課程全部通過的學生的姓名、學號

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 natural join pub.STUDENT_COURSE

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

1. 檢索先行課的學分為2的課程號、課程名

create or replace view test6_08 as select A.cid, A.NAME

from pub.COURSE A, pub.COURSE B

where A.FCID = B.CID and B.CREDIT = 2

1. 查詢統計2010級、化學與化工學院的學生總學分表，內容有學號、姓名、總學分sum_credit

注：學分為成績大於等於60才算

create or replace view test6_09 as

select pub.STUDENT.SID sid,pub.STUDENT.NAME name,sum(credit) sum_credit

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.STUDENT.CLASS=2010

and pub.STUDENT.DNAME='化學與化工學院'

and pub.STUDENT_COURSE.SCORE>=60                     

group by pub.STUDENT.SID,pub.STUDENT.NAME

1. 找出有間接先行課的所有課程的課程號、課程名稱

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

實驗步驟：（不要求羅列完整原始碼）

1. 統計名字的使用頻率

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.統計名字每個字的使用頻率

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,1)) letter from pub.STUDENT where substr(name,3,1) is not NULL))

group by letter

3.統計學院班級學分達標情況統計表1

create table test7_03 as

select * from

(select dname, class, (count(*)) P_count1

 from (select S.sid, S.DNAME, S.CLASS, sum_credit

    from pub.STUDENT S,

            (select sid, (sum(credit)) sum_credit

         from pub.STUDENT_COURSE SC, pub.COURSE C

         where SC.CID = C.CID

         and SC.SCORE >= 60

         and dname is not NULL

         group by sid) B

    where S.SID = B.SID)

 where sum_credit > = 10

 and dname is not NULL

 group by dname, class)

natural full outer join

(select dname, class, (count(*)) P_count2

 from (select S.sid, S.DNAME, S.CLASS, sum_credit

       from pub.STUDENT S,

            (select sid, (sum(credit)) sum_credit

         from pub.STUDENT_COURSE SC, pub.COURSE C

         where SC.CID = C.CID

         and SC.SCORE >= 60

             and dname is not NULL

         group by sid) B

       where S.SID = B.SID)

 where sum_credit < 10

 and dname is not NULL

 group by dname, class)

natural full outer join

(select *

 from (select dname, class, (count(*)) P_count

       from pub.STUDENT

       where dname is not NULL

       group by dname, class) sum_count)

4.統計學院班級學分達標情況統計表2

create table test7_04 as

select * from

(select dname, class, (count(*)) P_count1

    from (select A.sid, A.DNAME, A.CLASS, sum_credit

       from pub.STUDENT A, (select sid, (sum(credit)) sum_credit

       from pub.STUDENT_COURSE S, pub.COURSE K

       where S.CID = K.CID

       and S.SCORE >= 60

       group by sid) B

       where A.SID = B.SID and A.DNAME is not NULL)

       where (sum_credit > = 10)

    or (sum_credit > = 8 and class <= 2008)   group by dname, class)

natural full outer join

(select dname, class, (count(*)) P_count2

    from (select A.sid, A.DNAME, A.CLASS, sum_credit

       from pub.STUDENT A, (select sid, (sum(credit)) sum_credit

       from pub.STUDENT_COURSE S, pub.COURSE K

       where S.CID = K.CID

       and S.SCORE >= 60

       group by sid) B

       where A.SID = B.SID)

    where sum_credit < 10

    and dname is not NULL

    group by dname, class)

natural full outer join

(select *

from (select dname, class, (count(*)) P_count

    from pub.STUDENT

    where dname is not NULL

    group by dname, class) sum_count) 

實驗步驟：（不要求羅列完整原始碼）

1. 查詢各院系的資料結構平均成績和作業系統平均成績。

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 full outer 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)

1. 查詢電腦科學與技術學院同時選修了資料結構、作業系統兩門課的學生詳細資訊

create table test8_02 as

select A.SID, name, dname, ds_score, os_score

from

(select pub.STUDENT.SID, 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 = '資料結構'

group by pub.STUDENT.SID) A,

(select pub.STUDENT.SID, 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 = '作業系統'

group by pub.STUDENT.SID) B,

pub.STUDENT C

where A.SID = B.SID and B.SID = C.SID and dname = '電腦科學與技術學院'

1. 查詢電腦科學與技術學院選修了資料結構或作業系統的學生詳細資訊

create table test8_03 as

select sid, name, dname, ds_score, os_score

from (select * from

(select pub.STUDENT.SID, 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 = '資料結構'

group by pub.STUDENT.SID)

natural full outer join

(select pub.STUDENT.SID, 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 = '作業系統'

group by pub.STUDENT.SID)

) 

natural join pub.STUDENT

where dname = '電腦科學與技術學院'

1. 查詢電腦科學與技術學院所有學生的資訊

create table test8_04 as

select sid, name, dname, ds_score, os_score

from (select * from

(select pub.STUDENT.SID, 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 = '資料結構'

group by pub.STUDENT.SID)

natural full outer join

(select pub.STUDENT.SID, 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 = '作業系統'

group by pub.STUDENT.SID)

) 

natural right outer join pub.STUDENT

where dname = '電腦科學與技術學院'