主题：SQL练习——学生成绩查询

SQL脚本：
CREATE TABLE student
(
        sid varchar2(6) NOT NULL,
        name varchar2(8) NULL,
        sex number(1),
        birthday timestamp NULL,
        height number(3,2) NULL
);

insert into student values ( '000001','黎明',1,TO_DATE('15-4月 -1990','dd-Mon-yyyy'),1.78);
insert into student values ( '000002','赵怡春',0,TO_DATE('17-12月 -1992','dd-Mon-yyyy'),1.77);
insert into student values ( '000003','张富平',1,TO_DATE('1-2月 -1995','dd-Mon-yyyy'),1.78);
insert into student values ( '000004','白丽',0,TO_DATE('20-11月 -1996','dd-Mon-yyyy'),1.73);
insert into student values ( '000005','牛玉德',1,TO_DATE('1-5月 -1993','dd-Mon-yyyy'),1.74);
insert into student values ( '000006','姚华',0,TO_DATE('12-5月 -1996','dd-Mon-yyyy'),1.77);
insert into student values ( '000007','胡歌',1,TO_DATE('30-6月 -1995','dd-Mon-yyyy'),1.77);

CREATE TABLE grade
(
        sid varchar2(6) NULL,
        cid varchar2(3) NULL,
        grade number(3) NULL
);

insert into grade values ('000001','A03',56);
insert into grade values ('000002','A02',90);
insert into grade values ('000004','A02',77);
insert into grade values ('000005','B01',91);
insert into grade values ('000004','A03',76);
insert into grade values ('000003','A03',67);
insert into grade values ('000001','B01',78);
insert into grade values ('000002','A03',74);
insert into grade values ('000002','B01',86);
insert into grade values ('000003','A02',80);
insert into grade values ('000004','B01',90);

CREATE TABLE course
(
        cid varchar2(3) NOT NULL,
        title varchar2(50) NULL,
        credit number(1) NULL
);

insert into course values ('A02', 'Database', 5 );
insert into course values ('A03', 'Java', 3 );
insert into course values ('B01', 'Information System Management', 4 );
insert into course values ('C04', 'ERP', 3 );

（1）查询含有不及格成绩的课程名称
select distinct title from course natural join grade where grade<60
（2）查询各个身高数值和人数分布
select height,count(*) from student group by height
（3）查询所有男生的平均成绩
select avg(grade) from student natural join grade where sex=1
（4）在学生表中删除所有含有不及格成绩的学生记录
delete from student where sid in(select sid from grade where grade<60)
（5）将所有学分不满2分的课程都设置为2个学分
update course set credit=2 where credit<2
（6）查询所有男生的平均成绩和最高分
select avg(grade),max(grade) from student natural join grade where sex=1
（7）查询所有女生的总分
select max(grade) from student natural join grade where sex=0
（8）在课程表中删除所有学分不满3分的课程
delete from course where credit<3
（9）查询学分为2的课程平均成绩
select avg(grade) from course natural join grade where credit=2
（10）查询学分大于2的课程最高分
select max(grade) from course natural join grade where credit>2