DATABASE TEST6

DATABASE TEST6

6-1

CREATE VIEW test6_01 AS
SELECT sid,name,age,dname FROM pub.student WHERE
	age < 20 AND dname = '物理学院'
ORDER BY sid

6-2

CREATE TABLE test602 AS
	SELECT sid,name FROM pub.student WHERE class = '2009' AND 
	dname = '软件学院'

ALTER TABLE test602 ADD sum_score int

UPDATE test602 SET sum_score = NULL

UPDATE test602 t SET sum_score = 
	(SELECT SUM(score) FROM pub.student_course p 
     WHERE t.sid = p.sid)
     
CREATE VIEW test6_02 AS SELECT * FROM test602

6-3

CREATE TABLE test603 AS
SELECT DISTINCT sid,name
	FROM pub.student p1
	WHERE p1.class = '2010' AND dname = '计算机科学与技术学院'
	
ALTER TABLE test603 ADD score numeric

UPDATE test603  t SET score =
	(SELECT SCORE FROM pub.student_course p2 WHERE p2.sid = t.sid
    	AND cid = '300005')
    	
CREATE VIEW test6_03 AS SELECT * FROM test603    	

DELETE FROM test6_03 WHERE score IS NULL

6-4

CREATE VIEW test6_04 AS
SELECT sid,name FROM pub.student p1
WHERE sid IN (SELECT sid FROM pub.student_course WHERE
             score > 90 AND cid = 300003)

6-5

CREATE VIEW test6_05 AS
SELECT sid,cid，(SELECT name FROM pub.course p3 WHERE p3.cid = p2.cid)name,
score
FROM pub.student_course p2
WHERE p2.sid IN 
(SELECT sid FROM pub.student WHERE name = '李龙')

6-6

CREATE VIEW test6_06 AS
SELECT sid,name FROM pub.student p1 
WHERE ((SELECT COUNT(DISTINCT cid) FROM pub.student_course WHERE
	sid = p1.sid) = (SELECT COUNT(DISTINCT cid) FROM pub.student_course))

6-7

CREATE table test606 AS
SELECT sid,name FROM pub.student p1 
WHERE ((SELECT COUNT(DISTINCT cid) FROM pub.student_course WHERE
	sid = p1.sid) = (SELECT COUNT(DISTINCT cid) FROM pub.student_course))
	
CREATE table test6060 AS
SELECT sid,name FROM pub.student p1 
WHERE ((SELECT COUNT(cid) FROM pub.student_course WHERE
	sid = p1.sid AND score >= 60) = 
       (SELECT COUNT(cid) FROM pub.student_course WHERE
	sid = p1.sid))
	
CREATE VIEW test6_07 AS	
SELECT t1.sid,t2.name FROM test606 t1
JOIN  test6060 t2 ON
t2.sid = t1.sid

6-8

CREATE table test608 AS
SELECT sid,name FROM pub.student p1 
WHERE ((SELECT COUNT(DISTINCT cid) FROM pub.student_course WHERE
	sid = p1.sid) = (SELECT COUNT(DISTINCT cid) FROM pub.student_course))
	
CREATE table test6080 AS
SELECT sid,name FROM pub.student p1 
WHERE ((SELECT COUNT(DISTINCT cid) FROM pub.student_course WHERE
	sid = p1.sid AND score >= 60) = 
       (SELECT COUNT(DISTINCT cid) FROM pub.student_course WHERE
	sid = p1.sid))
	
CREATE VIEW test6_08 AS	
SELECT t1.sid,t2.name FROM test608 t1
JOIN  test6080 t2 ON
t2.sid = t1.sid

6-9

CREATE TABLE test609 ASSELECT sid,name,(SELECT sum_credit FROM test4_03 WHERE sid = p.sid)sum_creditFROM pub.student pWHERE class = '2010' AND dname = '化学与化工学院'DELETE FROM test609 WHERE sum_credit IS NULLCREATE VIEW test6_09 AS SELECT * FROM test609

6-10

CREATE VIEW test6_10 ASSELECT cid,name,fcid FROM pub.course p1 WHERE (SELECT COUNT(cid) FROM pub.course WHERE cid = p1.fcid AND fcid IS NOT NULL) != 0