==1

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

create table test1_teacher (

tid char(6) not null,

name varchar(10) not null,

sex char(2),

age int,

dname varchar(30)

)

==3

create table test1_course(

cid char(6) not null,

name varchar(40) not null,

fcid char(6),

credit numeric(4,1)

)

==4

create table test1_student_course(

sid char(12) not null,

cid char(6) not null,

score numeric(5, 1),

tid char(6)

)

==5

create table test1_teacher_course(

tid char(6) not null,

cid char(6) not null

)

==6

insert into test1_student values('200800020101', 'ÍõÐÀ', 'Å®', 19, date '1994-2-2','¼ÆËã»úѧԺ', '2010')

insert into test1_student values('200800020102', 'À', 'Å®', 20, date '1995-3-3','Èí¼þѧԺ', '2009')

insert into test1_student values('200800020103', 'ÕÔÑÒ', 'ÄÐ', 21, date '1996-4-4','Èí¼þѧԺ', '2009')

==7

insert into test1_teacher values('100101', 'ÕÅÀÏʦ', 'ÄÐ', 44, '¼ÆËã»úѧԺ')

insert into test1_teacher values('100102', 'ÀîÀÏʦ', 'Å®', 45, 'Èí¼þѧԺ')

insert into test1_teacher values('100103', 'ÂíÀÏʦ', 'ÄÐ', 46, '¼ÆËã»úѧԺ')

==8

insert into test1_course values('30001', 'Êý¾Ý½á¹¹', null, 2.0)

insert into test1_course values('30002', 'Êý¾Ý¿â', 300001, 2.5)

insert into test1_course values('30003', '²Ù×÷ϵͳ', 300001, 4.0)

==9

insert into test1_student_course values('200800020101', '300001', 91.5, '100101')

insert into test1_student_course values('200800020101', '300002', 92.6, '100102')

insert into test1_student_course values('200800020101', '300003', 93.7, '100103')

==10          

insert into test1_teacher_course values('100101', '300001')

insert into test1_teacher_course values('100102', '300002')

insert into test1_teacher_course values('100103', '300003')

实验二

==01

select sid, name from pub.STUDENT where sid

not in 

(select sid from pub.STUDENT_COURSE)

==02

create table test2_02 as

select c.SID, c.NAME from pub.STUDENT_COURSE a, pub.STUDENT_COURSE b, pub.STUDENT c

where a.SID = '200900130417'  and a.CID = b.CID and b.SID = c.SID

==03

create table test2_03 as

select c.SID, c.NAME from pub.COURSE a, pub.STUDENT_course b, pub.STUDENT c

where a.FCID = '300002' and b.SID = c.SID and a.CID = b.CID

==04

create table test2_04 as

select e.SID, e.NAME from pub.STUDENT_COURSE a natural join pub.COURSE b, pub.STUDENT_COURSE c natural join pub.COURSE d, pub.STUDENT e

where b.NAME = '操作系统' and d.NAME = '数据结构' and a.sid = c.sid and e.SID = a.sid 

==05

create table test2_05 as 

select a.SID, a.NAME, round(avg(b.SCORE),0) avg_score, sum(b.SCORE) sum_score from pub.STUDENT a, pub.student_course b

where a.AGE = 20 and b.SID = a.SID

group by a.SID, a.NAME

==06

create table test2_06 as

select cid, max(score) max_score from pub.student_course

group by cid

==07

create table test2_07 as

select sid, name from pub.STUDENT

where sid

not in

(select sid from pub.student 

where name like '李' or name like '张' or name like ' %')

==08

create table test2_08 as

select substr(name, 1, 1) second_name, count(*) p_count from pub.STUDENT

group by substr(name, 1, 1)

==09

create table test2_09 as

select * from pub.STUDENT natural join pub.STUDENT_COURSE

where cid = '300003'

==10

create table test2_10 as

select a.SID, a.CID from pub.STUDENT_COURSE a

where exists

(select b.SCORE from pub.STUDENT_COURSE b

where a.CID = b.CID)

实验三

==1

create table test3_01 as 

(

select * from pub.STUDENT_31

where trim(translate(sid, '01234567', ' ')) is null

and sid not like '% %'

)

==2

create table test3_02 as

(

select * from pub.STUDENT_31

where age = 2012 - extract(year from birthday)

)

==3

create table test3_03 as

(

select * from pub.STUDENT_31

where sex = '男' 

or sex = '女'

or sex is null

)

==4

create table test3_04 as 

(

select * from pub.STUDENT_31

where dname not like '% %'

and length(dname) > 2

and dname is not null

)

==5

create table test3_05 as

(

select * from pub.STUDENT_31

where class not like '%级'

and class not like '% %'

)

==6

create table test3_06 as

(

select * from pub.STUDENT_31

where trim(translate(sid, '01234567', ' ')) is null

and sid not like '% %'

and age = 2012 - extract(year from birthday)

and length(name) > 1

and name not like '% %'

and dname not like '% %'

and length(dname) > 2

and dname is not null

and class not like '%级'

and class not like '% %'

and (sex = '男' or sex = '女' or sex is null)

)

==7

create table test3_07 as 

(

select * from pub.student_course_32

where sid in 

(

select sid from pub.STUDENT

)

)

==8

create table test3_08 as

(

select count(*) from pub.STUDENT_COURSE_32

where (cid, tid)  in

(

select cid, tid  from pub.TEACHER_COURSE

)

)

==9

create table test3_09 as

(

select * from pub.STUDENT_COURSE_32

where score > -1

and score < 101

)

==10

create table test3_10 as 

(

select * from pub.STUDENT_COURSE_32

where sid in 

(

select sid from pub.STUDENT

)

and cid in 

(

select cid from pub.COURSE

)

and tid in

(

select tid from pub.TEACHER

)

and (cid, tid)  in

(

select cid, tid  from pub.TEACHER_COURSE

)

and score > -1

and score < 101

)

实验四

==1

create table test4_01 as

(select * from pub.STUDENT_41)

alter table test4_01

add sum_score numeric(10, 1)

update test4_01 a 

set sum_score = (

    select sum(score)

    from pub.STUDENT_COURSE b

    where a.SID = b.SID

    group by b.SID

)

==2

create table test4_02 as

(select * from pub.STUDENT_41)

alter table test4_02

add avg_score numeric(10, 1)

update test4_02 a 

set avg_score = (

    select avg(score)

    from pub.STUDENT_COURSE b

    where a.SID = b.SID

    group by b.SID

)

==3

create table test4_03 as

(select * from pub.STUDENT_41)

alter table test4_03

add sum_credit numeric(10, 1)

update test4_03 a 

set sum_credit = (

    select sum(credit)

    from pub.course natural join pub.STUDENT_COURSE

    where a.SID = SID

and score > 59

    group by SID

)

==4

create table test4_04 as

(select * from pub.STUDENT_41)

update test4_04 a 

set dname = (

    select did

    from pub.DEPARTMENT b

    where a.DNAME = b.DNAME

)

where dname in (

    select dname

    from pub.DEPARTMENT

)

==5

create table test4_05 as

(select * from pub.STUDENT_41)

update test4_05

set sum_score = (

    select sum(score)

    from pub.STUDENT_COURSE b

    where a.SID = b.SID

    group by b.SID

)

update test4_05 a 

set avg_score = (

    select avg(score)

    from pub.STUDENT_COURSE b

    where a.SID = b.SID

    group by b.SID

)

update test4_05 a 

set sum_credit = (

    select sum(credit)

    from pub.course natural join pub.STUDENT_COURSE

    where a.SID = SID

and score > 59

    group by SID

)

update test4_05 a 

set did = case

    when a.dname in (

    select dname

    from pub.DEPARTMENT

    )

    then (

    select did

    from pub.DEPARTMENT b

    where a.DNAME = b.DNAME

    )

    when a.DNAME in (

    select dname

    from pub.DEPARTMENT_41

    )

    then (

    select did

    from pub.DEPARTMENT_41 c

    where a.DNAME = c.DNAME

    )

    else '00'

end

==6

create table test4_06 as

(select * from pub.STUDENT_42)

update test4_06

set name = replace(name, ' ', '')

==7

create table test4_07 as

(select * from pub.STUDENT_42)

update test4_07

set sex = replace(sex, ' ', '')

update test4_07

set sex = case

    when sex = '男性' or sex = '男'

    then '男'

    when sex = '女性' or sex = '女'

    then '女'

end

==8

create table test4_08 as

(select * from pub.STUDENT_42)

update test4_08

set class = replace(class, '级', '')

where class like '%级'

==9

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

create table test4_10 as

(select * from pub.STUDENT_42)

update test4_10

set name = replace(name, ' ', '')

update test4_10

set dname = replace(dname, ' ', '')

update test4_10

set sex = replace(sex, ' ', '')

update test4_10

set sex = case

    when sex = '男性' or sex = '男'

    then '男'

    when sex = '女性' or sex = '女'

    then '女'

end

update test4_10

set class = replace(class, '级', '')

where class like '%级'

update test4_10

set age = 2012 - extract(year from birthday)

where age is null

实验五

==1

create table test5_00 as

(select * from pub.TEACHER)

88

90

90

86

90

90

86

86

76

86

实验六

==1

create view test6_01 as (

    select sid, name, dname

    from pub.student

where age < 20

    and dname = '物理学院'

    order by sid

)

==2

create 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

create view test6_03 as (

    select a.SID, a.NAME, b.SCORE from pub.STUDENT a, pub.STUDENT_COURSE b, pub.COURSE c

    where a.SID = b.SID

    and b.CID = c.CID

    and a.CLASS = '2010'

    and a.DNAME = '计算机科学与技术学院'

    and c.NAME = '操作系统'

)

==4

create view test6_04 as (

    select a.SID, a.NAME from pub.STUDENT a, pub.STUDENT_COURSE b, pub.COURSE c

    where a.SID = b.SID

    and b.CID = c.CID

and b.score > 90

    and c.NAME = '数据库系统'

)

==5

create view test6_05 as (

    select c.CID, c.name, b.score from pub.STUDENT a, pub.STUDENT_COURSE b, pub.COURSE c

    where a.SID = b.SID

    and b.CID = c.CID

    and a.name = '李龙'

)

==6

create view test6_06 as (

    select b.name, b.sid from pub.STUDENT b

    where not exists (

        (select cid

        from pub.COURSE)

        minus

        (select a.cid

        from pub.STUDENT_COURSE a

        where a.sid = b.sid)

    )

)

==7

create view test6_07 as (

    select b.name, b.sid from pub.STUDENT b

    where not exists (

        (select cid

        from pub.COURSE)

        minus

        (select a.cid

        from pub.STUDENT_COURSE a

        where a.sid = b.sid

     and a.score > 59)

    )

)

==8

create 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

)

==9

create view test6_09 as (

    select a.sid, a.name, sum(c.credit) sum_credit

    from pub.STUDENT a, pub.STUDENT_COURSE b, pub.COURSE c

    where a.sid = b.sid

    and b.cid = c.cid

and b.SCORE > 59

    and a.class = '2010'

    and a.dname = '化学与化工学院'

    group by a.sid, a.name

)

==10

create 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 (First_name varchar(4), frequency numeric(4))

insert into test7_01 (

    select replace(a.name, substr(a.NAME, 0, 1), '') , count(*) from pub.STUDENT a, pub.STUDENT b

    where replace(a.name, substr(a.NAME, 0, 1), '') = replace(b.name, substr(b.NAME, 0, 1), '')

    group by a.sid, a.name

)

==2

create table test7_02 (

letter varchar(2),

frequency numeric(4)

)

insert into test7_02 (

    select a.letter, count(*) from (

        (select substr(name, 3, 1) letter from pub.STUDENT

        where substr(name, 3, 1) is not null)

        union (--这个地方没有 all

        select substr(name, 2, 1) letter from pub.STUDENT

        )

    ) a, (

        (select substr(name, 3, 1) letter from pub.STUDENT

        where substr(name, 3, 1) is not null)

        union all (--这个地方有 all 

        select substr(name, 2, 1) letter from pub.STUDENT

        )

    ) b

    where a.letter = b.letter

    group by a.letter

)

==3

create table test7_03 (

dname varchar(30),

class varchar(10),

P_count1 int,

P_count2 int,

P_count int 

)

insert into test7_03 (

    select c.dname, c.class, count(*), 0, 0 from pub.STUDENT c, (select sid, sum(credit) sum_credit from pub.student_course a, pub.COURSE b

    where a.CID = b.CID

and a.SCORE > 59

    group by sid) temp

            where c.SID = temp.sid

         and temp.sum_credit > 9

            and c.dname is not null

            group by c.dname, c.class

)

update test7_03 

set p_count2 = (

    select count(*) from pub.STUDENT c, (select sid, sum(credit) sum_credit from pub.student_course a, pub.COURSE b

    where a.CID = b.CID

and a.SCORE > 59

    and c.dname = test7_03.dname

    and c.class = test7_03.class

    group by sid) temp

            where c.SID = temp.sid

         and temp.sum_credit < 10

            and c.dname is not null

            group by c.dname, c.class

)

update test7_03 

set p_count = (

    select count(*) from pub.STUDENT c

    where c.dname is not null

    and c.dname = test7_03.dname

    and c.class = test7_03.class

    group by c.dname, c.class

)

==4

create table test7_04 (

dname varchar(30),

class varchar(10),

P_count1 int,

P_count2 int,

P_count int 

)

insert into test7_04 (

select c.dname, c.CLASS, count(*), 0, 0 from pub.STUDENT c, (select sid, sum(credit) sum_credit from pub.student_course a, pub.COURSE b

    where a.CID = b.CID

and a.SCORE > 59

    group by sid) temp

            where c.SID = temp.sid

         and temp.sum_credit >= 8

         and to_number(c.CLASS) <= 2008

            and c.dname is not null

            group by c.dname, c.class

)

insert into test7_04 (

select c.dname, c.CLASS, count(*), 0, 0 from pub.STUDENT c, (select sid, sum(credit) sum_credit from pub.student_course a, pub.COURSE b

    where a.CID = b.CID

and a.SCORE > 59

    group by sid) temp

            where c.SID = temp.sid

         and temp.sum_credit >= 10

         and to_number(c.CLASS) > 2008

            and c.dname is not null

            group by c.dname, c.class

)

update test7_04

set p_count = (

    select count(*) from pub.STUDENT c

    where c.dname is not null

    and c.dname = test7_04.dname

    and c.class = test7_04.class

    group by c.dname, c.class

)

update test7_04 

set P_count2 = p_count - p_count1

实验八

==1

create table test8_01(Dname varchar(30), Avg_ds_score numeric(10, 1), Avg_os_score numeric(10, 1))

insert into test8_01 (

    select dname, 0, round(avg(max_score) ,0) 

    from pub.STUDENT e, (select sid, max(score) max_score from pub.STUDENT_COURSE a, pub.COURSE b

                        where a.CID = b.CID

                        and b.NAME = '操作系统'

                        group by sid) f

    where e.SID = f.sid

    and e.DNAME is not null

    group by dname 

)

update test8_01

set avg_ds_score = (

    select round(avg(max_score) ,0)

    from pub.STUDENT e, (select sid, max(score) max_score from pub.STUDENT_COURSE a, pub.COURSE b

                        where a.CID = b.CID

                        and b.NAME = '数据结构'

                        group by sid) f

    where e.SID = f.sid

    and e.DNAME = test8_01.DNAME

    and e.DNAME is not null

    group by dname 

)

==2

create table test8_02 (

sid char(12),

name varchar2(10),

dname varchar2(30),

ds_score numeric(10, 1),

os_score numeric(10, 1)

)

insert into test8_02 (

    select s.SID, s.NAME, s.DNAME, f.max_score, e.max_score from pub.STUDENT s, (select sid, max(score) max_score from pub.STUDENT_COURSE a, pub.COURSE b

            where a.CID = b.CID

            and b.NAME = '操作系统'

            group by a.sid) e, (select sid, max(score) max_score from pub.STUDENT_COURSE c, pub.COURSE d

            where c.CID = d.CID

            and d.NAME = '数据结构'

            group by c.sid) f

    where s.SID = e.SID

    and s.SID = f.SID

    and s.DNAME = '计算机科学与技术学院'

)

==3

create table test8_03 (

sid char(12),

name varchar2(10),

dname varchar2(30),

ds_score numeric(10, 1),

os_score numeric(10, 1)

)

insert into test8_03 (

    select c.sid, c.NAME, c.dname, null, null from pub.STUDENT_COURSE a, pub.COURSE b, pub.student c

        where b.NAME = '操作系统' 

        and a.SID = c.SID 

        and a.cid = b.CID

        and c.DNAME = '计算机科学与技术学院'

    union (

    select c.sid, c.NAME, c.dname, null, null from pub.STUDENT_COURSE a, pub.COURSE b, pub.student c

        where b.NAME = '数据结构' 

        and a.SID = c.SID 

        and a.cid = b.CID

        and c.DNAME = '计算机科学与技术学院'

    )

)

update test8_03 

set ds_score = (

    select max(a.score) from pub.STUDENT_COURSE a, pub.COURSE b

        where b.NAME = '数据结构' 

        and a.cid = b.CID

        and a.sid = test8_03.sid

        group by sid

)

update test8_03 

set os_score = (

    select max(a.score) from pub.STUDENT_COURSE a, pub.COURSE b

        where b.NAME = '操作系统' 

        and a.cid = b.CID

        and a.sid = test8_03.sid

        group by sid

)

==4

create table test8_04 (

sid char(12),

name varchar2(10),

dname varchar2(30),

ds_score numeric(10, 1),

os_score numeric(10, 1)

)

insert into test8_04 (

    select s.SID, s.NAME, s.DNAME, null, null from pub.STUDENT s

    where s.DNAME = '计算机科学与技术学院'

)

update test8_04 

set ds_score = (

    select max(a.score) from pub.STUDENT_COURSE a, pub.COURSE b

        where b.NAME = '数据结构' 

        and a.cid = b.CID

        and a.sid = test8_04.sid

        group by sid

)

update test8_04 

set os_score = (

    select max(a.score) from pub.STUDENT_COURSE a, pub.COURSE b

        where b.NAME = '操作系统' 

        and a.cid = b.CID

        and a.sid = test8_04.sid

        group by sid

)下载本文