sql习题整理

--（视图题）创建含学生姓名与平均成绩的视图
/*创建视图a*/
create view a
as
select姓名,avg(成绩)as平均成绩from s,sc
where s.学号=sc.学号
group by s.学号,姓名
/*执行视图a*/
select*from a
--（存储过程之无参）创建存储过程b内容为显示s表
/*创建存储过程b*/
create proc b
as
select*from s
/*执行存储过程b*/
exec b
--(存储过程之有参)判断同学平均成绩如果大于，则所有成绩减少%。

/*创建存储过程b2*/
create proc b2 @a char(6)
as
if(select avg(成绩)from sc where学号=@a)>60
begin
update sc set成绩=成绩*0.9 where学号=@a
end
/*执行存储过程b2*/
exec b2'J0401'
--(存储过程之‘exists’的使用）如果s表存在该同学那么查询其平均成绩
/*创建存储过程b3*/
create proc b3 @a char(6)
as
if exists(select学号from s where学号=@a)
begin
select学号,avg(成绩)as平均成绩from sc where学号=@a group by学号end
/*执行存储过程b3*/
exec b3'j0402'
--（存储过程之多参)已知该同学的系与性别，求该同学的相关其它信息。

/*创建存储过程b4*/
create proc b4
@a varchar(20),
@b char(2)
as
begin
select*from s
where系=@a and性别=@b
end
/*执行存储过程b4*/
exec b4'管理信息系','女'
--(触发器之修改数据）修改s表数据时,sc表数据同时修改(after=同时) /*创建触发器c1*/
create trigger c1
on s after update
as
begin
update sc
set学号=(select学号from s)
where学号=(select学号from deleted)
end
/*执行触发器c1*/
update s
set学号='j0101'
where学号='j0401'
--(触发器之删除数据)删除s表数据时,先删除sc表相关数据(先=for）
/*创建触发器c2*/
create trigger c2
on s for delete
as
begin
delete sc
where学号=(select学号from deleted)
delete s
where学号=(select学号from deleted)
end
/*执行触发器c2*/
delete s where学号='00101'
--(触发器之插入数据)为sc表插入数据时，转为s表插入（转为=instead of) /*创建触发器c3*/
create trigger c3
on sc instead of insert
as
begin
insert s(学号)
select学号from inserted
end
/*执行触发器c3*/--结果是SC表没有插入数据，S表成绩插入了学号
insert sc(学号)
values('00101')
--（函数之标量函数）通过给定的学号，输出学生的姓名
/*创建函数d1*/
create function d1(@a char(6))
returns char(8)
as
begin
return(select姓名from s where学号=@a)
end
/*执行函数d1*/
select dbo.d1('j0401')
--（函数之表值函数）返回学生成绩的等级
/*创建函数d2*/
create function d2()
returns table
as
return(select学号,等级=
case
when avg(成绩)<60 then'差'
when avg(成绩)<70 then'中'
when avg(成绩)<80 then'良'
when avg(成绩)<100 then'优'
end
from sc group by学号
)
/*执行函数d2*/
select*from dbo.d2()
--(函数之多语句表值函数)创建函数求指定（学号）同学的总成绩，平均成绩/*创建函数d3*/
create function d3(@a char(6))
returns @b table(学号char(6),
总成绩int,
平均成绩int
)
as
begin
insert @b
select学号,sum(成绩),avg(成绩)from sc
where学号=@a group by学号
return
end
/*执行函数d3*/
select*from dbo.d3('j0401')
--(流程控制之如果那么)如果课程C03的平均成绩低于，那么显示'不及格', --如果高于则显示'优秀',其他的显示'合格'
declare @a int
select @a=avg(成绩)from sc
where课程号='C01'
if @a<60 print'不及格'
else if @a <90 print'合格'
else print'优秀'
--(流程控制之case之有参)判断同学是学工科还是文科的
select姓名,科目=
case系
when'管理信息系'then'工科'
when'外语'then'文科'
end
from s
--(流程控制之case之无参)所有同学是后，后，还是后
select姓名,年代=
case
when year(出生日期)<1980 then'70后'
when year(出生日期)<1990 then'80后'
when year(出生日期)<2000 then'90后'
end
from s
--(流程控制之while)求——的和
declare @a int,@b int
set @a=1
set @b=0
while @a<=100
begin
set @b=@a+@b
set @a=@a+1
end
select @b
--（事务）将某人的成绩提高%判断超过分回滚
--如果不超过则提交
/*创建含事务的存储过程b5*/
create proc b5
@a char(6)
as
begin tran e
update sc
set成绩=成绩*1.1
where学号=@a
if(select avg(成绩)from sc where学号=@a)>=100 rollback tran
else
commit tran
/*执行存储过程b5*/
exec b5'j0401'
select*from sc。