Test2(1)create table test2_01 as select sid,nameFrom pub.student pwhere not exists (select cid from pub.student_course where sid=p.sid)(2)create table test2_02 as select sid,nameFrom pub.student natural join pub.student_courseWhere cid in (select cid from pub.student_course where sid=’200900130417’)(3)create table test2_03 as select sid,nameFrom pub.student natural join pub.student_courseWhere cid in (select cid from pub.course where fcid=’300002’)(4)create table test2_04 as select sid,nameFrom pub.studentWhere sid in(select sid from pub.course ,pub .student_course where student_course.cid=course.cid and name='操作系统' )Andsid in(select sid from pub.course ,pub .student_course where student_course.cid=course.cid and name='数据结构' )(5)create table test2_05 as select student.sid,name,cast(avg(score) as numeric(5,0)) avg_score, sum (score) sum_scorefrom pub.student_course,pub.studentWhere pub.student_course.sid=pub.student.sid and age='20'group by student.sid,name(6)create table test2_06 as select cid,max(score)max_scoreFrom pub.student_courseGroup by cid(7)create table test2_07 as select sid,nameFrom pub.studentWhere name not in (select name from pub.student where name like (‘张%’) or name like (‘李%’) or name like (‘王%’)(8)create table test2_08 as select substr(name,1,1) second_name,count (*) p_countFrom pub.studentGroup by substr(name,1,1)(9)create table test2_09 as select pub.student.sid,name,scoreFrom pub.student,pub.student_courseWhere pub.student.sid=pub.student_course.sid and cid='300003'(10)create table test2_10 as select sid,cidFrom pub.student_courseWhere score is not nullTest3（1）create table test3_01 as select * from pub.Student_31delete from test3_01 where length(translate(sid,'\0123456789','\'))>0（2）create table test3_02 as select * from pub.Student_31delete from test3_02 where age<>2012-extract(year from birthday)（3）create table test3_03 as select * from pub.Student_31delete from test3_03 where sex not in (select sex from test3_03 where sex='男' or sex='女' or sex=null) （4）create table test3_04 as select * from pub.Student_31delete from test3_04 where dname is null or length(dname)<3 or dname like '% %'（5）create table test3_05 as select * from pub.Student_31delete from test3_05 where length(class)>4（6）create table test3_06 as select * from pub.Student_31delete from test3_06 where length(translate(sid,'\0123456789','\'))<12Delete from test3_06 where age<>2012-extract(year from birthday)Delete from test3_06 where sex not in (select sex from test3_03 where sex='男' or sex='女' or sex=null) Delete from test3_06 where dname is null or length(dname)<3 or dname like '% %'delete from test3_06 where length(class)>4delete from test3_06 where name like '% %' or length(name)<2(7)create table test3_07 as select * from pub.Student_course_32delete from test3_07 where sid not in (select sid from pub.student)(8)create table test3_08 as select * from pub.Student_course_32delete from test3_08 where (cid,tid) not in (select cid,tid from pub.teacher_course)(9)create table test3_09 as select * from pub.Student_course_32delete from test3_09 where score <0 or score >100(10)create table test3_10 as select * from pub.Student_course_32delete from test3_10 where score <0 or score >100delete from test3_10 where sid not in (select sid from pub.student)delete from test3_10 where cid not in (select cid from pub.course)delete from test3_10 where tid not in (select tid from pub.teacher)delete from test3_10 where (cid,tid) not in (select cid,tid from pub.teacher_course)Test 4(1)create table test4_01 as select * from pub.student_41alter table test4_01 add sum_score numberupdate test4_01 set sum_score = (select sum (score) from pub.student_course where test4_01.sid= pub.student_course.sid)(2)create table test4_02 as select * from pub.student_41alter table test4_02 add avg_score numeric(5,1)update test4_02 set avg_score = (select avg (score) from pub.student_course where test4_02.sid= pub.student_course.sid)(3)create table test4_03 as select * from pub.student_41alter table test4_03 add sum_credit intcreate table t4_031 as select * from pub.course natural join pub.student_courseupdate t4_031 set credit=0 where score<60update test4_03 set sum_credit = (select sum(credit) from t4_031 where test4_03.sid=t4_031.sid) (4)create table test4_04 as select * from pub.student_41update test4_04 set dname=(select did from pub.department where pub.department.dname=test4_04.dname)where dname in (select dname from pub.department)(5)create table test4_05 as select * from pub.student_41alter table test4_05 add sum_score numberalter table test4_05 add avg_score numeric(5,1)alter table test4_05 add sum_credit intalter table test4_05 add did varchar(2)update test4_05 set sum_score =(select sum (score) from pub.student_course where test4_05.sid= pub.student_course.sid)update test4_05 set avg_score = (select avg (score) from pub.student_course where test4_05.sid= pub.student_course.sid)update test4_05 set sum_credit = (select sum(credit) from t4_031 where test4_05.sid=t4_031.sid) create table a1 as select * from pub.departmentinsert into a1 select * from pub.department_41 where dname not in (select distinct dname from pub.department )（6）create table test4_06 as select * from pub.student_42update test4_06 set name =replace(name,' ','')（7）create table test4_07 as select * from pub.student_42update test4_07 set sex =replace(sex,'性','')update test4_07 set sex =replace(sex,' ','')(8)create table test4_08 as select * from pub.student_42update test4_08 set class=replace(class,'级','')update test4_08 set class=replace(class,' ','')(9)create table test4_09 as select * from pub.student_42update test4_09 set age=2012-extract(year from birthday) where age is null(10)create table test4_10 as select * from pub.student_42update test4_10 set name=replace(name,' ','')update test4_10 set dname=replace(dname,' ','')update test4_10 set sex=replace(sex,'性','')update test4_10 set sex=replace(sex,' ','')update test4_10 set class=replace(class,'级','')update test4_10 set class=replace(class,' ','')update test4_10 set age=2012-extract(year from birthday) where age is nullTest5create table test5_10 (test varchar(20),age numeric (3))insert into test5_10values ('结果1',88),insert into test5_10values ('结果2',90),insert into test5_10values ('结果3',90),insert into test5_10values ('结果4',86),insert into test5_10values ('结果5',90),insert into test5_10values ('结果6',90),insert into test5_10values ('结果7',86),insert into test5_10values ('结果8',86),insert into test5_10values ('结果9',76),insert into test5_10values ('结果10',86)Test6(1) create view test6_01 as select sid,name,dname from pub.student where age<20 and dname='物理学院'order by sid(2)create view test6_02 as select pub.student.sid,name,sum(score)sum_score from pub.student,pub.student_course where pub.student.sid=pub.student_course.sid and class='2009' and dname='软件学院' group by pub.student.sid,(3)create view test6_03 as select pub.student.sid,,pub.student_course.score from pub.student,pub.student_course where pub.student.sid=pub.student_course.sid and class='2010' and dname='计算机科学与技术学院' and pub.student_course.cid=(select cid from pub.course where ='操作系统')(4)create view test6_04 as select pub.student.sid, from pub.student,pub.student_course where pub.student.sid=pub.student_course.sid and score>90 and pub.student_course.cid=(select cid from pub.course where ='数据库系统')(5)create view test6_05 as select pub.student_course.sid,pub.student_course.cid,score, from pub.course,pub.student_course,pub.student where pub.course.cid=pub.student_course.cid and pub.student_course.sid=pub.student.sid and ='李龙'(6)create view test6_06 as select sid,name from pub.student where sid in (select sid from pub.student_course group by sid having count(*) >=(select count(*) from pub.course ))(7)create view test6_07 as select sid,name from pub.student where sid in (select sid from pub.student_course where score>=60 group by sid having count(*) >=(select count(*) from pub.course ))(8)create view test6_08 as select a1.cid, from pub.course a1,pub.course a2 where a1.fcid =a2.cid and a2.credit=2(9)create view test6_09 as select pub.student.sid, ,sum(credit) sum_credit from pub.student, pub.student_course,pub.course where pub.student.sid = pub.student_course.sid and pub.student_course.cid=pub.course.cid and class='2010' and dname='化学与化工学院' and score>=60 group by pub.student.sid, (10)create view test6_10 as select a1.cid, from pub.course a1,pub.course a2 where a1.fcid=a2.cid and a2.fcid is not nullTest7(1)create table a as select (substr(name,2)) first_name from pub.student create table test7_01 as select first_name,(count(*)) frequency from a group by first_name(2)Union和Union All的区别之一在于对重复结果的处理。