山东大学数据库实验答案一、二、三

实验一

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

SQL语句：

create table test1_student (sid char(12) not null,name varchar(10),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代表小数点后面长度）

SQL语句：

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

SQL语句：

Create table test1_student_course(sid char(12) not null,cid char(6) not null,score numeric(5,1),tid char(6))

 

1-4.给表test1_student插入如下2行数据：

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

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

SQL语句：

insert into test1_student values(‘200800020101’,’王欣’,’女’,19,data’1994-2-2’,’计算机学院’,’2010’)

Insert into test1_student values(‘200800020102’,’李华’,’女’,20,to_data(‘19950303’,’yyyymmdd’),’软件学院’,’2009’);

 

1-5.给表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行数据。

学号     课程号   成绩   教师编号
200800020101 300001   91.5   100101
200800020101 300002   92.6   100102

SQL：

insert into test1_student_course values(‘200800020101’,’300001’,’91.5’,’100101’)

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

实验二

1.找出没有选修任何课程的学生的学号、姓名(即没有选课记录的学生）。

SQL语句：

create view TEST2_01 as(select sid,name from pub.student) minus (select sid,name from pub.student natural join pub.student_course)

2． 找出至少选修了学号为"200900130417"的学生所选修的一门课的学生的学号、姓名。
SQL：

create 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))

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')

4．找出选修了"操作系统"并且也选修了"数据结构"的学生的学号、姓名。

create 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='数据结构'))

5． 查询20岁的所有有选课的学生的学号、姓名、平均成绩(avg_score，此为列名，下同)（平均成绩四舍五入到个位）、总成绩(sum_score)
Test2_05有四个列，并且列名必须是：sid、name、avg_score、sum_score

create view test2_05 as

select sid ,name,round(avg(score),0) avg_score,sum(score) sum_score

from pub.student_course natural join pub.student

where age='20'

group by sid,name

6． 查询所有课的最高成绩、次高成绩（次高成绩一定小于最高成绩）、最高成绩人数，test2_06有四个列：课程号cid、课程名称name、最高成绩max_score、次高成绩max_score2、最高成绩人数max_score_count（一个学生同一门课成绩都是第一，只计一次）。如果没有学生选课，则最高成绩为空值,最高成绩人数为零。如果没有次高成绩，则次高成绩为空值。

SQL：

create view test2_06 as

select t1.cid,t1.name,t2.max_score,t3.max_score2,t4.max_score_count

from pub.course t1

left join

(select cid,max(score) max_score

from pub.student_course

group by cid)t2 on t1.cid=t2.cid

left join

(select sc1.cid,max(score) max_score2

from pub.student_course sc1,(select cid,max(score) max1

from pub.student_course

group by cid) sc2

where sc1.cid=sc2.cid

and sc1.score not in sc2.max1

group by sc1.cid)t3 on t1.cid=t3.cid

left join

(select a.cid,count (distinct sid) max_score_count

from pub.student_course a,(select cid,max(score) max2

from pub.student_course

group by pub.student_course.cid) b

where a.cid=b.cid

and a.score=b.max2

group by a.cid)t4 on t1.cid=t4.cid

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

SQL语句：

create view test2_07 as

select sid ,name

from pub.student

where name not like '王%' and name not like '张%' and  name not like '李%'

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

SQL语句：

create view test2_08 as

select substr(name,1,1) second_name,count(*) p_count

from pub.student

group by substr(name,1,1)

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

create view test2_09 as

select sid ,name,score

from pub.student natural join pub.student_course

where cid='300003'

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

create or replace view test2_10 as

select sid ,name

from (select sid ,cid,count(score) cs

from pub.student_course

where score < 60

group by sid ,cid) natural join pub.student

where cs>1

实验三

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

SQL：

create table test3_01 as(

select *

from pub.student_31)

delete

from test3_01

where length (translate(sid,'?0123456789','?'))>0

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

SQL：

create table test3_02 as(

select *

from pub.student_31)

delete

from test3_02

where age <> (2012-extract(year from birthday))

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

SQL：

create table test3_03 as(

select *

from pub.student_31)

delete

from test3_03

where length(translate(sex,'/男女','/'))>0

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

SQL：

create table test3_04 as(

select *

from pub.student_31)

delete

from test3_04

where length(dname)<3 or dname like '% %'  or dname is null

5． 将pub用户下的Student_31及数据复制到主用户的表test3_05，删除表中的班级不规范的那些错误数据，不规范是指和大多数不一致。这个题重点是锻炼利用所学知识解决实际问题的能力。

SQL：

create table test3_05 as(

select *

from pub.student_31)

 

delete

from test3_05

where length(class)>4

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

SQL：

create table test3_06 as(

select *

from pub.student_31)

 

delete

from test3_06

where length (translate(sid,'?0123456789','?'))>0 or  age <> (2012-extract(year from birthday)) or  length(name)<2 or name like '% %'  or  length(translate(sex,'/男女','/'))>0 or

length(dname)<3 or dname like '% %'  or dname is null or length(class)>4

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

SQL：

create table test3_07 as(

select *

from pub.Student_course_32)

 

delete

from test3_07

where sid not in(

select sid

from pub.student)

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

SQL：

create table test3_08 as(

select *

from pub.Student_course_32)

 

delete

from test3_08

where (cid,tid) not in(

select test3_08.cid,test3_08.tid from test3_08,pub.teacher_course

where test3_08.cid=pub.teacher_course.cid and test3_08.tid=pub.teacher_course.tid)

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

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

SQL：

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 test3_08.cid,test3_08.tid from test3_08,pub.teacher_course

where test3_08.cid=pub.teacher_course.cid and test3_08.tid=pub.teacher_course.tid) or score>100 or score <0