S2_day_04高级查询

最新推荐文章于 2024-10-05 22:05:41 发布

原创最新推荐文章于 2024-10-05 22:05:41 发布 · 201 阅读

0 ·

CC 4.0 BY-SA版权

数据库专栏收录该内容

12 篇文章

订阅专栏

本文通过多个实际案例，深入浅出地介绍了SQL语言在数据库查询中的应用技巧，包括子查询、连接查询、条件判断及更新操作等核心内容。

--预习

select studentno,studentname, sex ,borndate,address from student 
where borndate>(select borndate from student where studentname='夏一桐')


select studentname,studentno from student where studentno in 
(
select studentno from  result 
where subjectid=(
select subjectid from subject
where subjectname='走进Java编程世界'

)and studentresult> 60

)
select studentno,studentname from student 
where studentno in
 --A级
(
select studentno from result
 where subjectid = 
 --B级
(
select subjectid from subject
where subjectname='走进Java编程世界'

)and examdate=
--B级
(
select max(examdate) from result
where subjectid=
(
--C级
select subjectid from subject
where subjectname='走进Java编程世界'
)

)

)



--上节复习

declare @subjectID int
select @subjectID=subjectid from subject
where subjectname='走进Java编程世界'

declare @date datetime
select @date=MAX(examdate) from  result 
where subjectid =@subjectID 

select studentname ,等级=
case 
when studentresult >90 then 'A'
when studentresult >80 then 'B'
when studentresult >70 then 'C'
when studentresult >60 then 'D'
else 'E'
end --案例：检查“oop”课程最近一次考试。--如果有80分以上的成绩，则每人提2分；
	--否则，每人提5分。最终的成绩不得大于100分
	if exists (
	select studentresult from result
	where subjectid=
	(
	
	select subjectid from subject
	where subjectname='oop'
	
	
	)and examdate =
	(
	select MAX(examdate) from result
	where subjectid=
	(
	select subjectid from subject
	where subjectname='oop'
	)
	
	)
	and studentresult>80
	
	
	)
	begin
	update result set studentresult=100
	where subjectid=
	(

	select subjectid from subject
	where subjectname='oop'
	
	
	)and examdate =
	(
	select MAX(examdate) from result
	where subjectid=
	(
	select subjectid from subject
	where subjectname='oop'
	)
	)
	and studentresult>98
	
	update result set studentresult+=2
	where subjectid=
	(
	
	select subjectid from subject
	where subjectname='oop'
	
	
	)and examdate =
	(
	select MAX(examdate) from result
	where subjectid=
	(
	select subjectid from subject
	where subjectname='oop'
	)
	)and studentresult<=98
	end
	else
	begin 
	update result set studentresult+=5
	where subjectid=
	(
	select subjectid from subject
	where subjectname='oop'
	
	
	)and examdate=
	(
	select MAX (examdate) from result
	where subjectid=
	(
	select subjectid from subject
	where subjectname='oop'
	)
	
	)
	
	
	end
	
	
--检查是否正确	
	select * from result
	where subjectid=
	(

	select subjectid from subject
	where subjectname='oop'
	
	
	)and examdate =
	(
	select MAX(examdate) from result
	where subjectid=
	(
	select subjectid from subject
	where subjectname='oop'
	)
	)

--上机4
--检查并更新S1的学生为S2

--查询是否存在S1学生
if exists(
select studentname ,gradeid from student
where gradeid in
(
select gradeid from grade
where gradename='S1'

)


)
--如果存在 将S1的年级编号改为S2的年级编号
begin

update student set gradeid=2
 where gradeid in
(
select gradeid from grade
where gradename='S1'

)
end



--进行检查是否将S1的年级编号改为S2的年级编号
select studentname ,gradeid from student
where gradeid in
(
select gradeid from grade
where gradename='S2'

)

from student,result where student.studentno=result.studentnoand subjectid =@subjectID