1.建表
/*学生表(student) (
学号(sno) 普通编码定长字符类型,长度7,主码,
姓名(sname) 普通编码定长字符类型,长度10,非空,
性别(ssex) 统一编码定长字符类型,长度3,
年龄(sage) 整型,
所在系(sdept) 统一编码可变长字符类型,长度20
)*/
create table student
(sno char(7) primary key,
sname char(10) not null,
ssex char(3),
sage int,
sdept varchar(20)
);
/*课程表(course) (
课程号(cno) 普通编码定长字符类型,长度6,主码,
课程名(cname) 统一编码定长字符类型,长度20,非空,
学分(credit) 整型,
学期(semester) 整型
)*/
create table course
(cno char(6) primary key,
cname char(20) not null,
credit int,
semester int
);
/*修课表(sc)(
学号(sno) 普通编码定长字符类型,长度7,主码,外码
课程号(cno) 普通编码定长字符类型,长度6,主码,外码
成绩(grade) 整型,(说明:空值表示选修了但未去上课,最终没有成绩)
修课类别(xklx)普通编码定长字符类型,长度6
)*/
create table sc
(sno char(7) references student(sno),
cno char(6) references course(cno),
grade int,
xklx char(6),
primary key(sno,cno)
);
2.查询
1.查询每个选课学生选课情况,输出学号、课程号、成绩(如果没有成绩则显示为0)。
nvl函数的用法:nvl(expr1,expr2),若第一个表达式的值为空,则输出表达式2的值。
select sno,cno,nvl(grade,0) from sc;
2.查询每个学生的选课情况,输出平均成绩超过60分,选课门数超过2次的学生学号、平均成绩和选课门数(空值按0处理)。
select sno,avg(nvl(grade,0)),count(sno) from sc
group by sno
having avg(nvl(grade,0))>60 and count(sno)>2;
3、查询每门课程的考试最高分、最低分和平均分,输出课程号和对应的分值(空值按0处理)
select cno,max(nvl(grade,0))最高分, min(nvl(grade,0))最低分,avg(nvl(grade,0))平均分
from sc
group by cno;
4、统计每个系男女生人数,输出系名、性别和人数
select sdept,ssex,count(*)人数 from student
group by sdept,ssex;
5、查询计算机系年龄超过20岁的李姓男生,输出姓名
select sname from student where sage>20 and sdept='计算机系' and sname like'李%';
6、查询最受学生欢迎的课程,输出课程号