Test2

(1)create table test2_01 as select sid,name

From pub.student p

where not exists (select cid from pub.student_course where sid=p.sid)

(2)create table test2_02 as select sid,name

From pub.student natural join pub.student_course

Where cid in (select cid from pub.student_course where sid=’200900130417’)

(3)create table test2_03 as select sid,name

From pub.student natural join pub.student_course

Where cid in (select cid from pub.course where fcid=’300002’)

(4)create table test2_04 as select sid,name

From pub.student

Where sid in(select sid from pub.course ,pub .student_course where student_course.cid=course.cid and name='操作系统' )

And

sid 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_score

from pub.student_course,pub.student

Where 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_score

From pub.student_course

Group by cid

(7)create table test2_07 as select sid,name

From pub.student

Where 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_count

From pub.student

Group by substr(name,1,1)

(9)create table test2_09 as select pub.student.sid,name,score

From pub.student,pub.student_course

Where pub.student.sid=pub.student_course.sid and cid='300003'

(10)create table test2_10 as select sid,cid

From pub.student_course

Where score is not null

Test3

（1）create table test3_01 as select * from pub.Student_31

delete from test3_01 where length(translate(sid,'\0123456789','\'))>0

（2）create table test3_02 as select * from pub.Student_31

delete from test3_02 where age<>2012-extract(year from birthday)