SQL存储过程试题及答案.docx
《SQL存储过程试题及答案.docx》由会员分享，可在线阅读，更多相关《SQL存储过程试题及答案.docx（35页珍藏版）》请在冰豆网上搜索。
SQL存储过程试题及答案
--写存储过程及调用存储过程
/*1.写出创建分数存储过程用于计算某门课程成绩最高分、最低分、
平均分,参数课程号。
*/
--2.写出统计某门课选人数的存储过程，输入参数课程号，输出参数人数。
/*3.创建存储过程,要求根据学生姓名查看学生的籍贯.
（要求:
在存储过程里定义两个参数,
第一个接收由调用程序指定的输入值（学生姓名）,
第二个参数用于将该值返回调用程序）*/
/*4.程序员工资表：
ProWage
字段名称
数据类型
说明
ID
int
自动编号，主键
PName
Char（10）
程序员姓名
Wage
int
工资
创建一个存储过程，对程序员的工资进行分析，月薪1500到10000不等，如果有百分之五十的人薪水不到2000元，给所有人加薪，每次加100，再进行分析，直到有一半以上的人大于2000元为止，存储过程执行完后，最终加了多少钱？
例如：
如果有百分之五十的人薪水不到2000，给所有人加薪，每次加100元，直到有一半以上的人工资大于2000元，调用存储过程后的结果如图：
请编写T-SQL来实现如下功能：
查询是否有一半程序员的工资在2200、3000、3500、4000、5000或6000元之上，如果不到分别每次给每个程序员加薪100元，至之一半程序员的工资达到2200，3000，3500，4000，5000或6000元。
/*5.编写一个存储过程PR_GET_PASS_RATE统计某门课程的及格率，其传入参数是课程号P_CNO，传出参数是该课程成绩的及格率P_PASSRATE,及格率的格式形如：
86.56%。
6．创建触发器T_1，功能是当向数据表学生添加记录时，显示学生的信息。
createtriggerT_1on学生
afterinsert
asselect*from学生
insertinto学生values（'3001','李四','男','计本10'）
7．创建触发器T_2，功能是当向数据表班级添加、修改和删除记录时，显示学生的信息。
createtriggerT_2on学生
afterinsert,update,delete
asselect*from学生
8．创建触发器T_3，功能是当修改班级班号，同步更新学生表的班号。
9．创建触发器T_4，功能是当删除学生表的记录时，同步删除选课表中的选课信息。
createtriggerT_4on学生
afterdelete
as
declare@xhchar（10）
select@xh=学号fromdeleted
deletefrom选课where学号=@xh
deletefrom学生where学号='3002'
/*1.写出创建分数存储过程用于计算某门课程成绩最高分、最低分、
平均分,参数课程号。
*/
usexsgl
go
createprocedureaa（@课程名nchar（16））
as
begin
select课程号,最高分=max（成绩）,最低分=min（成绩）,平均分=avg（成绩）from成绩
where课程号=@课程名
groupby课程号
end
go
executeaa'001'
--2.写出统计某门课选人数的存储过程，输入参数课程号，输出参数人数。
usexsgl
go
createprocedurebb@课程号char（10）,@人数intoutput
as
begin
select@人数=（selectcount（课程号）from成绩
where课程号=@课程号）
end
declare@人数int,@课程号char（4）
set@课程号='002'
execbb@课程号,@人数output
print'课程号为'+@课程号+'的人数：
'+cast（@人数aschar
（2））
/*3.创建存储过程,要求根据学生姓名查看学生的籍贯.
（要求:
在存储过程里定义两个参数,
第一个接收由调用程序指定的输入值（学生姓名）,
第二个参数用于将该值返回调用程序）*/
usexsgl
go
createprocedurecc（@姓名char（10）,@籍贯char（10）output）
as
begin
select@籍贯=（select籍贯from学生
where姓名=@姓名）
end
declare@姓名char（10）,@籍贯char（10）
set@姓名='廖小小'
execcc@姓名,@籍贯output
print'学生'+@姓名+'的籍贯是：
'+@籍贯
USEWage
GO
CREATETABLEProWage--程序员工资表
（
IDintidentity（1,1）primarykey,--工资编号
PNameCHAR（10）NOTNULL,--程序员姓名
WageintNOTNULL--工资
）
GO
--1、创建存储过程--
ifexists（select*fromsysobjectswherename='Sum_wage'）
dropprocedureSum_wage
GO
createprocedureSum_wage
@PWageint,
@AWageint,
@totalint
as
while（1=1）
begin
if（selectcount（*）fromProWage）>2*（selectcount（*）fromProWagewhereWage>=@PWage）
updateProWageset@total=@total+@AWage,Wage=Wage+@AWage
else
break
end
print'一共加薪：
'+convert（varchar,@total）+'元'
print'加薪后的程序员工资列表：
'
select*fromProWage
--调用存储过程1--
execSum_wage@PWage=2000,@AWage=100,@total=0
execSum_wage@PWage=2200,@AWage=100,@total=0
execSum_wage@PWage=3000,@AWage=100,@total=0
execSum_wage@PWage=4000,@AWage=100,@total=0
execSum_wage@PWage=5000,@AWage=100,@total=0
execSum_wage@PWage=6000,@AWage=100,@total=0
5：
编写一个存储过程PR_GET_PASS_RATE统计某门课程的及格率，其传入参数是课程号P_CNO，传出参数是该课程成绩的及格率P_PASSRATE,及格率的格式形如：
86.56%。
createorreplaceprocedurePR_passrate_count
（p_cnochar,P_PASSRATEoutchar）
as
v_passcntint;
v_totalcntint;
begin
--先求及格人数
selectcount（*）intov_passcntfromsc
wherecno=p_cnoandgrade>=60;
--若及格人数为0则给出结果
ifv_passcnt=0then
P_PASSRATE:
='0%';
else
selectcount（*）intov_totalcntfromsc
wherecno=p_cno;
P_PASSRATE:
=to_char（round（100*v_passcnt/v_totalcnt,2））||'%';
endif;
end;
ＳＱＬ实验
实验4
1.用select语句查询departments和salary表中的所有数据：
selectsalary.*,departments.*
fromsalary,departments
2、查询departments中的departmentid：
selectdepartmentidfromdepartments
go
3、查询salary中的income，outcome：
selectincome,outcomefromsalary
go
4、查询employees表中的部门号，性别，要用distinct消除重复行：
selectdistinct（departmentid）,sex
fromemployees
5、查询月收入高于2000元的员工号码：
selectemployeeidfromsalary
whereincome>2000
go
6、查询1970年以后出生的员工的姓名和住址：
selectname,address
fromemployees
wherebirthday>1970
go
7、查询所有财务部的员工的号码和姓名：
selectemployeeid,name
fromemployees
wheredepartmentidin（selectdepartmentidfromdepartmentswheredepartmentname='财务部'）
go
8、查询employees员工的姓名，住址和收入水平，2000元以下显示为低收入，2000~3000元显示为中等收入，3000元以上显示为高收入：
selectname,address,
case
whenincome-outcome<2000then'低收入'
whenincome-outcome>3000then'高收入'
else'中等收入'
endas'收入等级'
fromemployees,salary
whereemployees.employeeid=salary.employeeid
go
9、计算salary表中员工月收入的评价数：
selectavg（income）as'平均收入'fromsalary
10、查找employees表中最大的员工号码：
selectmax（employeeid）as'最大员工号码'fromemployees
11、计算salary表中的所有员工的总支出：
selectsum（outcome）as'总支出'fromsalary
12、查询财务部雇员的最高实际收入：
selectmax（income-outcome）fromsalary,employees,departments
wheresalary.employeeid=employees.employeeidandemployees.departmentid=departments.departmentidanddepartmentname='财务部'
go
13、查询财务部雇员的最低实际收入：
selectmin（income-outcome）fromsalary,employees,departments
wheresalary.employeeid=employees.employeeidandemployees.departmentid=departments.departmentidanddepartmentname='财务部'
go
14、找出所用地址中含有“中山”的雇员的号码及部门号：
selectemployeeid,departmentid
fromemployees
whereaddresslike'%中山%'
go
15、查找员工号码中倒数第二个数字为0的员工的姓名，地址和学历：
selecteducation,address,name
fromemployees
whereemployeeidlike'%0_'
go
16、使用into字句，由表employees创建“男员工1”表，包括编号和姓名：
selectemployeeid,name
into男员工表
fromemployees
wheresex='1'
go
17、用子查询的方法查找收入在2500元以下的雇员的情况：
select*fromemployees
whereemployeeidin
（selectemployeeidfromsalarywhereincome<2500）
go
18、用子查询的方法查找查找研发部比所有财务部雇员收入都高的雇员的姓名：
SELECTNameFROMEmployeesWHEREEmployeeIDIN
（
SELECTEmployeeIDFROMSalary
WHEREEmployeeIDIN
（
SELECTEmployeeIdFROMEmployees
WHEREDepartmentIDIN
（
SELECTDepartmentIDFROMDepartments
WHEREDepartmentName='研发部'
）
）
ANDInCome>ALL
（
SELECTInComeFROMSalary
WHEREEmployeeIDIN
（
SELECTEmployeeIdFROMEmployees
WHEREDepartmentIDIN
（
SELECTDepartmentIDFROMDepartments
WHEREDepartmentName='财务部'
）
）
）
）
19、用子查询的方法查找所有年龄比研发部雇员都大的雇员的姓名：
selectname
fromemployees
whereBirthday（selectbirthday
fromemployees
wheredepartmentidin
（selectdepartmentid
fromdepartments
wheredepartmentname='研发部'
）
）
20、查询每个员工的情况及其薪水的情况：
selectemployees.*,departments.departmentname
fromemployees,departments
whereemployees.departmentid=departments.departmentid
21、使用内连接方法查找不在财务部工作的所有员工信息：
selectemployees.*
fromemployeesinnerjoindepartmentsonemployees.departmentid=departments.departmentid
wheredepartmentname!
='财务部'
22、使用外连接方法查找出所有员工的月收入：
selectemployees.*,salary.income
fromemployeesjoinsalaryonemployees.employeeid=salary.employeeid
23、查找财务部雇员的最高收入：
selectmax（income）
fromsalary
whereemployeeidin
（selectemployeeid
fromemployees
wheredepartmentidin
（selectdepartmentid
fromdepartments
wheredepartmentname='财务部'
）
）
24、查询财务部雇员的最高实际收入：
selectmax（income-outcome）
fromsalary
whereemployeeidin
（selectemployeeid
fromemployees
wheredepartmentidin
（selectdepartmentid
fromdepartments
wheredepartmentname='财务部'
）
）
25、统计财务部收入在2500元以上的雇员人数：
selectcount（employeeid）
fromemployees
wheredepartmentidin
（selectdepartmentidfromdepartments
wheredepartmentname='财务部'）
andemployeeidin
（
selectemployeeid
fromsalary
whereincome>2500）
26、按部门列出在该部门工作的员工的人数：
selectdepartmentid,count（*）as人数
fromemployees
groupbydepartmentid
27、按员工的学历分组：
selecteducation,count（*）as人数
fromemployees
groupbyeducation
28、按员工的工作年份分组，统计年份人数：
selectworkyear,count（*）as人数
fromemployees
groupbyworkyear
29、按各雇员的情况收入由低到高排列：
selectemployees.*,salary.income
fromemployees,salary
whereemployees.employeeid=salary.employeeid
orderbyincome
30、将员工信息按出生时间从小到大排列：
select*
fromemployees
orderbybirthday
31、在orderby字句中使用子查询，查询员工姓名，性别和工龄信息，要求按实际收入从大到小排列：
selectname,sex,workyear,income-outcome
fromsalary,employees
wheresalary.employeeid=employees.employeeid
orderbyincome-outcomedesc
视图部分
1、创建view1：
Createviewview1
as
selectemployees.employeeid,name,departmentname,（income-outcome）ascome
fromemployees,departments,salary
whereemployees.departmentid=departments.departmentidandemployees.employeeid=salary.employeeid
2、查询视图employeeid：
3、向视图view1中插入一行数据：
insertintoview1values（'111111','谎言','1','30000'）
4、查看视图（没有影响）基本表：
实验5
1、定义一个变量，用于描述YGGL数据库的salary表中000001号员工的实际收入，然后查询该变量：
declare@hyint
set@hy=（selectincome-outcome
fromsalary
whereemployeeid='000001'）
select@hy
2、使用运算符“>”：
selectname
fromemployees
wherebirthday>'1974-10-10'
3、判断姓名为“王林”的员工实际收入是否高于3000元，如果是则显示“高收入”，否则显示“收入不高于3000”：
if（（selectincome
fromsalary,employees
wheresalary.employeeid=employees.employeeidandemployees.name='刘明'）>3000）
selectincomeas'高收入'
fromsalary,employees
wheresalary.employeeid=employees.employeeidandemployees.name='刘明'
else
select'收入不高于'
4、使用循环输出一个“*”三角形：
declare@iint
declare@jint
set@j=20
set@i=1
while@i<@j
begin
print（space（（@j-@i）/2）+replicate（'*',@i））
set@i=@i+2
end
4、按部门进行分类，使用if语句实现：
Createfunctionhy1（@departmentid1char（3））
returnschar（10）as
begin
declare@hy1char（10）
if（（selectdepartmentidfromdepartmentswhere@departmentid1=departmentid）='1'）
set@hy1='财务部'
if（（selectdepartmentidfromdepartmentswhere@departmentid1=departmentid）='2'）
set@hy1='人力资源部'
if（（selectdepartmentidfromdepartmentswhere@departmentid1=departmentid）='3'）
set@hy1='经理办公室'
if（（selectdepartmentidfromdepartmentswhere@departmentid1=departmentid）='4'）
set@hy1='研发部'
if（（selectdepartmentidfromdepartmentswhere@departmentid1=departmentid）='5'）
set@hy1='市场部'
return@hy1
end
selectemployeeid,name,address,dbo.hy1（departmentid）fromemployees
selectemployeeid,na
升级会员 