数据库实验结果

1-6

insert into test1_student_course values(
    '200800020101','300002',92.6,'100102', to_date('20090715101010','yyyymmdd hh24:mi:ss')
)

2-1

create or replace view test2_01 as 
select SID,NAME from pub.student 
where SID not in (select SID from pub.student_course);

2-2

create or replace view test2_02 as 
select pub.student.SID,NAME
from pub.student ,pub.student_course
where (
    pub.student.SID!='200900130417'
    and pub.student.SID in (
        select SID 
        from pub.student_course
        where  CID in (
            select CID 
            from pub.student_course 
            where SID='200900130417'
        )
    )
);

2-3

create or replace view test2_03 as
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'
    )
)

2-4

create or replace view test2_04 as
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='操作系统'
            )
    )
    and SID in(
        select SID
        from pub.student_course
        where CID in(
            select CID 
            from pub.course
            where NAME='数据结构'
        )
    )
);

2-5

create or replace view test2_05 as
select pub.student.SID,NAME,CAST(AVG(score) as decimal(2,0)) avg_score,SUM(score) sum_score
from pub.student,pub.student_course
where pub.student.age=20
and pub.student.SID=pub.student_course.SID
group by pub.student.SID,pub.student.NAME;

 

2-6

create or replace view test2_06 as 
select cid,name, max_score, max_score_count
from(
    pub.course natural left outer join (
        select cid, max(score) max_score
        from pub.student_course
        group by cid
    )natural left outer join (
        select cid,count(distinct sid) max_score_count
        from pub.student_course
        where (cid,score) in (
            select cid , max(score)
            from pub.student_course natural join pub.course
            group by cid
        )
        group by cid
    )
)

 

2-7

create or replace view test2_07 as 
select SID,NAME
from pub.student
where name not like '张%' and name not like '李%' and name not like '王%';

2-8

create or replace view test2_08 as 
select substr(name,0,1) second_name, count(*) p_count
from pub.student
group by substr(name,0,1);

2-9

create or replace view test2_09 as 
select pub.student.sid,name,score
from pub.student,pub.student_course
where pub.student.sid=pub.student_course.sid and pub.student_course.CID=300003;

2-10

create or replace view test2_10 as 
select sid,name
from (
    select pub.student.sid,name,cid,count(*) cnt
    from pub.student,pub.student_course
    where score<60 and pub.student.sid=pub.student_course.sid
    group by pub.student.sid,name,cid
)
where cnt>=2;

 

3-1 为什么不能用'[^0-9]',这个是什么意思？

create table test3_01 as
select * 
from pub.student_31
where regexp_like(sid,'^[[:digit:]]{12}$');

 

 

3-2 不等于关系可以写为a<>b

create table test3_02 as
select * 
from pub.student_31;

delete from test3_02
where not (AGE=2012-extract(year from birthday));

 

 

3-3 SEX=null为什么不行？

create table test3_03 as
select *
from pub.student_31;

delete from test3_03
where not (SEX='男' or SEX='女' or SEX is null);

 

 

3-4

create table test3_04 as
select *
from pub.student_31;

delete from test3_04
where (DNAME like '% %') or (DNAME is null) or length(DNAME)<3;

 

 3-5

create table test3_05 as
select *
from pub.student_31;

delete from test3_05
    where class not in(
    select class 
    from pub.student
);

 

 

3-6

create table test3_06 as
select *
from pub.student_31;

delete from test3_06
where (not regexp_like(sid,'^[[:digit:]]{12}$')) or
(AGE<>2012-extract(year from birthday)) or
(name like '% %') or length(name)<2 or
(not (SEX='男' or SEX='女' or SEX is null)) or
(DNAME like '% %') or (DNAME is null) or length(DNAME)<3 or
length(class)>4;

 

3-7

create table test3_07 as
select *
from pub.Student_course_32;

delete from test3_07
where SID not in (
    select SID
    from pub.student
);

 

3-8

create table test3_08 as
select *
from pub.student_course_32;

delete from test3_08
where (CID,TID) not in (
    select CID,TID
    from pub.teacher_course
);

 

3-9

create table test3_09 as
select *
from pub.student_course_32;

delete from test3_09
where (score) not in (
    select score 
    from pub.student_course
);

 

 

3-10

create table test3_10 as
select *
from pub.student_course_32;

delete from test3_10
where 
SID not in (
    select SID
    from pub.student
) or
CID not in (
    select CID
    from pub.course
) or
TID not in (
    select TID
    from pub.teacher
) or 
(CID,TID) not in (
    select CID,TID
    from pub.teacher_course
) or
score not in (
    select score 
    from pub.student_course
);

 

4-1

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

注意update语句set x=(select ...)是单行子查询

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
    where pub.student_course.SID=test4_01.SID
);

 

4-2

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

create table test4_02 as
select *
from pub.student_41;

alter table test4_02 add avg_score real;

update test4_02
set avg_score=(
    select round(avg(score),1)
    from pub.student_course
    where pub.student_course.SID=test4_02.SID
);

 

4-3

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

create table test4_03 as
select *
from pub.student_41;

alter table test4_03 add sum_credit int;

update test4_03
set sum_credit=(
    with c(SID,s_credit) as (
        select SID,sum(credit)
        from (
            select SID,credit,max(score) score1
            from pub.student_course natural left outer join pub.course
            group by SID,CID,credit
        )
        where score1>=60 and score1 is not null
        group by SID
    )
    select s_credit
    from c
    where c.SID=test4_03.SID
);

 

4-4

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

create table test4_04 as
select *
from pub.student_41;

update test4_04
set Dname=case
    when not exists (
        select dname from pub.department
        where test4_04.dname=pub.department.dname
    )
    then dname
    else (
        select did from pub.department
        where test4_04.dname=pub.department.dname
    )
end;

 

4-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。

create table test4_05 as
select *
from pub.student_41;

alter table test4_05 add (sum_score int,avg_score real,sum_credit int,did varchar(2));

update test4_05
set sum_score=(
    select sum(score)
    from pub.student_course
    where pub.student_course.SID=test4_05.SID
);

update test4_05
set avg_score=(
    select round(avg(score),1)
    from pub.student_course
    where pub.student_course.SID=test4_05.SID
);

update test4_05
set sum_credit=(
    with c(SID,s_credit) as (
        select SID,sum(credit)
        from (
            select SID,credit,max(score) score1
            from pub.student_course natural left outer join pub.course
            group by SID,CID,credit
        )
        where score1>=60 and score1 is not null
        group by SID
    )
    select s_credit
    from c
    where c.SID=test4_05.SID
);

update test4_05 set did='00';

update test4_05 
set did=(    
    select did from pub.department
    where test4_05.dname=pub.department.dname
)
where dname in (
    select dname from pub.department
);

update test4_05 
set did=(    
    select did from pub.department_41
    where test4_05.dname=pub.department_41.dname
)
where dname in (
    select dname from pub.department_41
);

 

4-6

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

如何去除字符串中的空格??

create table test4_06 as
select *
from pub.student_42;

update test4_06
set name=(
    select name from pub.student
    where test4_06.sid=pub.student.sid
);

 

4-7

create table test4_07 as
select *
from pub.student_42;

update test4_07
set SEX=translate(sex,'\性 ','\');

 

4-8

create table test4_08 as
select *
from pub.student_42;

update test4_08
set class=(
    select class from pub.student
    where test4_08.sid=pub.student.sid
);

 

4-9

create table test4_09 as
select *
from pub.student_42;

update test4_09
set age=case 
    when age is null then 2012-extract(year from birthday)
    else age
end;

 

4-10

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 = (
    select class from pub.student
    where test4_10.sid=pub.student.sid
);

update test4_10
set age=case 
    when age is null then 2012-extract(year from birthday)
    else age
end;

 

5-1

create table test5_01 (First_name varchar(4),frequency numeric(4));

insert into test5_01 (first_name,frequency)
select substr(name,2),count(*)
from pub.student
group by substr(name,2);

 

5-2

create table test5_02 (letter varchar(2),frequency numeric(4));

insert into test5_02
with S(let) as(
    (
        select substr(name,2,1) from pub.student
    )
    union all (
        select substr(name,3,1) from pub.student
        where substr(name,3,1) is not null
    )
)
select let,count(*)
from S
group by let;

 

5-3

create table test5_03 (dname varchar(30),class varchar(10),p_count1 int,p_count2 int,p_count int);

insert test5_03
select dname,class,null,null,null
from pub.student
where dname is not null
group by dname,class;

update test5_03
set p_count=(
    select count(*)
    from pub.student
    where pub.student.class=test5_03.class and pub.student.dname=test5_03.dname
    group by pub.student.class,pub.student.dname
);

update test5_03
set p_count1=(
    
    select count(*)
    from (
        with S as(
            select *
            from (pub.student_course natural join pub.student) join pub.course using (CID)
        )
        select sum(credit),S.class,S.dname
        from S
        where score>=60
        group by SID,S.class,S.dname
        having sum(credit)>=10
    ) u
    where u.class=test5_03.class and u.dname=test5_03.dname
);

update test5_03
set p_count2=p_count-p_count1;

 

5-4

create table test5_04 (dname varchar(30),class varchar(10),p_count1 int,p_count2 int,p_count int);

insert into test5_04
select dname,class,null,null,null
from pub.student
where dname is not null
group by dname,class;

update test5_04
set p_count=(
    select count(*)
    from pub.student
    where pub.student.class=test5_04.class and pub.student.dname=test5_04.dname
    group by pub.student.class,pub.student.dname
);

update test5_04
set p_count1=(
    select count(*)
    from (
        with S as(
            select *
            from (pub.student_course natural join pub.student) join pub.course using (CID)
        )
        select sum(credit),S.class,S.dname
        from S
        where score>=60
        group by SID,S.class,S.dname
        having sum(credit)>=10
    ) u
    where u.class=test5_04.class and u.dname=test5_04.dname and u.class>'2008'
)
where class>'2008';

update test5_04
set p_count1=(
    select count(*)
    from (
        with S as(
            select *
            from (pub.student_course natural join pub.student) join pub.course using (CID)
        )
        select sum(credit),S.class,S.dname
        from S
        where score>=60
        group by SID,S.class,S.dname
        having sum(credit)>=8
    ) u
    where u.class=test5_04.class and u.dname=test5_04.dname and u.class<='2008'
)
where class<='2008';

update test5_04
set p_count2=p_count-p_count1;

 

5-5

create table test5_05(dname varchar(30),Avg_ds_score int,Avg_os_score int);

insert into test5_05
select dname,null,null
from pub.student
where dname is not null
group by dname;

update test5_05
set Avg_ds_score=(
    select round(avg(mx),0)
    from (
        with S(score,d,SID) as(
            select score,dname d,SID
            from (pub.student_course natural join pub.course) join pub.student using (SID)
            where pub.course.name='数据结构'
        )
        select max(score) mx,d
        from S
        group by SID,d
    )
    group by d
    having d=test5_05.dname
);


update test5_05
set Avg_os_score=(
    select round(avg(mx),0)
    from (
        with S(score,d,SID) as(
            select score,dname d,SID
            from (pub.student_course natural join pub.course) join pub.student using (SID)
            where pub.course.name='操作系统'
        )
        select max(score) mx,d
        from S
        group by SID,d
    )
    group by d
    having d=test5_05.dname
);

 

5-6

create table test5_06 (sid char(12),name varchar(10),dname varchar(30),ds_score int,os_score int);

insert into test5_06
select sid,name,dname,null,null
from pub.student
where dname='计算机科学与技术学院' and sid in (
    select sid from pub.student_course natural join pub.course
    where name='数据结构'
)and sid in(
    select sid from pub.student_course natural join pub.course
    where name='操作系统'
);

update test5_06
set ds_score=(
    select max(score) from pub.student_course natural join pub.course
    where pub.student_course.SID=test5_06.SID and pub.course.name='数据结构'
);

update test5_06
set os_score=(
    select max(score) from pub.student_course natural join pub.course
    where pub.student_course.SID=test5_06.SID and pub.course.name='操作系统'
);

 

5-7

create table test5_07(sid char(12),name varchar(10),dname varchar(30),ds_score number(22,5),os_score number(22,5));

insert into test5_07
select sid,name,dname,null,null
from pub.student
where dname='计算机科学与技术学院' and sid in (
    select sid from pub.student_course natural join pub.course
    where pub.course.name='数据结构' or pub.course.name='操作系统'
);

update test5_07
set ds_score=(
    select max(score) from pub.student_course natural join pub.course
    where pub.student_course.SID=test5_07.SID and pub.course.name='数据结构'
)
where test5_07.sid in (
    select sid from pub.student_course natural join pub.course
    where pub.course.name='数据结构'
);

update test5_07
set os_score=(
    select max(score) from pub.student_course natural join pub.course
    where pub.student_course.SID=test5_07.SID and pub.course.name='操作系统'
)
where test5_07.sid in (
    select sid from pub.student_course natural join pub.course
    where pub.course.name='操作系统'
);

 

5-8

create table test5_08(sid char(12),name varchar(10),dname varchar(30),ds_score number(22,5),os_score number(22,5));

insert into test5_08
select sid,name,dname,null,null
from pub.student
where pub.student.dname='计算机科学与技术学院';

update test5_08
set ds_score=(
    select max(score) from pub.student_course natural join pub.course
    where pub.student_course.sid=test5_08.sid and pub.course.name='数据结构'
)
where test5_08.sid in (
    select sid from pub.student_course natural join pub.course 
    where pub.course.name='数据结构'
);

update test5_08
set os_score=(
    select max(score) from pub.student_course natural join pub.course
    where pub.student_course.sid=test5_08.sid and pub.course.name='操作系统'
)
where test5_08.sid in (
    select sid from pub.student_course natural join pub.course 
    where pub.course.name='操作系统'
);

 

6-1

create or replace view test6_01 as
select SID,name,dname
from pub.student
where age<20 and dname='物理学院'
order by SID;

 

6-2

create or replace view test6_02 as
select * from 
((select SID,name from pub.student where class=2009 and dname='软件学院' )
natural left outer join
(select SID,name,sum(score) sum_score
from pub.student natural join pub.student_course
where class=2009 and dname='软件学院' 
group by SID,name));

 

6-3

create or replace view test6_03 as
select SID,pub.student.name,score
from (pub.student natural join pub.student_course) join pub.course using (CID)
where class=2010 and dname='计算机科学与技术学院' and pub.course.name='操作系统';

 

6-4

create or replace view test6_04 as
select SID,pub.student.name
from (pub.student natural join pub.student_course) join pub.course using (CID)
where pub.course.name='数据库系统' and score>90;

 

6-5

create or replace view test6_05 as
select SID,CID,pub.course.name,score
from (pub.student natural join pub.student_course) join pub.course using (CID)
where pub.student.name='李龙';

 

6-6

create or replace view test6_06 as
select SID,name 
from pub.student 
where not exists(
    select CID from pub.course
    where not exists(
        select SID,CID from pub.student_course
        where pub.student.SID=pub.student_course.SID and pub.course.CID=pub.student_course.CID
    )
);

 或者

create or replace view test6_06 as
select SID,name 
from pub.student 
where not exists(
    select CID from pub.course
    where CID not in(
        select CID from pub.student_course
        where pub.student_course.SID=pub.student.SID
    )
);

 

6-7

create or replace view test6_07 as
select SID,name from pub.student
where not exists(
    select CID from pub.course 
    where not exists(
        select SID,CID from pub.student_course
        where pub.student.SID=pub.student_course.SID and pub.course.CID=pub.student_course.CID and score>=60
    )
);

 

6-8

create or replace view test6_08 as
select c1.CID,c1.name
from pub.course c1,pub.course c2
where c1.fcid=c2.cid and c2.credit=2;

 

6-9

create or replace view test6_09 as
select SID,pub.student.name,sum(credit) sum_credit
from (pub.student natural join pub.student_course) join pub.course using (CID)
where class=2010 and dname='化学与化工学院' and score>=60 
group by SID,pub.student.name;

 

6-10

create or replace view test6_10 as
select CID,name
from pub.course c1
where exists(
    select * from pub.course c2
    where c1.FCID=c2.CID and exists(
        select * from pub.course c3
        where c2.FCID=c3.CID
    )
);

 

7-1

create index t1 on test7_01(substr(name,1,1));

 

7-2

create index t2 on test7_02(birthday);

 

7-3

create or replace view test7_03 as
select * from
(select sid,name,birthday,
(select count(*) from pub.student
where name like concat(substr(t1.name,1,1),'%')
) samefirstname 
from pub.student_testindex t1) where samefirstname=7;

 

7-4

create or replace view test7_04 as
select * from 
(select sid,name,birthday,
(select count(*) from pub.student 
where birthday between trunc(t1.birthday,'mm') and last_day(t1.birthday)
) sameyearmonth,
(select count(*) from pub.student 
where birthday between trunc(t1.birthday,'yyyy') and last_day(add_months(trunc(t1.birthday,'yyyy'),11))
) sameyear
 from pub.student_testindex t1) where sameyearmonth=35;

 

7-5

create or replace view test7_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;

 

8-1

 

9-1

create table test9_01(SID char(12),name varchar(10),sex varchar(10),age number(22,0),birthday date,dname varchar(30),class varchar(10));

create unique index index_1 on test9_01(SID);

insert into test9_01
select * from pub.student
where pub.student.sex='女';

insert into test9_01
select * from pub.student_11_1 
where pub.student_11_1.SEX='女' and pub.student_11_1.SID not in (
    select SID from test9_01
);

insert into test9_01
select * from pub.student_11_2 
where pub.student_11_2.SEX='女' and pub.student_11_2.SID not in (
    select SID from test9_01
);

 

9-2

create table test9_02(SID char(12),name varchar(10),sex varchar(10),age number(22,0),birthday date,dname varchar(30),class varchar(10));

create unique index index_2 on test9_02(SID);

insert into test9_02
select * from pub.student 
where pub.student.SEX='女' and pub.student.SID in (
    select SID from pub.student_course
    where score<60
);

insert into test9_02
select * from pub.student_11_1 
where pub.student_11_1.SEX='女' and pub.student_11_1.SID in (
    select SID from pub.student_course
    where score<60
) and pub.student_11_1.SID not in (
    select SID from test9_02
);

insert into test9_02
select * from pub.student_11_2 
where pub.student_11_2.SEX='女' and pub.student_11_2.SID in (
    select SID from pub.student_course
    where score<60
) and pub.student_11_2.SID not in (
    select SID from test9_02
);