Oracle考試題作業

標籤：nextval   efault   建議   資訊   alt   seq   記錄   檢查   lte   

建立一張學員資訊表(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;

 

Oracle考試題作業