python数据分析与应用实训题答案第七章_祝锡永数据库第七章习题答案

/*

第七章习题

*/

/*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