山东大学Oracle数据库实验4：创建与更新操作

**************************1********************************
CREATE TABLE test4_01 AS
SELECT * FROM pub.student_41
ALTER TABLE test4_01 ADD sum_score smallint
ALTER TABLE test4_01 ADD avg_score  real
ALTER TABLE test4_01 ADD sum_credit smallint
ALTER TABLE test4_01 ADD did varchar(2)

UPDATE test4_01 
set sum_score=(
select sum(score)
from pub.student_course
where test4_01.sid= pub.student_course.sid
group by sid
)
 update dbtest set test=4
select * from dbscore
**************************2********************************
CREATE TABLE test4_02 AS
SELECT * FROM pub.student_41
ALTER TABLE test4_02 ADD sum_score smallint
ALTER TABLE test4_02 ADD avg_score  real
ALTER TABLE test4_02 ADD sum_credit smallint
ALTER TABLE test4_02 ADD did varchar(2)
 
UPDATE test4_02
SET     avg_score=
        (
                SELECT  ROUND(AVG(score) ,1)
                FROM    pub.student_course

                WHERE   test4_02.sid= pub.student_course.sid
                GROUP BY sid
        )
UPDATE dbtest SET test=4
SELECT * FROM dbscore
**************************3********************************
CREATE TABLE test4_03 AS
SELECT * FROM pub.student_41
ALTER TABLE test4_03 ADD sum_score smallint
ALTER TABLE test4_03 ADD avg_score  real
ALTER TABLE test4_03 ADD sum_credit smallint
ALTER TABLE test4_03 ADD did varchar(2)

CREATE TABLE test AS
SELECT * FROM pub.student_course NATURAL JOIN pub.course

UPDATE test4_03
SET     sum_credit=
        (
                SELECT  SUM(credit)
                FROM    test
                WHERE
                        (
                                test.score       >59
                                AND test4_03.sid = test.sid
                        )
                GROUP BY sid
        )
UPDATE dbtest SET test=4
SELECT * FROM dbscore