祝锡永数据库第七章习题答案Word格式文档下载.docx

1、 select amt from tmp where categoryname=cnameexecute p1 Confections/*2*/drop procedure p2create procedure p2 pname varchar（80）aswith tmp as （ select a.productid,productname,RANK（） over （order by sum（amount） as rankid group by a.ProductID ,productname） select rankid from tmp where ProductName=pname e

2、xecute p2 Tofu/*3*/drop procedure p3create procedure p3 tablename varchar（40）,cname varchar（40） 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+）+CHA

3、R（13） set sql=sql+char（9）+print +* set sql=sql+elseselect data_type,character_maximum_length from information_schema.columns where table_name= execute（sql）execute p3 Products,ProductName/*4*/drop function f1create function f1（cname varchar（40）,year int）returns t1 table（cname varchar（40）,month int,nu

4、m int,amt money）begin select companyname,month（orderdate） as xmonth,count（*） as num,sum（amount） as 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

5、 t1（cname,month,num,amt） select companyname,xmonth,num,amt from tmp returnendselect * from .dbo.f1（Alfreds Futterkiste,2009）/*5*/drop function f2create function f2（date datetime）returns t1 table（orderid int,customerid varchar（10）,employeeid varchar（10）,orderdate datetime,requireddate datetime,invoiv

6、edate datetime,shippeddate datetime,shipperid int,freight decimal（12,2）begin insert into t1 select * from orders where invoicedatedateselect * from .dbo.f2（2008-12-30）/*6*/drop function f3create function f3（cid varchar（10）,pid int）returns intdeclare n intselect n=num from （select customerid,count（*）

7、 as from orderitems ajoin orders b on a.orderid =b.orderid where customerid =cid and productid =pid group by customerid） as preturn nselect customerid from customers where mySales.dbo.f3（CustomerID,12）select productid from Products where .dbo.f3（ANTON,productid）/*7*/drop function f4create function f

8、4 （date1 datetime,date2 datetime）returns t table （productid int,productname varchar（100）,quantity varchar（40）,unitprice money,supplierid int,categoryid int） select productid ,sum（amount） as where orderdate between date1 and date2 group by productid ） insert into t select productid,productname,quanti

9、typerunit,unitprice,supplierid,categoryid from products where productid in （select top 10 percent productid from tmp order by amt desc）select distinct customerid from orders where orderid in （select OrderID from OrderItems where ProductID in （select ProductID from .dbo.f4（2009-1-12009-6-30）/*8*/drop

10、 function f5create function f5（price money）returns varchar（20） declare s varchar（20） if（price =0.01 and price=10.01 and price=20）moderate=20.01 and price=30）semi-expensive=30.01 and price=50）expensive else very expensive return sdrop function f6create function f6（sid int）returns t table（productid in

11、t,pricerange varchar（30）insert into tselect productid,mysales.dbo.f5（unitprice） from products where supplierid=sidreturnselect * from .dbo.f6（2）/*9*/drop table mycustomers drop table myorderitems select * into mycustomers from Customers select * into myorderitems from OrderItems alter table mycustom

12、ersadd amount moneyupdate mycustomers set amount=（select SUM（amount） from OrderItems a join Orders b on a.OrderID =b.OrderID where b.CustomerID =mycustomers.customerid）drop trigger t1create trigger t1 on myorderitems for update as update mycustomers set amount=amount-（select sum（amount） from deleted a join orders b on a.orderid=b.orderidwhere b.customerid =mycustomers.id）update mycustomers set a