SQL 高级测试题-答案
二、完成下列题目
（一）、SQL语句题 （6分/题 共24分）
    1、查询“张旭“教师任课的学生编号和成绩。
--简单联接查询
select s.SNO 学生编号,s.DEGREE 成绩 from SCORE s,TEACHER t,COURSE c
    where s.CNO=c.CNO and c.TNO=t.TNO and t.TNAME='张旭'
    --子查询
select sno 学生编号, degree 成绩 from score where cno =
    (select cno from course where tno=
    (select tno from teacher where tname='张旭'))
    2、查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录
select * from student where sno in
    (select distinct sno from score where cno ='3-105'and degree>
        (select degree from score where sno='109' and cno ='3-105'))
    3、查询选修某课程的同学人数多于5人的教师姓名。
select tname 教师名 from teacher where tno in
    (select tno from course where cno in
        (select cno from score group by cno having count(cno)>5))
4、查询所有选修“计算机导论”课程的“男”同学的姓名，选课名和成绩
select st.sname 姓名,c.cname 选课名,sc.degree 成绩
    from score sc,student st,course c
    where c.CNO=sc.CNO and sc.SNO=st.SNO
    and st.ssex='男' and c.cname='计算机导论'
（二）T-SQL编程题 （10分/题 共30分）
    1、统计并显示选修“计算机导论”的所有学生的平均分，如果平均分在70分以上，显示”成绩优秀“，并显示前三名学生的姓名和成绩；如果在70分以下，显示”成绩教差“，并显示后三名学员的姓名和成绩。
go
declare @cname varchar(20) ='计算机导论'  --变量名可以不按要求来
declare @avg  float
declare @cno  varchar(20)
select @cno=cno from course where cname=@cname
select @avg =avg(degree) from score where cno=@cno
if(@avg>80)                               
  begin
    print '成绩优秀'                    --如果没有这句话扣2分
    select top 3 st.sname 姓名,sc.degree 成绩 
from student st ,score sc
    where st.sno=sc.sno and sco=@cno
order by degree desc
  end
else
  begin
    print '成绩较差'                  --如果没有这句话扣2分
    select top 3 st.sname 姓名,sc.degree 成绩
from student st ,score sc
    where st.sno=sc.sno and sco=@cno
order by degree asc             
  end
2、查询"操作系统"选修课的平均分，如果平均分低于80分，就给所有选修此课程的学生加2分，再次判断平均分如果低于80分，就继续加2分，直至此课平均分高于80分后，打印出选修此课程的学生的最高分 （while循环来实现）
declare @courseName varchar(50) ='操作系统'    --选修课名
declare @courseID varchar(20)            --选修课编号
declare @avgDegree float                    --平均分
declare @maxDegree float                    --最高分
select @courseID=cno from COURSE where CNAME=@courseName
select @avgDegree= AVG(DEGREE) from SCORE where CNO=@courseID
while(1=1)
    begin
        if(@avgDegree<80)
            begin
                update SCORE set DEGREE=DEGREE+2 where CNO=@courseID
                select @avgDegree= AVG(DEGREE) from SCORE
where CNO=@courseID
                continue
            end
        else
            break
    end
-- 第二种 while写法开始
while(@avgDegree <80)
    begin
        update score set degree=degree+2 where cno=@courseID
        select @avgDegree =avg(degree) from score
where cno=@courseID
    end
--第二种while写法结束
select @maxDegree=max(DEGREE) from SCORE where CNO=@courseID
print '经过调分后选修'+@courseName+'的学生最高分为:'+convert(varchar(20),@maxDegree)+ '分'
--或cast(@maxDegree as varchar(20))
go   
3、根据所有学生的成绩和以下等级评分规则，编写T-SQL语句显示学生的学号、成绩及成绩等级（使用case语句来实现）
        优：90分以上
        良：80-90分
        中：70-79分
        差：60-69分
        不及格：60分以下
select sno 学号, DEGREE 成绩,成绩等级=
        case
            when DEGREE >90 then '优'
            when DEGREE between 80 and 90 then '良'
            when DEGREE between 70 and 79 then '中'
            when DEGREE betweenselect distinct from 60 and 69 then '差'
            --when DEGREE <60 then '不及格'
            else '不及格'
        end
    from SCORE
go
（三）创建视图 （12分）
    1、创建一个视图，名为V_newScore，要显示出学生姓名、课程名称、分数和教师姓名，并且记录中不包括成绩不及格的学生。(要求判断是否存在此视图)。
--判断是否存在此视图 4分
if(exists(select * from sysobjects where name='V_newScore'))
drop view V_newScore
go
create view V_newScore as
select st.sname,c.cname,sc.degree,t.tname
from student st,score sc,course c,teacher t
where st.sno=sc.sno and sc.cno=c.cno
and c.tno =t.tno and sc.degree>=60
go
（四）创建事务 （14分）
    1、由于教务老师失误，将‘陆君’的选修的"3-105"成绩和‘匡明’选修的"3-105"成绩输入时填写反了，现要求更改过来，请使用显式事务来实现此业务需求
begin transaction    --开启事务
declare @lsno varchar(20)  --声明变量
declare @ksno varchar(20)
declare @ldegree float
declare @kdegree float
--赋值变量
select @lsno =sno from student where sname='陆君'
select @ksno =sno from student where sname='匡明'
select @ldegree=degree from score where sno=@lsno and cno='3-105'
select @kdegree=degree from score where sno=@ksno and cno='3-105'
declare @errorCount int =0
update score set degree =@ldegree where sno=@ksno and cno='3-105'
set @errorCount =@errorCount +@@error
update score set degree =@kdegree where sno=@lsno and cno='3-105'
set @errorCount =@errorCount +@@error
if(@errorCount <> 0)
  begin
      print '发生异常，回滚'
      rollback transaction
  end
else
  begin
      print '没有异常，提交'
      commit transaction
  end
go
（五）存储过程 （20分）
    1、创建一个存储过程，根据输入的学生姓名'xxx'(如王芳)，查询该学生的学生编号，选课名，成绩和任课老师名，如果该学生没有选修课，则提示'xxx学生没有选修课程'，如果没有该学生姓名则提示'xxx学生不存在'
--判断是否存在此存储过程 4分
if(exists(select * from sysobjects where name='proc_sname_info'))
drop proc proc_sname_info
go
create proc proc_sname_info
  @sname varchar(20)
as
if(exists(select * from student where sname=@sname))
  begin
    if(exists(select * from score where sno=
(select sno from student where sname=@sname)))
    begin
        select sc.sno,c.cname,sc.degree,t.tname
        from student st,score sc,course c,teacher t
        where st.sno=sc.sno and sc.cno=c.cno
        and c.tno=t.tno and st.sname=@sname
        end
    else
      print @sname+'学生没有参加考试'
  end
else
  print @sname+'学生不存在'
go
exec proc_sname_info '王芳'
评分标准：
SQL语句题
6分/题  共24分
不限制答题语句方式答对得分答错不得分
T-SQL编程题
10分/题  共30分
创建视图
12分/题
创建事物
14分/题
存储过程
20分/题

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。