数据库SQL例题.docVIP

SQL补充例题 例：比较学号为9512101和9512102两位同学的c02课的成绩，结果显示成绩高的同学的学号、成绩。 select sno, grade from sc where grade=( SELECT max(grade) from sc where sno in (9512101 ,9512102) and cno=c02) select sno, grade from sc where sno in (9512101 ,9512102) and cno=c02 and grade=(SELECT max(grade) from sc where sno in (9512101 ,9512102) and cno=c02) USE college GO select * from (select sno, grade from sc where sno in (9512101 ,9512102) and cno=c02) sc1 join (SELECT sno, max(grade) as grade from sc where sno in (9512101 ,9512102) and cno=c02 group by sno ) sc2 on sc1.sno=sc2.sno 例：查询学号为9512101的c02课的成绩，结果显示成绩按优、良、中、及格、不及格。 DECLARE @grade_str char(10), @grade tinyint SELECT @grade=grade from sc where sno=9512101 and cno=c02 set @grade_str = CASE WHEN @grade=90 THEN 优 WHEN @grade=80 THEN 良  WHEN @grade=70 THEN 中 WHEN @grade=60 THEN 及格  ELSE 不及格 END print @grade_str 例 应用搜索CASE语句查询性别情况。 USE college GO SELECT sno as 学号,sname as 姓名, CASE ssex WHEN 男 THEN 男性 WHEN 女 THEN 女性 ELSE end as 性别 from student ORDER BY sno 例 用WHILE CONTINUE语句，用加法实现3x6 DECLARE @i int, @total int Set @i=0 set @total=0 WHILE @i3 Begin set @total=@total +6 set @i=@i +1 end Print @total 练：等到 ‘12：00：00’时刻显示 hellow WAITFOR TIME 12:00:00 Print hellow 练：延时10秒显示 hellow WAITFOR DELAY 00:00:10 Print ‘hellow’