【数据库】|部分实验整理

m1_72629485

已于 2024-04-11 11:22:30 修改

阅读量26

点赞数

分类专栏：数据库文章标签：数据库

于 2023-10-27 23:23:39 首次发布

本文链接：https://blog.csdn.net/m0_72629485/article/details/133633614

版权

数据库专栏收录该内容

4 篇文章 0 订阅

订阅专栏

第三章：SQL

实验重难点

实验三删除
- 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学院班级学分达标情况统计表1
- 5-4学院班级学分达标情况统计表2
- 5-5统计报表1
- 5-6统计报表2
- 5-7统计报表3
- 5-8统计报表4
- 5-9
- 5-10
实验六、创建视图、删除视图
- 6-1OK
- 6-2OK
- 6-3
- 6-4
- 6-5OK
- 6-6
- 6-7
- 6-8
- 6-9OK
- 6-10OK
实验七索引重要性
- 7-1
- 7-2
- 7-3
- 7-4
- 7-5
实验八
实验九
- 9-1
- 9-2

实验三删除

3-2 删除年龄错误的数据

将pub用户下的Student_31及数据复制到主用户的表test3_02，用delete删除语句，删除表中的出生日期和年龄(截止到2012年的年龄，即 年龄=2012-出生年份 )不一致的那些错误数据。
函数extract(year from birthday)返回birthday的年份

直接利用所给函数就行

create table test3_02 as
(select * from pub.Student_31)

delete from test3_02
where (2012-extract(year from birthday))!=age;

3-3 删除性别错误的数据

3．将pub用户下的Student_31及数据复制到主用户的表test3_03，删除表中的性别有错误的那些错误数据（性别只能够是"男"、"女"或者空值）。

用or来进行选择
由于是删除，因此是not

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删除院系错误的数据

4．将pub用户下的Student_31及数据复制到主用户的表test3_04，
删除表中的院系名称有空格的、院系名称为空值的或者院系名称小于3个字的那些错误数据。

考察字符串运算中like模式匹配，具体见笔记
在这里插入图片描述

create table test3_04 as
select *
from pub.Student_31;

delete from test3_04
where (dname like '% %') or (dname not like '%___%') or dname is null;

3-5删除班级错误的数据

5．将pub用户下的Student_31及数据复制到主用户的表test3_05，删除表中的班级不规范的那些错误数据，不规范是指和大多数不一致。
这个题知识点是学会用sql找出不规范的数据，而不是用人工办法找不规范。
提示：寻找不规范有很多解决思路，可以去对比大纲最后的提示。

思路：

查看哪些数据不太符合规范；
不符合规范的数据是那些 class 中间有空格、后面加上了 ‘级’ 字的数据；
因此可以确定符合规范的数据是全数字的 class 数据；
使用前面讲到的regexp_like() 函数来进行判断；
删除这些数据；
补充：正则表达式regexp_like的用法详解

create table test3_05 as
(select * from pub.Student_31);
 
delete from test3_05
where not regexp_like(class,'^[0-9]{4}$');

解释：

^匹配字符串开始位置
$匹配字符串结尾位置
[]中括号表达式，全是数字
{4}出现4次

3-6删除多项错误的错误数据

6．将pub用户下的Student_31及数据复制到主用户的表test3_06，删除表中的错误数据，不规范的数据也被认为是那些错误数据。
（1）学号不全是数字；
（2）出生日期和年龄不一致的(年龄=2012-出生年份)；
（3）姓名有空格的或者长度小于2个字的；函数length()返回字符串长度。
（4）性别有错误的（只能够是"男"、“女”、空值）；
（5）院系名称有空格的、院系名称为空值的；
（6）院系名称小于3个字的；
（7）班级数据有错误的（需要先找到班级里面的错误）。
保留最后全部正确的数据。

前面几个实验的总和，用or连接起来就行

create table test3_06 as
select* 
from pub.Student_31

delete from test3_06
where not regexp_like(sid,'^[0-9]+$')
      or (2012-extract(year from birthday))!=age
      or name like '% %' or length(name)<2
      or not(sex='男' or sex='女' or sex is null)
      or (dname like '% %') or (dname not like '%___%') or dname is null
      or not regexp_like(class,'^[0-9]{4}$');

3-7删除学号不存在的错误数据

7．将pub用户下的Student_course_32及数据复制到主用户的表test3_07，删除其中的错误数据，错误指如下情况：
学号在学生信息pub.student中不存在的；

先找到在pub.student中的学号，再进行删除

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删除没有教师教该课程的错误数据

8．将pub用户下的Student_course_32及数据复制到主用户的表test3_08，删除其中的错误数据，错误指如下情况：
课程号和教师编号在教师授课表pub.teacher_course中不同时存在的，即没有该教师教该课程；

create table test3_08 as
select* 
from pub.Student_course_32

delete from test3_08 S
where not exists
(select cid,tid
  from pub.teacher_course T 
  where S.cid=T.cid and S.tid=T.tid)

# 另一种写法
delete from test3_08
where 
     (cid,tid) not in 
     (select cid,tid from pub.teacher_course)

3-9删除成绩数据有错误的错误数据

9．将pub用户下的Student_course_32及数据复制到主用户的表test3_09，删除其中的错误数据，错误指如下情况：
成绩数据有错误（需要先找到成绩里面的错误）。
这个题知识点是学会用sql找出错误数据，而不是用人工办法找错误数据。
提示：寻找不规范有很多解决思路，可以去对比大纲最后的提示。

成绩只能在0~100，但发现成绩有>100或<0的数据，需要将它们删除

select score from test3_08
where score>100 or score<0

create table test3_09 as
select * 
from pub.Student_course_32

delete from test3_09
where score>100 or score<0

3-10删除多项错误的错误数据

10．将pub用户下的Student_course_32及数据复制到主用户的表test3_10，删除其中的错误数据，错误指如下情况：
（1）学号在学生信息pub.student中不存在的；
（2）课程号在课程信息pub.course中不存在的；
（3）教师编号在教师信息pub.teacher中不存在的；
（4）课程号和教师编号在教师授课表pub.teacher_course中不存在的；
（5）成绩数据有错误（需要先找到成绩里面的错误）。
保留最后正确的数据。

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>100 or score<0)

实验四复制表、修改表结构、修改数据

这一部分和前面查询联系挺大的，重点要把前面搞透了

4-1统计总成绩

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

先复制，再增加，最后更新

create table test4_01 as
select* 
from pub.student_41

alter table test4_01 add sum_score int;

update test4_01 t
set sum_score=
  (select sum(score)
  from pub.student_course p
  where t.sid=p.sid)

4-2统计平均成绩

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

关于小数点：
round() 函数是四舍五入用：第一个参数是我们要被操作的数据，第二个参数是设置我们四舍五入之后小数点后显示几位。

create table test4_02 as
select *
from pub.student_41

alter table test4_02 add avg_score numeric(3,1)

update test4_02 T
set avg_score=round(
  (select avg(score)
  from pub.student_course S
  where T.sid=S.sid),1)

4-3统计总学分

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

思路：

通过观察表中信息，可以知道需要将pub.course表与另一个和学号、课序号有关的表进行自然连接
这个表通过select进行获取，并且要取每个学生每个课的最高成绩
注意成绩及格：要求这门课最高成绩>=60

create table test4_03 as
select*
from pub.student_41;

alter table test4_03 add sum_credit int

update test4_03 T
set sum_credit=(
  select sum(credit)
  from pub.course natural join (
  select sid,cid,max(score) max_score
  from pub.student_course
  group by sid,cid
  ) S
  where S.sid=T.sid and S.max_score>=60
)

4-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 T
set dname=(
  select did
  from pub.department D
  where D.dname=T.dname
)
where T.dname in (select dname from pub.department)

4-5几项内容综合

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。

(4)用到的case语句详解当然也可以不用（挠头

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 S 
  where T.sid=S.sid
  )

update test4_05 T 
set avg_score=round((select avg(score)
  from pub.student_course S 
  where T.sid=S.sid),1)

update test4_05 T 
set sum_credit=(
  select sum(credit)
  from pub.course natural join (
    select sid,cid,max(score) max_score
    from pub.student_course
    group by sid,cid) S
where T.sid=S.sid and S.max_score>=60)

update test4_05 T 
set did=
case 
when dname in
  ((select dname 
  from pub.department)
  union(select dname from pub.department_41))
then 
  (select did 
  from
  ((select dname,did from pub.department)
  union
  (select dname,did from pub.department_41))
  where dname=T.dname)
else '00'
end

//(4)的另一种方法
alter table test4_05 add did varchar(2); 
update test4_05 set did ='00';//先全都置成00
 
update test4_05 t
set did= (
    select did
    from  pub.department a
    where t.dname=a.dname
)
where t.dname in (select dname from pub.department)
;
 
update test4_05 t
set did= (
    select did
    from  pub.department_41 a
    where t.dname=a.dname
)
where t.dname in (select dname from pub.department_41)
;

4-6剔除姓名中的空格

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

用replace(string, target_str, replace_str)函数

create table test4_06 as
select *
from pub.Student_42

update test4_06
set name=replace(name,' ','');

4-7规范性别

7．将pub用户下的Student_42及数据复制到主用户的表test4_07中,对表中的数据进行整理，修复那些不规范的数据：
对性别列进行规范（需要先确定哪些性别数据不规范，也就是那些和大多数不一样的就是不规范的）；

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 规范班级

8．将pub用户下的Student_42及数据复制到主用户的表test4_08中,对表中的数据进行整理，修复那些不规范的数据：
对班级列进行规范（需要先确定哪些班级不规范）。

create table test4_08 as
select*
from pub.Student_42

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

4-9 计算年龄

9．将pub用户下的Student_42及数据复制到主用户的表test4_09中,对表中的数据进行整理，修复那些不规范的数据：
年龄为空值的根据出生日期设置学生年龄（截止到2012年的年龄，即年龄=2012-出生年份），年龄不为空值的不要改变。

create table test4_09 as
select *
from pub.Student_42

//错误
update test4_09
set age=replace(age,(select age 
  from test4_09
  where age is null),2012-extract(year from birthday))

//正确 
update test4_09
set age=(2012-extract(year from birthday))
where age is null

4-10几项内容综合

10．将pub用户下的Student_42及数据复制到主用户的表test4_10中,对表中的数据进行整理，修复那些不规范的数据：
（1）剔除姓名列中的所有空格；
（2）剔除院系名称列中的所有空格；
（3）对性别列进行规范（需要先确定哪些性别数据不规范，也就是那些和大多数不一样的就是不规范的）；
（4）对班级列进行规范（需要先确定哪些班级不规范）。
（5）年龄为空值的根据出生日期设置学生年龄（截止到2012年的年龄，即年龄=2012-出生年份），年龄不为空值的不要改变。

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=replace(sex,' ','');
 
update test4_10
set class=replace(class,'级','');
 
update test4_10
set age=(2012-extract(year from birthday))
where age is null

实验五报表统计

掌握sum,count,group by函数的用法
部分题目可使用union all来解决
全部建成视图，考虑最高成绩（5.5，5.6，5.7，5.8，5.9，5.10）

5-1统计名字使用频率

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

substr函数:字符截取函数

格式1： substr(string string,int a,int b);
格式2：substr(string string,int a) ;
解释：
- 格式1：
  - string 需要截取的字符串
  - a 截取字符串的开始位置(注：当a等于0或1时，都是从第一位开始截取)
  - b 要截取的字符串的长度
- 格式2：
  - string 需要截取的字符串
  - a 可以理解为从第a个字符开始截取后面所有的字符串。

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

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

5-2统计名字的每个字使用频率

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

union：
- 对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序；
- union在进行表链接后会筛选掉重复的记录，所以在表链接后会对所产生的结果集进行排序运算，删除重复的记录再返回结果。
- 实际大部分应用中是不会产生重复的记录，最常见的是过程表与历史表UNION
union all：对两个结果集进行并集操作，包括重复行，不进行排序；
- 如果返回的两个结果集中有重复的数据，那么返回的结果集就会包含重复的数据了。

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

insert into test5_02
(select first_name,count(*)
  from (
  (select substr(name,2,1) as first_name
  from pub.student
  where length(name)>=2)
  union all
  (select substr(name,3,1) as first_name
  from pub.student
  where length(name)>=3)
  )
  group by first_name
  );

5-3学院班级学分达标情况统计表1

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
………………

思路：

首先，我们需要连接三个表：pub.student、pub.student_course和pub.course，以获取学生、课程和学生选课信息的综合数据。
通过以下子查询，我们计算出每个院系和班级中成绩达标（score >= 60）的学生人数（P_count1）：
- 首先，我们从pub.student_course表中获取每个学生选课的最高分数（max(score)）。
- 然后，我们将这些数据与pub.course表进行自然连接，以获取每个学生的总学分（sum(credit)）。
- 接下来，我们按学生（sid）分组，并计算每个组中成绩达标的课程数目（sum(case when score >= 60 then 1 else 0 end)）。
- 最后，我们按照院系（dname）和班级（class）分组，并统计每个组中达标学生的人数。
接下来，我们使用以下子查询，计算出每个院系和班级中的学生总人数（P_count）：
- 我们从pub.student表中选择院系（dname）和班级（class），并计算每个组中的学生人数（count(sid)）。
- 我们排除了院系为空的记录。
在主查询中，我们将子查询的结果进行连接，以获取每个院系和班级的名称（dname、class）、成绩达标学生人数（P_count1）、未达标学生人数（P_count-P_count1）和总学生人数（P_count）。
最后，我们将结果插入到test5_03表中。

create table test5_03
(Dname varchar(30),class varchar(10),P_count1 int,P_count2 int,P_count int)
//cuo
insert into test5_03
select s.dname,s.class,t1.P_count1,t2.P_count-t1.P_count1,t2.P_count
from
	(select s.dname,s.class,count(*) P_count1
	from pub.student s
	join pub.student_course sc on s.sid=sc.sid
	join pub.course c on sc.cid=c.cid
	where sc.score>=60
  group by s.dname,s.class
	having sum(c.credit)>=10) t1
join
	(select s.dname,s.class,count(*) P_count
	from pub.student s
	join pub.student_course sc on s.sid=sc.sid
	join pub.course c on sc.cid=c.cid
	where s.dname is not null
	group by s.dname,s.class) t2 on t1.dname=t2.dname and t1.class=t2.class
join pub.student s on t1.dname=s.dname and t1.class=s.class;
//dui
insert into test5_03
(select   Dname,class,p1,psum-p1,psum
from
(select
   Dname,class,sum(CASE WHEN sum_credit >= 10 THEN 1 ELSE 0 END) as p1
from
  (select  sid,sum(credit) sum_credit
   from
    (select sid,cid,max(score) score from pub.student_course where score>=60 group by sid,cid) a
    natural left join  pub.course b
   group by sid
  ) temp1
  natural join pub.student temp2
where  Dname is not null 
group by Dname,class
)  t1
natural join
(select dname,class,count(sid) as psum
  from pub.student 
  where dname is not null
  group by dname,class
)   t2
);

5-4学院班级学分达标情况统计表2

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 varchar(30),class varchar(10),P_count1 int,P_count2 int ,P_count int)

insert into test5_04
select s.dname,s.class,t1.P_count1,t2.P_count-t1.P_count1,t2.P_count
from
	(select s.dname,s.class,count(*) P_count1
	from pub.student s
	join pub.student_course sc on s.sid=sc.sid
	join pub.course c on c.cid=sc.cid
	where sc.score>=60
	group by s.dname,s.class
	having sum(c.credit)>=
		(case
			when class<=2008 then 8
			when class>2008 then 10
		end) )t1
join
	(select s.dname,s.class,count(*) P_count
	from pub.student s
	join pub.student_course sc on s.sid=sc.sid
	join pub.course c on sc.cid=c.cid
	where s.dname is not null
	group by s.dname,s.class ) t2 on t1.dname=t2.dname and t1.class=t2.class
join pub.student s on t1.dname=s.dname and t1.class=s.class;

insert into test5_04
(select Dname,class,P_count1,P_count-P_count1,P_count
from
(select
   Dname,class,sum(case when class<=2008 
then
  (CASE WHEN sum_credit >= 8 THEN 1 ELSE 0 END)
else
  (CASE WHEN sum_credit >= 10 THEN 1 ELSE 0 END)
 end ) as P_count1
from
  (select  sid,sum(credit) sum_credit
   from
    (select sid,cid,max(score) score from pub.student_course where score>=60 group by sid,cid) a
    natural left join  pub.course b
   group by sid
  ) temp1
  natural join pub.student temp2
where  Dname is not null 
group by Dname,class
)  t1
natural join
(select dname,class,count(sid) as P_count
  from pub.student 
  where dname is not null
  group by dname,class
)   t2
);

5-5统计报表1

5．查询各院系（不包括院系名称为空的）的数据结构平均成绩avg_ds_score、操作系统平均成绩avg_os_score，平均成绩四舍五到个位，创建视图test5_05，视图内容示意如下：
Dname Avg_ds_score Avg_os_score
马克思主义学院 72 70
软件学院 77 74
艺术学院 77 76
医学院 74 73

小数位数：
ROUND(column_name,decimals)

create view test5_05 as
select dname,ds as Avg_ds_score,os as Avg_os_score
from (
  select dname,round(avg(max_ds),0) ds
  from (
  select sid,dname,max(score) max_ds
  from pub.student s natural join pub.student_course sc join pub.course c on sc.cid=c.cid
  where s.dname is not null and c.name='数据结构'
  group by sid,dname
  )
  group by dname
)
natural join
(
  select dname,round(avg(max_os),0) os
  from(
  select sid,dname,max(score) max_os
  from pub.student s natural join pub.student_course sc join pub.course c on sc.cid=c.cid
  where s.dname is not null and c.name='操作系统'
  group by sid,dname
  )
  group by dname
)

5-6统计报表2

create view test5_06 as
select sid,s.name,dname,max(a.score) ds_score,max(b.score) os_score
from pub.student_course a 
join pub.student_course b on a.sid=b.sid
join pub.course c on a.cid=c.cid
join pub.course d on b.cid=d.cid
join pub.student s on s.sid=a.sid
where s.dname='计算机科学与技术学院' and c.name='数据结构' and d.name='操作系统'
group by sid,s.name,dname

5-7统计报表3

create view test5_07 as
select sid,name,dname,ds_score,os_score
from 
(select s.sid,name,dname,max(score) ds_score
  from pub.student s 
  join(
  select sid,score
  from pub.student_course 
  where cid=(select cid from pub.course where name='数据结构')) a on a.sid=s.sid
  where s.dname='计算机科学与技术学院'
  group by s.sid,name,dname)
natural full join
(select s.sid,name,dname,max(score) os_score
  from pub.student s 
  join(
  select sid,score
  from pub.student_course 
  where cid=(select cid from pub.course where name='操作系统')) a 
  on a.sid=s.sid
where s.dname='计算机科学与技术学院'
group by s.sid,name,dname)

5-8统计报表4

create view test5_08 as
select sid,name,dname,ds_score,os_score
from
(select sid,name,dname
  from pub.student
  where dname='计算机科学与技术学院')
natural left outer join
(select sid,max(score) ds_score
  from pub.student_course natural join pub.course
  where name='数据结构'
  group by sid)
natural left outer join
(select sid,max(score) os_score
  from pub.student_course natural join pub.course
  where name='操作系统'
  group by sid)

5-9

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

5-10

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

实验六、创建视图、删除视图

创建视图、删除视图
一、实验内容
oracle管理平台，针对公共用户pub下的表，完成创建视图、查询验证视图、删除视图。视图名为test6_(题号，题号长度两位，前面补零),例如test6_01。
二、实验题目
1．找出年龄小于20岁且是“物理学院”的学生的学号、姓名、院系名称,按学号排序。
2．查询统计2009级、软件学院所有学生的学号、姓名、总成绩（列名sum_score）（如果有学生没有选一门课，则总成绩为空值）。
3．查询所有课的最高成绩、最高成绩人数，test6_06有四个列：课程号cid、课程名称name、最高成绩max_score、最高成绩人数max_score_count（一个学生同一门课成绩都是第一，只计一次,需要考虑刷成绩情况，一个同学选了一个课程多次，两次都是最高分。如果结果错一行，可能就是没有考虑这种情况，这里是为了考核“去重复计数”知识点的）。如果没有学生选课，则最高成绩为空值,最高成绩人数为零。
提示：参考讲义关于标量子查询（只返回包含单个属性的单个元组）。
4．找出选修了“操作系统”并取得学分或者选修“数据结构”并且取得学分，但是没有选修“程序设计语言”或者没有取得这门课的学分的男学生的学号、姓名。
5．查询20岁的所有有选课的学生的学号、姓名、平均成绩(avg_score，此为列名，下同)（平均成绩四舍五入到个位）、总成绩(sum_score)
Test6_05有四个列，并且列名必须是：sid、name、avg_score、sum_score。通过下面方式实现列名定义：
create or replace view test6_05 as select sid,name，（表达式） avg_score,（表达式） sum_score from ……
6．找出同一个同学同一门课程有两次或以上不及格的所有学生的学号、姓名（即一门课程需要补考两次或以上的学生的学号、姓名）。
7．找出选修了所有课程并且每门课程每次考试成绩均及格的学生的学号、姓名。（题6的延伸和巩固）
8．找出选修了所有课程并且得到所有课程的学分（即每门课程最少一次考试及格）的学生的学号、姓名。（题6的延伸和巩固）。
9．查询统计2010级、化学与化工学院的学生总学分表，内容有学号、姓名、总学分sum_credit。（不统计没有选课的学生）。
10．查询学生表中每一个姓氏及其人数（不考虑复姓，用到取子串函数substr(string,postion,length)）），test6_10有两个列：second_name、p_count

6-1OK

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

6-2OK

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

6-3

create or replace view test6_03 as
select a.cid,
  (select name from pub.course c where a.cid=c.cid) name,
  (select max(score) from pub.student_course sc where sc.cid=a.cid) max_score,
  max_score_count
from pub.course a left join (
  select cid,score max_score,count(distinct sid) max_score_count
  from pub.student_course f
  where f.score=(select max(score) from pub.student_course sc where sc.cid=f.cid)
  group by cid,score
  ) t 
on t.cid=a.cid

6-4

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

6-5OK

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

6-6

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

6-7

create or replace view test6_07 as
select s.sid, name
from
(select sid,cid,min(score) min_score
from pub.student_course
group by sid,cid) s
join 
pub.student a
on s.sid=a.sid
where min_score>=60
group by s.sid,name
having count(*)=140

6-8

create or replace view test6_08 as
select sid,name
from pub.student s 
where not exists
((select cid from pub.course)
  minus
  (select cid from pub.student_course sc where sc.sid=s.sid))
and sid not in
  (select sid
  from
  (select sid,cid,max(score) max_score 
  from pub.student_course 
  group by sid,cid)
  where max_score<60)

6-9OK

create or replace view test6_09 as
select b.sid,b.name,sum(credit) sum_credit
from
(select sid,cid,max(score) max_score
  from pub.student_course
  group by sid,cid) a 
join pub.student b on a.sid=b.sid
join pub.course c on a.cid=c.cid
where class=2010 and dname='化学与化工学院' and max_score>=60
group by b.sid,b.name

6-10OK

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

实验七索引重要性

7-1

1．将pub用户下表student的3个列sid,name,birthday复制到表test7_01中。
2．执行如下查询，观察运行速度（5秒以上）。
查询Samefirstname相同姓氏的人数。
select * from
(select sid,name,birthday,
(select count(*) from test7_01 where substr(name,1,1)=substr(t1.name,1,1)) samefirstname
from pub.student_testindex t1)
where samefirstname=7
3．为test7_01创建一个仅仅一个索引，保证上面SQL耗时在1秒内。
4．交卷验证

create table test7_01 as
select sid,name,birthday from pub.student

create index first_index on test7_01(substr(name, 1, 1))

7-2

1．将pub用户下表student的3个列sid,name,birthday复制到表test7_02中。
2．将出生日期全部修改成一天：
Update test7_02 set birthday=to_date(‘19881018’,‘yyyymmdd’) where substr(sid,12,1)=‘0’;
3．为test7_02创建一个仅仅一个索引，保证下面SQL耗时在1秒内。
Samenamebirthday同名同生日的人数，Samebirthday相同出生日期的人数
select * from
(select sid,name,birthday,
(select count() from test7_02 where name=t1.name and birthday=t1.birthday) samenamebirthday,
(select count() from test7_02 where birthday=t1.birthday) samebirthday
from pub.student_testindex t1)
where samebirthday=403
4．交卷验证
5．思考题，test7_02不增建索引情况下，下面这个查询能使用索引吗？改进后能使用索引吗？
select * from
(select sid,name,birthday,
(select count(*) from test7_02 where name=t1.name) samename
from pub.student t1)
where samename=7

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 number_index on test7_02(birthday,name)

7-3

1． pub用户下表student已经用下面两句SQL创建了两索引。
Create index student_birthday on student(birthday);
Create index student_name on student(name);
2．下面SQL不能用索引耗时超过2秒，在逻辑不变情况下，修改SQL中标为记红色的子查询的where条件部分，不要修改其它地方，使其能使用索引。
说明：因为pub.student_testindex数据行数太少，不能通过修改主句where绕过问题。
查询samefirstname同姓氏的人数。
select * from
(select sid,name,birthday,
(select count(*) from pub.student
where substr(name,1,1)=substr(t1.name,1,1)
) samefirstname
from pub.student_testindex t1) where samefirstname=7
3．修改以后验证耗时在2秒之内，将修改以后语句创建成视图create view test7_03 as select ……。
4．交卷验证

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

1． pub用户下表student已经用下面两句SQL创建了两索引。
Create index student_birthday on student(birthday);
Create index student_name on student(name);
2．下面SQL不能用索引耗时超过1秒，在逻辑不变情况下，修改SQL中标为记红色的子查询的where条件部分，不要修改其它地方，使其能使用索引。
说明：因为pub.student_testindex数据行数太少，不能通过修改主句where绕过问题。
select * from
(select sid,name,birthday,
(select count() from pub.student
where to_char(birthday,‘yyyymm’)=to_char(t1.birthday,‘yyyymm’)
) sameyearmonth,
(select count() from pub.student
where extract (year from birthday) =extract (year from t1.birthday)
) sameyear
from pub.student_testindex t1) where sameyearmonth=35
3．修改以后验证耗时在1秒之内，将修改以后语句创建成视图create view test7_04 as select ……。
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

1． pub用户下表student已经用下面两句SQL创建了两索引。
Create index student_birthday on student(birthday);
Create index student_name on student(name);
2．下面SQL不能用索引耗时超过1秒，在逻辑不变情况下，修改SQL中标为记红色的子查询的where条件部分，不要修改其它地方，使其能使用索引。
说明：因为pub.student_testindex数据行数太少，不能通过修改主句where绕过问题。
查询nextbirthday晚一天出生的人数
select * from
(select sid,name,birthday,
(select count(*) from pub.student
where birthday-1=t1.birthday
) nextbirthday
from pub.student_testindex t1) where nextbirthday=7
3．修改以后验证耗时在1秒之内，将修改以后语句创建成视图create view test7_05 as select ……。
4．交卷验证

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

实验八

按照实验大纲一步步来即可

【1】	create table test8_00 as
select *from pub.teacher
【2】	grant select on test8_00 to userID

【3】	update test8_00 set age=88
【4】	select * from test8_00
【5】	commit
【6】	rollback
【7】	update test8_00 set age =age+1
【8】	rollback
【9】	commit
【10】	update test8_00 set age =age+2
【11】	commit
【12】	select * from test8_00
【13】	rollback
【14】	select * from user9ID.test8_00（主窗口）

【15】	update test8_00 set age =age-2
【16】	update test8_00 set age =age-2

【17】	select * from user9ID.test8_00
【18】	select * from user9ID.test8_00（主窗口）
【19】	commit（主窗口）
【20】	select * from user9ID.test8_00（主窗口）
【21】	rollback（主窗口）
【22】	update user9ID.test8_00 set age=age-10（主窗口）
【23】	select * from test8_00
【24】	create table test8_01 as select * from test8_00
【25】	rollback
【26】	select * from user9ID.test8_00
【27】	select * from user9ID.test8_00（主窗口）
【28】	rollback（主窗口）
【29】	select * from user9ID.test8_00（主窗口）

（主窗口）
【30】	create table test8_10(test varchar(20),age numeric(3))

【31】	insert into test8_10 values('结果1',88)
【32】	insert into test8_10 values('结果2',90)
【33】	insert into test8_10 values('结果3',90)
【34】	insert into test8_10 values('结果4',86)
【35】	insert into test8_10 values('结果5',90)
【36】	insert into test8_10 values('结果6',90)
【37】	insert into test8_10 values('结果7',86)
【38】	insert into test8_10 values('结果8',86)
【39】	insert into test8_10 values('结果9',76)
insert into test8_10 values('结果10',86)

实验九

9-1

一、实验内容
学会复制表结构、学会插入数据，特别是学会如何避免重复插入，也就是如何避免插入已经存在的数据。
二、实验题目1
1．创建表test9_01，表的结构同pub.student_11_1一样。
2．为test9_01的sid创建唯一不重复索引。
3．将pub用户下的Student中性别是“女”的数据添加到test9_01中。
4．将pub用户下的Student_11_1中性别是“女”的数据添加到test9_01中,如果某个学号已经包含在test9_01中，这个记录就不要再插入了（即不要插入重复学号的数据）。
5．将pub用户下的Student_11_2中性别是“女”的数据添加到test9_01中,如果某个学号已经包含在test9_01中，这个记录就不要再插入了（即不要插入重复学号的数据）。
6．要求完成上述功能，请采用1条create table、1条create index、3条insert共5条SQL方式完成。

create table test9_01 as
select * from pub.student_11_1
where 1=0;

create index sidindex 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 sid from test9_01
)

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

9-2

三、实验题目2
7．创建表test9_02，表的结构同pub.student_11_1一样。
8．为test9_02的sid创建唯一不重复索引。
9．将pub用户下的Student中性别是“女”的且pub.student_course中存在不及格成绩的同学添加到test9_02中。
10．将pub用户下的Student_11_1中性别是“女”的且pub.student_course中存在不及格成绩的同学数据添加到test9_02中,如果某个学号已经包含在test9_02中，这个记录就不要再插入了（即不要插入重复学号的数据）。
11．将pub用户下的Student_11_2中性别是“女”的且pub.student_course中存在不及格成绩的同学数据添加到test9_02中,如果某个学号已经包含在test9_02中，这个记录就不要再插入了（即不要插入重复学号的数据）。
12．要求完成上述功能，请采用1条create table、1条create index、3条insert共5条SQL方式完成。

create table test9_02 as
select * from pub.student_11_1
where 1=0;

create index sindex on test9_02(sid)

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

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

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