数据库习题答案及源码

习题答案及源码
习题1答案
1、代码：
CREATE TABLE Recipient
(
cOrderNo char(6) not null,
vFirstName varchar(20)not null,
vLastName varchar(20) not null,
vAddress varchar(20) null,
cCity char(15) null,
cState char(15) null,
cCountryId char(3) null,
cZipCode char(10)null,
cPhone char(15) null
)
2、先查看数据类型：
sp_addtype typCountryID,"char(3)"
重新创建表Country：
CREATE TABLE Country
(
cCountryId typCountryID,
cCountry char(25) ,
)
3、代码：
DROP TABLE Recipient
习题2答案
1、代码：
CREATE TABLE Category
(
cCategoryId char(3) CONSTRAINT pkCategoryId PRIMARY KEY,
cCategory char(20) CONSTRAINT unqCategory UNIQUE,
vDescription varchar(100) NULL
)
2、代码：
CREATE TABLE ToyBrand
(
cBrandId char(3) CONSTRAINT pkBrandId PRIMARY KEY,
cBrandName char(20) CONSTRAINT unqBrandName UNIQUE
)
3、代码：
CREATE TABLE Toys
(
cToyId char(6) CONSTRAINT pkToyid PRIMARY KEY CLUSTERED,
vToyName varchar(20) NOT NULL,
vToyDescription varchar(250)NOT NULL,
cCategoryId char(3) REFERENCES Category(cCategoryId) ,
mToyRate money NOT NULL,
cBrandId char(3),
imPhoto image null,
siToyQoh smallint CONSTRAINT chkToyQoh CHECK (siToyQoh>0),
siLowerAge smallint DEFAULT 1,
siUpperAge smallint ,
siToyWeight smallint,
vToyImgPath varchar(50) NULL
)
4、代码：
ALTER TABLE Toys
ADD CONSTRAINT fkBrandId FOREIGN KEY(cBrandId) REFERENCES ToyBrand(cBrandId)
ALTER TABLE Toys
ADD CONSTRAINT defUpperAge DEFAULT 1 FOR siUpperAge
5、1）代码：
CREATE RULE rulRate
AS
@rate>0
绑定规则：
sp_bindrule rulRate,'Toys.mToyRate'
2）代码：
CREATE DEFAULT rulWeight
AS
1
绑定规则：
sp_bindefault rulWeight,'Toys.siToyWeight'
习题3答案
1、SELECT * FROM Toys
2、SELECT * FROM Shopper
3、SELECT * FROM Recipient
4、SELECT vFirstName,vLastName,vEmailId FROM Shopper
5、SELECT cOrderNo,cShippingModeId,mGiftWrapCharges,mTotalCost
FROM Orders
6、SELECT vToyName,mToyRate
FROM Toys
7、SELECT vToyName,siLowerAge,siUpperAge
FROM Toys
8、SELECT "Toy"=vToyName,
"Quantity on Hand"=siToyQoh,
"Weight"=siToyWeight
FROM Toys
9、SELECT "Recipient First Name"=vFirstName,
"Recipient Last Name"=vLastName,
"Address",vAddress,
"Zip"=cZipCode
FROM Recipient
10、SELECT vFirstName,vLastName
FROM Shopper
WHERE cState='California'
11、SELECT * FROM Orders
WHERE mTotalCost>75
12、SELECT * FROM Toys
WHERE mToyRage<20
13、SELECT * FROM Toys
WHERE vToyName='Tin Drum'
14、SELECT * FROM Toys
WHERE cBrandId='004'
15、SELECT * FROM Shopper
WHERE vCreditCardType='Master Card'
16、SELECT * FROM Toys
WHERE cCategoryId='002'
17、SELECT * FROM ORDERS
WHERE dOrderDate='05/22/99'
18、SELECT vFirstName,vEmailId
FROM Shopper
WHERE cShopperId='000035'
19、SELECT "Order Number"=cOrderNo,"Shipping Charges"=mShippingCharges,
"Gift Wrap Charges"= mGiftWrapCharges,
"Handling Charges"=mShippingCharges+mGiftWrapCharges FROM Orders
20、SELECT * FROM Toys
WHERE mtoyrate >10 and mtoyrate <20
21、SELECT vFirstName,vLastName,vEmailId
FROM Shopper
WHERE cState='California' or cState='Illinios'
22、SELECT "Order Number"=cOrderNo,
"Order Date"=dOrderDate,
"Shopper Id"=cShopperId,
"Total Cost"=mTotalCost
FROM Orders
WHERE dOrderDate="1999/05/20" and mTotalCost>75
23、SELECT * FROM Toys
WHERE ccategoryid='002' and mtoyrate<20
24、SELECT * FROM OrderDetail
WHERE vmessage is NULL
25、SELECT * FROM Shopper
WHERE not cState = 'Texas'
26、SELECT vToyName,mToyRate
FROM Toys
ORDER BY mToyRate DESC
27、SELECT cOrderNo,cShopperID,mTotalCost
FROM Orders
ORDER BY mTotalCost ASC
28、SELECT AVG(mToyRate)
FROM Toys
29、SELECT COUNT(*)
FROM Toys
30、SELECT MAX(mToyRate),MIN(mToyRate),AVG(mToyRate)
FROM Toys
31、SELECT SUM(mGiftWrapCharges)
FROM Orders
32、SELECT "Order Number"=cOrderNo,
"Total Cost of Toy for an Order"=sum(mToyCost) FROM orderdetail
GROUP BY cOrderNo
33、SELECT "Order Number"=cOrderNo,
"Total Cost of Toy for an Order"=sum(mToyCost) FROM orderdetail
GROUP BY cOrderNo
HAVING SUM(mToyCost)>50
34、SELECT TOP 5 cToyId, iTotalSold
FROM PickOfMonth
WHERE iYear=1998
ORDER BY iTotalSold DESC
35、SELECT cOrderNo,cToyId,mToyCost
FROM OrderDetail
ORDER BY cOrderNo
COMPUTE SUM(mToyCost) BY cOrderNo
COMPUTE SUM(mToyCost)
36、SELECT vToyName,"Description"=SUBSTRING(vToyDescription,1,40),
mToyRate
FROM Toys
37、用DATEDIFF()求运送天数
38、同37
39、用DATEPART()求Day of Order，用DATENAME()求Weekday。

40、SELECT * FROM toys
WHERE vToyName like '%Racer%'
41、SELECT * FROM Shopper
WHERE vFirstName like "S%"
42、SELECT distinct cState
FROM Recipient
43、SELECT vToyName,cCategory
FROM Toys JOIN Category
ON ategoryId=ategoryID
44、SELECT "Order Number"=cOrderNo,
"Toy Id"=cToyId,
"Wrapper Description"=vDescription
FROM OrderDetail JOIN Wrapper
ON OrderDetail.cWrapperId=Wrapper.cWrapperId
45、SELECT vToyName,cBrandName,cCategory
FROM Toys JOIN Category
ON ategoryId=ategoryId
JOIN ToyBrand
ON Toys.cBrandId=ToyBrand.cBrandId
46、SELECT "Shopper"=Shopper.vFirstName,
"Shopper Address"=Shopper.vAddress,
"Recipient"=Recipient.vFirstName,
"Recipient Address"=Recipient.vAddress
FROM Orders Join Shopper
ON Orders.cShopperId=Shopper.cShopperId
JOIN Recipient
ON Orders.cOrderNo=Recipient.cOrderNo
47、SELECT vToyName,cCartId
FROM Toys LEFT OUTER JOIN ShoppingCart
ON Toys.cToyId=ShoppingCart.cToyId
48、SELECT * INTO PremiumToys
FROM Toys
WHERE mToyRate>$20
49、SELECT vFirstName,vLastName,vAddress,cCity
FROM Shopper
UNION
SELECT vFirstName,vLastName,vAddress,cCity
FROM Recipient
50、SELECT vToyName
FROM Toys
WHERE mToyRate= (SELECT MAX(mToyRate) FROM Toys)
习题4答案
1、代码：
INSERT INTO ToyBrand VALUES('001','Bobby ')
INSERT INTO ToyBrand VALUES ('002','Frances-Price')
INSERT INTO ToyBrand VALUES ('003','The Bernie Kids')
INSERT INTO ToyBrand VALUES ('004','Largo')
2、代码：
INSERT INTO Category
VALUES ('001','Activity','Activity toys encourage the childs social skills and interest in the world around them.')
INSERT INTO Category
VALUES ('002','Dolls','A wide range of dolls from all the leading brands.') INSERT INTO Category
VALUES('003','Arts And Crafts','Encourage children to create masterpieces with these incredible craft kits.')
3、代码：
INSERT INTO Toys
VALUES ("000001","Robby the Whale ","A giant Blue Whale with two heavy-duty handles that allow a child to ride on its back. ","001", 8.99 ,"001",NULL,50,3,9,1,null)
4、代码：
UPDATE Toys
SET mToyRate=mToyRate+1
WHERE cToyId='000001'
5、代码：
DELETE ToyBrand
WHERE cBrandName='Largo'
6、代码：
SELECT * INTO PreferredCategory
FROM Category
WHERE cCategory='Activity'
7、代码：
INSERT INTO PreferredCategory
SELECT * FROM Category
WHERE cCategory='Dolls'
习题5答案
1、代码：
SET showplan_all OFF
SELECT vFirstName,mTotalCost
FROM Shopper JOIN Orders
On Shopper.cShopperId=Orders.cShopperId
CREATE CLUSTERED INDEX idxShopper
ON Orders(cShopperId)
CREATE NONCLUSTERED INDEX idxOrder
ON Orders(cShopperId)
2、代码：
CREATE UNIQUE CLUSTERED INDEX idxToy
ON Toys(cToyId)
3、代码：
CREATE UNIQUE NONCLUSTERED INDEX idxCategory
ON Category(cCategory)
4、代码：
CREATE VIEW vwShopperToy
AS
SELECT Shopper.vFirstName, vToyName,siQty,mToyCost
FROM Shopper JOIN Orders
ON Shopper.cShopperId= Orders.cShopperId
JOIN OrderDetail
ON Orders.cOrderNo=OrderDetail.cOrderNo
JOIN Toys
ON OrderDetail.cToyId=Toys.cToyId
SELECT * FROM vwShopperToy
1）代码：
SELECT Shopper.vFirstName, vToyName
FROM vwShopperToy
2）代码：
SELECT Shopper.vFirstName, vToyName,siQty
FROM vwShopperToy
3）代码：
SELECT Shopper.vFirstName, vToyName,mToyCost
FROM vwShopperToy
5、代码：
UPDATE vwOrderWrapper
SET siQty=2
FROM vwOrderWrapper
WHERE cOrderNo='000001'
UPDATE vwOrderWrapper
SET mWrapperRate=mWrapperRate+1
FROM vwOrderWrapper
WHERE cOrderNo='000001'
习题6答案
1、代码：
DECLARE @deliverystatus char(1)
SELECT @deliverystatus=cDeliveryStatus
FROM Shipment
WHERE cOrderNo='000003'
IF @deliverystatus='d'
PRINT 'The order has been delivered'
ELSE
PRINT 'The order has been shipped but not delivered' 2、代码：
WHILE(SELECT AVG(mToyRate+0.5) from toys)<22.5
BEGIN
UPDATE toys
SET mToyRate=mToyRate+.5
END
SELECT AVG(mToyRate) FROM Toys
3、代码：
WHILE(SELECT AVG(mToyRate+0.5) from toys)<24.5
BEGIN
UPDATE toys
SET mToyRate=mToyRate+.5
IF (SELECT MAX(mToyRate+0.5) FROM toys)>=53
BREAK
ELSE
CONTINUE
END
SELECT MAX(mToyRate) FROM Toys
习题7答案
1、代码：
CREATE PROCEDURE prcDisplayToys
AS
SELECT vToyName,vToyDescription,mToyRate
FROM Toys
EXEC prcDisplayToys
2代码：
CREATE PROCEDURE prcDisplayShopper
AS
SELECT vFirstName, vLastName, vEmailId
FROM Shopper
EXEC prcDisplayShopper
3、代码：
CREATE PROCEDURE prcDisplayPrice @ToyId char(6)
AS
SELECT vToyName,mToyRate
FROM Toys
WHERE cToyId=@ToyId
SELECT * FROM Toys
EXEC prcDisplayPrice '000001'
4、代码：
CREATE PROCEDURE prcAddBrand @BrandId char(3),
@BrandName char(20)
AS
INSERT ToyBrand
VALUES(@BrandID,@BrandName)
EXEC prcAddBrand '009','Fun World'
5、代码：
CREATE PROCEDURE prcAddCategory @CategoryId char(3),
@Category char(20),
@Description varchar(100)
AS
INSERT Category
VALUES (@CategoryId,@Category,@Description)
EXEC prcAddCategory '018','Electronic Games','These games contain a screen with which children interact.'
6、代码：
DROP PROCEDURE prcAddCategory
7、代码：
CREATE PROCEDURE prcCharges @OrderNo char(6),
@ShippingCharges money OUTPUT,
@GiftWrapCharges money OUTPUT
AS
SELECT @ShippingCharges=mShippingCharges,
@GiftWrapCharges=mGiftWrapCharges
FROM Orders
WHERE cOrderNo=@OrderNo
DECLARE @Shipping money
DECLARE @Gift money
EXEC prcCharges '000003',@Shipping OUTPUT,@Gift OUTPUT
SELECT @Shipping,@Gift
8、代码：
CREATE PROCEDURE prcHandlingCharges @OrderNo char(6)
AS
DECLARE @HandlingCharges money
DECLARE @Shipping money
DECLARE @Gift money
EXEC prcCharges @OrderNo,@Shipping OUTPUT,@Gift OUTPUT
SELECT @HandlingCharges=@Shipping+@Gift
SELECT @HandlingCharges
RETURN
EXEC prcHandlingCharges '000001'
习题8答案
1、代码：
CREATE PROCEDURE prcOrder(@CartId char(6),@ShopperId char(6))
AS
DECLARE @Order char(6)
EXEC prcGenOrder @Order OUTPUT
SELECT @Order
INSERT Orders
VALUES(@Order,getdate(),@CartId,@ShopperId,null,null,null,null,null ,null)
INSERT INTO OrderDetail(cOrderNo,cToyId,siQty)
SELECT @Order,cToyId,siQty FROM ShoppingCart WHERE cCartId = @CartId
UPDATE OrderDetail
SET mToyCost=mToyRate*siQty
FROM OrderDetail JOIN Toys
ON OrderDetail.cToyId=Toys.cToyId
WHERE cOrderNo=@Order
RETURN
EXEC prcOrder '000001','000001'
2、代码：
1）
CREATE PROCEDURE prcOrder(@CartId char(6),@ShopperId char(6))
AS
DECLARE @Order char(6)
EXEC prcGenOrder @Order OUTPUT
SELECT @Order
2）
INSERT Orders
VALUES(@Order,getdate(),@CartId,@ShopperId,NULL,NULL,NULL,NULL,NULL,NUL L)
3)
INSERT INTO OrderDetail(cOrderNo,cToyId,siQty)
SELECT @Order,cToyId,siQty FROM ShoppingCart WHERE cCartId = @CartId
4)
UPDATE OrderDetail
SET mToyCost=mToyRate*siQty
FROM OrderDetail JOIN Toys
ON OrderDetail.cToyId=Toys.cToyId
WHERE cOrderNo=@Order
RETURN
3、代码：
1)
CREATE PROCEDURE prcGiftWrap
@OrderNo char(6),
@ToyId char(6),
@WrapperId char(),
vMessage varchar()
AS
UPDATE OrderDetail
SET O
sp_help shoppingcart
SELECT * FROM Orders
SELECT * FROM ShoppingCart
SELECT * FROM OrderDetail
SELECT * FROM Orders
SELECT * FROM Shopper
SELECT * FROM Toys
UPDATE Orders
SET mGiftWrapCharges=0
WHERE cOrderNo='000010'
SELECT * FROM Orders
SELECT * FROM Wrapper
SELECT * FROM orderdetail
UPDATE OrderDetail
SET cGiftWrap='Y', cWrapperId='001'
WHERE cOrderNo='000010' and cToyId='000020'
2)
UPDATE Orders
SET mGiftWrapCharges=mGiftWrapCharges+mWrapperRate*siQty
FROM Orders JOIN OrderDetail
ON Orders.cOrderNo=OrderDetail.cOrderNo
JOIN Wrapper
ON OrderDetail.cWrapperId=Wrapper.cWrapperId
AND Orders.cOrderNo='000010' and cToyId='000020'
EXEC prcOrderDetail '000010','000020','005'
SELECT * FROM Orders
4、代码：
CREATE PROCEDURE prcRecipient
@OrderNo char(6),
@FirstName varchar(20),
@LastName varchar(20),
@City varchar(20,
@State char(15),
@CountryId char(3),
@ZipCode char(10),
@Phone char(10)
AS
INSERT Recipient
VALUES(@OrderNo,@FirstName,@LastName,@City,@State,@CountryId,@ZipCode,@ Phone)
RETURN
sp_help Recipient
SELECT * FROM ShippingRate
DECLARE @rate money
DECLARE @Weight int
DECLARE @Qty int
DECLARE @ToyId char(6)
SELECT @Rate=mRatePerPound
FROM ShippingRate
WHERE cCountryId='001' and cModeId='01'
SELECT @Rate
SELECT @Weight=siToyWeight
FROM Toys
WHERE cToyId='000020'
SELECT @Weight
SELECT @Qty=siQty
FROM OrderDetail
WHERE cOrderNo='000010' and cToyId='000020'
SELECT @Qty
UPDATE Orders
SET mShippingCharges=mShippingCharges+@Rate*@Qty*@Weight
WHERE cOrderNo='000010'。