山东大学软件学院数据库实验1-9

最新推荐文章于 2023-11-03 19:24:50 发布

Jevies

最新推荐文章于 2023-11-03 19:24:50 发布

阅读量9.2k

点赞数 8

文章标签： sql

本文链接：https://blog.csdn.net/qq_45794281/article/details/115657866

版权

SDU 数据库系统实验

实验一
实验二
实验三
实验四
实验五
实验六
实验七
实验八
实验九
- 9-1
- 9-2

实验一

1-1创建test1_student表

创建学生信息表（学生编号、姓名、性别、年龄、出生日期、院系名称、班级）：
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。
建表语句常见错误如下：
关键词拼写错误，少关键词、少逗号、少括号。
SQL:

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创建test1_course表

创建课程信息表(仅考虑一门课程最多一个先行课的情况):
课程编号、课程名称、先行课编号、学分
test1_course：cid char 6 not null、name varchar 40 not null、fcid char 6、
credit numeric 4，1（其中4代表总长度，1代表小数点后面长度）。
SQL:

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

1-3创建teset1_student_course表

创建学生选课信息表（学号、课程号、成绩、教师编号、选课时间）
test1_student_course：sid char 12 not null、cid char 6 not null、
score numeric 5，1（其中5代表总长度，1代表小数点后面长度）、tid char 6, sctime date
SQL:

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表test1_student插入2行数据

给表test1_student插入如下2行数据。
输入日期类型数据的格式，插入一句有严格语法格式，不要想当然自创语法格式。：
采用：insert into t1 values(‘200700030101’,‘赵中华’,‘男’,19,date ‘2012-02-02’,‘计算机学院’,‘2010’)
或者
采用：insert into t1 values(200700030101,‘赵中华’,‘男’,19,to_date(‘20120202’,‘yyyymmdd’),‘计算机学院’,‘2010’)

     学号          姓名  性别 年龄  出生日期  院系名称    班级

200800020101 王欣女 21 1994/2/2 计算机学院 2010
200800020102 李华女 20 1995/3/3 软件学院 2009
SQL:

insert into test1_student
     values('200800020101','王欣','女',21,date '1994-02-02','计算机学院','2010');

insert into test1_student
     values('200800020102','李华','女',20,date '1995-03-03','软件学院','2009');

1-5表test1_course插入2行数据

给表test1_course插入如下2行数据。

注意空值的插入使用null

课程号课程名先行课程号学分
300001 数据结构 2
300002 数据库 300001 2.5
SQL:

insert into test1_course
     values('300001','数据结构',null,'2');

insert into test1_course
     values('300002','数据库','300001','2.5');

1-6表test1_student_course插2行

给表test1_student_course插入如下2行数据。

学号课程号成绩教师编号选课时间
200800020101 300001 91.5 100101 2009-7-15 09:09:09
200800020101 300002 92.6 100102 2009-7 10:10:10
SQL:

insert into test1_student_course
     values('200800020101','300001','91.5','100101',to_date ('2009-07-15 09:09:09','yyyy-mm-dd hh24-mi-ss'))；

insert into test1_student_course
     values('200800020101','300002','92.6','100102',to_date ('2009-07-15 10:10:10','yyyy-mm-dd hh24-mi-ss'))；

实验二

2-1查询1

找出没有选修任何课程的学生的学号、姓名(即没有选课记录的学生）。
自己认为查询语句正确后，通过下面语句将查询语句创建成视图test2_01
Create or replace view test2_01 as select ……
然后就可以点击实验二的题目1的【交卷验证】，验证正确性，正确后就有得分。
SQL:

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

注意as

2-2查询2

找出至少选修了学号为“200900130417”的学生所选修的一门课的学生的学号、姓名(不包含这名同学)。
自己认为查询语句正确后，通过下面语句将查询语句创建成视图test2_02
Create or replace view test2_02 as select ……
然后就可以点击实验二的题目2的【交卷验证】，验证正确性，正确后就有得分。
以下各题操作类似。
SQL:

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

2-3查询3

找出至少选修了一门其先行课程号为"300002"号课程的学生的学号、姓名。
SQL:

create or replace view test2_03 as
     select  sid,name
     from pub.student_course natural join pub.student
     where cid in(
                          select cid 
                          from  pub.course
                          where fcid='300002');

2-4查询4

找出选修了“操作系统”并且也选修了“数据结构”，但是没有选修“程序设计语言”的学生的学号、姓名。
SQL:

create or replace view test2_04 as
     select  sid,name
     from pub.student_course natural join pub.student 
     where cid in(select cid
                         from pub.course
                         where name='数据结构')
     intersect(select  sid,name
                   from pub.student_course natural join pub.student 
                   where cid in(select cid
                                       from pub.course
                                       where name='操作系统'))
    minus(select  sid,name
                   from pub.student_course natural join pub.student 
                   where cid in(select cid
                                       from pub.course
                                       where name='程序设计语言'));

2-5查询5

查询20岁的所有有选课的学生的学号、姓名、平均成绩(avg_score，此为列名，下同)（平均成绩四舍五入到个位）、总成绩(sum_score)

Test2_05有四个列，并且列名必须是：sid、name、avg_score、sum_score。通过下面方式实现列名定义：
create or replace view test2_05 as select sid,name，（表达式） avg_score,（表达式） sum_score from ……
SQL:

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

2-6查询6

查询所有课的最高成绩、最高成绩人数，
test2_06有四个列：课程号cid、课程名称name、最高成绩max_score、最高成绩人数max_score_count（一个学生同一门课成绩都是第一，只计一次）。
如果没有学生选课，则最高成绩为空值,最高成绩人数为零。
提示1：任何select 确保只返回一个结果 from …… where ……t1.xx=t2.xx （条件中还可以出现主表的列来限制每行结果的不同）可以是另外一个select的一个输出表达式。格式如：select sid，（select……）列别名 from …… where ……。
提示2：任何select 确保只返回一个结果 from …… where ……（不能引用主表来）可以出现在另外一个sql的条件表达式中。格式如：select …… from …… where xx=（select……）。
提示3：任何select …… from …… where ……可以是另外一个sql的表，即派生表。格式如：select …… from student，（select……）表别名 where ……。
提示4：这个题目比较复杂，也有很多解决思路，多多换换思路解决这个问题，如果没有思路可以去查看大纲最后的提示1。

SQL:

2-7 查询7

查询所有不姓张、不姓李、也不姓王的学生的学号sid、姓名name
SQL:

create or replace view test2_07 as
select sid,name
from pub.student
where name not in(select name 
                  from pub.student
                  where name like '张%' or name like '李%' or name like'王%');

~~srds,这样写麻烦了~~

2-8 查询8

查询学生表中每一个姓氏及其人数（不考虑复姓），test2_08有两个列：second_name、p_count
SQL:

`create or replace view test2_08 as 
select count(name)p_count,substr(name,1,2)second_name
from pub.student
group by  substr(name,1,2);`

2-9 查询9

查询选修了300003号课程的学生的sid、name、score
SQL:

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

2-10 查询10

找出同一个同学同一门课程有两次或以上不及格的所有学生的学号、姓名（即一门课程需要补考两次或以上的学生的学号、姓名）。
SQL:

create or replace view test2_10 as 
     select sid,name
     from (select sid,cid,count(score)time
               from pub.student_course
               where score<60
               group by sid,cid)natural join pub.student
     where time>1;

Pay Attention to the count inside.
记得子嵌套结束后natural join

实验三

3-1 删除学号错误的错误数据

将pub用户下的Student_31及数据复制到主用户的表test3_01，删除表中的学号不全是数字的那些错误数据，学号应该是数字组成，不能够包含字母空格等非数字字符。
方法之一：用substr函数，例如Substr(sid,1,1)返回学号的第一位，判断是否是数字。

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

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

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

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

delete要从test3_02里面选数据，不能从pub.student_31里选，否则会出现数据过多

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

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

 create table test3_03 as(
select *
from pub.student_31);
delete
from test3_03
where not(sex = '女' or sex = '男' or sex is null);

不能写成
此写法错误
然后如果第二次操作错误，此时表中已经空了，想要再次尝试，可以先把test3_03 drop,重新create即可。

3-4 删除院系错误的错误数据

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

 create table test3_04 as(
select *
from pub.student_31);
delete
from test3_04
where dname is null or length(dname)<3 or REGEXP_LIKE(dname, '( )+');

length()返回的是字符，不是字节

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

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

create table test3_05 as(
select *
from pub.student_31);
delete 
from test3_05
where substr(class,5,1) is not null;

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

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

基本和前几题一样，没啥好说的

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

将pub用户下的Student_course_32及数据复制到主用户的表test3_07，删除其中的错误数据，错误指如下情况：
学号在学生信息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 删除没有教师教该课程的错误

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

create table test3_08 as(
select *
from pub.student_course_32);
delete 
from test3_08
where (cid,tid)not in(select A.cid ,A.tid
                         from pub.teacher_course A, pub.student_course_32 B
                         where A.tid=B.tid and A.cid=B.cid
                        );

注意**(cid,tid)**

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

将pub用户下的Student_course_32及数据复制到主用户的表test3_09，删除其中的错误数据，错误指如下情况：

成绩数据有错误（需要先找到成绩里面的错误）。
这个题知识点是学会用sql找出错误数据，而不是用人工办法找错误数据。
提示：寻找不规范有很多解决思路，可以去对比大纲最后的提示。

create table test3_09 as(
select *
from pub.student_course_32);
delete 
from test3_09
where score>100 or score<0;

3-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 A.cid ,A.tid
                         from pub.teacher_course A, pub.student_course_32 B
                         where A.tid=B.tid and A.cid=B.cid
                        ) or score>100 or score<0;

实验四

4-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
set sum_score=(select sum(score)
                           from pub.student_course A
                          where A.sid=test4_01.sid
 );

不加where 会出错

4-2 统计平均成绩

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

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

alter时类型应为numeric,否则update计算avg时会出错

4-3 统计总学分

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

create table test4_03 as(
select *
from pub.student_41);
alter table test4_03
add sum_credit int;
update test4_03
set sum_credit=(select sum(credit)
                           from (select sid,cid,max(score)score
                                     from pub.student_course
                                     group by sid,cid) A natural join pub.course                      
                           where score>=60 and A.sid=test4_03.sid);

*Pay Attention to: This from clause!

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
set dname=(select did
                    from pub.department A
                    where A.dname=test4_04.dname)
where test4_04.dname in(select dname
                                          from pub.department);

4-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。

create table test4_05 as(
select *
from pub.student_41);

alter table test4_05
add did varchar(2);
update test4_05
set dname=‘00’
where test4_04.dname not in(select dname
from pub.department);


update test4_05
set did=(select did
                        from pub.DEPARTMENT
                        where pub.DEPARTMENT.DNAME=test4_05.dname)
where dname in(select dname from pub.DEPARTMENT)

update test4_05
set did=(select did
                        from pub.DEPARTMENT_41 
                        where pub.DEPARTMENT_41.DNAME=test4_05.dname)
where dname in(select dname from pub.DEPARTMENT_41）

update test4_05
set did='00' 
where did is null

4-6 剔除姓名中的空格

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

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

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

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

create table test4_08 as(
select *
from pub.student_42);
update test4_08
set class=translate(class,'/级','/');

4-9 计算年龄

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

create table test4_09 as(
select *
from pub.student_42);
update test4_09
set age=2012-extract(year from birthday);

4-10 几项内容综合

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

实验五

5-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
(first_name,frequency)
select substr(name,2), count(*)
from pub.student
group by substr(name,2)

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

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

create table test5_02
(letter varchar(2),
 frequency numeric(4));
insert into test5_02
(letter,frequency)
select lastname,count(*)
from((select substr(name,2,1)lastname
from pub.student)
union all(select substr(name,3,1)lastname
from pub.student))
where lastname is not null
group by lastname ;

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

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

5-6 统计报表2

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

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

5-7 统计报表3

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

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

连接词不一样

5-8 统计报表4

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

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

实验六

6-1 视图1

例如：找出年龄小于20岁的所有学生的学号、姓名、年龄
正确执行:create view test6_00 as select sid，name，age from pub.student where age>20
Oracle扩展后方便写法：
create or replace view test6_00 as select sid，name，age from pub.student where age>20
直行select count(*) from test6_00 检查是否能够5分钟内查询出全部结果，如果超时说明可能有错误，这种情况下严禁执行"update dbtest set test=6"进行交卷。
找出年龄小于20岁且是"物理学院"的学生的学号、姓名、院系名称,按学号排序。

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

看清楚最后一行才是题目

6-2 视图2

查询统计2009级、软件学院每个学生的学号、姓名、总成绩（列名sum_score）（如果有学生没有选一门课，则总成绩为空值）。

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

注意括号里面的限定条件
并且如果不用左外自然连接就要用using

6-3 视图3

查询2010级、计算机科学与技术学院、操作系统的学生成绩表，内容有学号、姓名、成绩。

 create or replace view test6_03 as 
select sid,name,score
from pub.student natural join pub.student_course
where  dname='计算机科学与技术学院' and class=2010 and cid=300005

注意课程是操作系统

6-4 视图4

找出选修"数据库系统"课程，且成绩大于90的学生的学号、姓名

create or replace view test6_04 as 
select sid,name
from pub.student natural join pub.student_course
where  score>90 and cid=300003

6-5 视图5

找出姓名叫"李龙"的学生的学号及其选修全部课程的课程号、课程名和成绩。

create or replace view test6_05 as 
select sid,cid,B.name,score
from (select sid,cid,score
         from pub.student_course
         where sid in(select sid
                              from pub.student
                              where name='李龙'))A  join pub.course B using(cid)

注意student和course表都有name因此不能直接natural join 的问题，于是用嵌套子句查询

6-6 视图6

找出选修了所有课程的学生的学号、姓名

create or replace view test6_06 as 
select sid,name
from pub.student A
where not exists(
(select cid
         from pub.student_course) 
minus
(select cid
from pub.student_course natural join pub.student
where A.sid=sid))

6-7 视图7

找出选修了所有课程并且所有课程全部通过的学生的学号、姓名

create or replace view test6_07 as 
select sid,name
from pub.student A
where not exists(
(select cid
         from pub.student_course) 
minus
(select cid
from pub.student_course natural join pub.student
where score>=60 and A.sid=sid))

6-8 视图8

检索先行课的学分为2的课程号、课程名。

create or replace view test6_08 as 
select A.cid,A.name
from pub.course A,pub.course B
where A.fcid=B.cid and B.credit=2

6-9 视图9

查询统计2010级、化学与化工学院的学生总学分表，内容有学号、姓名、总学分sum_credit。

6-10 视图10

找出有间接先行课的所有课程的课程号、课程名称。
create or replace view test6_10 as
select A.cid,A.name
from pub.course A,pub.course B
where A.fcid=B.cid and B.fcid is not null

实验七

7-1 修改条件，提高查询Samefirstname相同姓氏的人数

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 INDEX indexFor701
ON test7_01 (substr(name,1,1))

7-2 修改条件，提高查询Samenamebirthday同名同生日的人数

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
CREATE INDEX indexFor702
ON test7_02 (birthday,name)

(name,birthday)会超时

7-3 修改条件，提高查询samefirstname同姓氏的人数

说明：因为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 concat (substr(t1.name,1,1),'%')
) samefirstname 
from pub.student_testindex t1) where samefirstname=7

concat & like

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

实验八

数据修改的提交和回退、实体授权
一、实验内容
启动两个不同浏览器，firefox登录主账号userID、360浏览器登录备用账号userbID，测试提交和回退的作用,了解锁等待、授权知识。
二、实验步骤
1．使用主用户userID登入数据库，简称主窗口。
2．使用备用用户userbID登入数据库，简称备用窗口。
3．关闭自动提交复选框。
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个结果。

create table teat8_10 as
(test varchar(20),age numeric (3));
insert into test8_10 (test,age) values(‘结果1’,88)
insert into test8_10 (test,age) values(‘结果2’,90)
insert into test8_10 (test,age) values(‘结果3’,90)
insert into test8_10 (test,age) values(‘结果4’,86)
insert into test8_10 (test,age) values(‘结果5’,90)
insert into test8_10 (test,age) values(‘结果6’,90)
insert into test8_10 (test,age) values(‘结果7’,86)
insert into test8_10 (test,age) values(‘结果8’,86)
insert into test8_10 (test,age) values(‘结果9’,76)
insert into test8_10 (test,age) 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(
sid char(12) not null ,
name varchar2(10) not null,
sex char(2), 
age int, 
birthday date,
dname varchar2(30), 
class varchar2(10) );

create UNIQUE index ssid 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(
sid char(12) not null ,
name varchar2(10) not null,
sex char(2), 
age int, 
birthday date,
dname varchar2(30), 
class varchar2(10) )

create UNIQUE index ssid902 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)

Jevies

关注

8
点赞
踩
118

收藏

觉得还不错? 一键收藏
2
评论
山东大学软件学院数据库实验1-9

SDU 数据库系统实验实验一1-1创建test1_student表1-2创建test1_course表1-3创建teset1_student_course表1-4表test1_student插入2行数据1-5表test1_course插入2行数据1-6表test1_student_course插2行实验二2-1查询12-2查询2实验一1-1创建test1_student表创建学生信息表（学生编号、姓名、性别、年龄、出生日期、院系名称、班级）：test1_student：sid char 12 no
复制链接

扫一扫