Oracle学习笔记（三）

<例子>
建立表如下:

学生基本信息表
CREATE Student(
[Studentid][Int]IDENTITY(1,1)NOT NULL primary key,--主键
[StudentName][char]NOT NULL

)
课程信息表
CREATE Subject(
[SubjectID][char]NOT NULL primary key,       --主键
[SubjectName][char]NOT NULL
)
成绩表
CREATE Grade(
[Studentid][Int]NOT NULL,  --联合主键
[SubjectID][char]NOT NULL,  --联合主键
[Grade] [INT]NOT NULL,
primary key (studentid,subjectid)
)

1.将建表命令改为ORACLE语句在ORACLE中建表
create table student( --学生表
studentid number(3) primary key, --学生编号
studentname varchar2(20) --学生的姓名
);

create table subject( --课程表
subjectid char(3) primary key, --课程编号
subjectname varchar2(20)  --课程的名字
);

create table grade( --分数表
studentid number(3) references student(studentid), --学生id
subjectid char(3) references subject(subjectid), --课程id
mark      number(3), --分数
primary key (studentid,subjectid) --联合主键
);

 

insert into student values (101,'张三');
insert into student values (102,'李云');
insert into student values (103,'未');

insert into subject values ('A01','C++');
insert into subject values ('A02','ASP');
insert into subject values ('A03','JAVA');

insert into grade values (101,'A01',59);
insert into grade values (101,'A02',72);
insert into grade values (101,'A03',90);

insert into grade values (102,'A01',75);
insert into grade values (102,'A02',91);

insert into grade values (103,'A01',71);

 

2.作如下4题

第一问：查询出以下信息

学号 学生姓名 课程名称 成绩 （要全部学生信息）

关联查询 (多张表的)
别名

select a.studentid as "学  号",studentname "学生姓名",
subjectname "课程名称",mark "成  绩"
from student a , subject b , grade c
where a.studentid = c.studentid
and b.subjectid = c.subjectid;

[select a.studentid "学  号",studentname "学生姓名",
subjectname "课程名称",mark "成  绩"
from student a , subject b , grade c] 笛卡尔积

       3 * 3 * 6 = 54;

第二问：查询出以下信息

学号 学生姓名 课程名称 成绩（只显示每科最高分）

select a.studentid "学  号",studentname "学生姓名",
subjectname "课程名称",mark "成  绩"
from student a,subject b,grade c
where a.studentid = c.studentid
and b.subjectid = c.subjectid
and (subjectname,mark)
in (select subjectname "课程名称",max(mark) "成  绩"
from student a,subject b,grade c
where a.studentid = c.studentid
and b.subjectid = c.subjectid
group by subjectname)

(最高分---分数比我高的学生的人数=0)
select a.studentid "学  号",studentname "学生姓名",
subjectname "课程名称",mark "成  绩"
from student a,subject b,grade c
where a.studentid = c.studentid
and b.subjectid = c.subjectid
and (select count(*) from grade
where subjectid = b.subjectid and
mark > c.mark) = 0

第三问：查询出以下信息

学号 学生姓名 课程名称 成绩 （成绩大于60时的显示及格，小于60时的显示不及格）

select a.studentid "学  号",studentname "学生姓名",
subjectname "课程名称",
decode(sign(mark-60),-1,'不及格','及格') "成  绩"
from student a,subject b,grade c
where a.studentid = c.studentid
and b.subjectid = c.subjectid

第四问：查询出以下信息

学号 学生姓名 （查询出课程超过1门以上学生的信息）

select a.studentid "学  号",studentname "学生姓名",
count(subjectname)
from student a , subject b , grade c
where a.studentid = c.studentid
and b.subjectid = c.subjectid
group by a.studentid,studentname
having count(subjectname) >= 2