目录

前言

实验代码

实验一

1-1

1-2 

1-3 

1-4 

1-5 

1-6 

实验二

2-1

 2-2

2-3 

2-4

2-5 

2-6

2-7

2-8

2-9

2-10

实验三 

3-1

3-2

3-3

3-4

3-5

3-6

3-7

3-8

3-9

3-10

实验四 

4-1

4-2

4-3

4-4

4-5

4-6

4-7

4-8

4-9

4-10

实验五 

5-1

5-2

5-3

5-4

5-5

5-6

5-7

5-8

5-9

5-10

实验六 

6-1

6-2

6-3

6-4

6-5

6-6

6-7

6-8

6-9

6-10

 实验七

7-1

7-2

7-3

7-4

7-5

实验八 

实验九 

9-1

9-2

更详细题目讲解

---

前言

这个是2023年山东大学软件学院数据库实验,使用的数据库管理系统为oracle

实验代码全部通过,并且已经拿到满分，但是还是希望学弟学妹们能够参考着去完成自己的实验，而不是直接copy。因为完成实验能够大大提高我们写sql的能力，这可不是看看书能比的哦！

2024年甚至更远的未来的同学们参考时要留一个心眼，因为每一年实验的题目都会有一点点的变化，要仔细阅读题目要求。

实验代码

实验一

1-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)
);

1-2 

create table test1_course(
       cid char(6) not null,
       name varchar(40) not null,
       fcid char(6),
      credit numeric(4，1)
)

1-3 

create table test1_student_course(
      sid char(12) not null,
      cid char(6) not null,
      score numeric(5,1),
      tid char(6),
      sctime date
);

1-4 

insert into test1_student values('201800020101','王欣','女',21,to_date('19940202','yyyymmdd'),'计算机学院','2010');
 
insert into test1_student values('201800020102','李华','女',20,to_date('19950303','yyyymmdd'),'软件学院','2009');

1-5 

insert into test1_course values('800001','数据结构',null,2);
insert into test1_course values('800002','数据库',800001,2.5)

1-6 

insert into test1_student_course values
     ('201800020101','300001',91.5,'200101',
      to_date(' 2009-07-15 09:09:09','yyyy-mm-dd hh24-mi-ss'));
 
insert into test1_student_course values
     ('201800020101','300002',92.6,'200102',
      to_date(' 2009-07-15 10:10:10','yyyy-mm-dd hh24-mi-ss'));

实验二

2-1

create view test2_01 as
  select sid,name
  from pub.student
  where sid not in
  (
    select distinct sid
    from pub.student_course
  )

 2-2

create view test2_02 as
  select sid,name
  from pub.student
  where sid in
  (
    select sid
    from pub.student_course
    where cid in
      (
        select cid
        from pub.student_course
        where sid='200900130417'
      )
  )
  minus
  (
    select sid, name
		from pub.student
		where sid ='200900130417'
  )

2-3 

create 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 view test2_04 as
  select sid，name
  from pub.student
  where sid in
  (
    (
      select sid
      from pub.student_course
      where cid=(
        select cid
        from pub.course
        where name='操作系统'
      )
    )
    intersect
    (
      select sid
      from pub.student_course
      where cid=(
        select cid
        from pub.course
        where name='数据结构'
      )
    )
    minus
    (
      select sid
      from pub.student_course
      where cid=(
        select cid
        from pub.course
        where name='程序设计语言'
      )
    )
  )

2-5 

create view test2_05 as
  select sid,cid,name,score
  from pub.student_course natural join pub.course
  where sid in
  (
    select sid
    from pub.student
    where name='李龙'
  )

2-6

create view test2_06 as
  select sid,name,score
  from pub.student_course natural join pub.student
  where class='2010' and dname='计算机科学与技术学院' and cid=(
    select cid
    from pub.course
    where name='操作系统'
  )

2-7

create 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 view test2_08 as
select cid,name
from pub.course
where fcid in(
  select cid
  from pub.course
  where fcid is not null
)

2-9

create view test2_09 as
select sid,name,score
from pub.student_course natural join pub.student
where cid='300003'

2-10

create view test2_10 as
select sid,name
from pub.student
where not exists(
  select cid
  from pub.course
  where not exists(
    select *
    from pub.student_course
    where pub.student_course.sid=pub.student.sid and pub.student_course.cid=pub.course.cid
  )
)

实验三 

3-1

create table test3_01 as
	select *
	from pub.student_31
delete from test3_01
where sid not in
	(select sid
	from test3_01
	where regexp_like(sid, '^[0-9]+$')) --检验字符串是否是全数字

3-2

create table test3_02 as
	select *
	from pub.student_31
delete from test3_02
where age <> 2012 - extract(year from birthday)

3-3

create table test3_03 as
	select *
	from pub.student_31

delete from test3_03
where sex <> '男' and sex <> '女' and sex is not 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 test3_05
	where regexp_like(class, '^[0-9]+$'))

3-6

create table test3_06 as
	select *
	from pub.student_31
delete
from test3_06
where not regexp_like(sid, '^[0-9]+$')
or age <> 2012 - extract(year from birthday)
or name like '% %'
or length(name) < 2
or sex in
	(select distinct sex
	from test3_06
	where sex <> '男'
		and sex <> '女'
		and sex is not null)
or dname like '% %'
or dname is null
or length(dname) < 3
or class not in
	(select class
	from test3_05
	where regexp_like(class, '^[0-9]+$'))

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 T
where not exists(
  select cid, tid
  from pub.teacher_course A
  where T.cid=A.cid and T.tid=A.tid
)

3-9

create table test3_09 as
	select *
	from pub.student_course_32
delete
from test3_09
where score not between 0 and 100

3-10

create table test3_10 as
	select *
	from pub.student_course_32
delete
from test3_10 S
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 not exists
	(select cid, tid
	from pub.teacher_course T
	where S.cid = T.cid
	and S.tid = T.tid)
or score not between 0 and 100

实验四 

4-1

create table test4_01 as
  select * from pub.student_41
 
alter table test4_01
  add sum_score int
 
update test4_01 S
  set sum_score=(
    select sum(score)
    from pub.student_course T
    where S.sid=T.sid
  )

4-2

create table test4_02 as
  select * from pub.student_41
 
alter table test4_02
  add avg_score numeric(3,1)
 
update test4_02 S
  set avg_score=(
    select avg(score)
    from pub.student_course T
    where S.sid=T.sid
  )

4-3

create table test4_03 as
  select * from pub.student_41
 
alter table test4_03
  add sum_credit int
 
update test4_03 S
  set sum_credit=(
    select sum(credit)
    from pub.course
    where cid in(
      select distinct cid
      from pub.student_course SC
      where S.sid=SC.sid
      and SC.score>=60
    )
  )

4-4

create table test4_04 as
  select * from pub.student_41
 
update test4_04 T
set dname=(
  select did
  from pub.department D
  where T.dname=D.dname
)
where T.dname in(
  select dname
  from pub.department
)

4-5

create table test4_05 as
  select * from pub.student_41
 
alter table test4_05 
  add sum_score int
 
alter table test4_05 
  add avg_score numeric(3, 1)
 
alter table test4_05 
  add sum_credit int
 
alter table test4_05 
  add did varchar(2)
update test4_05 T
set 
  sum_score=(
    select sum(score)
  	from pub.student_course SC
  	where T.sid = SC.sid
  ),
  avg_score=(
    select avg(score)
    from pub.student_course SC
    where T.sid=SC.sid
  ),
  sum_credit=(
    select sum(credit)
    from pub.course
    where cid in(
      select distinct cid
      from pub.student_course SC
      where T.sid=SC.sid
      and SC.score>=60
      )
   )
 
update test4_05 T
set did=(
    select did
    from (
      select did,dname
      from pub.department
    union
      select did,dname
      from pub.department_41
    ) department
    where T.dname=department.dname
  ）
 
update test4_05
set did='00'
where did is null;

4-6

create table test4_06 as
  select * from pub.student_42
update test4_06
set name=replace(name,' ','')

4-7

create table test4_07 as
  select * from pub.student_42

update test4_07
set sex= replace( sex,' ','')
 
update test4_07
set sex= replace( sex,'性','')

4-8

create table test4_08 as
  select * from pub.student_42

update test4_08
set class=replace(class,'级','');

4-9

create table test4_09 as
  select * from pub.student_42

update test4_09 T
set age=(
  select 2012-extract (year from birthday)
  from test4_09 S
  where T.sid=S.sid
  )
where T.age is null;

4-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 age=(
select (2012-extract (year from birthday))
from pub.student_42
where test4_10.sid=sid
)
where test4_10.age is null;
 
update test4_10
set sex= replace( sex,' ','');
update test4_07
set sex= replace( sex,'性','');
 
update test4_10
set class=replace(class,'级','');

实验五 

5-1

create table test5_01(
  First_name varchar(4),
  frequency numeric(4)
)
insert into test5_01(
  select substr(name,2),count(*)
  from pub.student
  group by substr(name,2)
)

5-2

create table test5_02(
  letter varchar(4),
  frequency numeric(4)
)
insert into test5_02(
  select letter,count(*)
  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
)

5-3

create table test5_03
(
    dname varchar2(30),
    class varchar(10),
    p_count1 int,
    p_count2 int,
    p_count int
)
insert into test5_03(dname,class,p_count)(
  select dname,class,count(sid)
  from pub.student
  where dname is not null
  group by dname,class
)
update test5_03 t
  set p_count1=(
    select count(sid)
    from(
      select S.sid,S.dname,S.class
      from pub.student S,pub.course C,(
            SELECT sid, cid, MAX(score) AS max_score
            FROM pub.student_course
            GROUP BY sid, cid
        ) SC
      where S.sid = SC.sid and C.cid=SC.cid and SC.max_score >= 60
      group by S.sid,S.dname,S.class
      having sum(C.credit)>=10
    )a
    where t.dname=a.dname
    and t.class=a.class
  )
update test5_03 t
  set p_count2=(
    select count(sid)
    from(
      (
        select S.dname,S.class,S.sid
        from pub.student S,pub.course C,(
            SELECT sid, cid, MAX(score) AS max_score
            FROM pub.student_course
            GROUP BY sid, cid
        ) SC
        where SC.sid=S.sid
        and SC.cid=C.cid
        and SC.max_score>=60
        group by S.dname,S.class,S.sid
        having sum(C.credit)<10
      ) 
    union
      (
        select dname,class,sid
        from pub.student
        where sid not in(
            select sid
            from pub.student_course
          )
      )
    )a
    where t.dname=a.dname
    and t.class=a.class
  )

5-4

create table test5_04 
(
    dname varchar2(30),
    class varchar(10),
    p_count1 int,
    p_count2 int,
    p_count int
)
update test5_04 t set p_count1 = 
(select count(sid)
from 
(
	(select S.sid,S.dname,S.class
	from pub.student S,pub.course C,(
            SELECT sid, cid, MAX(score) AS max_score
            FROM pub.student_course
            GROUP BY sid, cid
        ) SC
	where S.sid = SC.sid and C.cid=SC.cid and SC.max_score >= 60 and S.class > 2008
	group by S.sid,S.dname,S.class
	having sum(C.credit) >= 10) 
	union
    (select S.sid,S.dname,S.class
	from pub.student S,pub.course C,(
            SELECT sid, cid, MAX(score) AS max_score
            FROM pub.student_course
            GROUP BY sid, cid
        ) SC
	where S.sid = SC.sid and C.cid=SC.cid and SC.max_score >= 60 and S.class <= 2008
	group by S.sid,S.dname,S.class
	having sum(C.credit) >= 8)
) temp
where t.dname = temp.dname and t.class  = temp.class)
update test5_04 t set p_count2 = 
(
	select count(sid)
	from 
	(
		(select S.sid,S.dname,S.class
		from pub.student S,pub.course C,(
            SELECT sid, cid, MAX(score) AS max_score
            FROM pub.student_course
            GROUP BY sid, cid
        ) SC
		where S.sid = SC.sid and C.cid=SC.cid and SC.max_score >= 60 and S.class > 2008
		group by S.sid,S.dname,S.class
		having sum(C.credit) < 10) 
		union 
        (select S.sid,S.dname,S.class
		from pub.student S,pub.course C,(
            SELECT sid, cid, MAX(score) AS max_score
            FROM pub.student_course
            GROUP BY sid, cid
        ) SC
		where S.sid = SC.sid and C.cid=SC.cid and SC.max_score >= 60 and S.class <= 2008
		group by S.sid,S.dname,S.class
		having sum(C.credit) < 8) 
		union
		(
			select sid,dname,class 
			from pub.student 
			where sid 
			not in
			(
				select sid 
				from pub.student_course
			)
		)
) temp
where t.dname = temp.dname and t.class  = temp.class)

5-5

create view test5_05 as (
select *
from
  (
    select S.dname,round(avg(SC.max_score)) avg_ds_score
    from pub.student S,pub.course C,(
      select sid,cid,max(score) max_score
      from pub.student_course
      group by sid,cid
    )SC
    where S.sid=SC.sid and C.cid=SC.cid and C.name='数据结构' and S.dname is not null
    group by S.dname
  )
  natural full outer join
  (
    select S.dname,round(avg(SC.max_score)) avg_os_score
    from pub.student S,pub.course C,(
      select sid,cid,max(score) max_score
      from pub.student_course
      group by sid,cid
    )SC
    where S.sid=SC.sid and C.cid=SC.cid and C.name='操作系统' and S.dname is not null
    group by S.dname
  )
)

5-6

create view test5_06 as (
select *
from
  (
    select S.sid,S.name,S.dname,SC.max_score ds_score
    from pub.student S,pub.course C,(
          select sid,cid,max(score) max_score
          from pub.student_course
          group by sid,cid
        )SC
    where S.sid=SC.sid and C.cid=SC.cid and S.dname='计算机科学与技术学院' and C.name='数据结构' and S.sid in(
      (
        select sid
        from pub.student_course SC,pub.course C
        where C.cid=SC.cid and C.name='数据结构'
      )
      intersect
      (
        select sid
        from pub.student_course SC,pub.course C
        where C.cid=SC.cid and C.name='操作系统'
      )
    )
  )
  natural full outer join
  (
    select S.sid,S.name,S.dname,SC.max_score os_score
    from pub.student S,pub.course C,(
          select sid,cid,max(score) max_score
          from pub.student_course
          group by sid,cid
        )SC
    where S.sid=SC.sid and C.cid=SC.cid and S.dname='计算机科学与技术学院' and C.name='操作系统' and S.sid in(
      (
        select sid
        from pub.student_course SC,pub.course C
        where C.cid=SC.cid and C.name='数据结构'
      )
      intersect
      (
        select sid
        from pub.student_course SC,pub.course C
        where C.cid=SC.cid and C.name='操作系统'
      )
    )
  )
)

5-7

create view test5_07 as (
select *
from
  (
    select S.sid,S.name,S.dname,SC.max_score ds_score
    from pub.student S,pub.course C,(
          select sid,cid,max(score) max_score
          from pub.student_course
          group by sid,cid
        )SC
    where S.sid=SC.sid and C.cid=SC.cid and S.dname='计算机科学与技术学院' and C.name='数据结构' and S.sid in(
      (
        select sid
        from pub.student_course SC,pub.course C
        where C.cid=SC.cid and C.name='数据结构'
      )
      union
      (
        select sid
        from pub.student_course SC,pub.course C
        where C.cid=SC.cid and C.name='操作系统'
      )
    )
  )
  natural full outer join
  (
    select S.sid,S.name,S.dname,SC.max_score os_score
    from pub.student S,pub.course C,(
          select sid,cid,max(score) max_score
          from pub.student_course
          group by sid,cid
        )SC
    where S.sid=SC.sid and C.cid=SC.cid and S.dname='计算机科学与技术学院' and C.name='操作系统' and S.sid in(
      (
        select sid
        from pub.student_course SC,pub.course C
        where C.cid=SC.cid and C.name='数据结构'
      )
      union
      (
        select sid
        from pub.student_course SC,pub.course C
        where C.cid=SC.cid and C.name='操作系统'
      )
    )
  )
)

5-8

create view test5_08 as (
select *
from
  (
    select S.sid,S.name,S.dname,SC.max_score ds_score
    from pub.student S,pub.course C,(
          select sid,cid,max(score) max_score
          from pub.student_course
          group by sid,cid
        )SC
    where S.sid=SC.sid and C.cid=SC.cid and S.dname='计算机科学与技术学院' and C.name='数据结构' 
  )
  natural full outer join
  (
    select S.sid,S.name,S.dname,SC.max_score os_score
    from pub.student S,pub.course C,(
          select sid,cid,max(score) max_score
          from pub.student_course
          group by sid,cid
        )SC
    where S.sid=SC.sid and C.cid=SC.cid and S.dname='计算机科学与技术学院' and C.name='操作系统'
  )
  natural full outer join
  (
  select S.sid,S.name,S.dname
  from pub.student S
  where S.dname = '计算机科学与技术学院' and S.sid not in (select S.sid from pub.student S,pub.course C,pub.student_course SC where S.sid = SC.sid and C.cid = SC.cid and (C.name = '数据结构' or C.name = '操作系统'))
  ) 
)

5-9

create view test5_09 as
with temp as(
  select SC.sid,SC.score
  from pub.student_course SC
  where SC.score>=60
)
select temp.score,count(sid) count1,
  (select count(sid) from temp) count0,
  (round(count(sid)/(select count(sid) from temp),4)*100) percentage
from temp
group by score

5-10

create or replace view test5_10 as
with temp as (
  select score, sid, cid
  from pub.student_course
  where score >= 60 and score <= 149
)
select
  sc.cid,
  c.name as cname,
  to_char(trunc(sc.score, -1), 'fm000') || '-' || to_char(trunc(sc.score, -1) + 9, 'fm000') as score,
  count(*) as count1,
  (select count(*)
   from temp
   where temp.cid = sc.cid) as count0,
  (round(count(sid)/(select count(*)
   from temp
   where temp.cid = sc.cid),4)*100) as percentage
from
  pub.course c,
  temp sc
where
  c.cid = sc.cid
group by
  sc.cid,
  c.name,
  to_char(trunc(sc.score, -1), 'fm000') || '-' || to_char(trunc(sc.score, -1) + 9, 'fm000');

实验六 

6-1

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

6-2

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

6-3

create or replace view test6_03 as
select*
from
(
  select C.cid,C.name,max(SC.max_score) max_score
  from pub.course C left outer join 
    (select sid,cid,max(score) max_score
     from pub.student_course
     group by sid,cid
    )SC
  on C.cid=SC.cid
  group by C.cid,C.name
) 
natural full outer join
(
  select SC.cid,count(sid) max_score_count
  from (select sid,cid,max(score) max_score
     from pub.student_course
     group by sid,cid
    )SC
  group by SC.cid,SC.max_score
  having (SC.max_score=(select max(score) 
                   from pub.student_course temp
                   where SC.cid=temp.cid)
    )
)

6-4

create or replace view test6_04 as
select SC.sid,S.name
from pub.student_course SC,pub.student S,pub.course C
where SC.sid=S.sid and SC.cid=C.cid
and SC.score>=60 and (C.name='操作系统' or C.name='数据结构'）
and S.sex='男' and (SC.sid not in(
  select SC.sid
  from pub.student_course SC,pub.course C
  where SC.cid=C.cid and
  C.name='程序设计语言' and
  SC.score>=60)
  )

6-5

create or replace view test6_05 as
select S.sid sid,S.name name,round(avg(score),0) avg_score,round(sum(score),0) sum_score
from pub.student S,pub.student_course SC
where S.sid=SC.sid and S.age=20
group by S.sid,S.name

6-6

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

6-7

create or replace view test6_07 as
select distinct S.sid,S.name
from pub.student S,pub.student_course SC
where S.sid=SC.sid and S.sid in(
  select sid
  from pub.student_course SC
  where not exists(
    select cid
    from pub.course C
    minus
    select cid
    from pub.student_course Temp
    where Temp.sid=SC.sid
  )
) and S.sid not in(
  select sid
  from pub.student_course SC
  where SC.score<60
)

6-8

create or replace view test6_08 as
with T as(
    select sid, cid, MAX(score) AS max_score
    from pub.student_course
    group by sid, cid
) 
select distinct S.sid,S.name
from pub.student S,T SC
where S.sid=SC.sid and S.sid in(
  select sid
  from T SC
  where not exists(
    select cid
    from pub.course C
    minus
    select cid
    from T Temp
    where Temp.sid=SC.sid
  )
) and S.sid not in(
  select sid
  from T SC
  where SC.max_score<60
)

6-9

create or replace view test6_09 as
select S.sid,S.name,sum(C.credit) sum_credit
from pub.student_course SC,pub.student S,pub.course C
where SC.cid=C.cid and SC.sid=S.sid
and S.dname='化学与化工学院'
and S.class=2010 and SC.score>=60
group by S.sid,S.name

6-10

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

 实验七

7-1

create table test7_01 as
select S.sid, S.name, S.birthday
from pub.student S
create index test7_01_index  on test7_01 (substr(name,1,1))

7-2

create table test7_02 as
select sid,name,birthday from pub.student;
update test7_02
set birthday=to_date('19881018','yyyymmdd')
where substr(sid,12,1)='0'
create index test7_02_index on test7_02 (birthday,name)

7-3

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

7-4

create view test7_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,'yyyy'),11))
  ) sameyear
from pub.student_testindex t1
) 
where sameyearmonth=35

7-5

create 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

实验八 

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

insert all 
  into test8_10 values ('结果1', 88)
  into test8_10 values ('结果2', 90)
  into test8_10 values ('结果3', 90)
  into test8_10 values ('结果4', 86)
  into test8_10 values ('结果5', 90)
  into test8_10 values ('结果6', 90)
  into test8_10 values ('结果7', 86)
  into test8_10 values ('结果8', 86)
  into test8_10 values ('结果9', 76)
  into test8_10 values ('结果10', 86)
select * from dual

实验九 

9-1

create table test9_01(
sid char(12) not null ,
name varchar2(10) not null,
sex char(2), 
age int, 
birthday date,
dname varchar2(30), 
class varchar2(10) 
);

create index suoyin on test9_01(sid)

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

insert into test9_01（
  select *
  from pub.student_11_1
  where sex='女' and sid not in(
    select distinct sid
    from pub.student
    where sex='女'
  )
）

insert into test9_01（
  select *
  from pub.student_11_2
  where sex='女' and sid not in(
    select distinct sid
    from test9_01
  )
）

9-2

create table test9_02(
sid char(12) not null ,
name varchar2(10) not null,
sex char(2), 
age int, 
birthday date,
dname varchar2(30), 
class varchar2(10) 
);

create index suoyin1 on test9_02(sid)

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

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

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

更详细题目讲解

数据库SQL语言实战（二）-CSDN博客

数据库SQL语言实战（三）-CSDN博客

数据库SQL语言实战（四）(数据库系统概念第三章练习题）-CSDN博客

数据库SQL语言实战（五）(数据库系统概念第三章练习题）_数据库系统概括第三章sql语句数据练习-CSDN博客

 数据库SQL语言实战（六）-CSDN博客

数据库SQL语言实战（七）-CSDN博客

数据库SQL语言实战（八）-CSDN博客

数据库SQL语言实战（九）（索引）-CSDN博客

数据库SQL语言实战（十）（最后一篇）-CSDN博客

1、基本对应实验，从实验二开始 

2、中间有几篇是老师布置的作业题

 

 总结 

本文的所有题目均来自《数据库系统概念》（黑宝书）、山东大学数据库实验一到九。不可用于商业用途转发。

如果能帮助到大家，大家可以点点赞、收收藏呀~