T-SQL编程复习
T-SQL编程复习
注：以书上的journey为数据库
--1（必考—if…else语句的应用）、统计并显示“国内短线游”的价格的平均值，如果平均值在5000元以上，显示“价格较高”，并显示前三条路线的名称和价格；如果在5000元以下，显示“价格较低”，并显示后三条路线的名称和价格。
go
declare@typeName varchar(20)='国内短线游'
declare@avgPrice money
select@avgPrice=AVG(price)from Line where lineTypeID=(select lineTypeID from LineType where typeName=@typeName)
if(@avgPrice>5000)
begin
print'价格较高'
select top 3 lineName线路名称,price价格from Line where lineTypeID=(select lineTypeID from LineType where typeName=@typeName)
order by price desc
end
else
begin
print'价格较低'
select top 3 lineName线路名称,price价格from Line where lineTypeID=(select lineTypeID from LineType where typeName=@typeName)
order by price asc
end
--2（必考—while语句的应用）查询出‘国内短线游’的最低价格，如该价格低于800元，则将所有“国内短线游”的价格上调10%再次查询出加价之后的“国内短线游”的最低价格，如该价格仍低于800元，则将所有"国内短线游"的价格再次上调10%,依次反复，直到所有"国内短线游"的最低价格高于800元，最后打印输出“经过调价后‘国内短线游’的最低价格为：xxx元”
select*from Line
select*from Line where lineTypeID=1
go
declare@typeName varchar='国内短线游'
declare@lineTypeID int
declare@minPrice money
select@lineTypeID=lineTypeID from LineType where typeName=@typeName
select@minPrice=min(price)from Line where lineTypeID=@lineTypeID
while (@minPrice<800)
begin
update Line set price=price*1.1 where lineTypeID=@lineTypeID
select@minPrice=min(price)from Line where lineTypeID=@lineTypeID
end
print'经过调价后‘国内短线游’的最低价格为：'+convert(varchar(20),@minPrice)
--3（必考—case语句的应用）、根据所有“国内长线游”的价格和以下等级划分规则，编写T-SQL语句显示“国内长线游”的线路名、价格及价格等级（使用case语句来实现）高价格：3000元以上、一般价格：3000-2000元之间、低价格：2000元以下。效果图如下图所示（界面会根据数据表中的数据有所不同）：
go
declare@typeName varchar(20)='国内长线游'
select lineName线路名,price价格,价格等级=
case
when price>=3000 then'高价格'
when price>=2000 and price<3000 then'一般价格'
when price<2000 then'低价格'
end
from Line where lineTypeID=(
select lineTypeID from LineType where typeName=@typeName)
--4（必考—视图的应用）创建一个视图，名为v_newLine，要显示出线路编号、线路名称、价格，并且记录中不包括价格在5000元以下的线路。(要求判断是否存在此视图)
IF EXISTS(SELECT 1 FROM sys.views WHERE name='v_newLine')DROP VIEW v_newLine
go
create view v_newLine as
(select l.lineTypeID线路编号,typeName线路名称,price价格
from Line l,LineType lt where l.lineTypeID=lt.lineTypeID and price>=5000)
go
--5(必考—事务的应用)--将journey数据库Customer表中的“郝琼琼”与"雷亚波"的性别互换，用事务方法实现
go
declare@errorCount int=0
declare@name1varchar(20)='郝琼琼'
declare@name2varchar(20)='雷亚波'
declare@gender1varchar(2)--“郝琼琼”的性别
declare@gender2varchar(2)--"雷亚波"的性别
select@gender1=gender from Customer where CustomerName=@name1
select@gender2=gender from Customer where CustomerName=@name2
begin tran
update Customer set gender=@gender2where customerName=@name1
set@errorCount+=@@ERROR
update Customer set gender=@gender1where customerName=@name2
set@errorCount+=@@ERROR
if(@errorCount=0)
commit tran
else
rollback tran
--6、（创建存储过程）创建一个存储过程，要求输入某一线路的线路名(如“凤凰古城”)，显示出该线路名的团购价，并输出该线路名与同类线路类型("国内短线游")平均价之间的差值，如果高于平均价，则显示‘高于平均价’如果低于平均价，则显示‘低于平均价’（注：要先判断你所创建的存储过程是否存在）。
go
if exists(select*from sysobjects where name='proc_disparityPriceAndAvg')
drop procedure proc_disparityPriceAndAvg
go
create procedure proc_disparityPriceAndAvg(@lineName varchar(20),@price money
output,@avgPrice money output,@disparityPriceAndAvg money output)
as
set@price=(select price from Line where lineName=@lineName)
set@avgPrice=(select avg(price)from Line where lineTypeID=(select lineTypeID from Line where lineName=@lineName))
set@disparityPriceAndAvg=@price-@avgPrice
if@disparityPriceAndAvg>0 print'高于平均价'else print'低于平均价'
go
declare@lineName varchar(20)='桂林'
declare@price money
declare@avgPrice money
declare@disparityPriceAndAvg money
exec proc_disparityPriceAndAvg@lineName,@price output,@avgPrice output,
@disparityPriceAndAvg output
print@lineName+'的价格是:'+convert(varchar(20),@price)+',同类型线路的平均价是:'
+convert(varchar(20),@avgPrice)+',其之间的差值
是:'+convert(varchar(20),@disparityPriceAndAvg)