
实验一	建表、删除表、插入数据
一、	实验内容
创建3个表，为每个表输入2行数据，没有逻辑难度，只是熟悉环境，学会创建表。
表名、列名采用英文,oracle不区分大小写，有not null的列代表不允许为空。
二、	实验步骤
1．	在SQL编辑区输入建表SQL，创建学生信息表（学生编号、姓名、性别、年龄、出生日期、院系名称、班级）：
 	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。
2．	创建课程信息表(仅考虑一门课程最多一个先行课的情况)（课程编号、课程名称、先行课编号、学分）
test1_course：cid char 6 not null、name varchar 40 not null、fcid char 6、
credit numeric 4，1（其中4代表总长度，1代表小数点后面长度）。
3．	创建学生选课信息表（学号、课程号、成绩、教师编号、选课时间）
test1_student_course：sid char 12 not null、cid char 6 not null、
score numeric 5，1（其中5代表总长度，1代表小数点后面长度）、tid char 6、sctime date。
4．	给表test1_student插入2行数据。
5．	给表test1_course插入2行数据。
6．	给表test1_student_course插入2行数据。

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．	找出没有选修任何课程的学生的学号、姓名(即没有选课记录的学生）。
自己认为查询语句正确后，通过下面语句将查询语句创建成视图test2_01
Create or replace view test2_01 as select ……
然后就可以点击实验二的题目1的【交卷验证】，验证正确性，正确后就有得分。
如果结果不对，会提示错误几行或者多几行或者少几行，这时你需要思考解题思路有没有遗漏的情况，所有情况是否都有相应条件进行约束，或者显示所得数据，一条一条查看是否符合条件。
特别提醒：
1、逻辑错误尽量不要去问老师错在哪里了，老师虽然知道答案但那不是唯一答案，在不了解你的解题思路情况下，根本不知道你的错误是思路错误，还是实现的语句有错误。
2、如果出现错误一两行情况，可以私信QQ群李老师，提醒你可能你忽略的特殊情况。
2．	找出至少选修了学号为“200900130417”的学生所选修的一门课的学生的学号、姓名(不包含这名同学)。
自己认为查询语句正确后，通过下面语句将查询语句创建成视图test2_02
Create or replace view test2_02 as select ……
然后就可以点击实验二的题目2的【交卷验证】，验证正确性，正确后就有得分。
以下各题操作类似。
3．	找出至少选修了一门其先行课程号为“300002”号课程的学生的学号、姓名。
4．	找出选修了“操作系统”并且也选修了“数据结构”，但是没有选修“程序设计语言”的学生的学号、姓名。
5．	找出姓名叫“李龙”的学生的学号及其选修全部课程的课程号、课程名和成绩。
6．	查询2010级、计算机科学与技术学院、操作系统的学生成绩表，内容有学号、姓名、成绩。
7．	查询所有不姓张、不姓李、也不姓王的学生的学号sid、姓名name
8．	找出有间接先行课的所有课程的课程号、课程名称。
9．	查询选修了300003号课程的学生的sid、name、score
10．	找出选修了所有课程的学生的学号、姓名。

2-1


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

2-2


create or replace view test2_02 
  as 
select 
    a.sid,a.name
from
   pub.student a
   join
    (select distinct sid from pub.student_course
      where cid in (select cid from course where fcid='300002')
    ) b
    on
    a.sid=b.sid

2-3


create or replace view test2_03
  as 
select 
    a.sid,a.name
from
   pub.student a
   join
    (select sid,z.name
        from 
     (select sid,pub.course.name from pub.sutdent_course a join course b on a.cid=b.cid) x
     join
     (select sid,pub.course.name from pub.sutdent_course a join course b on a.cid=b.cid) y
     on
     x.sid=y.sid and x.name='操作系统' and y.name='数据库'
     left join
     (select sid,pub.course.name from pub.sutdent_course a join course b on a.cid=b.cid) z
     on
     x.sid=z.sid and z.name='程序设计语言'
    ) b
    on
    a.sid=b.sid
where
    z.name is null

2-4


create or replace view test2_04
   as 
select 
    a.sid,b.cid,c.name,b.score
from
   pub.student a
   join
   pub.student_course b
   on    a.sid=b.sid
   join
   pub.course c
   on    b.cid=c.cid
where
   a.name='李龙'

2-5


create or replace view test2_05
   as 
select 
    a.sid,b.cid,c.name,b.score
from
   pub.student a
   join
   pub.student_course b
   on    a.sid=b.sid
   join
   pub.course c
   on    b.cid=c.cid
where
   a.name='李龙'

2-6


create or replace view test2_06
as
select
    sid,name
from
    pub.student a
where
   name not like '张%'
   and
   name not like '李%'
   and
   name not like '王%'

2-7


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

2-8


create or replace view test2_08
as
select
    a.sid,a.name,c.score
from
    pub.student a
    join 
    pub.studen_course b
    on
    a.sid=b.sid
    join
     pub.course  c
    on
    b.cid=c.cid
where
   b.cid='300003'

2-9


create or replace view test2_09
as
select
    a.sid,name
from
    pub.student a
    join 
    pub.student_course b
    on
    a.sid=b.sid
group by
    a.sid,name
having
    sum(b.cid) = (select sum(cid) from pub.course)

2-10


Create or replace view test2_10
as
select
    a.sid,name
from
    pub.student a
    join 
    pub.student_course b
    on
    a.sid=b.sid
group by
    a.sid , name
having
    count(distinct b.cid) = (select count(b.cid) from pub.course)

实验3


二、	实验题目
1．	将pub用户下的Student_31及数据复制到主用户的表test3_01(注意：test3_xx要建成表不是视图，否则删除数据时会显示无此权限)，删除表中的学号不全是数字的那些错误数据，学号应该是数字组成，不能够包含字母空格等非数字字符。
方法之一：用substr函数，例如Substr(sid,1,1)返回学号的第一位，判断是否是数字。
2．	将pub用户下的Student_31及数据复制到主用户的表test3_02，删除表中的出生日期和年龄(截止到2012年的年龄，即年龄=2012-出生年份)不一致的那些错误数据。
函数extract(year from birthday)返回birthday的年份
3．	将pub用户下的Student_31及数据复制到主用户的表test3_03，删除表中的性别有错误的那些错误数据（性别只能够是“男”、“女”或者空值）。
4．	将pub用户下的Student_31及数据复制到主用户的表test3_04，删除表中的院系名称有空格的、院系名称为空值的或者院系名称小于3个字的那些错误数据。
5．	将pub用户下的Student_31及数据复制到主用户的表test3_05，删除表中的班级不规范的那些错误数据，不规范是指和大多数不一致。
这个题知识点是学会用SQL找出不规范的数据，而不是用人工办法找不规范。
提示：寻找不规范有很多解决思路，可以去对比大纲最后的提示。
6．	将pub用户下的Student_31及数据复制到主用户的表test3_06，删除表中的错误数据，不规范的数据也被认为是那些错误数据。
	学号不全是数字；
	出生日期和年龄不一致的(年龄=2012-出生年份)；
	姓名有空格的或者长度小于2个字的；函数length()返回字符串长度。
	性别有错误的（只能够是“男”、“女”、空值）；
	院系名称有空格的、院系名称为空值的；
	院系名称小于3个字的；
	班级数据有错误的（需要先找到班级里面的错误）。
保留最后全部正确的数据。
7．	将pub用户下的Student_course_32及数据复制到主用户的表test3_07，删除其中的错误数据，错误指如下情况：
学号在学生信息pub.student中不存在的；
8．	将pub用户下的Student_course_32及数据复制到主用户的表test3_08，删除其中的错误数据，错误指如下情况：
课程号和教师编号在教师授课表pub.teacher_course中不同时存在的，即没有该教师教该课程；
9．	将pub用户下的Student_course_32及数据复制到主用户的表test3_09，删除其中的错误数据，错误指如下情况：
成绩数据有错误（需要先找到成绩里面的错误）。
这个题知识点是学会用SQL找出错误数据，而不是用人工办法找错误数据。
提示：寻找不规范有很多解决思路，可以去对比大纲最后的提示。
10．	将pub用户下的Student_course_32及数据复制到主用户的表test3_10，删除其中的错误数据，错误指如下情况：
（1）	学号在学生信息pub.student中不存在的；
（2）	课程号在课程信息pub.course中不存在的；
（3）	教师编号在教师信息pub.teacher中不存在的；
（4）	课程号和教师编号在教师授课表pub.teacher_course中不存在的；
（5）	成绩数据有错误（需要先找到成绩里面的错误）。
保留最后正确的数据。

3-1


create table test3_01 as
(select * from pub.Student_31);
 
delete from test3_01
where not regexp_like(sid,'^[0-9]+$');

3-2


create table test3_02 as
(select * from pub.Student_31);
 
delete from test3_02
where (2012-extract(year from birthday))!=age;

3-3


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 not like '%___%' or dname is null;

3-5


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

3-6


create table test3_06 as
(select * from pub.Student_31);
 
delete from test3_06
where  not regexp_like(sid,'^[0-9]+$')
        or
       name like '% %' or name not like '%__%'
        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}$')
        or
       (2012-extract(year from birthday))!=age

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

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 not in (select cid from pub.teacher_course) or
tid not in (select tid from pub.teacher_course) or
  (cid,tid) not in (select cid,tid from pub.teacher_course) or
score <0 or score >100

实验4


实验四	复制表、修改表结构、修改数据
三、	实验内容
利用oracle管理平台完成对表的结构、数据进行修改，每一个问题可以通过多个SQL语句完成。
四、	实验题目
1．	将pub用户下表student_41及数据复制到主用户的表test4_01中，使用alter table语句为表增加列：“总成绩:sum_score”。
使用update语句,利用pub.student_course，统计 “总成绩”；
2．	将pub用户下表student_41及数据复制到主用户的表test4_02中，使用alter table语句为表增加列“平均成绩:avg_score” （小数点后保留1位）。
利用pub.student_course，统计“平均成绩”，四舍五入到小数点后1位
3．	将pub用户下表student_41及数据复制到主用户的表test4_03中，使用alter table语句为表增加列：“总学分:sum_credit”。
使用update语句,利用pub.student_course、pub.course，统计 “总学分”；
这是需要注意：成绩及格才能够计算所得学分,一门课多个成绩都及格只计一次学分。
4．	将pub用户下表student_41及数据复制到主用户的表test4_04中。
根据列院系名称dname到pub.department找到对应院系编号did，将对应的院系编号回填到院系名称列dname中，如果表中没有对应的院系名称，则列dname中内容不变仍然是原来的内容。
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。
说明：执行update后，在查询表中数据，可能出现顺序变化，这是正常，因为数据在表中是无序。需要顺序的时候可以通过orderby实现。
6．	将pub用户下的Student_42及数据复制到主用户的表test4_06中,对表中的数据进行整理，修复那些不规范的数据：
剔除姓名列中的所有空格；
7．	将pub用户下的Student_42及数据复制到主用户的表test4_07中,对表中的数据进行整理，修复那些不规范的数据：
对性别列进行规范（需要先确定哪些性别数据不规范，也就是那些和大多数不一样的就是不规范的）；
8．	将pub用户下的Student_42及数据复制到主用户的表test4_08中,对表中的数据进行整理，修复那些不规范的数据：
对班级列进行规范（需要先确定哪些班级不规范）。
9．	将pub用户下的Student_42及数据复制到主用户的表test4_09中,对表中的数据进行整理，修复那些不规范的数据：
年龄为空值的根据出生日期设置学生年龄（截止到2012年的年龄，即年龄=2012-出生年份），年龄不为空值的不要改变。
10．	将pub用户下的Student_42及数据复制到主用户的表test4_10中,对表中的数据进行整理，修复那些不规范的数据：
（1）	剔除姓名列中的所有空格；
（2）	剔除院系名称列中的所有空格； 
（3）	对性别列进行规范（需要先确定哪些性别数据不规范，也就是那些和大多数不一样的就是不规范的）；
（4）	对班级列进行规范（需要先确定哪些班级不规范）。
（5）	年龄为空值的根据出生日期设置学生年龄（截止到2012年的年龄，即年龄=2012-出生年份），年龄不为空值的不要改变。

4-1


create table test4_01 as
(select * from pub.Student_41);
 
alter table test4_01 add sum_score int;
 
update test4_01 a
set sum_score=(
    select sum(score) 
    from pub.student_course P
    where  a.sid=P.sid
)

4-2


create table test4_02 as
(select * from pub.Student_41);
 
alter table test4_02 add avg_score int;
 
alter table test4_02 drop column avg_score;
 
alter table test4_02 add avg_score numeric(3,1);
 
update test4_02 a
set avg_score=round((
    select avg(score) 
    from pub.student_course P
    where  a.sid=P.sid
),1)

4-3


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(temp.credit)
    from 
    (select a.sid,b.cid,max(score) score,c.credit
     from test4_03 a join pub.Student_course b on a.sid=b.sid
     join pub.course c on b.cid=c.cid 
     group by a.sid,b.cid,c.credit
    ) temp
    where
    t.sid=temp.sid and score>=60
)

4-4


drop table if exists test4_04;
 
create table test4_04 as
(select * from pub.Student_41);
 
update test4_04 t
set dname= (
    select did
    from  pub.department a
    where t.dname=a.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;
 
update test4_05 a
set sum_score=(
    select sum(score) 
    from pub.student_course P
    where  a.sid=P.sid
)
 
alter table test4_05 add avg_score numeric(3,1);
 
update test4_05 a
set avg_score=round((
    select avg(score) 
    from pub.student_course P
    where  a.sid=P.sid
),1)
 
alter table test4_05 add did varchar(2);
 
update test4_05 set did ='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)
;
 
alter table test4_05 add sum_credit int;
 
update test4_05 t
set sum_credit=(
    select sum(temp.credit)
    from 
    (select a.sid,b.cid,max(score) score,c.credit
     from test4_05 a join pub.Student_course b on a.sid=b.sid
     join pub.course c on b.cid=c.cid 
     group by a.sid,b.cid,c.credit
    ) temp
    where
    t.sid=temp.sid and score>=60
)

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
set age=(2012-extract(year from birthday))
where 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 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

实验5


二、实验题目
1．	在学生表pub.student中统计名字（姓名的第一位是姓氏，其余为名字，不考虑复姓）的使用的频率，将统计结果放入表test5_01中
2．	在学生表pub.student中统计名字（姓名的第一位是姓氏，不作统计，名字指姓名的第二个之后的汉字）的每个字使用的频率，将统计结果放入表test5_02中，表结构如下。
3．	先创建“学院班级学分达标情况统计表1”test5_03，依据pub.student， pub.course，pub.student_course统计形成表中各项数据，成绩>=60为及格计入学分，总学分>=10算作达标，院系为空值的数据不统计在下表中，表结构：院系名称dname、班级class、学分达标人数p_count1、学分未达标人数p_count2、总人数p_count。
排错提示：
平台提示有多行数据错误，特别是行数比较多，可以从结果的第一行开始，一个列一个列进行单独统计，找到错误的是那个列数，然后对照你形成这个列的部分寻找原因。假设输出结果如上图，可以这样统计：
select count(*) from pub.STUDENT where dname='计算机学院' and class=2006，检查统计计算机学院2006级是不是9。
Select count(*) …………检查统计计算机学院2006级达标人数是不是4。
Select count(*) …………检查统计计算机学院2006级不达标人数是不是5。
通过这样办法找到错在那个列，然后再去对应的SQL部分找错误就容易了。
4．	创建“学院班级学分达标情况统计表2”test5_04，依据pub.student， pub.course，pub.student_course统计形成表中各项数据，成绩>=60为及格计入学分，2008级及之前的班级总学分>=8算作达标，2008级之后的班级学分>=10算作达标，院系为空值的数据不统计在下表中，表结构：院系名称dname、班级class、学分达标人数p_count1、学分未达标人数p_count2、总人数p_count。
5．	查询各院系（不包括院系名称为空的）的数据结构平均成绩avg_ds_score、操作系统平均成绩avg_os_score，平均成绩四舍五入到个位，创建表test5_05
6．	查询”计算机科学与技术学院”的同时选修了数据结构、操作系统两门课的学生的学号sid、姓名name、院系名称dname、数据结构成绩ds_score、操作系统成绩os_score，创建表test5_06
7．	查询计算机科学与技术学院的选修了数据结构或者操作系统的学生的学号sid、姓名name、院系名称dname、数据结构成绩ds_score、操作系统成绩os_score，创建表test5_07
8．	查询计算机科学与技术学院所有学生的学号sid、姓名name、院系名称dname、数据结构成绩ds_score、操作系统成绩os_score，创建表test5_08，表结构及格式如下
9．	创建“成绩及格学生每个成绩人数百分比统计表” 视图test5_09，依据pub.student_course统计形成视图中各项数据，成绩>=60为及格，视图结构：成绩score、对应成绩人数count1、及格总人数count0、站总人数的百分比percentage（即前面两列相除后乘以100）。其中百分比四舍五入到小数点后面2位。输出按成绩排序。上面说的“人数”理解成 “人次”更加准确，亦既一个同学同一门考试考两次，算两人次。
视图内容示例如下：
10．	创建“每个课程成绩在60-99分之间每段成绩的人数百分比统计表”视图test5_10，依据pub.course，pub.student_course统计形成视图中各项数据，仅仅统计成绩在60-99（包含60和99）之间成绩，视图结构：课程编号cid、课程名称cname、成绩score（这个列内容不要包含空格）、对应成绩人数count1、及格总人数count0（仅统计60-99分）、站总人数的百分比percentage（即前面两列相除后乘以100）。其中百分比四舍五入到小数点后面2位。输出按课程编号、成绩排序。上面说的“人数”理解成 “人次”更加准确，亦既一个同学同一门考试考两次，算两人次。

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(2),frequency numeric(4));
 
insert into test5_02
(select
   last,count(*)
from
  (
   (select substr(name,2,1) as last
    from   pub.student
    where length(name)>=2)
   union all
   (select substr(name,3,1) as last
    from   pub.student
    where length(name)>=3)
  ) 
   group by last
);

5-3


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


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,p1,psum-p1,psum
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 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-5


create or replace view test5_05 as
select
   dname,a as Avg_ds_score,b as Avg_os_score
from
   (select dname ,round(avg(max_ds)) a
    from 
   (select sid,dname,max(score) max_ds
   from pub.student a natural join pub.student_course b join pub.course c on b.cid=c.cid
   where dname is not null and c.name='数据结构'
   group by sid,dname ) temp
   group by  dname)
 
   natural join
 
   (select dname ,round(avg(max_ds)) b
    from 
   (select sid,dname,max(score) max_ds
   from pub.student a natural join pub.student_course b join pub.course c on b.cid=c.cid
   where dname is not null and c.name='操作系统'
   group by sid,dname ) temp
   group by  dname)
;

5-6


 
create or replace view test5_06 as
(select  sid,n.name,n.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 c1   on   a.cid=c1.cid
join  pub.course c2   on   b.cid=c2.cid
join  pub.student n  on  n.sid=a.sid
where  c1.name='数据结构' and c2.name='操作系统' and n.dname='计算机科学与技术学院'
group by   sid,n.name,n.dname
);

5-7


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

5-8


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

5-9


create or replace view test5_09 as
with temp as
(select score ,sid from pub.student_course where score>=60)
select 
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
select
   cid,
  (select name from pub.course where cid=a.cid) cname,
  to_char(trunc(score,-1),'fm00')||'-'||to_char(trunc(score,-1)+9,'fm00') Score,
  count(*) count1,
  (select count(*) from pub.student_course t where a.cid=t.cid and score>=60 and score<100) count0,
  round(count(*)/(select count(*) from pub.student_course where cid=a.cid and score>=60 and score<100)*100,2) percentage
from
   pub.student_course a
where
   score>=60 and score<=99
group by
   cid, to_char(trunc(score,-1),'fm00')||'-'||to_char(trunc(score,-1)+9,'fm00')
;

实验6


创建视图、删除视图
一、	实验内容
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-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
   b.sid,b.name,sum(Sco) sum_score
from
  (
  select b.sid,b.cid,max(score) Sco
  from pub.student_course b
  join pub.course c on b.cid=c.cid
  group by b.sid,b.cid
   ) a
  right join  pub.student b  on a.sid=b.sid
where
   class=2009 and dname='软件学院'
group by 
  b.name,b.sid
;

6-3


create or replace view test6_03 as
select
  a.cid ,
  (select name from pub.course tt where tt.cid=a.cid) name,
  max_score,
  (case when max_score_count is null then 0 else  max_score_count end)  max_score_count
from
   pub.course a
   left join
   (
  select
   cid,score max_score,count(distinct sid) max_score_count
  from 
  pub.student_course a
   where
  a.score = (select max(score) from pub.student_course temp where temp.cid=a.cid)
   group by  cid,score 
   ) b
   on a.cid=b.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='300002'  or  cid='300005')
) 
where
sex='男' 
  and
sid not in
(select  distinct sid
from   pub.student_course 
where  cid='300001' and  score>=60 )

6-5


create or replace view test6_05 as
select 
  u.sid,
  name,
  round(avg(score),0) avg_score,
  sum(score) sum_score
from
pub.student_course u
join 
pub.student a
on u.sid=a.sid
where age=20
group by u.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 
  u.sid, name
from
(select 
  sid,cid,min(score) sc
from
  pub.student_course
group by
  sid,cid) u
join 
pub.student a
on u.sid=a.sid
where sc>=60
group by
u.sid,name
having 
count(*)=140
;

6-8


create or replace view test6_08 as
select 
  u.sid, name
from
(select 
  sid,cid,max(score) sc
from
  pub.student_course
group by
  sid,cid) u
join 
pub.student a
on u.sid=a.sid
where sc>=60
group by
u.sid,name
having 
count(*)=140
;

6-9


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

6-10


create or replace view test6_10 as
select
substr(name,1,1) second_name , sum(*) p_count
from
pub.student
group by
substr(name,1,1);
 
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创建一个仅仅一个索引test7_01_index，保证上面SQL耗时在1秒内。
三、	题目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创建一个仅仅一个索引test7_02_index，保证下面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．	验证问题：用计划验证下面这个查询能使用test7_02_index的索引吗？
select * from 
(select sid,name,birthday,
	(select count(*) from test7_02 where name=t1.name) samename 
from pub.student_testindex t1)
where samename=7
四、	题目3
1．	pub用户下表student已经用下面两句SQL创建了两索引。
Create index student_birthday on student(birthday);
Create index student_name on student(name);
2．	下面SQL在访问pub.student时不能用索引student_name因此耗时超过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
1．	pub用户下表student已经用下面两句SQL创建了两索引。
Create index student_birthday on student(birthday);
Create index student_name on student(name);
2．	下面SQL在访问pub.student时不能用索引student_birthday因此耗时超过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 ……。
六、	题目5
1．	pub用户下表student已经用下面两句SQL创建了两索引。
Create index student_birthday on student(birthday);
Create index student_name on student(name);
2．	下面SQL在访问pub.student时不能用索引student_birthday因此耗时超过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 ……。

7-1


create table test7_01 as 
select sid,name,birthday from pub.student;
create index suoyin 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 index02
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

7-6

(??怎么少了一个题??我是按着报告写的啊)

实验8


实验一	提交commit和回滚rollback、实体授权
一、	实验内容
测试提交commit和回滚rollback的作用,了解锁等待、授权等知识。
二、	实验步骤
1．	使用主用户userID登录数据库，简称主窗口。
2．	使用备用用户userbID登录数据库，简称备用窗口。
3．	关闭自动提交复选框。按【提交】执行commit，按【回滚】执行rollback。
4．	主用户访问备用用户的表之前，需要在备用账号中将相应的表的相应的权限授权给主用户，这样主用户才可以查询操作备用用户的相应的表。
在主用户下可以执行select * from userbId.test8_00查询备用用户的表test8_00的数据，如果没有授权，则会提示表没有表找到。
如果备用用户执行grant select on  test8_00 to userID，即授权表test8_00的select权限给用户userID，上面的查询语句就可以正确执行，并查询到相应的结果。
5．	常用的授权、命令:
grant select on  test8_00 to userID授权表test8_00的select权限给用户userID。
grant update on  test8_00 to userID授权表test8_00的update权限给用户userID。
grant insert on  test8_00 to userID授权表test8_00的insert权限给用户userID。
grant delete on  test8_00 to userID授权表test8_00的delete权限给用户userID。
grant all on  test8_00 to userID授权表test8_00的all权限给用户userID。
revoke select on  test8_00 from userID收回表test8_00的insert权限从用户userID。
6．	在备用用户下将pub.teacher复制到test8_00中，然后将其所有权限给主用户。
7．	按表中序号在相应窗口执行对应的命令（主用户访问备用用户表需要授权）。
8．	假设数据中有张老师，通过上面的操作以后，他在每次查询的时候的年龄是多少？根据你的判断得出结果，然后按步骤进行实验验证，在主用户下创建一个表test8_10(test varchar(20),age numeric (3)),插入10行数据，分表存放10个结果。

8-1


create table test8_10(test varchar(20),age numeric (3));
 
insert into test8_10 values(‘结果1’,88)
insert into test8_10 values(‘结果2’,90)
insert into test8_10 values(‘结果3’,90)
insert into test8_10 values(‘结果4’,86)
insert into test8_10 values(‘结果5’,90)
insert into test8_10 values(‘结果6’,90)
insert into test8_10 values(‘结果7’,86)
insert into test8_10 values(‘结果8’,86)
insert into test8_10 values(‘结果9’,76)
insert into test8_10 values(‘结果10’,86)

实验9


实验一	条件数据插入
一、	实验内容
学会复制表结构、学会插入数据，特别是学会如何避免重复插入，也就是如何避免插入已经存在的数据。
二、	实验题目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方式完成。
三、	实验题目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方式完成。

9-1


create table test7_01 as 
select sid,name,birthday from pub.student;
create index suoyin on test7_01 substr(name,1,1);
 
 
 
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 index02
ON test7_02 (birthday,name);
 
 
 
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
 
 
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
 
 
 
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 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 suo 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 test9_01)
);
 
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 su1 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)
);

关于要注意的一些问题

我在实验开始的三天后写完的,不知道有些东西后面会不会说到,这里就先说说

(1)提交的时候要注意,这是一个事务,提交对应的是commit,回滚rollback,这俩时清空当前当前窗口的日志,然后进行修改

commit,就代表当前执行确定了,无法再回撤

(2)关于数据,有很多数据非常奇怪,比如135分的数据结构,-1分的机组,没有性别的同学

对自己的查询自信一点

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/weixin_40725706/article/detail/473176