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’;