目录
前言
这个是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语言实战（五）(数据库系统概念第三章练习题）_数据库系统概括第三章sql语句数据练习-CSDN博客
1、基本对应实验，从实验二开始
2、中间有几篇是老师布置的作业题
总结
本文的所有题目均来自《数据库系统概念》（黑宝书）、山东大学数据库实验一到九。不可用于商业用途转发。
如果能帮助到大家，大家可以点点赞、收收藏呀~
