–1.查询与“张志国”同一班级的学生信息(使用连接查询和子查询方式)
--连接查询(子连接查询) select X.SNo,X.SN,X.Sex,X.Class,X.Birthday,X.Phone from S as X,S as Y where Y.SN='张志国' and X.Class=Y.Class and X.SN!='张志国' --子查询 select * from S where Class = ( select Class from S where SN='张志国') and SN!='张志国'
–2.查询比“计算机应用基础”学时多的课程信息(使用连接查询和子查询方式)
--连接查询 select X.CNo,X.CN,X.Credit,X.CT,X.Teach from C as X,C as Y where Y.CN='计算机应用基础' and X.CT>Y.CT --子查询 select * from C where CT>( select CT from C where CN='计算机应用基础')
–3.查询选修课程号为K002的学生的学号、姓名(使用连接查询、普通子查询、相关子查询、使用exists关键字的相关子查询)
--连接查询(内连接查询) select R1.SNo,R1.SN from (select SNo,SN from S) as R1 inner join (select CNo,SNo from SC where CNo='K002') as R2 on R1.SNo=R2.SNo --普通子查询 select SNo,SN from S where SNo in( select SNo from SC where CNo='K002') select SNo,SN from S where SNo=any( select SNo from SC where CNo='K002') --相关子查询 select SNo,SN from S where SNo in( select SNo from SC where CNo='K002' and SNo=S.SNo) --使用exists关键字的相关子查询 select SNo,SN from S where exists( select * from SC where CNo='K002' and SNo=S.SNo)
–4.查询没有选修K001和M001课程的学号、课程号和三次成绩(使用子查询)
--子查询(普通子查询) select SNo,CNo,Score_1,Score_2,Score_3 from SC where CNo not in ('K001','M001')
–5.在学生表中添加一条学生记录,其中,学号为1593,姓名为张乐,性别为男,专业班级为电子05
insert into S(SNo,SN,Sex,Class) values(1593,'张乐','男','电子05')
–6.将所有课程的学分数变为原来的两倍
update C set Credit=Credit*2
–7.删除张乐的信息
delete from S where SN='张乐'