petshop_dataload.sql

基于微软的 ASP.NET+C#开发的PETSHOP(网上宠物店)项目,在性能及开发效率上明显优于基于SUN J2EE框架开发的PETSHOP. 项目包括所有源码及数据库建库脚本,是不错的学习 AS

/******************************************************************************
**        File: DataPopulation.sql
**        Name: DataPopulation
**        Desc: Populates the .NET PetShop database.
**
**        Date: 11/6/2001
**
*******************************************************************************/

-- switch to the .NET PetShop database
USE petshop;

-- insert Signon table data
INSERT INTO Signon VALUES('dotnet', 'dotnet');

-- insert Account table data
ALTER TABLE Account NOCHECK CONSTRAINT FK_Account_Signon;
ALTER TABLE Account NOCHECK CONSTRAINT FK_Account_Profile;
INSERT INTO Account (userid, email, firstname, lastname, status, addr1, addr2, city, state, zip, country, phone) VALUES('dotnet','[e-mail]','ABC', 'XYX', 'OK', '901 San Antonio Road', 'MS UCUP02-206', 'Palo Alto', 'CA', '94303', 'USA',  '555-555-5555');
ALTER TABLE Account CHECK CONSTRAINT FK_Account_Profile;
ALTER TABLE Account CHECK CONSTRAINT FK_Account_Signon;

-- insert Profile table data
ALTER TABLE Profile NOCHECK CONSTRAINT FK_Profile_BannerData;
INSERT INTO Profile VALUES('dotnet','english','dogs','1','1');
ALTER TABLE Profile CHECK CONSTRAINT FK_Profile_BannerData;

-- insert BannerData table data
INSERT INTO BannerData VALUES ('fish','<image src="images/banner_fish.gif">');
INSERT INTO BannerData VALUES ('cats','<image src="images/banner_cats.gif">');
INSERT INTO BannerData VALUES ('dogs','<image src="images/banner_dogs.gif">');
INSERT INTO BannerData VALUES ('reptiles','<image src="images/banner_reptiles.gif">');
INSERT INTO BannerData VALUES ('birds','<image src="images/banner_birds.gif">');

-- insert Category table data
INSERT INTO Category VALUES ('FISH','Fish','<image src="images/fish_icon.gif"><font size="5" color="blue"> Fish</font>');
INSERT INTO Category VALUES ('DOGS','Dogs','<image src="images/dogs_icon.gif"><font size="5" color="blue"> Dogs</font>');
INSERT INTO Category VALUES ('REPTILES','Reptiles','<image src="images/reptiles_icon.gif"><font size="5" color="blue"> Reptiles</font>');
INSERT INTO Category VALUES ('CATS','Cats','<image src="images/cats_icon.gif"><font size="5" color="blue"> Cats</font>');
INSERT INTO Category VALUES ('BIRDS','Birds','<image src="images/birds_icon.gif"><font size="5" color="blue"> Birds</font>');

-- insert Product table data
INSERT INTO Product VALUES ('AV-CB-01', 'BIRDS', 'Amazon Parrot', '<image src="DataImages/bird2.jpg"><br>Great companion for up to 75 years');
INSERT INTO Product VALUES ('AV-SB-02', 'BIRDS', 'Finch', '<image src="DataImages/bird1.jpg"><br>Great stress reliever');
INSERT INTO Product VALUES ('FI-FW-01', 'FISH', 'Koi', '<image src="DataImages/fish3.jpg"><br>Fresh Water fish from Japan');
INSERT INTO Product VALUES ('FI-FW-02', 'FISH', 'Goldfish', '<image src="DataImages/fish2.jpg"><br>Fresh Water fish from China');
INSERT INTO Product VALUES ('FI-SW-01', 'FISH', 'Angelfish', '<image src="DataImages/fish1.jpg"><br>Salt Water fish from Australia');
INSERT INTO Product VALUES ('FI-SW-02', 'FISH', 'Tiger Shark', '<image src="DataImages/fish4.jpg"><br>Salt Water fish from Australia');
INSERT INTO Product VALUES ('FL-DLH-02', 'CATS', 'Persian', '<image src="DataImages/cat1.jpg"><br>Friendly house cat, doubles as a princess');
INSERT INTO Product VALUES ('FL-DSH-01', 'CATS', 'Manx', '<image src="DataImages/cat2.jpg"><br>Great for reducing mouse populations');
INSERT INTO Product VALUES ('K9-BD-01', 'DOGS', 'Bulldog', '<image src="DataImages/dog1.jpg"><br>Friendly dog from England');
INSERT INTO Product VALUES ('K9-CW-01', 'DOGS', 'Chihuahua', '<image src="DataImages/dog2.jpg"><br>Great companion dog');
INSERT INTO Product VALUES ('K9-DL-01', 'DOGS', 'Dalmation', '<image src="DataImages/dog3.jpg"><br>Great dog for a Fire Station');
INSERT INTO Product VALUES ('K9-PO-02', 'DOGS', 'Poodle', '<image src="DataImages/dog6.jpg"><br>Cute dog from France');
INSERT INTO Product VALUES ('K9-RT-01', 'DOGS', 'Golden Retriever', '<image src="DataImages/dog4.jpg"><br>Great family dog');
INSERT INTO Product VALUES ('K9-RT-02', 'DOGS', 'Labrador Retriever', '<image src="DataImages/dog5.jpg"><br>Great hunting dog');
INSERT INTO Product VALUES ('RP-LI-02', 'REPTILES', 'Iguana', '<image src="DataImages/lizard1.jpg"><br>Friendly green friend');
INSERT INTO Product VALUES ('RP-SN-01', 'REPTILES', 'Rattlesnake', '<image src="DataImages/snake1.jpg"><br>Doubles as a watch dog');

-- insert Supplier table data
INSERT INTO Supplier VALUES (1,'XYZ Pets','AC','600 Avon Way','','Los Angeles','CA','94024','212-947-0797');
INSERT INTO Supplier VALUES (2,'ABC Pets','AC','700 Abalone Way','','San Francisco ','CA','94024','415-947-0797');

-- insert Item table data
INSERT INTO Item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES ('EST-1','FI-SW-01',16.50,10.00,1,'P','Large');
INSERT INTO Item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES ('EST-2','FI-SW-01',16.50,10.00,1,'P','Small');
INSERT INTO Item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES ('EST-3','FI-SW-02',18.50,12.00,1,'P','Toothless');
INSERT INTO Item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES ('EST-4','FI-FW-01',18.50,12.00,1,'P','Spotted');
INSERT INTO Item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES ('EST-5','FI-FW-01',18.50,12.00,1,'P','Spotless');
INSERT INTO Item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES ('EST-6','K9-BD-01',18.50,12.00,1,'P','Male Adult');
INSERT INTO Item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES ('EST-7','K9-BD-01',18.50,12.00,1,'P','Female Puppy');
INSERT INTO Item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES ('EST-8','K9-PO-02',18.50,12.00,1,'P','Male Puppy');
INSERT INTO Item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES ('EST-9','K9-DL-01',18.50,12.00,1,'P','Spotless Male Puppy');
INSERT INTO Item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES ('EST-10','K9-DL-01',18.50,12.00,1,'P','Spotted Adult Female');
INSERT INTO Item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES ('EST-11','RP-SN-01',18.50,12.00,1,'P','Venomless');
INSERT INTO Item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES ('EST-12','RP-SN-01',18.50,12.00,1,'P','Rattleless');
INSERT INTO Item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES ('EST-13','RP-LI-02',18.50,12.00,1,'P','Green Adult');
INSERT INTO Item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES ('EST-14','FL-DSH-01',58.50,12.00,1,'P','Tailless');
INSERT INTO Item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES ('EST-15','FL-DSH-01',23.50,12.00,1,'P','With tail');
INSERT INTO Item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES ('EST-16','FL-DLH-02',93.50,12.00,1,'P','Adult Female');
INSERT INTO Item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES ('EST-17','FL-DLH-02',93.50,12.00,1,'P','Adult Male');
INSERT INTO Item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES ('EST-18','AV-CB-01',193.50,92.00,1,'P','Adult Male');
INSERT INTO Item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES ('EST-19','AV-SB-02',15.50, 2.00,1,'P','Adult Male');
INSERT INTO Item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES ('EST-20','FI-FW-02',5.50, 2.00,1,'P','Adult Male');
INSERT INTO Item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES ('EST-21','FI-FW-02',5.29, 1.00,1,'P','Adult Female');
INSERT INTO Item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES ('EST-22','K9-RT-02',135.50, 100.00,1,'P','Adult Male');
INSERT INTO Item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES ('EST-23','K9-RT-02',145.49, 100.00,1,'P','Adult Female');
INSERT INTO Item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES ('EST-24','K9-RT-02',255.50, 92.00,1,'P','Adult Male');
INSERT INTO Item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES ('EST-25','K9-RT-02',325.29, 90.00,1,'P','Adult Female');
INSERT INTO Item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES ('EST-26','K9-CW-01',125.50, 92.00,1,'P','Adult Male');
INSERT INTO Item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES ('EST-27','K9-CW-01',155.29, 90.00,1,'P','Adult Female');
INSERT INTO Item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES ('EST-28','K9-RT-01',155.29, 90.00,1,'P','Adult Female');

-- insert Inventory table data
INSERT INTO Inventory (itemid, qty ) VALUES ('EST-1', 10000);
INSERT INTO Inventory (itemid, qty ) VALUES ('EST-2', 10000);
INSERT INTO Inventory (itemid, qty ) VALUES ('EST-3', 10000);
INSERT INTO Inventory (itemid, qty ) VALUES ('EST-4', 10000);
INSERT INTO Inventory (itemid, qty ) VALUES ('EST-5', 10000);
INSERT INTO Inventory (itemid, qty ) VALUES ('EST-6', 10000);
INSERT INTO Inventory (itemid, qty ) VALUES ('EST-7', 10000);
INSERT INTO Inventory (itemid, qty ) VALUES ('EST-8', 10000);
INSERT INTO Inventory (itemid, qty ) VALUES ('EST-9', 10000);
INSERT INTO Inventory (itemid, qty ) VALUES ('EST-10', 10000);
INSERT INTO Inventory (itemid, qty ) VALUES ('EST-11', 10000);
INSERT INTO Inventory (itemid, qty ) VALUES ('EST-12', 10000);
INSERT INTO Inventory (itemid, qty ) VALUES ('EST-13', 10000);
INSERT INTO Inventory (itemid, qty ) VALUES ('EST-14', 10000);
INSERT INTO Inventory (itemid, qty ) VALUES ('EST-15', 10000);
INSERT INTO Inventory (itemid, qty ) VALUES ('EST-16', 10000);
INSERT INTO Inventory (itemid, qty ) VALUES ('EST-17', 10000);
INSERT INTO Inventory (itemid, qty ) VALUES ('EST-18', 10000);
INSERT INTO Inventory (itemid, qty ) VALUES ('EST-19', 10000);
INSERT INTO Inventory (itemid, qty ) VALUES ('EST-20', 10000);
INSERT INTO Inventory (itemid, qty ) VALUES ('EST-21', 10000);
INSERT INTO Inventory (itemid, qty ) VALUES ('EST-22', 10000);
INSERT INTO Inventory (itemid, qty ) VALUES ('EST-23', 10000);
INSERT INTO Inventory (itemid, qty ) VALUES ('EST-24', 10000);
INSERT INTO Inventory (itemid, qty ) VALUES ('EST-25', 10000);
INSERT INTO Inventory (itemid, qty ) VALUES ('EST-26', 10000);
INSERT INTO Inventory (itemid, qty ) VALUES ('EST-27', 10000);
INSERT INTO Inventory (itemid, qty ) VALUES ('EST-28', 10000);

PRINT 'Script Complete: Data Population Script';