SQL存储过程实例

请编写T-SQL来实现如下功能：

1) 创建存储过程，查询是否有一半程序员的工资在2200、3000、3500、4000、5000或6000

元之上，如果不到分别每次给每个程序员加薪100元，至之一半程序员的工资达到2200，3000，3500，4000，5000或6000元。

2) 创建存储过程，查询程序员平均工资在4500元，如果不到则每个程序员每次加200元，

至到所有程序员平均工资达到4500元。

建表语句 USE master GO /*$$$$$$$$$$$$$建库$$$$$$$$$$$$$$$$$$$$$$$$*/ --检验数据库是否存在，如果为真，删除此数据库-- IF exists(SELECT * FROM sysdatabases WHERE name='Wage') DROP DATABASE Wage GO CREATE DATABASE Wage GO --建数据表-- USE Wage GO CREATE TABLE ProWage --程序员工资表 ( ID int identity(1,1) primary key, --工资编号 PName CHAR(10) NOT NULL , --程序员姓名 Wage int NOT NULL --工资 ) GO --插入数据-- INSERT INTO ProWage(PName,Wage)VALUES('青鸟',1900) INSERT INTO ProWage(PName,Wage)VALUES('张三',1200) INSERT INTO ProWage(PName,Wage)VALUES('李四',1800) INSERT INTO ProWage(PName,Wage)VALUES('二月',3500) INSERT INTO ProWage(PName,Wage)VALUES('蓝天',2780) 标准答案： --1、创建存储过程-- if exists (select * from sysobjects where name='Sum_wage') drop procedure Sum_wage GO create procedure Sum_wage @PWage int, @AWage int, @total int as while (1=1) begin if (select count(*) from ProWage)>2*(select count(*) from ProWage where Wage>=@PWage) update ProWage set @total=@total+@AWage,Wage=Wage+@AWage else break end print'一共加薪：'+convert(varchar,@total)+'元' print'加薪后的程序员工资列表：' select * from ProWage --调用存储过程1-- exec Sum_wage @PWage=2000,@AWage=100,@total=0 exec Sum_wage @PWage=2200,@AWage=100,@total=0 exec Sum_wage @PWage=3000,@AWage=100,@total=0 exec Sum_wage @PWage=4000,@AWage=100,@total=0 exec Sum_wage @PWage=5000,@AWage=100,@total=0 exec Sum_wage @PWage=6000,@AWage=100,@total=0 --2、创建存储过程2-- if exists (select * from sysobjects where name='Avg_wage') drop procedure Avg_wage GO create procedure Avg_wage @PWage int, @AWage int, @total int as while (1=1) begin if ((select Avg(Wage) from ProWage)<=@PWage) update ProWage set @total=@total+@AWage,Wage=Wage+@AWage else break end print'一共加薪：'+convert(varchar,@total)+'元' print'加薪后的程序员工资列表：' select * from ProWage --调用存储过程-- exec Avg_wage @PWage=3000,@AWage=200,@total=0 exec Avg_wage @PWage=4500,@AWage=200,@total=0

题目3：

学生成绩信息三个表，结构如下： 学生表：Member

字段名称 MID MName 课程表：

字段名称 FID FName 成绩表：Score

字段名称 SID FID MID Score int Char(10) Char(10) int 数据类型 说明 自动编号，主键，成绩记录号 课程号，外键 学生号，外键 成绩 数据类型 Char(10) Char(50) 课程，主键 课程名 说明 数据类型 Char(10) Char(50) 说明 学生号，主键 姓名 请编写T-SQL语句来实现如下功能：

1) 查询各个学生语文、数学、英语、历史课程成绩，例如下表：

姓名 张萨 王强 李三 李四 语文 78 89 70 80 数学 67 67 87 78 英语 89 84 92 97 历史 76 96 56 66 2) 查询四门课中成绩低于70分的学生及相对应课程名和成绩。 3) 统计各个学生参加考试课程的平均分，且按平均分数由高到底排序。

4) 创建存储过程，分别查询参加1、2、3、4门考试及没有参加考试的学生名单，要求显

示姓名、学号。 建表语句:

USE master GO /*$$$$$$$$$$$$$建库$$$$$$$$$$$$$$$$$$$$$$$$*/ --检验数据库是否存在，如果为真，删除此数据库-- IF exists(SELECT * FROM sysdatabases WHERE name='Student') DROP DATABASE Student GO CREATE DATABASE Student GO --建数据表-- USE Student GO CREATE TABLE Member --学生表 ( MID char(10) primary key, --学生号 MName CHAR(50) NOT NULL --姓名 ) GO CREATE TABLE F --课程表 ( FID char(10) primary key, --课程号 FName CHAR(50) NOT NULL --课程名 ) GO CREATE TABLE score --学生成绩表 ( SID int identity(1,1) primary key, --成绩记录号 FID char(10) foreign key(FID) references F(FID) , --课程号 MID char(10) foreign key(MID) references Member(MID) , --学生号 Score int NOT NULL --成绩 ) GO --课程表中插入数据-- INSERT INTO F(FID,FName)VALUES('F001','语文') INSERT INTO F(FID,FName)VALUES('F002','数学') INSERT INTO F(FID,FName)VALUES('F003','英语') INSERT INTO F(FID,FName)VALUES('F004','历史') --学生表中插入数据-- INSERT INTO Member(MID,MName)VALUES('M001','张萨') INSERT INTO Member(MID,MName)VALUES('M002','王强') INSERT INTO Member(MID,MName)VALUES('M003','李三') INSERT INTO Member(MID,MName)VALUES('M004','李四') INSERT INTO Member(MID,MName)VALUES('M005','阳阳') INSERT INTO Member(MID,MName)VALUES('M006','虎子') INSERT INTO Member(MID,MName)VALUES('M007','夏雪') INSERT INTO Member(MID,MName)VALUES('M008','璐璐') INSERT INTO Member(MID,MName)VALUES('M009','珊珊') INSERT INTO Member(MID,MName)VALUES('M010','香奈儿') --成绩表中插入数据-- INSERT INTO Score(FID,MID,Score)VALUES('F001','M001',78) INSERT INTO Score(FID,MID,Score)VALUES('F002','M001',67) INSERT INTO Score(FID,MID,Score)VALUES('F003','M001',89) INSERT INTO Score(FID,MID,Score)VALUES('F004','M001',76) INSERT INTO Score(FID,MID,Score)VALUES('F001','M002',89) INSERT INTO Score(FID,MID,Score)VALUES('F002','M002',67) INSERT INTO Score(FID,MID,Score)VALUES('F003','M002',84) INSERT INTO Score(FID,MID,Score)VALUES('F004','M002',96) INSERT INTO Score(FID,MID,Score)VALUES('F001','M003',70) INSERT INTO Score(FID,MID,Score)VALUES('F002','M003',87) INSERT INTO Score(FID,MID,Score)VALUES('F003','M003',92) INSERT INTO Score(FID,MID,Score)VALUES('F004','M003',56) INSERT INTO Score(FID,MID,Score)VALUES('F001','M004',80) INSERT INTO Score(FID,MID,Score)VALUES('F002','M004',78) INSERT INTO Score(FID,MID,Score)VALUES('F003','M004',97) INSERT INTO Score(FID,MID,Score)VALUES('F004','M004',66) INSERT INTO Score(FID,MID,Score)VALUES('F001','M006',88) INSERT INTO Score(FID,MID,Score)VALUES('F002','M006',55) INSERT INTO Score(FID,MID,Score)VALUES('F003','M006',86) INSERT INTO Score(FID,MID,Score)VALUES('F004','M006',79) INSERT INTO Score(FID,MID,Score)VALUES('F002','M007',77) INSERT INTO Score(FID,MID,Score)VALUES('F003','M008',65) INSERT INTO Score(FID,MID,Score)VALUES('F004','M007',48) INSERT INTO Score(FID,MID,Score)VALUES('F004','M009',75) INSERT INTO Score(FID,MID,Score)VALUES('F002','M009',88)

标准答案: -- 1)查询各个学生语文、数学、英语、历史课程成绩-- SELECT Member.MName AS 姓名, 英语 = SUM(CASE F.FName WHEN '语文' THEN Score.Score END), 数学 = SUM(CASE F.FName WHEN '数学' THEN Score.Score END), 语文 = SUM(CASE F.FName WHEN '英语' THEN Score.Score END), 历史 = SUM(CASE F.FName WHEN '历史' THEN Score.Score END) FROM Score, Member,F