有3张表,分别为 student学生表:sid(学号,number),sname(姓名,varchar2); class课程表:cid(课程号,number),cname(课程名称,varchar2); score成绩表:sid,cid,score(成绩,number),re(备注,varchar2)。请写出SQL语句: A、查询成绩大于90分的学生姓名及课程名称; B、将成绩表中成绩介于70-80的记录的re字段更新为“良”; C、将成绩表中sid未曾在student或cid未曾在class表出现过的记录删除
create table student(
sid number(8) primary key,
sname varchar(20)
);
create table class(
cid number(10) primary key,
cname varchar2(20)
);
create table score(
sid number(8)
constraint score_fk_student
references student(sid),
cid number(10)
constraint score_fk_class
references class(cid),
score number(2),
res varchar2(50),
constraint score_pk primary key(sid,cid)
);
--student数据
insert into student(sid,sname)
values(12082110,'李辉');
insert into student(sid,sname)
values(12082214,'黄汉文');
insert into student(sid,sname)
values(12082401,'李明');
insert into student(sid,sname)
values(12093312,'龙少化');--class数据
insert into class(cid,cname)
values(11101,'算法分析与设计');
insert into class(cid,cname)
values(11125,'Java程序设计');
insert into class(cid,cname)
values(11500,'计算机网络');
insert into class(cid,cname)
values(11250,'网页设计');
--score数据
insert into score(sid,cid,score)
values(12082110,11250,85);
insert into score(sid,cid,score)
values(12082214,11125,78);
insert into score(sid,cid,score)
values(12082401,11101,65);
insert into score(sid,cid,score)
values(12093312,11500,98);
--
--A:查询成绩大于90分的学生姓名及课程名称
select student.sname,class.cname
from student,class,score
where score.sid=student.sid and
score.cid=class.cid and
score.score>90;
SNAME CNAME
-------------------- --------------------
龙少化 计算机网络--B:将成绩表中成绩介于70-80的记录的re字段更新为“良”
update score set res='良'
where score between 70 and 80;SQL> select * from score; SID CID SCORE REMARKS
--------- ----------- ----- --------------------------------------------------
12082110 11250 85
12082214 11125 78 良
12082401 11101 65
12093312 11500 98 --C:将成绩表中sid未曾在student或cid未曾在class表出现过的记录删除
delete from score
where not exits(
(select 1 from student where score.sid=student.sid) and
(select 1 from class where score.cid=class.cid))--对于最后一个问题,因为score表中的sid和cid是参照student(sid)和class(cid)的,
--所以不存在score表中有信息,而找不到这个学生和这门课程。
sid number(8) primary key,
sname varchar(20)
);
create table class(
cid number(10) primary key,
cname varchar2(20)
);
create table score(
sid number(8)
constraint score_fk_student
references student(sid),
cid number(10)
constraint score_fk_class
references class(cid),
score number(2),
res varchar2(50),
constraint score_pk primary key(sid,cid)
);
--student数据
insert into student(sid,sname)
values(12082110,'李辉');
insert into student(sid,sname)
values(12082214,'黄汉文');
insert into student(sid,sname)
values(12082401,'李明');
insert into student(sid,sname)
values(12093312,'龙少化');--class数据
insert into class(cid,cname)
values(11101,'算法分析设计');
insert into class(cid,cname)
values(11125,'Java程序设计');
insert into class(cid,cname)
values(11500,'计算机网络');
insert into class(cid,cname)
values(11250,'网页设计');
--score数据
insert into score(sid,cid,score)
values(12082110,11250,85);
insert into score(sid,cid,score)
values(12082214,11125,78);
insert into score(sid,cid,score)
values(12082401,11101,65);
insert into score(sid,cid,score)
values(12093312,11500,98);
--
--A:查询成绩大于90分的学生姓名及课程名称
select student.sname,class.cname
from student,class,score
where score.sid=student.sid and
score.cid=class.cid and
score.score>90;
SNAME CNAME
-------------------- --------------------
龙少化 计算机网络--B:将成绩表中成绩介于70-80的记录的re字段更新为“良”
update score set res='良'
where score between 70 and 80;SQL> select * from score; SID CID SCORE REMARKS
--------- ----------- ----- --------------------------------------------------
12082110 11250 85
12082214 11125 78 良
12082401 11101 65
12093312 11500 98 --C:将成绩表中sid未曾在student或cid未曾在class表出现过的记录删除
方法一:DELETE FROM score
WHERE sid NOT IN (SELECT sid FROM student)
OR cid NOT IN (SELECT cid FROM class);
方法二:DELETE FROM score
WHERE sid NOT IN (SELECT sid FROM student WHERE sid IS NOT NULL)
OR cid NOT IN (SELECT cid FROM class WHERE cid IS NOT NULL);