DATABASE TEST9

DATABASE TEST9

1

CREATE TABLE test9_01 AS
	SELECT * FROM pub.student_11_1

DELETE FROM test9_01

CREATE INDEX id_index ON test9_01(sid)

INSERT INTO test9_01 VALUE(
	SELECT * FROM pub.student WHERE sex = '女'
	)
	
INSERT INTO test9_01 VALUE(
	SELECT * FROM pub.student_11_1 WHERE 
	sex = '女' AND sid NOT IN 
    (SELECT sid FROM test9_01)
    )
    
INSERT INTO test9_01 VALUE(
	SELECT * FROM pub.student_11_2 WHERE 
	sex = '女' AND sid NOT IN 
    (SELECT sid FROM test9_01)
    )   

2

CREATE TABLE test9_02 AS 
	SELECT * FROM pub.student_11_1
	
CREATE INDEX sid_index ON test9_02(sid)

DELETE FROM test9_02

INSERT INTO test9_02 VALUE(
	SELECT * FROM pub.student WHERE
	sex = '女' AND 
    sid IN (SELECT sid FROM pub.student_course WHERE
           score < 60)
	)

INSERT INTO test9_02 VALUE(
	SELECT * FROM pub.student_11_1 WHERE
	sex = '女' AND 
    sid IN (SELECT sid FROM pub.student_course WHERE
           score < 60) AND
    sid NOT IN (SELECT sid FROM test9_02)
	)
	
INSERT INTO test9_02 VALUE(
	SELECT * FROM pub.student_11_2 WHERE
	sex = '女' AND 
    sid IN (SELECT sid FROM pub.student_course WHERE
           score < 60) AND
    sid NOT IN (SELECT sid FROM test9_02)
	)