Oracle考试题作业
新建一张学员信息表(student),要求:
1. 字段如下:学号(sid),姓名(name),性别(sex),年龄(age),地址(address).
2. 分别为字段添加约束:学号为主键,姓名为非空,性别为检查约束,年龄为检查约束,地址为默认约束.
3. 创建序列插入学号(sid)列, 建议初始值从为1001, 增量为1.
4. 插入记录.
create table student(sid int, name char(1), sex char(2), age int, address varchar2(20));
alter table student modify sid primary key;
alter table student modify name not null;
alter table student modify sex check(sex='m' or sex='w');
alter table student modify age check(age between 10 and 100);
alter table student modify address varchar2(500) default'20';
create sequence student_sid start with 1001 increment by 1;
insert into student(sid,name,sex,age,address) values(student_sid.nextval,'1', 'w', 16, 'china');
insert into student values(student_sid.nextval, 2, 'm', 18, 'china'); insert into student values(student_sid.nextval, 3, 'm', 19, 'china');
commit;
新建一张课程表(course),要求:
1. 字段如下:课程编号(cid),课程名称(subject).
2. 分别为字段添加约束:课程编号为主键,课程名称为非空
3. 创建序列插入课程编号列(cid), 建议初始值从为1, 增量为1.
4. 插入记录.
create table course(cid int, subject char(20));
alter table course modify cid primary key;
alter table course modify subject not null;
create sequence course_cid start with 1 increment by 1;
insert into course values(course_cid.nextval,'yuwen', 'shuxue', 'yingyu');
commit;
新建一张学员考试成绩表(grade),要求:
1. 字段如下:成绩编号(gid),学号(sid),课程编号(cid),考试成绩(score).
2. 分别为字段添加约束:成绩编号为主键,学号为外键,课程编号为外键,考试成绩为非空.
3. 创建序列插入成绩编号列(gid), 建议初始值从为101, 增量为1.
4. 插入记录.
create table grade(gid int, sid int, cid int, score int);
alter table grade modify gid primary key;
alter table grade add constraint fk_grade_sid foreign key(sid) references student(sid);
alter table grade add constraint fk_grade_cid foreign key(cid) references student(cid);
create sequence grade_gid start with 101 increment by 1;
insert into grade values(grade_gid.nextval,1001,1,50);
insert into grade values(grade_gid.nextval,1001,2,90);
insert into grade values(grade_gid.nextval,1001,3,80);
insert into grade values(grade_gid.nextval,1002,1,50);
insert into grade values(grade_gid.nextval,1002,2,70);
insert into grade values(grade_gid.nextval,1002,3,80);
insert into grade values(grade_gid.nextval,1003,1,50);
insert into grade values(grade_gid.nextval,1003,2,59);
insert into grade values(grade_gid.nextval,1003,3,70);
select * from grade;
commit;
针对以上三张表,要求完成如下:
1. 按照课程编号分组并求出每一组的平均分数(每门课程的平均分)
2. 按照课程编号分组并求出每一组及格人数(grade >= 60)的平均分数(每门课程的及格人数的平均分)
3. 求每个学员所有的课程的平均分
4. 求每个学员所有的(考试成绩)及格课程的平均分
5. 每次内部测试不同学员的平均成绩(每个学员的每门课程的平均分)
6. 查询补考过的学员的平均成绩(求出学员的课程编号(cid)在分组内出现过一次以上)(每个学员的每门课
程的课程编号出现过一次以上)
7. 使用多表内连接查询,求出学员的学号,姓名,考号,科目和成绩
8. 建立一张视图,包含学员的学号,姓名,考号,科目,成绩这些字段
1. select cid,avg(score) from grade group by cid;
2. select cid,avg(score) from grade where score>=60 group by cid;
3. select sid, avg(score) from grade group by sid;
4. select sid,avg(score) from grade where score>=60 group by sid;
5. select cid,sid,avg(score) from grade group by sid,cid order by sid;
6. select sid,avg(score) from grade where sid in(select sid from grade group by sid,cid having count(cid)>1) group by sid;
插入一条补考过得记录:insert into grade values(grade_gid.nextval,1003,2,70);
7.Select s.sid ,s.name,g.gid,c.cid,g.score
form student s,course c,grade g
where s.sid=g.sid
and c.cid=g.cid;
8. create or replace view score as
Select s.sid, s.name, g.gid, c.cid, g.score
from student s, course c, grade g
where s.sid=g.sid and c.cid=g.cid;