SQL project 1

profilelvlupnow
project_1_ddl.txt

DROP VIEW CUSTOMER_UNSHIPPED_PRODUCTS; DROP TABLE ORDER_ITEMS; DROP TABLE ORDERS; DROP TABLE SHOPPING_CART_ITEMS; DROP TABLE CUSTOMERS; DROP TABLE INVENTORY_ITEMS; DROP TABLE PRODUCT_FEATURES; DROP TABLE PRODUCTS; DROP TABLE CATEGORIES; CREATE TABLE CATEGORIES ( ID NUMBER PRIMARY KEY, Name VARCHAR2(20) NOT NULL); CREATE TABLE PRODUCTS ( ID NUMBER PRIMARY KEY, CatID NUMBER, Name VARCHAR2(20) NOT NULL, Price NUMBER NOT NULL, FOREIGN KEY (CatID) REFERENCES CATEGORIES(ID)); CREATE TABLE PRODUCT_FEATURES ( ProdID NUMBER NOT NULL, Name VARCHAR2(20) NOT NULL, Value VARCHAR(50) NOT NULL, FOREIGN KEY (ProdID) REFERENCES PRODUCTS(ID)); CREATE TABLE INVENTORY_ITEMS ( ProdID NUMBER PRIMARY KEY, Quantity NUMBER NOT NULL, FOREIGN KEY (ProdID) REFERENCES PRODUCTS(ID)); CREATE TABLE CUSTOMERS ( ID NUMBER PRIMARY KEY, Name VARCHAR2(50) NOT NULL, Email VARCHAR2(50)); CREATE TABLE SHOPPING_CART_ITEMS ( CustID NUMBER NOT NULL, ProdID NUMBER NOT NULL, Quantity NUMBER NOT NULL, WishList CHAR(1) DEFAULT 'N' CHECK (WishList IN ('Y','N')), LastUpdate DATE DEFAULT SYSDATE, PRIMARY KEY (CustID, ProdID), FOREIGN KEY (CustID) REFERENCES CUSTOMERS(ID), FOREIGN KEY (ProdID) REFERENCES PRODUCTS(ID)); CREATE TABLE ORDERS ( ID NUMBER PRIMARY KEY, CustID NUMBER NOT NULL, DatePlaced DATE DEFAULT SYSDATE, FOREIGN KEY (CustID) REFERENCES CUSTOMERS(ID)); CREATE TABLE ORDER_ITEMS ( OrderID NUMBER NOT NULL, ProdID NUMBER NOT NULL, Quantity NUMBER DEFAULT 1, DateShipped DATE, FOREIGN KEY (OrderID) REFERENCES ORDERS(ID), FOREIGN KEY (ProdID) REFERENCES PRODUCTS(ID)); INSERT INTO CATEGORIES VALUES (1, 'Books'); INSERT INTO CATEGORIES VALUES (2, 'Electronics'); INSERT INTO CATEGORIES VALUES (3, 'Outdoors'); INSERT INTO PRODUCTS VALUES (1, 1, 'The Lost Years', 15); INSERT INTO PRODUCT_FEATURES VALUES (1, 'Author', 'Mary Higgings Clark'); INSERT INTO PRODUCT_FEATURES VALUES (1, 'Category', 'Suspense'); INSERT INTO PRODUCTS VALUES (2, 1, 'iPhone Development', 24); INSERT INTO PRODUCT_FEATURES VALUES (2, 'Author', 'David Mark'); INSERT INTO PRODUCT_FEATURES VALUES (2, 'Author', 'Jack Nutting'); INSERT INTO PRODUCT_FEATURES VALUES (2, 'Author', 'Jeff LaMarche'); INSERT INTO PRODUCT_FEATURES VALUES (2, 'Category', 'Technology'); INSERT INTO PRODUCTS VALUES (3, 1, 'A Raisin in the Sun', 11); INSERT INTO PRODUCT_FEATURES VALUES (3, 'Author', 'Lorraine Hansberry'); INSERT INTO PRODUCT_FEATURES VALUES (3, 'Category', 'Drama'); INSERT INTO PRODUCTS VALUES (4, 1, 'Lone Wolf', 17); INSERT INTO PRODUCT_FEATURES VALUES (4, 'Author', 'Jodi Picoult'); INSERT INTO PRODUCT_FEATURES VALUES (4, 'Category', 'Contemporary'); INSERT INTO PRODUCTS VALUES (5, 1, 'Harry Potter', 50); INSERT INTO PRODUCT_FEATURES VALUES (5, 'Author', 'J.K. Rowling'); INSERT INTO PRODUCT_FEATURES VALUES (5, 'Category', 'Children'); INSERT INTO PRODUCTS VALUES (6, 2, 'USB Flash Drive', 25); INSERT INTO PRODUCT_FEATURES VALUES (6, 'Brand', 'Kingston'); INSERT INTO PRODUCT_FEATURES VALUES (6, 'Capacity', '16 GB'); INSERT INTO PRODUCTS VALUES (7, 2, 'Laptop Mouse', 35); INSERT INTO PRODUCT_FEATURES VALUES (7, 'Brand', 'Logitech'); INSERT INTO PRODUCT_FEATURES VALUES (7, 'Connection', 'Wireless'); INSERT INTO PRODUCTS VALUES (8, 2, 'Laser Printer', 250); INSERT INTO PRODUCT_FEATURES VALUES (8, 'Brand', 'HP'); INSERT INTO PRODUCT_FEATURES VALUES (8, 'Print Speed', '15 ppm'); INSERT INTO PRODUCT_FEATURES VALUES (8, 'Color', 'Yes'); INSERT INTO PRODUCTS VALUES (9, 2, 'Speaker System', 65); INSERT INTO PRODUCT_FEATURES VALUES (9, 'Brand', 'Logitech'); INSERT INTO PRODUCT_FEATURES VALUES (9, 'Power', '200 W'); INSERT INTO PRODUCTS VALUES (10, 2, 'Broadband Router', 75); INSERT INTO PRODUCT_FEATURES VALUES (10, 'Brand', 'Linksys'); INSERT INTO PRODUCT_FEATURES VALUES (10, 'Number of Ports', '4'); INSERT INTO PRODUCT_FEATURES VALUES (10, 'Connection', 'Wireless'); INSERT INTO PRODUCTS VALUES (11, 3, 'Student Backpack', 35); INSERT INTO PRODUCT_FEATURES VALUES (11, 'Capacity', '31 liters'); INSERT INTO PRODUCT_FEATURES VALUES (11, 'Color', 'Black'); INSERT INTO PRODUCT_FEATURES VALUES (11, 'Brand', 'Jan Sport'); INSERT INTO PRODUCTS VALUES (12, 3, 'Flash Light', 80); INSERT INTO PRODUCT_FEATURES VALUES (12, 'Weight', '6.2 onces'); INSERT INTO PRODUCT_FEATURES VALUES (12, 'LifeTime', '100000 hours'); INSERT INTO PRODUCT_FEATURES VALUES (12, 'Waterproof', 'Yes'); INSERT INTO PRODUCTS VALUES (13, 3, 'Men''s Jacket', 99); INSERT INTO PRODUCT_FEATURES VALUES (13, 'Brand', 'Columbia'); INSERT INTO PRODUCT_FEATURES VALUES (13, 'Color', 'Red'); INSERT INTO PRODUCT_FEATURES VALUES (13, 'Waterproof', 'No'); INSERT INTO PRODUCTS VALUES (14, 3, 'Binocular', 60); INSERT INTO PRODUCT_FEATURES VALUES (14, 'Brand', 'Celestron'); INSERT INTO PRODUCT_FEATURES VALUES (14, 'Magnification', '8x'); INSERT INTO PRODUCTS VALUES (15, 3, 'Swiss Army Knife', 20); INSERT INTO PRODUCT_FEATURES VALUES (15, 'Number of Blades', '5'); INSERT INTO PRODUCT_FEATURES VALUES (15, 'Size', '2 1/4 inches'); INSERT INTO INVENTORY_ITEMS VALUES (1, 10); INSERT INTO INVENTORY_ITEMS VALUES (2, 15); INSERT INTO INVENTORY_ITEMS VALUES (3, 5); INSERT INTO INVENTORY_ITEMS VALUES (4, 25); INSERT INTO INVENTORY_ITEMS VALUES (5, 30); INSERT INTO INVENTORY_ITEMS VALUES (6, 10); INSERT INTO INVENTORY_ITEMS VALUES (7, 15); INSERT INTO INVENTORY_ITEMS VALUES (8, 20); INSERT INTO INVENTORY_ITEMS VALUES (9, 25); INSERT INTO INVENTORY_ITEMS VALUES (10, 5); INSERT INTO INVENTORY_ITEMS VALUES (11, 3); INSERT INTO INVENTORY_ITEMS VALUES (12, 5); INSERT INTO INVENTORY_ITEMS VALUES (13, 12); INSERT INTO INVENTORY_ITEMS VALUES (14, 15); INSERT INTO INVENTORY_ITEMS VALUES (15, 9); INSERT INTO CUSTOMERS VALUES (1, 'Johh M Newton', '[email protected]'); INSERT INTO CUSTOMERS VALUES (2, 'David S Harrington', '[email protected]'); INSERT INTO CUSTOMERS VALUES (3, 'Bob R Hume', '[email protected]'); INSERT INTO CUSTOMERS VALUES (4, 'Jeff A Newman', '[email protected]'); INSERT INTO CUSTOMERS VALUES (5, 'Bill G Davidson', '[email protected]'); INSERT INTO SHOPPING_CART_ITEMS VALUES (1, 1, 1, 'Y', '31-MAR-2012'); INSERT INTO SHOPPING_CART_ITEMS VALUES (1, 6, 2, NULL, '25-MAR-2012'); INSERT INTO SHOPPING_CART_ITEMS VALUES (1, 12, 1, NULL, '10-MAR-2012'); INSERT INTO SHOPPING_CART_ITEMS VALUES (2, 3, 1, 'Y', '30-MAR-2012'); INSERT INTO SHOPPING_CART_ITEMS VALUES (2, 9, 1, NULL, '22-MAR-2012'); INSERT INTO SHOPPING_CART_ITEMS VALUES (2, 11, 4, NULL, '17-MAR-2012'); INSERT INTO SHOPPING_CART_ITEMS VALUES (2, 14, 2, NULL, '12-MAR-2012'); INSERT INTO SHOPPING_CART_ITEMS VALUES (3, 2, 2, 'N', '29-MAR-2012'); INSERT INTO SHOPPING_CART_ITEMS VALUES (3, 7, 1, NULL, '21-MAR-2012'); INSERT INTO SHOPPING_CART_ITEMS VALUES (3, 3, 1, 'Y', '9-MAR-2012'); INSERT INTO SHOPPING_CART_ITEMS VALUES (3, 12, 2, NULL, '18-MAR-2012'); INSERT INTO SHOPPING_CART_ITEMS VALUES (5, 4, 2, 'N', '24-MAR-2012'); INSERT INTO SHOPPING_CART_ITEMS VALUES (5, 5, 1, NULL, '26-MAR-2012'); INSERT INTO SHOPPING_CART_ITEMS VALUES (5, 8, 2, 'N', '9-MAR-2012'); INSERT INTO SHOPPING_CART_ITEMS VALUES (5, 10, 3, NULL, '11-MAR-2012'); INSERT INTO SHOPPING_CART_ITEMS VALUES (5, 13, 1, NULL, '1-MAR-2012'); INSERT INTO ORDERS VALUES (1, 1, '25-MAR-2012'); INSERT INTO ORDERS VALUES (2, 2, '26-MAR-2012'); INSERT INTO ORDERS VALUES (3, 3, '27-MAR-2012'); INSERT INTO ORDERS VALUES (4, 5, '28-MAR-2012'); INSERT INTO ORDER_ITEMS VALUES (1, 1, 2, '25-MAR-2012'); INSERT INTO ORDER_ITEMS VALUES (1, 3, 1, '25-MAR-2012'); INSERT INTO ORDER_ITEMS VALUES (1, 5, 3, '25-MAR-2012'); INSERT INTO ORDER_ITEMS VALUES (1, 9, 2, '25-MAR-2012'); INSERT INTO ORDER_ITEMS VALUES (2, 11, 1, '27-MAR-2012'); INSERT INTO ORDER_ITEMS VALUES (2, 13, 4, '27-MAR-2012'); INSERT INTO ORDER_ITEMS VALUES (2, 15, 2, '27-MAR-2012'); INSERT INTO ORDER_ITEMS VALUES (3, 2, 1, '28-MAR-2012'); INSERT INTO ORDER_ITEMS VALUES (3, 4, 1, '28-MAR-2012'); INSERT INTO ORDER_ITEMS VALUES (3, 14, 2, '28-MAR-2012'); INSERT INTO ORDER_ITEMS VALUES (3, 7, 2, NULL); INSERT INTO ORDER_ITEMS VALUES (4, 5, 2, '29-MAR-2012'); INSERT INTO ORDER_ITEMS VALUES (4, 8, 2, '29-MAR-2012'); INSERT INTO ORDER_ITEMS VALUES (4, 6, 3, NULL); INSERT INTO ORDER_ITEMS VALUES (4, 9, 2, NULL);