Deutsch English Espanol Francais Italiano Nihongo Nederlands Portuguese

Wine Database System
SQL Relational Database: Table Creation

Database Table Creation

CREATE TABLE CUSTOMER_ZIP_T
(Zip_Code VARCHAR(9) ,
Customer_City VARCHAR(25) ,
Customer_State VARCHAR(2) ,
CONSTRAINT CUSTOMER_ZIP_PK PRIMARY KEY (Zip_Code));

CREATE TABLE CUSTOMER_T
(Customer_Id NUMBER NOT NULL,
Customer_Name VARCHAR(25) ,
Customer_Email VARCHAR(40) ,
DOB DATE ,
Customer_Address VARCHAR(30) ,
Zip_Code VARCHAR(9) ,
CONSTRAINT CUSTOMER_PK PRIMARY KEY (Customer_Id),
CONSTRAINT CUSTOMER_FK FOREIGN KEY (Zip_Code) REFERENCES CUSTOMER_ZIP_T (Zip_Code));

CREATE TABLE ORDER_T
(Order_ID VARCHAR(25) NOT NULL,
Order_Date DATE ,
Customer_ID number ,
CONSTRAINT ORDER_PK PRIMARY KEY (Order_ID),
CONSTRAINT ORDER_FK FOREIGN KEY (Customer_ID) REFERENCES CUSTOMER_T (Customer_ID));

CREATE TABLE PRODUCT_T
(Bar_Code number NOT NULL,
Product_Quantity number,
Varietal VARCHAR(30),
Product_name VARCHAR(30),
Description VARCHAR (300),
Price number,
Alcohol number,
Appellation VARCHAR(50),
Vintage number,
CONSTRAINT PRODUCT_PK PRIMARY KEY (BAR_CODE));

CREATE TABLE ORDER_LINE_T
(Order_Line_ID number NOT NULL,
Bar_Code number,
order_ID VARCHAR(25),
order_quantity number,
CONSTRAINT ORDER_LINE_PK PRIMARY KEY (ORDER_LINE_ID),
CONSTRAINT Order_line_FK1 FOREIGN KEY (Order_Id) REFERENCES ORDER_t(Order_Id),
CONSTRAINT Order_line_FK2 FOREIGN KEY (BAR_CODE) REFERENCES PRODUCT_t(BAR_CODE));

CREATE TABLE PRODUCT_RATING_T
(BAR_CODE NUMBER NOT NULL,
RATING NUMBER,
CONSTRAINT PRODUCT_RATING_PK PRIMARY KEY (BAR_CODE),
CONSTRAINT PRODUCT_RATING_FK FOREIGN KEY (BAR_CODE) REFERENCES PRODUCT_T(BAR_CODE));

CREATE TABLE SUPPLIER_ZIP_T
(Zip_Code VARCHAR(9) ,
SUPPLIER_City VARCHAR(25) ,
SUPPLIER_State VARCHAR(2) ,
CONSTRAINT SUPPLIER_ZIP_PK PRIMARY KEY (Zip_Code));

CREATE TABLE SUPPLIER_T
(SUPPLIER_Id number NOT NULL,
SUPPLIER_Name VARCHAR(25) ,
SUPPLIER_Email VARCHAR(40) ,
SUPPLIER_Address VARCHAR(30) ,
Zip_Code VARCHAR(9) ,
CONSTRAINT SUPPLIER_PK PRIMARY KEY (SUPPLIER_Id),
CONSTRAINT SUPPLIER_FK FOREIGN KEY (Zip_Code) REFERENCES SUPPLIER_ZIP_T (Zip_Code));

CREATE TABLE SHIPMENT_T
(SHIPMENT_ID NUMBER NOT NULL,
SHIPMENT_DATE DATE,
SUPPLIER_ID NUMBER,
CONSTRAINT SHIPMENT_PK PRIMARY KEY (SHIPMENT_ID),
CONSTRAINT SHIPMENT_FK FOREIGN KEY (SUPPLIER_ID) REFERENCES SUPPLIER_T (SUPPLIER_ID));

CREATE TABLE INCLUDES_T
(BAR_CODE NUMBER NOT NULL,
SHIPMENT_ID NUMBER,
CONSTRAINT INCLUDES_PK PRIMARY KEY (BAR_CODE, SHIPMENT_ID),
CONSTRAINT INCLUDES_FK1 FOREIGN KEY (BAR_CODE) REFERENCES PRODUCT_RATING_T (BAR_CODE),
CONSTRAINT INCLUDES_FK2 FOREIGN KEY (SHIPMENT_ID) REFERENCES SHIPMENT_T (SHIPMENT_ID));

Database Data Insertion

INSERT INTO CUSTOMER_ZIP_T (Zip_Code, Customer_City, Customer_State)
VALUES (32601, 'St. Paul', 'VA');
INSERT INTO CUSTOMER_ZIP_T (Zip_Code, Customer_City, Customer_State)
VALUES (94574, 'Hampton', 'NY');
INSERT INTO CUSTOMER_ZIP_T (Zip_Code, Customer_City, Customer_State)
VALUES (92115, 'San Diego', 'CA');
INSERT INTO CUSTOMER_ZIP_T (Zip_Code, Customer_City, Customer_State)
VALUES (26559, 'Los Angeles', 'CA');
INSERT INTO CUSTOMER_ZIP_T (Zip_Code, Customer_City, Customer_State)
VALUES (46887, 'Detroit', 'MI');
INSERT INTO CUSTOMER_ZIP_T (Zip_Code, Customer_City, Customer_State)
VALUES (33668, 'Cleveland', 'OH');
INSERT INTO CUSTOMER_ZIP_T (Zip_Code, Customer_City, Customer_State)
VALUES (11254, 'Sacramento', 'CA');
INSERT INTO CUSTOMER_ZIP_T (Zip_Code, Customer_City, Customer_State)
VALUES (53421, 'Washington', 'WA');
INSERT INTO CUSTOMER_ZIP_T (Zip_Code, Customer_City, Customer_State)
VALUES (23445, 'Fairfield', 'CA');
INSERT INTO CUSTOMER_ZIP_T (Zip_Code, Customer_City, Customer_State)
VALUES (19854, 'Maui', 'HA');
INSERT INTO CUSTOMER_ZIP_T (Zip_Code, Customer_City, Customer_State)
VALUES (56448, 'Springfield', 'MA');
INSERT INTO CUSTOMER_ZIP_T (Zip_Code, Customer_City, Customer_State)
VALUES (61237, 'Boston', 'MA');
INSERT INTO CUSTOMER_ZIP_T (Zip_Code, Customer_City, Customer_State)
VALUES (62738, 'Hometown', 'WI');
INSERT INTO CUSTOMER_ZIP_T (Zip_Code, Customer_City, Customer_State)
VALUES (92345, 'Houston', 'TX');
INSERT INTO CUSTOMER_ZIP_T (Zip_Code, Customer_City, Customer_State)
VALUES (23465, 'Ashland', 'OR');

INSERT INTO CUSTOMER_t (Customer_Id, Customer_Name, Customer_Email, DOB, Customer_Address, Zip_Code)
VALUES (1, 'Greg Banks', 'gbanks@spamail.com', '12/OCT/1972', '2078 W. 17th St.', 32601);
INSERT INTO CUSTOMER_t (Customer_Id, Customer_Name, Customer_Email, DOB, Customer_Address, Zip_Code)
VALUES (2, 'Kelly Slater', 'kslater@spamail.com', '02/NOV/1948', '1955 4th Ave.', 94574);
INSERT INTO CUSTOMER_t (Customer_Id, Customer_Name, Customer_Email, DOB, Customer_Address, Zip_Code)
VALUES (3, 'John Deer', 'jdeer@spamail.com', '10/DEC/1939', '2665 Dove St.', 92115);
INSERT INTO CUSTOMER_t (Customer_Id, Customer_Name, Customer_Email, DOB, Customer_Address, Zip_Code)
VALUES (4, 'Mohammad Oskoorouchi', 'moskoorouchi@spamail.com', '03/SEP/1954', '1514 Castelar St.', 26559);
INSERT INTO CUSTOMER_t (Customer_Id, Customer_Name, Customer_Email, DOB, Customer_Address, Zip_Code)
VALUES (5, 'Monique Johnson', 'mjohnson@spamail.com', '07/FEB/1978', '2054 El Cajon Blvd.', 46887);
INSERT INTO CUSTOMER_t (Customer_Id, Customer_Name, Customer_Email, DOB, Customer_Address, Zip_Code)
VALUES (6, 'Kimihiko Nakamura', 'knakamura@spamail.com', '05/MAY/1974', '188 University Ave.', 33668);
INSERT INTO CUSTOMER_t (Customer_Id, Customer_Name, Customer_Email, DOB, Customer_Address, Zip_Code)
VALUES (7, 'Mason Dixon', 'mdixon@spamail.com', '03/OCT/1767', '6542 Hunt Ave.', 11254);
INSERT INTO CUSTOMER_t (Customer_Id, Customer_Name, Customer_Email, DOB, Customer_Address, Zip_Code)
VALUES (8, 'Fred Cummings', 'fcummings@spamail.com', '10/FEB/1981', '1955 Allyn Ave.', 53421);
INSERT INTO CUSTOMER_t (Customer_Id, Customer_Name, Customer_Email, DOB, Customer_Address, Zip_Code)
VALUES (9, 'Yi Sun', 'ysun@spamail.com', '14/AUG/1962', '1744 Jason St.', 23445);
INSERT INTO CUSTOMER_t (Customer_Id, Customer_Name, Customer_Email, DOB, Customer_Address, Zip_Code)
VALUES (10,'Ernest Wendt', 'ewendt@spamail.com', '03/SEP/1958', '85 Spring St.', 19854);
INSERT INTO CUSTOMER_t (Customer_Id, Customer_Name, Customer_Email, DOB, Customer_Address, Zip_Code)
VALUES (11,'Shaoyi He', 'she@spamail.com', '19/JAN/1962', '85 East Hwy.', 56448);
INSERT INTO CUSTOMER_t (Customer_Id, Customer_Name, Customer_Email, DOB, Customer_Address, Zip_Code)
VALUES (12,'William Burns', 'wburns@spamail.com', '17/JUL/1955', '2233 Space St.', 61237);
INSERT INTO CUSTOMER_t (Customer_Id, Customer_Name, Customer_Email, DOB, Customer_Address, Zip_Code)
VALUES (13,'Jack Leu', 'jleu@spamail.com', '28/JUN/1960', '4652 Mason Blvd.', 62738);
INSERT INTO CUSTOMER_t (Customer_Id, Customer_Name, Customer_Email, DOB, Customer_Address, Zip_Code)
VALUES (14,'Jim Hamerly', 'jhamerly@spamail.com', '29/MAR/1959', '2386 First St.', 92345);
INSERT INTO CUSTOMER_t (Customer_Id, Customer_Name, Customer_Email, DOB, Customer_Address, Zip_Code)
VALUES (15,'Robert Aboolian', 'raboolian@spamail.com', '01/APR/1955', '6687 Second Ave.', 23465);

INSERT INTO ORDER_t (Order_Id, Order_Date, Customer_Id)
VALUES (1001, '21/AUG/2004', 1);
INSERT INTO ORDER_t (Order_Id, Order_Date, Customer_Id)
VALUES (1002, '16/SEP/2004', 8);
INSERT INTO ORDER_t (Order_Id, Order_Date, Customer_Id)
VALUES (1003, '20/SEP/04', 15);
INSERT INTO ORDER_t (Order_Id, Order_Date, Customer_Id)
VALUES (1004, '04/Oct/04', 5);
INSERT INTO ORDER_t (Order_Id, Order_Date, Customer_Id)
VALUES (1005, '10/Oct/04', 3);
INSERT INTO ORDER_t (Order_Id, Order_Date, Customer_Id)
VALUES (1006, '13/Oct/04', 2);
INSERT INTO ORDER_t (Order_Id, Order_Date, Customer_Id)
VALUES (1007, '25/Oct/04', 11);
INSERT INTO ORDER_t (Order_Id, Order_Date, Customer_Id)
VALUES (1008, '30/Oct/04', 12);
INSERT INTO ORDER_t (Order_Id, Order_Date, Customer_Id)
VALUES (1009, '03/Nov/04', 4);
INSERT INTO ORDER_t (Order_Id, Order_Date, Customer_Id)
VALUES (1010, '06/Nov/04', 9);

INSERT INTO PRODUCT_t (Bar_Code, Product_Quantity, Varietal, Product_name, Description, Price, Alcohol, Appellation, Vintage)
VALUES (12113, 48, 'Merlot', 'Dynamite Vineyards Merlot', 'Explosive fruit flavors and intriguing aromas of plums, black cherries and spice.', 15.99, 13.5, 'California', 2001);
INSERT INTO PRODUCT_t (Bar_Code, Product_Quantity, Varietal, Product_name, Description, Price, Alcohol, Appellation, Vintage)
VALUES (23445, 24, 'Sangiovese', 'Louis Martini 1999 Sangiovese', 'Ripe, sweet, plummy nose that mingles with the varietal spice and an oak bouquet of Eastern European woods. Deep strawberry/raspberry fruit with citrus and cashew edges.', 14.99, 13.5, 'California', 1999);
INSERT INTO PRODUCT_t (Bar_Code, Product_Quantity, Varietal, Product_name, Description, Price, Alcohol, Appellation, Vintage)
VALUES (36748, 48, 'Pinot Noir', 'Acacia Beckstoffer Pinot Noir', 'Dark, healthy red. Superripe aromas of bitter cherry, flowers and cola, with a suggestion of aged steak.', 18.99, 12.5, 'Carneros, California', 2002);
INSERT INTO PRODUCT_t (Bar_Code, Product_Quantity, Varietal, Product_name, Description, Price, Alcohol, Appellation, Vintage)
VALUES (46247, 48, 'Chardonnay', 'Amberhill 2002 Chardonnay', 'Wonderful fruit flavors of peach, pear, and apple. Balanced by crisp acidity and soft oak in the finish.', 8.99, 13.3, 'California', 2002);
INSERT INTO PRODUCT_t (Bar_Code, Product_Quantity, Varietal, Product_name, Description, Price, Alcohol, Appellation, Vintage)
VALUES (53125, 12, 'Cabernet Sauvignon', 'Alexander Valley 2002 Cabernet', 'Dark and rich, this is a bottle that delivers.', 13.99, 13.4, 'California', 2002);
INSERT INTO PRODUCT_t (Bar_Code, Product_Quantity, Varietal, Product_name, Description, Price, Alcohol, Appellation, Vintage)
VALUES (66498, 96, 'Rosso', 'Coppola 2002 Rosso', 'Fancy and fruity, oh what a great wine it is.', 7.99, 11.2, 'California', 2002);
INSERT INTO PRODUCT_t (Bar_Code, Product_Quantity, Varietal, Product_name, Description, Price, Alcohol, Appellation, Vintage)
VALUES (71347, 24, 'Sauvignon Blanc', 'Kenwood 2002 Sauvignon Blanc', 'Bright and vibrant, fresh and crisp, boasting tropical fruit, pear and citrus flavors.', 7.99, 13.1, 'Napa Valley', 2002);
INSERT INTO PRODUCT_t (Bar_Code, Product_Quantity, Varietal, Product_name, Description, Price, Alcohol, Appellation, Vintage)
VALUES (83225, 12, 'Zinfandel', 'Buehler 2001 Zinfandel Napa', 'Zincredible! This mid to full-bodied zin has got some nice spice to it and will pair well with grilled foods.', 9.59, 13.8, 'Napa', 2001);

INSERT INTO Order_line_t (Order_Line_ID, Bar_Code, Order_ID, Order_Quantity)
VALUES (001, 12113, '1001', 2);
INSERT INTO Order_line_t (Order_Line_ID, Bar_Code, Order_ID, Order_Quantity)
VALUES (002, 23445, '1001', 1);
INSERT INTO Order_line_t (Order_Line_ID, Bar_Code, Order_ID, Order_Quantity)
VALUES (003, 36748, '1002', 1);
INSERT INTO Order_line_t (Order_Line_ID, Bar_Code, Order_ID, Order_Quantity)
VALUES (004, 12113, '1003', 3);
INSERT INTO Order_line_t (Order_Line_ID, Bar_Code, Order_ID, Order_Quantity)
VALUES (005, 46247, '1004', 6);
INSERT INTO Order_line_t (Order_Line_ID, Bar_Code, Order_ID, Order_Quantity)
VALUES (006, 12113, '1004', 2);
INSERT INTO Order_line_t (Order_Line_ID, Bar_Code, Order_ID, Order_Quantity)
VALUES (007, 23445, '1004', 1);
INSERT INTO Order_line_t (Order_Line_ID, Bar_Code, Order_ID, Order_Quantity)
VALUES (008, 46247, '1005', 1);
INSERT INTO Order_line_t (Order_Line_ID, Bar_Code, Order_ID, Order_Quantity)
VALUES (009, 83225, '1006', 1);
INSERT INTO Order_line_t (Order_Line_ID, Bar_Code, Order_ID, Order_Quantity)
VALUES (010, 83225, '1007', 4);
INSERT INTO Order_line_t (Order_Line_ID, Bar_Code, Order_ID, Order_Quantity)
VALUES (011, 12113, '1007', 6);
INSERT INTO Order_line_t (Order_Line_ID, Bar_Code, Order_ID, Order_Quantity)
VALUES (012, 12113, '1007', 1);
INSERT INTO Order_line_t (Order_Line_ID, Bar_Code, Order_ID, Order_Quantity)
VALUES (013, 46247, '1008', 1);
INSERT INTO Order_line_t (Order_Line_ID, Bar_Code, Order_ID, Order_Quantity)
VALUES (014, 23445, '1008', 2);
INSERT INTO Order_line_t (Order_Line_ID, Bar_Code, Order_ID, Order_Quantity)
VALUES (015, 71347, '1009', 3);
INSERT INTO Order_line_t (Order_Line_ID, Bar_Code, Order_ID, Order_Quantity)
VALUES (016, 36748, '1009', 3);
INSERT INTO Order_line_t (Order_Line_ID, Bar_Code, Order_ID, Order_Quantity)
VALUES (017, 53125, '1010', 1);
INSERT INTO Order_line_t (Order_Line_ID, Bar_Code, Order_ID, Order_Quantity)
VALUES (018, 71347, '1010', 2);

INSERT INTO PRODUCT_RATING_t (Bar_Code, Rating)
VALUES (12113, 3);
INSERT INTO PRODUCT_RATING_t (Bar_Code, Rating)
VALUES (23445, 5);
INSERT INTO PRODUCT_RATING_t (Bar_Code, Rating)
VALUES (36748, 3);
INSERT INTO PRODUCT_RATING_t (Bar_Code, Rating)
VALUES (46247, 5);
INSERT INTO PRODUCT_RATING_t (Bar_Code, Rating)
VALUES (53125, 4);
INSERT INTO PRODUCT_RATING_t (Bar_Code, Rating)
VALUES (66498, 4);
INSERT INTO PRODUCT_RATING_t (Bar_Code, Rating)
VALUES (71347, 5);
INSERT INTO PRODUCT_RATING_t (Bar_Code, Rating)
VALUES (83225, 5);
INSERT INTO SUPPLIER_ZIP_t (Zip_Code, Supplier_City, Supplier_State)
VALUES ('91111', 'National City', 'CA');
INSERT INTO SUPPLIER_ZIP_t (Zip_Code, Supplier_City, Supplier_State)
VALUES ('91911', 'Chula Vista', 'CA');
INSERT INTO SUPPLIER_ZIP_t (Zip_Code, Supplier_City, Supplier_State)
VALUES ('92107', 'El Cajon', 'CA');
INSERT INTO SUPPLIER_ZIP_t (Zip_Code, Supplier_City, Supplier_State)
VALUES ('99210', 'San Diego', 'CA');
INSERT INTO SUPPLIER_ZIP_t (Zip_Code, Supplier_City, Supplier_State)
VALUES ('99213', 'San Diego', 'CA');

INSERT INTO SUPPLIER_t (Supplier_ID, Supplier_Name, Supplier_Email, Supplier_Address, Zip_Code)
VALUES (1, 'Steve Wentzel', 'steve@hotmail.com', '3375 Naple st.', 99213);
INSERT INTO SUPPLIER_t (Supplier_ID, Supplier_Name, Supplier_Email, Supplier_Address, Zip_Code)
VALUES (2, 'Jeff Hanks', 'jh@hotmail.com', '1125 Palm St.', 99210);
INSERT INTO SUPPLIER_t (Supplier_ID, Supplier_Name, Supplier_Email, Supplier_Address, Zip_Code)
VALUES (3, 'Mike Metroyanis', 'mike@yahoo.com', '556 L St.', 92107);
INSERT INTO SUPPLIER_t (Supplier_ID, Supplier_Name, Supplier_Email, Supplier_Address, Zip_Code)
VALUES (4, 'Wilman Banks', 'wilman@gmail.com', '25116 Fletcher Dr.', 91911);
INSERT INTO SUPPLIER_t (Supplier_ID, Supplier_Name, Supplier_Email, Supplier_Address, Zip_Code)
VALUES (5, 'Peter Chestfield', 'Peter@hotmail.com', '4213 Broadway', 91111);

INSERT INTO SHIPMENT_t (Shipment_ID, Shipment_Date, Supplier_ID)
VALUES (01, '05/AUG/2004', 3);
INSERT INTO SHIPMENT_t (Shipment_ID, Shipment_Date, Supplier_ID)
VALUES (02, '26/AUG/2004', 1);
INSERT INTO SHIPMENT_t (Shipment_ID, Shipment_Date, Supplier_ID)
VALUES (03, '01/SEP/2004', 5);
INSERT INTO SHIPMENT_t (Shipment_ID, Shipment_Date, Supplier_ID)
VALUES (04, '15/SEP/2004', 2);
INSERT INTO SHIPMENT_t (Shipment_ID, Shipment_Date, Supplier_ID)
VALUES (05, '20/SEp/2004', 1);
INSERT INTO SHIPMENT_t (Shipment_ID, Shipment_Date, Supplier_ID)
VALUES (06, '08/OCT/2004', 4);
INSERT INTO SHIPMENT_t (Shipment_ID, Shipment_Date, Supplier_ID)
VALUES (07, '11/OCT/2004', 5);
INSERT INTO SHIPMENT_t (Shipment_ID, Shipment_Date, Supplier_ID)
VALUES (08, '12/OCT/2004', 2);
INSERT INTO SHIPMENT_t (Shipment_ID, Shipment_Date, Supplier_ID)
VALUES (09, '08/NOV/2004', 1);
INSERT INTO SHIPMENT_t (Shipment_ID, Shipment_Date, Supplier_ID)
VALUES (10, '30/NOV/2004', 3);

INSERT INTO INCLUDES_t (Bar_Code, Shipment_ID)
VALUES (12113, 01);
INSERT INTO INCLUDES_t (Bar_Code, Shipment_ID)
VALUES (12113, 04);
INSERT INTO INCLUDES_t (Bar_Code, Shipment_ID)
VALUES (23445, 10);
INSERT INTO INCLUDES_t (Bar_Code, Shipment_ID)
VALUES (36748, 09);
INSERT INTO INCLUDES_t (Bar_Code, Shipment_ID)
VALUES (46247, 08);
INSERT INTO INCLUDES_t (Bar_Code, Shipment_ID)
VALUES (53125, 05);
INSERT INTO INCLUDES_t (Bar_Code, Shipment_ID)
VALUES (66498, 02);
INSERT INTO INCLUDES_t (Bar_Code, Shipment_ID)
VALUES (71347, 07);
INSERT INTO INCLUDES_t (Bar_Code, Shipment_ID)
VALUES (83225, 03);
INSERT INTO INCLUDES_t (Bar_Code, Shipment_ID)
VALUES (83225, 06);

< Previous Next >

Related

Calculate Lifetime Value of a Customer
Ecommerce Shopping Cart Software

Smart Home Technology
Leapfrog and Powertouch
How to set up an Internet Radio Station
Pressure Sensitive Label Waste

Topics

Tech Business Computer Engineering Electrical Engineering Selected Topics

© Copyright 2005 Castelarhost.com