oracle_populate.sql

此程序都是企业级 的数据库开发程序 全面揭示了JAVA对数据库的操作

--Countries Table:
------------------
--Create a sequence for the countries table to make sure the primary keys start at 1:

CREATE SEQUENCE seqCountries INCREMENT BY 1 START WITH 1 NOCYCLE;

INSERT INTO Countries (CountryID,CountryAbbrev,CountryName) VALUES (seqCountries.NextVal,'USA','United States');
INSERT INTO Countries (CountryID,CountryAbbrev,CountryName) VALUES (seqCountries.NextVal,'CA','Canada');
INSERT INTO Countries (CountryID,CountryAbbrev,CountryName) VALUES (seqCountries.NextVal,'MEX','Mexico');
INSERT INTO Countries (CountryID,CountryAbbrev,CountryName) VALUES (seqCountries.NextVal,'UK','United Kingdom');
INSERT INTO Countries (CountryID,CountryAbbrev,CountryName) VALUES (seqCountries.NextVal,'JAP','Japan');
INSERT INTO Countries (CountryID,CountryAbbrev,CountryName) VALUES (seqCountries.NextVal,'GER','Germany');
INSERT INTO Countries (CountryID,CountryAbbrev,CountryName) VALUES (seqCountries.NextVal,'FRA','France');
INSERT INTO Countries (CountryID,CountryAbbrev,CountryName) VALUES (seqCountries.NextVal,'ITA','Italy');
INSERT INTO Countries (CountryID,CountryAbbrev,CountryName) VALUES (seqCountries.NextVal,'BEL','Belgium');
INSERT INTO Countries (CountryID,CountryAbbrev,CountryName) VALUES (seqCountries.NextVal,'SPA','Spain');
INSERT INTO Countries (CountryID,CountryAbbrev,CountryName) VALUES (seqCountries.NextVal,'GRE','Greece');
INSERT INTO Countries (CountryID,CountryAbbrev,CountryName) VALUES (seqCountries.NextVal,'IRE','Ireland');



--PostalCodes Table:
--------------------
--No need for a sequence here since the primary key is not numerical

INSERT INTO PostalCodes (PostalCode, City,StateProv,CountryID) VALUES ('06484','Shelton','CT',1);
INSERT INTO PostalCodes (PostalCode, City,StateProv,CountryID) VALUES ('06488','Southbury','CT',1);
INSERT INTO PostalCodes (PostalCode, City,StateProv,CountryID) VALUES ('06877','West Haven','CT',1);
INSERT INTO PostalCodes (PostalCode, City,StateProv,CountryID) VALUES ('06628','Trumbull','CT',1);
INSERT INTO PostalCodes (PostalCode, City,StateProv,CountryID) VALUES ('10022','Manhattan','NY',1);
INSERT INTO PostalCodes (PostalCode, City,StateProv,CountryID) VALUES ('06601','Bridgeport','CT',1);
INSERT INTO PostalCodes (PostalCode, City,StateProv,CountryID) VALUES ('92121','San Diego','CA',1);
INSERT INTO PostalCodes (PostalCode, City,StateProv,CountryID) VALUES ('90232','Culver City','CA',1);
INSERT INTO PostalCodes (PostalCode, City,StateProv,CountryID) VALUES ('33487','Boca Raton','FL',1);
INSERT INTO PostalCodes (PostalCode, City,StateProv,CountryID) VALUES ('06602','Bridgeport','CT',1);




--StoreTypes Table:
------------------
--Create a sequence for this table to make sure the primary keys start at 1:

CREATE SEQUENCE seqStoreTypes INCREMENT BY 1 START WITH 1 NOCYCLE;

INSERT INTO StoreTypes (StoreTypeID,StoreTypeDescription) Values (seqStoreTypes.NextVal,'BrickAndMorter');
INSERT INTO StoreTypes (StoreTypeID,StoreTypeDescription) Values (seqStoreTypes.NextVal,'Corporate Website');
INSERT INTO StoreTypes (StoreTypeID,StoreTypeDescription) Values (seqStoreTypes.NextVal,'Warehouse');
INSERT INTO StoreTypes (StoreTypeID,StoreTypeDescription) Values (seqStoreTypes.NextVal,'Affiliate Website');




--Stores Table:
------------------
--Create a sequence for this table to make sure the primary keys start at 1:

CREATE SEQUENCE seqStores INCREMENT BY 1 START WITH 1 NOCYCLE;


INSERT INTO Stores (StoreID,StoreDescription,StoreTypeID ,StoreAddress1,StoreAddress2,StoreCity,StorePostalCode) VALUES (seqStores.NextVal,'www.Music4YouAndMe.com',2,'190 Grunge Way','Suite 100','Manhatten','10022');
INSERT INTO Stores (StoreID,StoreDescription,StoreTypeID ,StoreAddress1,StoreAddress2,StoreCity,StorePostalCode) VALUES (seqStores.NextVal,'Manhattan',1,'450 Madison Ave','1st Floor','New York','10022');
INSERT INTO Stores (StoreID,StoreDescription,StoreTypeID ,StoreAddress1,StoreAddress2,StoreCity,StorePostalCode) VALUES (seqStores.NextVal,'Westfield Shopping Park',1,'55 Elm Street',null,'Bridgeport','06601');
INSERT INTO Stores (StoreID,StoreDescription,StoreTypeID ,StoreAddress1,StoreAddress2,StoreCity,StorePostalCode) VALUES (seqStores.NextVal,'New England Warehouse',3,'918 Main Street South',null,'Bridgeport','06602');
INSERT INTO Stores (StoreID,StoreDescription,StoreTypeID ,StoreAddress1,StoreAddress2,StoreCity,StorePostalCode) VALUES (seqStores.NextVal,'HillAndDale' ,1,'4987 Terman Court',null,'San Diego','92121');
INSERT INTO Stores (StoreID,StoreDescription,StoreTypeID ,StoreAddress1,StoreAddress2,StoreCity,StorePostalCode) VALUES (seqStores.NextVal,'West Coast Supper Mall',1,'10202 West Washington',null,'Culver City','90232');
INSERT INTO Stores (StoreID,StoreDescription,StoreTypeID ,StoreAddress1,StoreAddress2,StoreCity,StorePostalCode) VALUES (seqStores.NextVal,'Shady Palm Shopping Galeria',1,'6200 Congress Ave',null,'Boca Raton','33487');
INSERT INTO Stores (StoreID,StoreDescription,StoreTypeID ,StoreAddress1,StoreAddress2,StoreCity,StorePostalCode) VALUES (seqStores.NextVal,'Western USA',1,'600 West Central Road',null,'Culver City','90232');




--CustomerTitles Table:
-----------------------
--Create a sequence for this table to make sure the primary keys start at 1:

CREATE SEQUENCE seqCustomerTitles INCREMENT BY 1 START WITH 1 NOCYCLE;

INSERT INTO CustomerTitles (CustomerTitleID,CUSTOMERTITLEABBREV) VALUES (seqCustomerTitles.NextVal,'Mr.');
INSERT INTO CustomerTitles (CustomerTitleID,CUSTOMERTITLEABBREV) VALUES (seqCustomerTitles.NextVal,'Mrs.');
INSERT INTO CustomerTitles (CustomerTitleID,CUSTOMERTITLEABBREV) VALUES (seqCustomerTitles.NextVal,'Miss');
INSERT INTO CustomerTitles (CustomerTitleID,CUSTOMERTITLEABBREV) VALUES (seqCustomerTitles.NextVal,'Dr.');
INSERT INTO CustomerTitles (CustomerTitleID,CUSTOMERTITLEABBREV) VALUES (seqCustomerTitles.NextVal,'Ms');



--CustomerTypes Table:
-----------------------
--Create a sequence for this table to make sure the primary keys start at 1:

CREATE SEQUENCE seqCustomerTypes INCREMENT BY 1 START WITH 1 NOCYCLE;

INSERT INTO CustomerTypes (CustomerTypeID,CUSTOMERTYPEDESCRIPTION) VALUES(seqCustomerTypes.NextVal,'Individual');
INSERT INTO CustomerTypes (CustomerTypeID,CUSTOMERTYPEDESCRIPTION) VALUES(seqCustomerTypes.NextVal,'Corporation');
INSERT INTO CustomerTypes (CustomerTypeID,CUSTOMERTYPEDESCRIPTION) VALUES(seqCustomerTypes.NextVal,'Nonprofit Organization/Non-Gov');
INSERT INTO CustomerTypes (CustomerTypeID,CUSTOMERTYPEDESCRIPTION) VALUES(seqCustomerTypes.NextVal,'Government');
INSERT INTO CustomerTypes (CustomerTypeID,CUSTOMERTYPEDESCRIPTION) VALUES(seqCustomerTypes.NextVal,'Educational Institution');



--PaymentTerms Table:
-----------------------
--Create a sequence for this table to make sure the primary keys start at 1:

CREATE SEQUENCE seqPaymentTerms INCREMENT BY 1 START WITH 1 NOCYCLE;

INSERT INTO PaymentTerms (PaymentTermsID,PAYMENTTERMSDESCRIPTION) VALUES (seqPaymentTerms.NextVal,'Cash Only');
INSERT INTO PaymentTerms (PaymentTermsID,PAYMENTTERMSDESCRIPTION) VALUES (seqPaymentTerms.NextVal,'Net 30');
INSERT INTO PaymentTerms (PaymentTermsID,PAYMENTTERMSDESCRIPTION) VALUES (seqPaymentTerms.NextVal,'Net 60');
INSERT INTO PaymentTerms (PaymentTermsID,PAYMENTTERMSDESCRIPTION) VALUES (seqPaymentTerms.NextVal,'Net 90');



--CreditCardTypes Table:
------------------------
--Create a sequence for this table to make sure the primary keys start at 1:

CREATE SEQUENCE seqCreditCardTypes INCREMENT BY 1 START WITH 1 NOCYCLE;

INSERT INTO CreditCardTypes (CreditCardTypeID,CREDITCARDDESCRIPTION) VALUES (seqCreditCardTypes.NextVal,'VISA');
INSERT INTO CreditCardTypes (CreditCardTypeID,CREDITCARDDESCRIPTION) VALUES (seqCreditCardTypes.NextVal,'Master Card');
INSERT INTO CreditCardTypes (CreditCardTypeID,CREDITCARDDESCRIPTION) VALUES (seqCreditCardTypes.NextVal,'American Express');


--Customers Table:
------------------
--Create a sequence for this table to make sure the primary keys start at 1:

CREATE SEQUENCE seqCustomers INCREMENT BY 1 START WITH 1 NOCYCLE;

INSERT INTO Customers (CustomerID,CUSTOMERTYPEID,CUSTOMERFIRSTNAME,CUSTOMERLASTNAME,
CUSTOMERTITLEID,CUSTOMERADDRESS1,CUSTOMERADDRESS2,CUSTOMERCITY,
CUSTOMERPOSTALCODE,PAYMENTTERMSID,CREDITLIMIT,CREDITCARDHOLDER,
CREDITCARDNUMBER,CREDITCARDTYPEID,CREDITCARDEXPIRYMONTH,
CREDITCARDEXPIRYYEAR,EMAILADDRESS,PASSWORD,PASSWORDREMINDERTEXT)
VALUES(seqCustomers.NextVal,1,'Catherine','Crean',3,'62 Windsor Drive',
'','Shelton','06484',1,0,null,null,null,null,null,null,null,null);

INSERT INTO Customers (CustomerID,CUSTOMERTYPEID,CUSTOMERFIRSTNAME,CUSTOMERLASTNAME,
CUSTOMERTITLEID,CUSTOMERADDRESS1,CUSTOMERADDRESS2,CUSTOMERCITY,
CUSTOMERPOSTALCODE,PAYMENTTERMSID,CREDITLIMIT,CREDITCARDHOLDER,
CREDITCARDNUMBER,CREDITCARDTYPEID,CREDITCARDEXPIRYMONTH,
CREDITCARDEXPIRYYEAR,EMAILADDRESS,PASSWORD,PASSWORDREMINDERTEXT) VALUES(seqCustomers.NextVal,1,'Steve','Davis',1,
'567 Captain''s Bluff','Unit 4A','West Haven','06877',1,0,
'STEPHEN DAVIS','3299-87-9887',1,'10','2003','[e-mail]',
'rabbit','bunny');

INSERT INTO Customers (CustomerID,CUSTOMERTYPEID,CUSTOMERFIRSTNAME,CUSTOMERLASTNAME,
CUSTOMERTITLEID,CUSTOMERADDRESS1,CUSTOMERADDRESS2,CUSTOMERCITY,
CUSTOMERPOSTALCODE,PAYMENTTERMSID,CREDITLIMIT,CREDITCARDHOLDER,
CREDITCARDNUMBER,CREDITCARDTYPEID ,CREDITCARDEXPIRYMONTH,
CREDITCARDEXPIRYYEAR,EMAILADDRESS,PASSWORD,PASSWORDREMINDERTEXT) VALUES (seqCustomers.NextVal,2,'Villiage DJ','','',
'125 Stanford Road','','Trumbull','06628',2,500,
'VILLIAGE DJ LLC','5664-655-987-87',2,'12','2002',
'[e-mail]','werock','what we do');

INSERT INTO Customers (CustomerID,CUSTOMERTYPEID,CUSTOMERFIRSTNAME,CUSTOMERLASTNAME,
CUSTOMERTITLEID,CUSTOMERADDRESS1,CUSTOMERADDRESS2,CUSTOMERCITY,
CUSTOMERPOSTALCODE,PAYMENTTERMSID,CREDITLIMIT,CREDITCARDHOLDER,
CREDITCARDNUMBER,CREDITCARDTYPEID,CREDITCARDEXPIRYMONTH,
CREDITCARDEXPIRYYEAR,EMAILADDRESS,PASSWORD,PASSWORDREMINDERTEXT) VALUES (seqCustomers.NextVal,1,'Diane ','Asmus',3,'324 White Street',
'','Shelton','06484',1,0,'DIANE ASMUS','3299-54-5442',1,'2','2002',
'[e-mail]','javaqueen','my nickname from Jim');



--OrderSources Table:
---------------------
--Create a sequence for this table to make sure the primary keys start at 1:

CREATE SEQUENCE seqOrderSources INCREMENT BY 1 START WITH 1 NOCYCLE;

INSERT INTO OrderSources (OrderSourceID,ORDERSOURCEDESCRIPTION) VALUES (seqOrderSources.NextVal,'Website');
INSERT INTO OrderSources (OrderSourceID,ORDERSOURCEDESCRIPTION) VALUES (seqOrderSources.NextVal,'Store');
INSERT INTO OrderSources (OrderSourceID,ORDERSOURCEDESCRIPTION) VALUES (seqOrderSources.NextVal,'Telephone');
INSERT INTO OrderSources (OrderSourceID,ORDERSOURCEDESCRIPTION) VALUES (seqOrderSources.NextVal,'Fax');



--OrderStatuses Table:
----------------------
--Create a sequence for this table to make sure the primary keys start at 1:

CREATE SEQUENCE seqOrderStatuses INCREMENT BY 1 START WITH 1 NOCYCLE;

INSERT INTO OrderStatuses (OrderStatusID,ORDERSTATUSDESCRIPTION) VALUES (seqOrderStatuses.NextVal,'Order Placed');
INSERT INTO OrderStatuses (OrderStatusID,ORDERSTATUSDESCRIPTION) VALUES (seqOrderStatuses.NextVal,'Order Cancelled');
INSERT INTO OrderStatuses (OrderStatusID,ORDERSTATUSDESCRIPTION) VALUES (seqOrderStatuses.NextVal,'In Process');
INSERT INTO OrderStatuses (OrderStatusID,ORDERSTATUSDESCRIPTION) VALUES (seqOrderStatuses.NextVal,'Waing for Backorder');
INSERT INTO OrderStatuses (OrderStatusID,ORDERSTATUSDESCRIPTION) VALUES (seqOrderStatuses.NextVal,'Shipped');



--CustomerOrders Table:
-----------------------
--Create a sequence for this table to make sure the primary keys start at 1:

CREATE SEQUENCE seqCustomerOrders INCREMENT BY 1 START WITH 1 NOCYCLE;

INSERT INTO CustomerOrders (OrderID,ORDERDATE,CUSTOMERID,
ORDERSTATUSID,SHIPTONAME,SHIPTOADDRESS1,SHIPTOADDRESS2,
SHIPTOCITY,SHIPTOPOSTALCODE,ORDERSOURCEID,CREDITCARDHOLDER,
CREDITCARDNUMBER,CREDITCARDTYPEID,CREDITCARDEXPIRYMONTH,
CREDITCARDEXPIRYYEAR) VALUES
(seqCustomerOrders.NextVal,'10-JUN-01',1,
1,'Cathy Crean','63 Windsor Drive',null,
'Shelton','06484',3,'CATHERINE CREAN',
'8776-65-6554-76',3,7,2002);

INSERT INTO CustomerOrders (OrderID,ORDERDATE ,CUSTOMERID,
ORDERSTATUSID,SHIPTONAME,SHIPTOADDRESS1,SHIPTOADDRESS2,
SHIPTOCITY,SHIPTOPOSTALCODE,ORDERSOURCEID,CREDITCARDHOLDER,
CREDITCARDNUMBER,CREDITCARDTYPEID,CREDITCARDEXPIRYMONTH,
CREDITCARDEXPIRYYEAR) VALUES
(seqCustomerOrders.NextVal,'13-JUL-01',2,2,'Stephen Davis',
'77 West Park Ave',null,'Trumbull','06628',2,'STEPHEN DAVIS',
'3299-87-9887',1,10,2003);