成绩max_score
create table test2_06 as select sid，max(score） max_score from pub.student_course group by cid
7、查询所有不姓张、不姓李、也不姓王的学生的学号sid、姓名name 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、查询学生表中每一个姓氏及其人数（不考虑复姓），test2_08有两个列：second_name、p_count
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、查询选修了300003号课程的学生的sid、name、score
create table test2_09 as select student.sid，student.name，score from pub.student,pub.student_course where student.sid = student_course.sid and cid ='300003'
10、查所有有成绩记录的学生sid和cid create table test2_10 as select sid，cid from pub.student_course where score is not null
数据库实验（三） 复制表、删除数据
1.将pub用户下的Student_31及数据复制到主用户的表test3_01，删除表中的学号不是12位数字的错误数据。
create table test3_01 as select * from pub.Student_31
delete from test3_01 where length(translate(sid,'\\0123456789','\\'))>0
2.将pub用户下的Student_31及数据复制到主用户的表test3_02，删除表中的出生日期和年龄不一致 (年龄=2012-出生年份) 的 错误数据。
create table test3_02 as select * from pub.Student_31
delete from test3_02 where age < 2012 - extract(year from birthday) delete from test3_02 where age > 2012 - extract(year from birthday)
3.将pub用户下的Student_31及数据复制到主用户的表test3_03，删除表中的性别有错误的数据（性别只能够是“男”、“女”或者空值）。
create table test3_03 as select * from pub.Student_31 delete from test3_03 where sex not in(select sex from test3_03 where sex='男' or sex='女' or sex=null)
4.将pub用户下的Student_31及数据复制到主用户的表test3_04，删除表中的院系名称有空格的、院系名称为空值的或者院系名称小于3个字的错误数据。
create table test3_04 as select * from pub.student_31
delete from test3_04 where dname is null or length(dname)<3 or dname like '% %'
5.将pub用户下的Student_31及数据复制到主用户的表test3_05，删除表中的班级不规范的数据，不规范是指和大多数不一致。
create table test3_05 as select * from pub.student_31 delete from test3_05 where length(class)>4
6.将pub用户下的Student_31及数据复制到主用户的表test3_06，删除表中的错误数据，不规范的数据也被认为是错误的数据。
create table test3_06 as select * from pub.student_31
delete from test3_06 where length(translate(sid,'/0123456789','/'))<12
delete from test3_06 where age>2012-extract(year from birthday) or age<2012-extract(year from birthday)
delete from test3_06 where name is null or length(name)<2 or name like '% %'
delete from test3_06 where sex not in (select sex from test3_06 where sex ='男' or sex='女' or sex=null)
delete from test3_06 where dname is null or length(dname)<3 or name like
'% %'
delete from test3_06 where length(class)>4
7.将pub用户下的Student_course_32及数据复制到主用户的表test3_07，删除其中的错误数据，错误指如下情况：学号在学生信息pub.student中不存在的；
create table test3_07 as select * from pub.Student_course_32
delete from test3_07 where sid not in (select sid from pub.student)
8.将pub用户下的Student_course_32及数据复制到主用户的表test3_08，删除其中的错误数据，错误指如下情况：课程号和教师编号在教师授课表pub.teacher_course中不同时存在的，即没有该教师教该课程；
create table test3_08 as select * from pub.student_course_32
delete from test3_08 where (cid,tid) not in(select test3_08.cid,test3_08.tid from test3_08,pub.teacher_course where test3_08.cid=pub.teacher_course.cid and test3_08.tid=pub.teacher_course.tid)
9.将pub用户下的Student_course_32及数据复制到主用户的表test3_09，删除其中的错误数据，错误指如下情况：成绩数据有错误（需要先找到成绩里面的错误）。
create table test3_09 as select * from pub.student_course_32
delete from test3_09 where score<0 or score>100
10.将pub用户下的Student_course_32及数据复制到主用户的表test3_10，删除其中的错误数据。
create table test3_10 as select * from pub.student_course_32
delete from test3_10 where score<0 or score>100
delete 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 test3_10.cid,test3_10.tid from test3_10,pub.teacher_course where
test3_10.cid=pub.teacher_course.cid test3_10.tid=pub.teacher_course.tid)
and
Test4 复制表、修改表结构、修改数据
1、将pub用户下表student_41及数据复制到主用户的表test4_01中，使用alter table语句为表增加五个列：“总成绩:sum_score”、 “平均成绩:avg_score”（四舍五入到个位）、“总学分:sum_credit”、“院系编号:did varchar(2) ”。
使用update语句,利用pub.student_course、pub.course，统计 “总成绩”；
create table test4_01 as select* from pub.student_41 alter table test4_01 add sum_score int
alter table test4_01 add avg_score numeric(5,1) alter table test4_01 add sum_credit int alter table test4_01 add did varchar(2) select *from test4_01
create table test01 as select sid,sum(score) sum_score from pub.student_course group by sid update test4_01
set sum_score=(select test01.sum_score from test01
where test01.sid=test4_01.sid)
2、将pub用户下表student_41及数据复制到主用户的表test4_02中，使用alter table语句为表增加五个列：“总成绩:sum_score”、 “平均成绩:avg_score”（四舍五入到个位）、“总学分:sum_credit”、“院系编号:did varchar(2) ”。
利用pub.student_course、pub.course，统计“平均成绩”；
create table test4_02 as select* from pub.student_41 alter table test4_02 add sum_score int
alter table test4_02 add avg_score numeric(5,1) alter table test4_02 add sum_credit int alter table test4_02 add did varchar(2) select *from test4_02
create table test02 as select sid,avg(score) avg_score from pub.student_course group by sid update test4_02
set avg_score=(select test02.avg_score from test02
where test02.sid=test4_02.sid)
3、将pub用户下表student_41及数据复制到主用户的表test4_03中，使用alter table语句为表增加五个列：“总成绩:sum_score”、 “平均成绩:avg_score”（四舍五入到个位）、“总学分:sum_credit”、“院系编号:did varchar(2) ”。
使用update语句,利用pub.student_course、pub.course，统计 “总学分”；
drop table test4_03
create table test4_03 as select* from pub.student_41 alter table test4_03 add sum_score int
alter table test4_03 add avg_score numeric(5,1) alter table test4_03 add sum_credit int alter table test4_03 add did varchar(2) select *from pub.course drop table test03
create table test031 as select sid,cid,score from pub.student_course alter table test031 add credit int