表（一）Student (学生表)
|
属性名 |
数据类型 |
可否为空 |
含 义 |
|
Sno |
Varchar2(3) |
否 |
学号（主码） |
|
Sname |
Varchar2(8) |
否 |
学生姓名 |
|
Ssex |
Varchar2(2) |
否 |
学生性别 |
|
Sbirthday |
Date |
可 |
学生出生年月 |
|
Class |
Varchar2(5) |
可 |
学生所在班级 |
create table student
(
Sno varchar2(3) not null,
Sname varchar2(8) not null,
Ssex varchar2(2) not null,
Sbirthday Date,
Class varchar2(5)
)
;
comment on column student.Sno
is '学号';
comment on column student.Sname
is '学生姓名';
comment on column student.Ssex
is '学生性别';
comment on column student.Sbirthday
is '学生出生年月';
comment on column student.Class
is '学生所在班级';
表（二）Course（课程表）
|
属性名 |
数据类型 |
可否为空 |
含 义 |
|
Cno |
Varchar2(5) |
否 |
课程号（主码） |
|
Cname |
Varchar(10) |
否 |
课程名称 |
|
Tno |
Varchar2(3) |
否 |
教工编号（外码） |
create table Course
(
Cno varchar2(5) not null,
Cname varchar(10) not null,
Tno varchar2(3) not null
)
;
comment on column Course.Cno
is '课程号';
comment on column Course.Cname
is '课程名称';
comment on column Course.Tno
is '教工编号';
alter table Course
add constraint pk_Course primary key (Cno);
alter table Course
add constraint fk_Tno foreign key (Tno)
references Teacher (Tno);
表（三）Score(成绩表)
|
属性名 |
数据类型 |
可否为空 |
含 义 |
|
Sno |
Varchar2(3) |
否 |
学号（外码） |
|
Cno |
Varchar2(5) |
否 |
课程号（外码） |
|
Degree |
Number(4,1) |
可 |
成绩 |
|
主码：Sno+ Cno |
|||
create table Score
(
Sno varchar2(3) not null,
Cno varchar2(5) not null,
Degree Number(4,1)
)
;
comment on column Score.Sno
is '学号';
comment on column Score.Cno
is '课程号';
comment on column Score.Degree
is '成绩';
alter table Score
add constraint fk_Sno foreign key (Sno)
references student (SNO);
alter table Score
add constraint fk_Cno foreign key (Cno)
references Course (Cno);
表（四）Teacher(教师表)
|
属性名 |
数据类型 |
可否为空 |
含 义 |
|
Tno |
Varchar2(3) |
否 |
教工编号（主码） |
|
Tname |
Varchar2(4) |
否 |
教工姓名 |
|
Tsex |
Varchar2(2) |
否 |
教工性别 |
|
Tbirthday |
Date |
可 |
教工出生年月 |
|
Prof |
Varchar2(6) |
可 |
职称 |
|
Depart |
Varchar(10) |
否 |
教工所在部门 |
create table TEACHER
(
tno VARCHAR2(3) not null,
tname VARCHAR2(4) not null,
tsex VARCHAR2(2) not null,
tbirthday DATE,
prof VARCHAR2(6),
depart VARCHAR2(10) not null
)
comment on column TEACHER.tno
is '教工编号';
comment on column TEACHER.tname
is '教工姓名';
comment on column TEACHER.tsex
is '教工性别';
comment on column TEACHER.tbirthday
is '教工出生年月';
comment on column TEACHER.prof
is '职称';
comment on column TEACHER.depart
is '教工所在部门';
alter table TEACHER
add constraint PK_Tno primary key (TNO)
表1-2数据库中的数据
表（一）Student
|
Sno |
Sname |
Ssex |
Sbirthday |
class |
|
108 |
曾华 |
男 |
1977-09-01 |
95033 |
|
105 |
匡明 |
男 |
1975-10-02 |
95031 |
|
107 |
王丽 |
女 |
1976-01-23 |
95033 |
|
101 |
李军 |
男 |
1976-02-20 |
95033 |
|
109 |
王芳 |
女 |
1975-02-10 |
95031 |
|
103 |
陆君 |
男 |
1974-06-03 |
95031 |
insert into Student values('108','曾华','男',to_date ('1977-09-01','yyyy-mm-dd'),'95033');
insert into Student values('105','匡明','男',to_date ('1975-10-02','yyyy-mm-dd'),'95031');
insert into Student values('107','王丽','女',to_date ('1976-01-23','yyyy-mm-dd'),'95033');
insert into Student values('101','李军','男',to_date ('1976-02-20','yyyy-mm-dd'),'95033');
insert into Student values('109','王芳','女',to_date ('1975-02-10','yyyy-mm-dd'),'95031');
insert into Student values('103','陆君','男',to_date ('1974-06-03','yyyy-mm-dd'),'95031');
表（二）Course
|
Cno |
Cname |
Tno |
|
3-105 |
计算机导论 |
825 |
|
3-245 |
操作系统 |
804 |
|
6-166 |
数字电路 |
856 |
|
9-888 |
高等数学 |
831 |
insert into Course values('3-105','计算机导论','825');
insert into Course values('3-245' ,'操作系统','804');
insert into Course values('6-166' ,'数字电路', '856');
insert into Course values('9-888', '高等数学', '831');
表（三）Score
|
Sno |
Cno |
Degree |
|
103 |
3-245 |
86 |
|
105 |
3-245 |
75 |
|
109 |
3-245 |
68 |
|
103 |
3-105 |
92 |
|
105 |
3-105 |
88 |
|
109 |
3-105 |
76 |
|
101 |
3-105 |
64 |
|
107 |
3-105 |
91 |
|
108 |
3-105 |
78 |
|
101 |
6-166 |
85 |
|
107 |
6-166 |
79 |
|
108 |
6-166 |
81 |
insert into score values('108', '6-166', '81');
表（四）Teacher
|
Tno |
Tname |
Tsex |
Tbirthday |
Prof |
Depart |
|
804 |
李诚 |
男 |
1958-12-02 |
副教授 |
计算机系 |
|
856 |
张旭 |
男 |
1969-03-12 |
讲师 |
电子工程系 |
|
825 |
王萍 |
女 |
1972-05-05 |
助教 |
计算机系 |
|
831 |
刘冰 |
女 |
1977-08-14 |
助教 |
电子工程系 |
insert into Teacher values('804', '李诚', '男', to_date('1958-12-02','yyyy-mm-dd'), '副教授', '计算机系');
insert into Teacher values('856', '张旭', '男', to_date('1969-03-12','yyyy-mm-dd'), '讲师', '电子工程系');
insert into Teacher values('825', '王萍', '女', to_date('1972-05-05','yyyy-mm-dd'), '助教', '计算机系');
insert into Teacher values('831', '刘冰', '女', to_date('1977-08-14','yyyy-mm-dd'), '助教', '电子工程系');
1、 查询Student表中的所有记录的Sname、Ssex和Class列。
select sname,ssex,class from student;
2、 查询教师所有的单位即不重复的Depart列。
select distinct * from student;
3、 查询Student表的所有记录。
select * from Student;
4、 查询Score表中成绩在60到80之间的所有记录。
select * from Score where DEGREE > 60 and DEGREE < 80;
5、 查询Score表中成绩为85，86或88的记录。
select * from Score where DEGREE = 85 or DEGREE = 86 or DEGREE = 88;
6、 查询Student表中“95031”班或性别为“女”的同学记录。
select * from Student where CLASS = 95031 or SSEX = '女';
7、 以Class降序查询Student表的所有记录。
select * from student order by class desc;
8、 以Cno升序、Degree降序查询Score表的所有记录。
select * from score order by cno asc , degree desc;
9、 查询“95031”班的学生人数。
select class,count(1) as 数量 from student where class ='95031' group by class;
10、 查询Score表中的最高分的学生学号和课程号。（子查询或者排序）
select sno || cno as 学号和课程号 from score order by DEGREE desc;
11、 查询每门课的平均成绩。
select Cno,count(1) as 数量, avg(Degree) as 平均值 from Score group by Cno;
12、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。
select Cno,count(1) as 数量, avg(Degree) as 平均值 from Score where cno like'3%' group by Cno having count(cno)>5;
13、查询分数大于70，小于90的Sno列。
select * from Score where Degree between 70 and 90;
14、查询所有学生的Sname、Cno和Degree列。
Select s.sname,c.cno,c.degree from student s , score c where s.sno=c.sno;
15、查询所有学生的Sno、Cname和Degree列。
Select c.sno,q.cname,c.degree from student s , score c,course q where s.sno=c.sno and q.cno=c.cno;
16、查询所有学生的Sname、Cname和Degree列。
Select s.sname,q.cname,c.degree from student s , score c,course q where s.sno=c.sno and q.cno=c.cno;
17、 查询“95033”班学生的平均分。
Select class,avg(degree) as 平均值 from student s,score c where s.sno=c.sno and class = '95033' group by class ;
18、 假设使用如下命令建立了一个grade表：
create table grade(low number(3),upp number (3),rank char(1))
insert into grade values(90,100,'A');
insert into grade values(80,89,'B');
insert into grade values(70,79,'C');
insert into grade values(60,69,'D');
insert into grade values(0,59,'E');
现查询所有同学的Sno、Cno和rank列。
select s.sno,s.cno,g.rank from score s , grade g where s.Degree between g.low and g.upp;