数据库实验五

DATABASE TEST5

5-1

CREATE TABLE test5_01 (
	First_name varchar(4),
	frequency numeric(4))
	
INSERT INTO test5_01(First_name) 
		SELECT DISTINCT SUBSTR(name,2,100) FROM pub.student
		
UPDATE test5_01 t51 SET frequency = 
	(SELECT count(sid) FROM pub.student WHERE SUBSTR(name,2,100) = first_name)

5-2

CREATE TABLE test5_02 (letter varchar(2),frequency numeric(4))

INSERT INTO test5_02(letter)
	SELECT DISTINCT SUBSTR(first_name,1,1) FROM test5_01
	
INSERT INTO test5_02(letter)
	SELECT DISTINCT SUBSTR(first_name,2,1) FROM test5_01
	
INSERT INTO test5_02(letter)
	SELECT DISTINCT SUBSTR(first_name,3,1) FROM test5_01

DELETE FROM test5_02 WHERE letter IS NULL

UPDATE test5_02 SET frequency = 
	(
        SELECT count(sid) FROM pub.student WHERE
     	letter = SUBSTR(name,2,1)
    )
    
UPDATE test5_02 SET frequency = 
	(
        frequency +
        (SELECT count(sid) FROM pub.student WHERE
     	letter = SUBSTR(name,3,1))
    )

UPDATE test5_02 SET frequency = 
	(
        frequency +
        (SELECT count(sid) FROM pub.student WHERE
     	letter = SUBSTR(name,4,1))
    )
    

5-3

CREATE TABLE test5_03 (
	dname varchar(30),
    class varchar(10),
    p_count1 int,
    p_count2 int,
    p_count int
)

INSERT INTO test5_03(dname,class)
	SELECT DISTINCT dname,class FROM pub.student WHERE dname IS NOT NULL

UPDATE test5_03 t SET p_count =
	(SELECT DISTINCT COUNT(DISTINCT sid) FROM pub.student p WHERE
    	p.dname = t.dname AND p.class = t.class)
    	
UPDATE test5_03 t SET p_count1 =
	(SELECT COUNT (DISTINCT sid) FROM test4_03 t_ WHERE
    	t.class = t_.class AND t.dname = t_.dname AND t_.sum_credit >=10)
    	
UPDATE test5_03 t SET p_count2 =
	p_count - p_count1

注意：这里的test4_03因为在实验4中使用的表是pub.student43，我们需要用test4_03的代码从pub.student中重新构建一遍test4_03，否则会有15行的错误。

5-4

CREATE TABLE test5_04 (
	dname varchar(30),
    class varchar(10),
    p_count1 int,
    p_count2 int,
    p_count int
)

INSERT INTO test5_04(dname,class)
	SELECT DISTINCT dname,class FROM pub.student WHERE dname IS NOT NULL
	
UPDATE test5_04 t SET p_count =
	(SELECT DISTINCT COUNT(DISTINCT sid) FROM pub.student p WHERE
    	p.dname = t.dname AND p.class = t.class)
    	
UPDATE test5_04 t SET p_count1 =
	(SELECT COUNT (DISTINCT sid) FROM test4_03 t_ WHERE
    	t.class = t_.class AND t.dname = t_.dname AND t_.sum_credit >=10)
	WHERE cast(t.class as int) > 2008


UPDATE test5_04 t SET p_count1 =
	(SELECT COUNT (DISTINCT sid) FROM test4_03 t_ WHERE
    	t.class = t_.class AND t.dname = t_.dname AND t_.sum_credit >=8)
	WHERE cast(t.class as int) <= 2008

UPDATE test5_04 t SET p_count2 =
	p_count - p_count1

5-5

CREATE TABLE test5_05 (dname varchar(30),
                       avg_ds_score numeric(2),
                       avg_os_score numeric(2))

INSERT INTO test5_05(dname) 
	SELECT DISTINCT dname FROM pub.student WHERE pub.student.dname IS NOT NULL
	
CREATE TABLE test505 AS
SELECT sid,cid,(max(score))max_score FROM pub.student_course GROUP BY sid,cid

ALTER TABLE test505 ADD dname varchar(30)

UPDATE test505 t3 SET dname = 
	(SELECT dname FROM pub.student p WHERE p.sid = t3.sid)

UPDATE test5_05 t SET avg_ds_score =
	(SELECT ROUND(AVG(max_score),0) FROM test505 t5 WHERE
    	t5.dname = t.dname AND cid = '300002')
    	
UPDATE test5_05 t SET avg_os_score =
	(SELECT ROUND(AVG(max_score),0) FROM test505 t5 WHERE
    	t5.dname = t.dname AND cid = '300005')  
       

5-6

CREATE TABLE test5_06 (sid varchar(30),
                       name varchar(10),
                       dname varchar(30),
                       ds_score numeric(2),
                       os_score numeric(2)
	)

INSERT INTO test5_06(sid,name,dname) SELECT DISTINCT sid,name,dname
FROM pub.student WHERE
		dname = '计算机科学与技术学院'
		
DELETE FROM test5_06 WHERE sid NOT IN (
	SELECT sid FROM pub.student_course WHERE cid = '300002') OR
	sid NOT IN(SELECT sid FROM pub.student_course WHERE cid = '300005')
		
UPDATE test5_06 t SET ds_score =
	(SELECT max(score) FROM pub.student_course p WHERE
     	p.sid = t.sid AND p.cid = '300002'),
	os_score = 
	(SELECT max(score) FROM pub.student_course p WHERE
     	p.sid = t.sid AND p.cid = '300005')

5-7

CREATE TABLE test5_07 (sid varchar(30),
                       name varchar(10),
                       dname varchar(30),
                       ds_score numeric(2),
                       os_score numeric(2)
	)

INSERT INTO test5_07(sid,name,dname) SELECT DISTINCT sid,name,dname
FROM pub.student WHERE
		dname = '计算机科学与技术学院'
		
DELETE FROM test5_07 WHERE sid NOT IN (
	SELECT sid FROM pub.student_course WHERE cid = '300002') AND
	sid NOT IN(SELECT sid FROM pub.student_course WHERE cid = '300005')
		
UPDATE test5_07 t SET ds_score =
	(SELECT max(score) FROM pub.student_course p WHERE
     	p.sid = t.sid AND p.cid = '300002'),
	os_score = 
	(SELECT max(score) FROM pub.student_course p WHERE
     	p.sid = t.sid AND p.cid = '300005')

5-8

CREATE TABLE test5_08 (sid varchar(30),
                       name varchar(10),
                       dname varchar(30),
                       ds_score numeric(2),
                       os_score numeric(2)
	)

INSERT INTO test5_08(sid,name,dname) SELECT DISTINCT sid,name,dname
FROM pub.student WHERE
		dname = '计算机科学与技术学院'
		
UPDATE test5_08 t SET ds_score =
	(SELECT max(score) FROM pub.student_course p WHERE
     	p.sid = t.sid AND p.cid = '300002'),
	os_score = 
	(SELECT max(score) FROM pub.student_course p WHERE
     	p.sid = t.sid AND p.cid = '300005')