山东大学数据库实验一、二、三、四、五、六、七、八、九答案（大集合）

实验目的

不仅仅熟练掌握 SQL 的语法，更加主要目的熟练掌握各种常用的、逻辑复杂的 SQL。

经验之谈

实验期间，如果出现错误找不到，不要轻易怀疑答案，更不要怀疑 Oralce 数据库系统，工 作以后也要记住一句经验之谈，“一定是你错了，只是不知道自己错在哪里了”。

说在前面

我的答案一定不是最好的，希望看到这篇文章的同学不要只是复制答案，这样做没有意义，正确的做法是看到正确答案后自己独立思考理解答案，将知识变为自己的东西。

实验用表

pub.teacher_course

pub.teacher

pub.student_course

pub.student

pub.course

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

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：cid char 6 not null、name varchar 40 not null、fcid char 6、
credit numeric 4，1（其中4代表总长度，1代表小数点后面长度）。

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

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

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

1-4
给表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

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

1-5
给表test1_course插入如下2行数据。
注意空值的插入使用null
课程号 课程名 先行课程号 学分
300001 数据结构 2
300002 数据库 300001 2.5

insert into test1_course
values (‘300001’,‘数据结构’,null,‘2’)
insert into test1_course
values (‘300002’,‘数据库’,‘300001’,‘2.5’)

1-6
给表test1_student_course插入如下2行数据。
学号 课程号 成绩 教师编号 选课时间
200800020101 300001 91.5 100101 2009-7-15 09:09:09
200800020101 300002 92.6 100102 2009-7-15 10:10:10

insert into test1_student_course
values ( ‘200800020101’,‘300002’,92.6,‘100102’,to_date(‘20090715101010’,‘yyyymmddhh24miss’) )

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

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

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

create or replace view test2_02 as
(select sid,name
from pub.student
where sid in
( select sid
from pub.student_course
where cid in
(select cid
from pub.student_course
where sid=200900130417 ) ))
minus
(select sid,name
from pub.student
where sid=200900130417)//最后要去掉那一个学生，不包括该学号的学生

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

create 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
找出选修了"操作系统"并且也选修了"数据结构"的学生的学号、姓名。

Create or replace view test2_04 as
select s.sid,name
from pub.student S
where not exists
(( select cid
from pub.course
where name=‘数据结构’ or name=‘操作系统’ )
minus
( select cid
from pub.student_course T
where t.sid=s.sid))

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

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

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

create or replace view test2_06 as
select a.cid,a.name,max_score,max_score_count
from
( select cid,name
from pub.course) a,
( select cid,max(score) max_score
from pub.student_course
group by cid) b,
(
select sc.cid,count(distinct sid) max_score_count
from pub.student_course sc,
( select cid,max(score) max_score from pub.student_course
group by cid ) max_sc
where sc.cid=max_sc.cid and sc.score=max_sc.max_score
group by sc.cid) d
where a.cid=b.cidand b.cid=d.cid
//此题较难，建议多思考几遍

2-7
查询所有不姓张、不姓李、也不姓王的学生的学号sid、姓名name

create or replace view test2_07 as
(select sid,name
from pub.student)
minus
(select sid,name
from pub.student
where name like ‘张%’ or name like ‘李%’ or name like ‘王%’

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

create or replace view test2_08 as
( select substr(name,1,1) second_name ,count(*) p_count
from pub.student
group by substr(name,1,1));
//用到了特殊的聚集函数

2-9
查询选修了300003号课程的学生的sid、name、score

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

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

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

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

create table test3_01 as
select * from pub.student_31
where regexp_like(sid,’¹{12} ′ ) 此 处 使 用 正 则