祝锡永数据库第七章习题答案
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
/*第七章习题*/
/*1*/
drop procedure p1
go
create procedure p1 @cname varchar(50)
as
;with tmp as(
select b.categoryid,categoryname,SUM(amount) as 'amt' from OrderItems a
join Products b on a.ProductID =b.ProductID
join Categories c on b.CategoryID =c.CategoryID
group by b.CategoryID ,CategoryName )
select amt from tmp where categoryname=@cname
go
execute p1 'Confections'
/*2*/
drop procedure p2
go
create procedure p2 @pname varchar(80)
as
;with tmp as (
select a.productid,productname,RANK() over (order by sum(amount)) as 'rankid' from OrderItems a
join Products b on a.ProductID =b.ProductID
group by a.ProductID ,productname)
select rankid from tmp where ProductName=@pname
go
execute p2 'Tofu'
/*3*/
drop procedure p3
go
create procedure p3 @tablename varchar(40),@cname varchar(40)
as
declare @sql varchar(2000)
set @sql='if not exists (select data_type,character_maximum_length from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='''
set @sql=@sql+@tablename+''' and COLUMN_NAME='''
set @sql=@sql+@cname+''')'+CHAR(13)
set @sql=@sql+char(9)+'print '''+'*'+''''+CHAR(13)
set @sql=@sql+'else'+CHAR(13)
set @sql=@sql+char(9)+'select data_type,character_maximum_length from
information_schema.columns where table_name='''
set @sql=@sql+@tablename+''' and COLUMN_NAME='''
set @sql=@sql+@cname+''''
execute(@sql)
go
execute p3 'Products','ProductName'
/*4*/
drop function f1
go
create function f1(@cname varchar(40),@year int)
returns @t1 table(cname varchar(40),month int,num int,amt money)
as
begin
;with tmp as(
select companyname,month(orderdate) as 'xmonth',count(*) as 'num',sum(amount) as 'amt' from orderitems a
join orders b on a.orderid =b.orderid
join customers c on b.customerid =c.customerid
where year(orderdate)=@year and companyname=@cname
group by companyname,month(orderdate))
insert into @t1(cname,month,num,amt)
select companyname,xmonth,num,amt from tmp
return
end
go
select * from .dbo.f1('Alfreds Futterkiste',2009)
/*5*/
drop function f2
go
create function f2(@date datetime)
returns @t1 table(orderid int,customerid varchar(10),employeeid varchar(10),orderdate datetime,requireddate datetime,invoivedate datetime,shippeddate datetime,shipperid int,freight decimal(12,2))
as
begin
insert into @t1
select * from orders where invoicedate<=@date and shippeddate >@date
return
end