数据库实验四

数据库实验四

4-1

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 t SET sum_score = 
	(SELECT SUM(score) FROM pub.student_course p WHERE
    	t.sid = p.sid)
	

4-2

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 NUMBER

UPDATE test4_02 t SET avg_score = 
	(SELECT ROUND(AVG(score),1) FROM pub.student_course p WHERE
    	t.sid = p.sid)

4-3

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 NUMBER

CREATE TABLE test403 AS
	SELECT * FROM pub.student_course

ALTER TABLE test403 ADD credit NUMBER

CREATE TABLE test404 AS
SELECT sid,cid,max(score) FROM test403 GROUP BY sid,cid

ALTER TABLE test404 ADD credit NUMBER

UPDATE test404 t SET credit =
	(SELECT credit FROM pub.course p WHERE p.cid = t.cid)

DELETE FROM test404 t4 WHERE score < 60

UPDATE test4_03 t SET sum_credit = 
	(
        SELECT SUM(credit) FROM test404 t3 WHERE
	    t.sid = t3.sid AND t3.score >= 60
    )

此题可能会出现一名学生选修同一门课多次，且都及格，这时学分只需要算一次。

4-1

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 t4 SET	dname = 
	(SELECT DISTINCT did FROM pub.department p WHERE 
    	p.dname = t4.dname)
	WHERE dname in (SELECT dname FROM pub.department)

4-5

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 (
    sum_score NUMBER,avg_score NUMBER,
	sum_credit NUMBER,did varchar(2)
)

UPDATE test4_05 t5 SET sum_score = 
	(SELECT DISTINCT sum_score FROM test4_01 t1 WHERE t1.sid = t5.sid),
	avg_score = 
	(SELECT DISTINCT avg_score FROM test4_02 t2 WHERE t2.sid = t5.sid),
	sum_credit = 
	(SELECT DISTINCT sum_credit FROM test4_03 t3 WHERE t3.sid = t5.sid)

UPDATE test4_05 t5 SET did = 
	(SELECT DISTINCT dname FROM test4_04 t4 WHERE
     LENGTH(dname) = 2 AND
     t4.sid = t5.sid )

UPDATE test4_05 t5 SET did = 
	(SELECT did FROM pub.department_41 p WHERE
    	p.dname = t5.dname)
	WHERE did = '00'

UPDATE test4_05 t5 SET did = 
	('00') 
	WHERE did IS NULL
	

4-6

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

7． 将pub用户下的Student_42及数据复制到主用户的表test4_07中,对表中的数据进行整理，修复那些不规范的数据：
对性别列进行规范（需要先确定哪些性别数据不规范，也就是那些和大多数不一样的就是不规范的）；

CREATE TABLE test4_07 AS
	SELECT * FROM pub.student_42
	
UPDATE test4_07 SET sex = 
	(REPLACE(sex,' ',''))
WHERE sex != '男' AND sex != '女'

UPDATE test4_07 SET sex = 
	(SUBSTR(sex,1,1))
WHERE sex != '男' AND sex != '女'

4-8

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

CREATE TABLE test4_08 AS
	SELECT * FROM pub.student_42

UPDATE test4_08 SET class =
	(SUBSTR(class,1,4))

4-9

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))
WHERE
	age is null

4-10

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

CREATE TABLE test4_10 AS
	SELECT * FROM pub.student_42 

UPDATE test4_10 t SET name = 
	(SELECT DISTINCT name FROM test4_06 t4 WHERE t4.sid = t.sid),
	dname = 
	(REPLACE(dname,' ','')),
	sex = 
	(SELECT DISTINCT sex FROM test4_07 t7 WHERE t7.sid = t.sid),
	class = 
	(SELECT DISTINCT class FROM test4_08 t8 WHERE t8.sid = t.sid),
	age = 
	(SELECT DISTINCT age FROM test4_09 t9 WHERE t9.sid = t.sid)