oracle增删改习题笔记，小白必看！（内有福利）

                                                     增删改习题笔记

1.删除学习“谌燕”老师课的SC 表记录；
select course.cno from teacher,course
where teacher.tno=course.tno and teacher.tname='谌燕';

delete from sc where sc.cno in (
select course.cno from teacher,course
where teacher.tno=course.tno and teacher.tname='谌燕'
);
--恢复 sc表
drop table sc ;
create table sc as select * from sc1;
2.查询c002课的平均成绩

select avg(score) from sc where cno='c002';

3.查询没有上过c002课程的同学学号
-- 先查上过的学号
select sno from sc where cno='c002';

select sno from student where sno not in (
select sno from sc where cno='c002'
);

4.向SC表中插入一些记录，这些记录要求符合以下条件：
--没有上过编号“c002”课程的同学学号、“c002”号课的平均成绩；
-- 没上过的学号
select sno from student where sno not in (
select sno from sc where cno='c002'
);

insert into sc(sno,cno,score)
select sno,'c002',(select avg(score) from sc where cno='c002') 
from student where sno not in (
select sno from sc where cno='c002'
)

select * from sc;

select * from emp
select empno,'CLERK',(select avg(comm) from emp where empno =7499)
from emp where empno not in (7698,7782)

5.查询c001课成绩小于80分的同学的学号和分数
select sno,score from sc where sc.cno='c001'and sc.score<80;

6.将c002课程的成绩增加5分
--恢复sc表
drop table sc ;
create table sc as select * from sc1;

update sc set score=score+5 where cno='c002';

select * from sc;

7.将c001课程成绩小于80分的同学的成绩增加10分
--恢复sc表
drop table sc ;
create table sc as select * from sc1;
select * from sc;

update sc set score=score+10 where cno='c001'and score<80;

8.增加一个学生:学号's013'，姓名：'王麻子'，年龄：28，性别：男
select * from student;
insert into student values('s013','王麻子',28,'男');

9.找出没有选择c003课程的学生，并为他们选上c003课程，默认分类为60分
--恢复sc表
drop table sc ;
create table sc as select * from sc1;
select * from sc;
-- 学过的学号
select sno from sc where cno='c003';
-- 没学过的学号
select sno from student where sno not in (select sno from sc where cno='c003');

insert into sc select sno,'c003','60' from student 
where sno not in (select sno from sc where cno='c003');

10.给所有女学生的成绩加10分
--恢复sc表
drop table sc ;
create table sc as select * from sc1;
select * from sc;

select sno from student where ssex='女';

update sc set score=score+10 where sno in (select sno from student where ssex='女')

11.创建一张和sc表相同的表，并将s001和s002学生的选课信息插入新表中
create table ssc as select * from  sc where sno='s001' or sno='s002' ;
select * from ssc;

12.将所有c001课程成绩低于平均成绩的同学的分数改为60分
--恢复sc表
drop table sc ;
create table sc as select * from sc1;
select * from sc;
--先查 c001的avg
select avg(score) from sc where cno='c001';

update sc set score=60 where cno='c001' 
and score<(select avg(score) from sc where cno='c001')

13.删除学生s002学生选择c001课的记录
--恢复sc表
drop table sc ;
create table sc as select * from sc1;
select * from sc;
delete from sc where sno='s002'and cno='c001';

14、删除学习“谌燕”老师课的SC 表记录；
15.向SC表中插入一些记录，这些记录要求符合以下条件：没有上过编号“c002”课程的同学学号、“c002”号课的平均成绩；
16.删除“s002”同学的“c001”课程的成绩
--恢复sc表
drop table sc ;
create table sc as select * from sc1;
select * from sc;

update sc set score=null where sno='s002' and cno='c001';

17.将s001学生的所有课程成绩改为他自己的平均成绩
--恢复sc表
drop table sc ;
create table sc as select * from sc1;
select * from sc;
-- 查询s001的avg
select avg(score) from sc where sno='s001';

update sc set score=(select avg(score) from sc where sno='s001')where sno='s001';

18.将s001学生的所有课程成绩改为各科的平均成绩
--恢复sc表
drop table sc ;
create table sc as select * from sc1;
select * from sc;

-- 各科avg
select avg(score) from sc group by cno;

update sc s1 set score=(
select avg(score) from sc s2 group by cno having s1.cno= s2.cno
)where s1.sno='s001';
-- s1 -- s2