DML语句习题及答案

1.删除学习“谌燕”老师课的SC 表记录；

delete from sc where
cno IN (
select cno from course
where tno=(select tno from teacher where tname=‘谌燕’)
);

select * from sc where cno IN (
select cno from course
where tno=(select tno from teacher where tname=‘谌燕’)
);

2.查询c002课的平均成绩

select avg(score) from sc where cno=‘c002’

3.删除没有上过c002课程的同学的学生信息
delete from student where sno not in(
select sno from sc where cno=‘c002’);

select * from student where sno not in(
select sno from sc where cno=‘c002’);

select * from student

4.向SC表中插入一些记录，这些记录要求符合以下条件：
没有上过编号“c002”课程的同学学号、课程号： C002、
“c002”号课的平均成绩；
插入数据，查数据（没有上过c002）

SELECT * FROM SC;

SNO:没有上过编号“c002”课程的同学学号
CNO:c002
SCORE:“c002”号课的平均成绩；

查询出要插入的数据
SELECT S1.SNO,‘c002’ CNO,(SELECT AVG(SCORE) FROM SC WHERE CNO=‘c002’)
FROM STUDENT S1 left join SC S2
on S1.SNO = S2.SNO
where S1.SNO NOT IN (SELECT SNO FROM SC WHERE CNO = ‘c002’)

insert into sc
(sno, cno, score)

5.将c001课成绩小于80分的同学的分数加10分
select * from sc where cno=‘c001’ and score <80;

update sc set score=score+10 where cno=‘c001’ and score <80;
select * from sc;

6.将c002课程的成绩增加5分
update sc set score=score+5 where cno=‘c002’ ;

7.将c001课程成绩小于80分的同学的成绩增加10分

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

8.增加一个学生:学号’s013’，姓名：‘王麻子’，年龄：28，性别：男

select * from student;
–1
insert into student(sno,sname,sage,ssex) values (‘s013’,‘王麻子’,28,‘男’);
–2
insert into student select ‘s013’,‘王麻子’,28,‘男’ from dual;

9.找出没有选择c003课程的学生，并为他们选上c003课程，默认分为60分
–思考：如果SC表中有学生选择了C003课程，将其C003课程的分数置为80？？？
select * from sc;
–1ti
insert into sc
select sno,‘c003’ cno,60 score from student where sno not in(
select sno from sc where cno =‘c003’)
–2ti
update from sc set score=80 where cno=‘c003’

merge into sc a
using student b
on (a.sno=b.sno and a.cno =‘c003’)

when matched then
update set score=80
when not matched then
insert values(b.sno,‘c003’,60);

10.给所有女学生的成绩加10分

select * from sc;
select * from student ;

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

11.创建一张和sc表相同的表，并将s001和s002学生的选课信息插入新表中

–1.
create table sc_1 as select sno,cno,score from sc where sno in(‘s001’,‘s002’);
select * from sc_1;
–2
create table sc_2 as select * from sc where 1=2;
insert into sc_2 select 语句;

12.将所有c001课程成绩低于平均成绩的同学的分数改为60分

–1查
select * from sc where cno=‘c001’
and score<(
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’ ) ;

select * from sc;

13.删除学生s002学生选择c001课的记录

select * from sc where sno=‘s002’ and cno=‘c001’;

delete from sc where sno=‘s002’ and cno=‘c001’;

14.将s001学生的所有课程成绩改为他自己的平均成绩（更新）

select * from sc;
update sc set score=(
select avg(score) from sc where sno=‘s001’)
where sno=‘s001’;

15.将s001学生的所有课程成绩改为各科的平均成绩

select * from sc;

update sc a set score=(
select avg(score) from sc b where a.cno=b.cno )
where sno=‘s001’;