第五至十八章上机练习题(图形说明,含答案).docVIP

上机练习题（图形说明） 注：图形是截图，如果后面的…仅为结果的一部分，并不是标准结果，还有一部分没有显示 第二章 局部变量 declare @n int select @n=stuSeat from student where name=李文才 select * from student where stuSeat=@n+1 or stuSeat=@n-1或 select * from student where stuSeat in(@n-1,@n+1)或 set @n=@n+1 select * from student where stuSeat=@n set @n=@n-2 select * from student where stuSeat=@n IF … ELSE …练习 declare @n int set @n=(select avg(writtenExam) from score)或select @n=(select avg(writtenExam) from score)或 select @n=avg(writtenExam) from score if @n70 begin print 成绩优秀 --显示在文本结果页中 select top 3 * from score order by writtenExam desc--显示在表格结果页中，默认 end else begin print 本班成绩较差 select top 3 * from score order by writtenExam end while练习 declare @min int while(1=1) begin select @min=min(writtenExam) from score if(@min=60) break update score set writtenExam=writtenExam+2 end或 declare @n int select @n=count(*) from score where writtenExam60 while(@n0) begin update score set writtenExam=writtenExam+2 select @n=count(*) from score where writtenExam60 end update score set writtenExam=100 where writtenExam100--可以忽略 case ... when ...end 练习 select writtenExam,case when writtenExam=90 then A when writtenExam=80 and writtenExam90 then B when writtenExam=70 and writtenExam80 then C when writtenExam=60 and writtenExam70 then D else E end from score 或 select writtenExam,case writtenExam/10 when 9 then A when 8 then B when 7 then C when 6 then D else E end from score 第五章 普通Select语句（表达式变形） 1.查询菜品信息:列的名称为[菜品名称,单价,是否打折],要求是否打折的内容显示为可以打折,不打折 …… 2.查询菜品信息:列的名称为[菜品名称,单价],要求是如果菜品名称为空值或者空串时显示为未命名菜品,如果单价为空或者零时则显示未设置价格 … … 注意：类型转化cast,convert 3.查询各分类菜品的平均价格和菜品总数,[如果做得好请关联菜品分类表显示出各菜品分类名称,如右图] 或者 select dishSortID 分类菜品ID,avg(price) 平均价格,count(*) 菜品总数 from dbo.T_DishInfo group by dishSortID 或者 select dishsortname 分类,avg(t.price) 平均价格,count(t.dishID) 菜品总数 from T_DishInfo t inner join T_DishSort on t.dishsortid=T_DishSort.dishsortid group by t.dishSortID,dishsortname 4.查询出价格最贵的所有菜品信息(使用两种方法进行查询,课堂有讲过) ①declare @maxprice numeric(18,2) select @maxprice=m