1.1_创建practicedb1数据库中的表.sql

这是我在学习《SQL Server数据库系统结构基础》时所用到的三个脚本文件

insert into shopper values('000032','kate','Michelle','Hernandez','[e-mail]','1353 Realm Lakes ','Naperville','Illinois','001','60563','294-5385','6965753564534554','Visa Card','01/02/2000')
insert into shopper values('000033','pirate','Nancy','King','[e-mail]','429 Ash Birch Lane  ','North Andover','Massachusetts','001','01845','563-2298','3464276587468846','Master Card','01/01/2000')
insert into shopper values('000034','titanic','Patricia','Wright','[e-mail]','5689 Marshland Road ','Clayton','California','001','94517-1440','345-8765','3743567985785344','Master Card','02/21/2000')
insert into shopper values('000035','fire','Paul','Lopez','[e-mail]','13459 Campton Street ','Sherman Oaks ','California','001','91401','912-7905','4436465768677778','Master Card','03/03/2000')
insert into shopper values('000036','pizza','Richard','Hill','[e-mail]','1125 Feeder Road ','Glencoe','Illinois','001','60022','459-8749','9568765745645666','Visa Card','10/06/2001')
insert into shopper values('000037','heman','Robert','Scott','[e-mail]','5296 Hardboard Dr ','Oakland','California','001','94618','709-5565','8678457546556555','Master Card','10/02/2001')
insert into shopper values('000038','herby','Ruth','Green','[e-mail]','459 Ridge Road ','Mendham','New Jersey','001','07945','347-9082','9887654445423443','Visa Card','12/02/2001')
insert into shopper values('000039','superman','Sandra','Adams','[e-mail]','12454 Boggy Blvd. ','Orlando','Florida','001','32824','982-9503','7455463534636555','Master Card','04/18/2000')
insert into shopper values('000040','emerand','Sarah','Baker','[e-mail]','2394 Emerald Street ','Tarzana','California','001','91356','439-2309','8745654544323343','Visa Card','07/30/2000')
insert into shopper values('000041','hills','Sharon','Gonzalez','[e-mail]','1289 Belvedere Street  ','Hillsdale','New Jersey','001','07642-0006','230-8040','8765563434543554','Master Card','11/27/2000')
insert into shopper values('000042','apartment','Shirley','Nelson','[e-mail]','56700 Chain Boulevard Apartment # 899 ','Austin','Texas','001','78728','409-2387','8656554233242334','Master Card','01/27/2000')
insert into shopper values('000043','loft','Susan','Carter','[e-mail]','3478 Central St.  Loft 201  ','Dallas','Texas','001','75226','309-8056','9654323233457864','Visa Card','12/29/2001')
insert into shopper values('000044','sugar','Thomas','Mitchell','[e-mail]','2103 Ancient Fable Drive','Sugar Land ','Texas','001','77478','408-3389','8545432346765545','Visa Card','12/30/2001')
insert into shopper values('000045','lakes','William','Perez','[e-mail]','987 Tetragon Avenue ','Chickasha','Oklahoma','001','73018','234-8764','8553435667455454','Visa Card','12/31/2001')
insert into shopper values('000046','trail','David','Cooper','[e-mail]','5614 Raffle Court  ','Concord','California ','001','94521','678-3458','7877654435543424','Master Card','03/14/2000')
insert into shopper values('000047','drive','Peter','Prescott','[e-mail]','340 Miasma Drive','Edinboro','Pennsylvania','001','16412','890-4084','8965453446556577','Visa Card','03/13/2000')
insert into shopper values('000048','california','John','Doran','[e-mail]','678 Warren Drive - Suite 2B ','Sunnyvale','California','001',' 94086','349-9152','8565456533457545','Master Card','03/14/2000')
insert into shopper values('000049','clay','Jane','Schaffer','[e-mail]','521?State Street','Annapolis','Maryland','001','21403','280-9999','8556543443545444','Visa Card','10/08/2001')
insert into shopper values('000050','master','Heather','Landis','[e-mail]','1204 West Draper'   , 'Chicago','Illinois','001','60614','765-2322','7545475565655433','Master Card','10/15/2001')

/*插入数据到表toys*/
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)
insert into toys values('000002','Water Channel System ','Children enjoy playing with water.  The Water Channel System consists of 22 interchangeable pieces including a dock with moveable crane and a water wheel, which creates a current in the water and four boats','001', 33.99,'001',NULL,60,5,9,2,null)
insert into toys values('000003','Parachute and Rocket ','Step on the launch pad and the rocket is launched which sends a parachute slowly down to earth. ','001', 6.99,'003',NULL,90,7,9,1,null)
insert into toys values('000004','Super Deluge','Create artificial rainfall in your garden with this super deluge.','001', 35.99,'005',NULL,74,8,9,1,null)
insert into toys values('000005','Light Show Lamp','Build a revolving, glowing mood lamp with a set of colorful scenes. ','002', 15.99,'001',NULL,58,7,9,1,null)
insert into toys values('000006','Glass Decoration','Make your own beautiful, art work. Set includes eight paints, one paintbrush, patterns and complete instructions.','002', 12.99,'004',NULL,99,8,9,2,null)
insert into toys values('000007','Tie Dye Kit','CHECK THE ORIGINAL TOY LIST','002', 19.99,'002',NULL,76,7,9,1,null)
insert into toys values('000008','Alice in Wonderland','A huge coloring book.  ','002', 14.99,'001',NULL,82,4,8,1,null)
insert into toys values('000009','Glamorous Doll','She is ready for her acting audition in this blue denim dress and jacket. To carry her film gear, she has a video camera and red tote bag. ','002', 18.99,'001',NULL,39,6,9,2,null)

--insert into toys values('000010','Bubble Fairy Doll','You can make a little girls wishes come true with the Bubble Fairy magic bubbles! You just need to dip the magic wands into the magic solution, and then press the button on her shoe. She then spins, making lots of bubbles. The magical bubble-making solution, a dipping tray, and three bubble-making wands which snap easily onto the Bubble Fairy抯 wrists are included in the kit.','002', 9.99,'002',NULL,78,4,8,1,null)
insert into toys values('000011','Sleeping Beauty Doll','Let your child enact this magical fairy tale with the Sleeping Beauty Doll. ','002', 18.99,'005',NULL,65,4,8,1,null)
insert into toys values('000012','Pet Loving Doll','A beautiful doll with a small pet puppy.','002', 10.99,'001',NULL,82,4,8,1,null)
insert into toys values('000013','Beautifull Hair Doll','Girls can change the hair style and color of this Doll .','002', 14.99,'003',NULL,55,4,8,1,null)
insert into toys values('000015','Flower Loving Doll','A doll with a bouquet of flowers in her hand.','002', 49.99,'004',NULL,43,8,9,1,null)
insert into toys values('000016','Victorian Dollhouse','A beautiful dollhouse that every girl will love.','002',43.25,'003',NULL,36,5,9,1,null)
insert into toys values('000017','Kitchen Set','A complete kitchen set with utensils, stove and all other kitchen gadgets.','006', 23.99,'002',NULL,76,5,9,2,null)
insert into toys values('000018','Childrens Bedroom','A childrens bedroom set with bunk beds with a ladder and a closet. ','006', 16.99,'005',NULL,15,5,9,2,null)
insert into toys values('000019','Nursery','A nursery with a crib and a baby.','006', 8.99,'001',NULL,35,4,9,2,null)
insert into toys values('000020','Victorian Family ','Let your child travel back in time and out for a stroll with this Victorian family. ','006', 8.99,'001',NULL,45,4,9,2,null)
insert into toys values('000021','Birthday Party','A children抯 party with a magician and his tricks, a big game wheel, trees, balloons of all shapes and sizes, presents, and games. ','006', 25.99,'004',NULL,56,4,9,1,null)
insert into toys values('000023','Tin Drum','A tin drum, which is perfect for neighborhood parades. Includes carrying strap and drumsticks.','012', 15.99,'001',NULL,88,3,8,1,null)
insert into toys values('000024','Key Boom Guitar','A guitar with dual volume control and amplifying circuit and includes a shoulder strap and batteries.','012', 25.99,'001',NULL,75,5,8,1,null)
insert into toys values('000025','My First Flashlight','Makes flashlight play fun and easy. This durable flashlight has a big button for easy activation. Requires one AA battery (not included).','013', 7.99,'003',NULL,65,3,5,1,null)
insert into toys values('000026','Electronic Safe','A large size safe. Just press a button to open the safe and hear an alarm. Insert money through a secret slot in the back and use an electronic combination lock. ','013', 22.99,'005',NULL,66,5,9,1,null)
insert into toys values('000027','X-90 Racers Set','The fast-paced action racing track is the ultimate challenge for X-90 Racers. Contains cars with motors inside for incredibly fast and furious racing action. ','005', 19.99,'001',NULL,77,5,9,1,null)
insert into toys values('000028','Dune Racer','A set of dune buggies with a racing track.','005', 9.99,'004',NULL,78,4,9,1,null)
insert into toys values('000029','Spiral Zoom  Way','Give children a chance to enjoy racing excitement with this easy-to-assemble track','005', 14.99,'002',NULL,88,2,7,2,null)
insert into toys values('000030','Racing Truck','Zoom past the competition with this durable plastic truck featuring oversized tires.','005', 35.99,'005',NULL,78,3,7,2,null)
insert into toys values('000031','Large Duck','Big, bright and soft to the touch, this Large Duck. It is made of durable cotton and is filled with flameproof machine-washable material.','009', 17.99,'001',NULL,88,1,2,1,null)
insert into toys values('000032','Baby Minnie','Your infant needs to merely touch Baby Minnie, and she will shake her little rattle, just like a real Baby would. ','009', 14.99,'002',NULL,66,1,3,1,null)

/*插入数据到表shoppingcart*/
insert into ShoppingCart values('000001','000001',1)
insert into ShoppingCart values('000001','000007',1)
insert into ShoppingCart values('000001','000008',1)
insert into ShoppingCart values('000002','000016',1)
insert into ShoppingCart values('000002','000009',1)
insert into ShoppingCart values('000003','000017',1)
insert into ShoppingCart values('000004','000030',1)
insert into ShoppingCart values('000004','000004',1)
insert into ShoppingCart values('000005','000001',1)
insert into ShoppingCart values('000005','000024',1)
insert into ShoppingCart values('000005','000030',1)
insert into ShoppingCart values('000005','000018',1)
insert into ShoppingCart values('000006','000017',1)
insert into ShoppingCart values('000006','000013',1)
insert into ShoppingCart values('000007','000006',1)
insert into ShoppingCart values('000008','000023',1)
insert into ShoppingCart values('000009','000018',1)

/*插入数据到表orders*/
insert into Orders values('000001','05/20/2001','000002','000002','01',6,1.2500,'Y',62.2200,'05/24/2001')
insert into Orders values('000002','05/20/2001','000001','000005','02',8,2.0000,'Y',96.5000,'05/23/2001')
insert into Orders values('000003','05/20/2001','000003','000007','01',12,0,'Y',83.9700,'05/24/2001')
insert into Orders values('000004','05/20/2001','000004','000006','01',4,1.0000,'Y',40.9900,'05/24/2001')
insert into Orders values('000005','05/21/2001','000005','000002','03',90,7.7500,'Y',231.6800,'05/25/2001')
insert into Orders values('000006','05/21/2001','000003','000012','03',40,4.0000,'Y',97.9700,'05/22/2001')
insert into Orders values('000007','05/22/2001','000002','000008','01',4,0,'Y',16.9900,'05/26/2001')
insert into Orders values('000008','05/22/2001','000002','000009','03',20,2.0000,'Y',53.9800,'05/26/2001')
insert into Orders values('000009','05/22/2001','000004','000010','02',8,2.000,'Y',26.9900,'05/25/2001')
insert into Orders values('000010','05/22/2001','000005','000003','02',20,4.0000,'Y',67.9700,'05/26/2001')

/*插入数据到表orderdetails*/
insert into OrderDetail values('000001','000007',2,'N',NULL,NULL,39.9800)
insert into OrderDetail values('000001','000008',1,'Y','002','Surprise',14.9900)
insert into OrderDetail values('000002','000016',2,'Y','001','I Love you',86.5000)
insert into OrderDetail values('000003','000017',3,'N',NULL,NULL,71.9700)
insert into OrderDetail values('000004','000030',1,'Y','001','I Love You',35.9900)
insert into OrderDetail values('000005','000001',4,'Y','001','Happy Birthday',35.9600)
insert into OrderDetail values('000005','000024',1,'Y','002','Best Wishes',25.9900)
insert into OrderDetail values('000005','000030',2,'Y','002','Surprise',71.9800)
insert into OrderDetail values('000006','000017',1,'Y','001','With love',29.9800)
insert into OrderDetail values('000006','000013',2,'Y','003','Happy Birth day',23.9900)
insert into OrderDetail values('000007','000006',1,'N',NULL,NULL,12.9900)
insert into OrderDetail values('000008','000023',2,'Y','001','For you with love',31.9800)
insert into OrderDetail values('000009','000018',1,'Y','004','Congratulations',16.9900)
insert into OrderDetail values('000010','000020',2,'Y','005','You are the best',17.9800)
insert into OrderDetail values('000010','000021',1,'Y','001','Happy Birth day',25.9900)

/*插入数据到表shipment*/
insert into Shipment values('000001','05/23/2001','d','05/24/2001')
insert into Shipment values('000002','05/23/2001','d','05/23/2001')
insert into Shipment values('000003','05/23/2001','s',null)
insert into Shipment values('000004','05/24/2001','d','05/26/2001')
insert into Shipment values('000005','05/24/2001','d','05/25/2001')
insert into Shipment values('000006','05/22/2001','d','05/23/2001')
insert into Shipment values('000007','05/25/2001','s',null)
insert into Shipment values('000008','05/24/2001','d','05/24/2001')
insert into Shipment values('000009','05/24/2001','d','05/25/2001')
insert into Shipment values('000010','05/26/2001','d','05/28/2001')

/*插入数据到表recipient*/
insert into Recipient values('000001','Barbara','Johnson','227 Beach Ave.','Sunnyvale','California','001','94087-1147','123-5673')
insert into Recipient values('000002','Catherine','Roberts','5508 Aquiline Court','San Jose','California','001','95233-4123','445-2256')
insert into Recipient values('000003','Christopher','Davis','4896 11th ST','Hill Avenue','Utah','001','84056-5410','556-9087')
insert into Recipient values('000005','Barbara','Johnson','227 Beach Ave.','Sunnyvale','California','001','94087-1147','123-5673')
insert into Recipient values('000006','Donna','Anderson','7930 Orange St.','Las Vegas','Nevada','001','89328-2517','845-2323')
insert into Recipient values('000009','David','Moore','8808 Joviality Drive','San Ramon','California','001','94453-5849','982-5577')
insert into Recipient values('000010','Betty','Williams','1 Tread Road','Virginia Beach','Virginia','001','23455-2456','458-3299')
insert into Recipient values('000004','Jennifer','Martin','9812  76th Street','Brooklyn','Maryland','001','21254-0025','569-7789')
insert into Recipient values('000007','Laura','Rodriguez','3242 Limestone  ','WayMarietta','Georgia','001','30062-5423','567-3345')
insert into Recipient values('000008','Michelle','Hernandez','1353 Realm Lakes','Naperville','Illinois','001','60563-1256','294-5385')

/*插入数据到表pickofmonth*/
insert into pickofmonth values('000001',1,2000,1000)
insert into pickofmonth values('000001',2,2000,1230)
insert into pickofmonth values('000005',3,2000,4000)
insert into pickofmonth values('000007',4,2000,5000)
insert into pickofmonth values('000003',5,2000,2000)
insert into pickofmonth values('000002',6,2000,3000)
insert into pickofmonth values('000003',7,2000,5670)
insert into pickofmonth values('000007',8,2000,2340)
insert into pickofmonth values('000011',9,2000,5600)
insert into pickofmonth values('000020',10,2000,2300)
insert into pickofmonth values('000021',11,2000,4500)
insert into pickofmonth values('000026',12,2000,6500)
insert into pickofmonth values('000024',1,2001,3200)
insert into pickofmonth values('000015',2,2001,3100)
insert into pickofmonth values('000012',3,2001,2500)
go

CREATE PROCEDURE prcGenOrder
@OrderNo char(6)OUTPUT
as
SELECT @OrderNo=Max(cOrderNo) FROM Orders
SELECT @OrderNo= 
CASE 
	WHEN @OrderNo >=0 and @OrderNo<9 Then '00000'+Convert(char,@OrderNo+1)
	WHEN @OrderNo>=9 and @OrderNo<99 Then '0000'+Convert(char,@OrderNo+1)
	WHEN @OrderNo>=99 and @OrderNo<999 Then '000'+Convert(char,@OrderNo+1)
	WHEN @OrderNo>=999 and @OrderNo<9999 Then '00'+Convert(char,@OrderNo+1)
	WHEN @OrderNo>=9999 and @OrderNo<99999 Then '0'+Convert(char,@OrderNo+1)
	WHEN @OrderNo>=99999 Then Convert(char,@OrderNo+1)
END
RETURN  
go

Create view vwOrderWrapper
as
SELECT cOrderNo,cToyId,siQty,vDescription,mWrapperRate
FROM OrderDetail JOIN Wrapper
ON OrderDetail.cWrapperId=Wrapper.cWrapperId
go
raiserror('The PracticeDB1 database in now ready for use....',0,1)