山东大学软件学院数据库系统实验五

最新推荐文章于 2023-04-25 19:13:49 发布

叶卡捷琳堡

最新推荐文章于 2023-04-25 19:13:49 发布

阅读量4k

点赞数 10

分类专栏： # 数据库实验文章标签：山东大学 sql Oracle 数据库

本文链接：https://blog.csdn.net/weixin_46841376/article/details/116494382

版权

数据库实验专栏收录该内容

7 篇文章 5 订阅

订阅专栏

文章目录

- 一、实验时间
- 二、实验题目

一、实验时间

2021年5月4日星期二，第10周

二、实验题目

1．在学生表pub.student中统计名字（姓名的第一位是姓氏，其余为名字，不考虑复姓）的使用的频率，将统计结果放入test5_01中，表结构如下。
First_name varchar(4) frequency numeric(4)
国强 1034
红 1232
卫东 2323
………………

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

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

这里使用了Oracle数据库中的substr函数

2．在学生表pub.student中统计名字（姓名的第一位是姓氏，不作统计，名字指姓名的第二个之后的汉字）的每个字使用的频率，将统计结果放入test5_02中（特别提示：需要区别union和union all的不同），表结构如下。
letter varchar(2) frequency numeric(4)
锋 1034
红 1232
鹏 2323
………………

create table test5_02
(
letter varchar2(2),
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
)

3．创建"学院班级学分达标情况统计表1"test5_03，依据pub.student， pub.course，pub.student_course统计形成表中各项数据，成绩>=60为及格计入学分，总学分>=10算作达标，院系为空值的数据不统计在下表中，表结构：院系名称dname、班级class、学分达标人数p_count1、学分未达标人数p_count2、总人数p_count。
Dname varchar(30) class varchar(10) P_count1 Int P_count2 int P_count int
计算机学院 2006
计算机学院 2007
软件学院 2006
………………

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

更新p_count1

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,pub.student_course SC
	where S.sid = SC.sid and C.cid=SC.cid and SC.score >= 60
	group by S.sid,S.dname,S.class
	having sum(C.credit) >= 10
) temp
where t.dname = temp.dname and t.class  = temp.class)

更新p_count2

update test5_03 t set p_count2 = 
(
	select count(sid)
	from 
	(
		select S.sid,S.dname,S.class
		from pub.student S,pub.course C,pub.student_course SC
		where S.sid = SC.sid and C.cid=SC.cid and SC.score >= 60
		group by S.sid,S.dname,S.class
		having sum(C.credit) < 10 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)

4．创建"学院班级学分达标情况统计表2"test5_04，依据pub.student， pub.course，pub.student_course统计形成表中各项数据，成绩>=60为及格计入学分，2008级及之前的班级总学分>=8算作达标，2008级之后的班级学分>=10算作达标，院系为空值的数据不统计在下表中，表结构：院系名称dname、班级class、学分达标人数p_count1、学分未达标人数p_count2、总人数p_count。
Dname varchar(30) class varchar(10) P_count1 int P_count2 int P_count int
计算机学院 2006
计算机学院 2007
软件学院 2006
………………

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

更新院系，班级，学生总人数

insert into test5_04(dname,class,p_count)
select dname,class,count(sid)
from pub.student
where dname is not null
group by dname,class

更新p_count1

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,pub.student_course SC
	where S.sid = SC.sid and C.cid=SC.cid and SC.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,pub.student_course SC
	where S.sid = SC.sid and C.cid=SC.cid and SC.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)

更新p_count2

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,pub.student_course SC
		where S.sid = SC.sid and C.cid=SC.cid and SC.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,pub.student_course SC
		where S.sid = SC.sid and C.cid=SC.cid and SC.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．查询各院系（不包括院系名称为空的）的数据结构平均成绩avg_ds_score、操作系统平均成绩avg_os_score，平均成绩四舍五入到个位，创建表test5_05，表结构及格式如下：
Dname Avg_ds_score Avg_os_score
马克思主义学院 72 70
软件学院 77 74
艺术学院 77 76
医学院 74 73

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

6．查询"计算机科学与技术学院"的同时选修了数据结构、操作系统两门课的学生的学号sid、姓名name、院系名称dname、数据结构成绩ds_score、操作系统成绩os_score，创建表test5_06

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

去重
这里我用了特殊的方法，通解下面的题目里有，这里不再写了

delete from test5_06 where name = '董育红' and ds_score = 19

7．查询计算机科学与技术学院的选修了数据结构或者操作系统的学生的学号sid、姓名name、院系名称dname、数据结构成绩ds_score、操作系统成绩os_score，创建表test5_07

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

8．查询计算机科学与技术学院所有学生的学号sid、姓名name、院系名称dname、数据结构成绩ds_score、操作系统成绩os_score，创建表test5_08，表结构及格式如下

create table test5_08 as
select *
from
(select S.sid,S.name,S.dname,max_score ds_score
from (select sid,cid,max(score) max_score 
        from pub.student_course
	group by sid,cid) SC,pub.student S,pub.course C
where S.sid = SC.sid and SC.cid = C.cid and S.dname = '计算机科学与技术学院' and C.name = '数据结构')
natural full outer join
(select S.sid,S.name,S.dname,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 SC.cid = C.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 = '操作系统'))
)