山东大学2012年数据库实验四答案(更新)

1.

CREATE TABLE test4_01 AS

SELECT *

FROM pub.student_41

WHERE LENGTH(sid) = 12

AND LENGTH(name) >=2

AND dname IS NOT NULL

AND LENGTH(dname)>=3

AND LENGTH(class) =4

//(1)完成学号不是12位，姓名长度小于2个字的, 院系名称为空值的，院系名称小于3个字的被跳过。

delete from test4_01 where name like '% %' or dname like '% %'

//(2)删除名字和院系名中带空格的

delete from test4_01 where length(sex)<>1

delete from test4_01 where sex like '%南%'

//(3)性别有误

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

//(4) 出生日期和年龄不一致的

delete from test4_01 where sid like '% %'

delete from test4_01 where sid like '%A%'

//(5)学号中有非数字的（看了眼数据，感觉只有这两种，居然猜对了）

//【求助】学号中有非数字的正规思路怎么写？

2.

create table test4_02 as select * from pub.Student_course_42

delete from test4_02 where test4_02.sid not in (select sid from pub.student)

//(1)学号在学生信息pub.student中不存在的

delete from test4_02 where test4_02.cid not in (select cid from pub.course)

//(2) 课程号在课程信息pub.course中不存在的

delete from test4_02 where test4_02.tid not in (select tid from pub.teacher)

//(3)教师编号在教师信息pub.teacher中不存在的

create table tt as

selecta.sid,a.cid,a.score,a.tid

from test4_02 a, pub.teacher_course b

wherea.cid in b.cid and a.tid in b.tid

drop table test4_02

create table test4_02 as select * from tt

drop table tt

//(4)有点混乱，先建一个临时表tt，把test4_02中“满足课程号和教师编号在教师授课表pub.teacher_course中存在”的数据放到tt中，在drop掉test4_02，然后重新定义test4_02

//【求助】感觉不太正规，肯定有比这好很多的方法

delete from test4_02 where score not between 0 and 100

//(5)成绩数据有错误

3.

create table t1 as select distinct name from pub.student

//去除重复的名字

CREATE TABLE test4_03 AS

SELECT first_name,

COUNT(*) Frequency

FROM

(SELECT SUBSTR(name,2,LENGTH(name)-1)first_name FROM t1

)

GROUP BY first_name

Drop table t1

//一定要drop table t1这只是一个临时表，虽然一会还要用,不过这是一个很好

的习惯（ps：上一个答案的drop忘记复制了）

4.

create table t2 as select distinct name from pub.student

create table test4_04 as

SELECT letter，count(*) Frequency

FROM

(

SELECT SUBSTR(name,2,1) letter FROM t2

UNION ALL

SELECT SUBSTR(name,3,1) letter FROM t2 WHERE LENGTH(name)>2

)

GROUP BY letter

Drop table t2