Oracle——某次作业

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、查询最受学生欢迎的课程,输出课程号