database sql

sample.sql

-- START -- SETUP Create user CREATE USER tester1 IDENTIFIED BY Password11 DEFAULT TABLESPACE USERS QUOTA 10M ON USERS; -- Allow user to connect GRANT CREATE SESSION TO tester1; -- All user basic table privileges -- GRANT resource to tester1; -- Specific privileges GRANT CREATE TABLE TO tester1; GRANT CREATE VIEW TO tester1; GRANT SELECT ANY TABLE TO tester1; GRANT UPDATE ANY TABLE TO tester1; GRANT INSERT ANY TABLE TO tester1; GRANT DROP ANY TABLE TO tester1; -- CONNECT CONNECT tester1/Password11; -- DROP TABLES BEGIN EXECUTE IMMEDIATE 'DROP TABLE ORDER_LINES'; EXCEPTION WHEN OTHERS THEN NULL; END; / BEGIN EXECUTE IMMEDIATE 'DROP TABLE ORDERS'; EXCEPTION WHEN OTHERS THEN NULL; END; / BEGIN EXECUTE IMMEDIATE 'DROP TABLE MUSIC_PRODUCTS'; EXCEPTION WHEN OTHERS THEN NULL; END; / BEGIN EXECUTE IMMEDIATE 'DROP TABLE CUSTOMERS'; EXCEPTION WHEN OTHERS THEN NULL; END; / BEGIN EXECUTE IMMEDIATE 'DROP VIEW VIEW_PRODUCTS'; EXCEPTION WHEN OTHERS THEN NULL; END; / BEGIN EXECUTE IMMEDIATE 'DROP VIEW VIEW_ORDER_INFO'; EXCEPTION WHEN OTHERS THEN NULL; END; / COMMIT; -- CREATE TABLES -- CREATE CUSTOMERS TABLE CREATE TABLE CUSTOMERS ( CUSTOMER_ID INT, FNAME VARCHAR2(20), LNAME VARCHAR2(25), EMAIL VARCHAR(50) NOT NULL UNIQUE, CCARD_NUM VARCHAR2(80), ED_EXPIRE_DAY CHAR(2), EM_EXPIRE_MON CHAR(2), EY_EXPIRE_YR CHAR(4), STREET VARCHAR2(50), CITY VARCHAR2(20), STATE_CUST CHAR(2), ZIPCODE INT, PRIMARY KEY (CUSTOMER_ID), CHECK (ED_EXPIRE_DAY BETWEEN 1 AND 31), CHECK (EM_EXPIRE_MON BETWEEN 1 AND 12), CHECK (EY_EXPIRE_YR > 2015) ); -- CREATE MUSIC_PRODUCTS TABLE CREATE TABLE MUSIC_PRODUCTS ( PRODUCT_ID INT, PRODUCT_NAME VARCHAR2(30), DESCRIPTION VARCHAR2(150), ONHAND INT, PRICE NUMBER(6,2), PRIMARY KEY (PRODUCT_ID), CHECK (PRICE >= 0), CHECK (ONHAND >= 0) ); -- CREATE ORDERS TABLE CREATE TABLE ORDERS ( ORDER_ID INT, ORDER_DATETIME TIMESTAMP, CUSTOMER_ID INT, PRIMARY KEY (ORDER_ID), FOREIGN KEY (CUSTOMER_ID) REFERENCES CUSTOMERS(CUSTOMER_ID) ); -- CREATE ORDER_LINES TABLE CREATE TABLE ORDER_LINES ( ORDER_LINE_ID INT, ORDER_ID INT, PRODUCT_ID INT, NUM_ORDER INT, PRIMARY KEY (ORDER_LINE_ID), FOREIGN KEY (ORDER_ID) REFERENCES ORDERS(ORDER_ID), FOREIGN KEY (PRODUCT_ID) REFERENCES MUSIC_PRODUCTS(PRODUCT_ID) ); COMMIT; -- DESC ALL TABLES DESC CUSTOMERS; DESC MUSIC_PRODUCTS; DESC ORDERS; DESC ORDER_LINES; -- INSERT RECORDS -- CUSTOMERS INSERT ALL INTO CUSTOMERS VALUES (1001,'Abe', 'Smith', '[email protected]', '1234123412341234', 10, 2, 2017, '123 A Street', 'Palma', 'FL', 12345) INTO CUSTOMERS VALUES (1002,'Bob', 'Thomas', '[email protected]', '1233123312331244', 21, 5, 2017, '12th B Court', 'Orlandio', 'FL', 21223) INTO CUSTOMERS VALUES (1003,'Mark', 'Fisher', '[email protected]', '1111333322224444', 1, 2, 2018, '45th street south', 'Charlot', 'NC', 33212) INTO CUSTOMERS VALUES (1004,'Alice', 'Gomez', '[email protected]', '4444555536365757', 9, 3, 2019, '5th Blvd East', 'Talma', 'NC', 44244) INTO CUSTOMERS VALUES (1005,'Jill', 'Smith', '[email protected]', '1234123412341234', 10, 2, 2017, '123 A Street', 'Henderson', 'MD', 54345) INTO CUSTOMERS VALUES (1006,'Sandy', 'Blondie', '[email protected]', '2222111121213333', 23, 12, 2019, '22nd Ave West', 'Sunnindale', 'CA', 23234) SELECT 1 FROM DUAL; COMMIT; -- MUSIC_PRODUCTS INSERT ALL INTO MUSIC_PRODUCTS VALUES (100,'Electric Guitar', 'A solid wood electric guitar made by Gibson', 5, 1299.99) INTO MUSIC_PRODUCTS VALUES (101,'Acoustic Guitar', 'A solid wood Acoustic guitar made by Yamaha', 10, 599.99) INTO MUSIC_PRODUCTS VALUES (102,'Snare', 'A popular snare drum made by ziljian', 7, 299.99) INTO MUSIC_PRODUCTS VALUES (103,'Bass Guitar', 'A solid wood electric bass guitar made by Peavy', 4, 899.99) INTO MUSIC_PRODUCTS VALUES (104,'Amplifier', 'A solid wood caninet with 2 speakers. Very loud and EXtreme!!', 12, 799.99) SELECT 1 FROM DUAL; COMMIT; -- ORDERS INSERT ALL INTO ORDERS VALUES (1, TIMESTAMP '2015-12-20 12:32:00', 1001) INTO ORDERS VALUES (2, TIMESTAMP '2015-12-24 02:15:30', 1003) INTO ORDERS VALUES (3, TIMESTAMP '2016-01-02 15:24:45', 1003) INTO ORDERS VALUES (4, TIMESTAMP '2016-01-15 16:05:41', 1004) INTO ORDERS VALUES (5, TIMESTAMP '2016-01-21 08:42:36', 1002) SELECT 1 FROM DUAL; COMMIT; -- ORDER_LINES INSERT ALL INTO ORDER_LINES VALUES (51, 1, 103, 1) INTO ORDER_LINES VALUES (52, 2, 102, 2) INTO ORDER_LINES VALUES (53, 2, 100, 2) INTO ORDER_LINES VALUES (54, 2, 104, 1) INTO ORDER_LINES VALUES (55, 5, 101, 1) SELECT 1 FROM DUAL; COMMIT; -- SELECT TEST SET LINESIZE 350 -- SELECT * FROM CUSTOMERS; -- SELECT * FROM MUSIC_PRODUCTS; -- SELECT * FROM ORDERS; -- SELECT * FROM ORDER_LINES; -- CREATE VIEW CREATE VIEW VIEW_ORDER_INFO AS SELECT ORDERS.ORDER_ID, C.CUSTOMER_ID, C.FNAME, C.LNAME, MP.PRODUCT_NAME, OLINE.NUM_ORDER, MP.PRICE AS PRICE_PER FROM CUSTOMERS C, MUSIC_PRODUCTS MP, ORDERS, ORDER_LINES OLINE WHERE C.CUSTOMER_ID = ORDERS.CUSTOMER_ID AND ORDERS.ORDER_ID = OLINE.ORDER_ID AND OLINE.PRODUCT_ID = MP.PRODUCT_ID AND ORDERS.ORDER_ID = 2 ; COMMIT; -- SELECT STATEMENTS 7 TOTAL -- 0 TEST VIEW SELECT * FROM VIEW_ORDER_INFO; -- 1 GET PRODUCT TOTAL FROM VIEW SELECT PRODUCT_NAME, SUM(NUM_ORDER * PRICE_PER) AS PRODUCT_TOTAL FROM VIEW_ORDER_INFO GROUP BY PRODUCT_NAME; -- 2 GET ORDER TOTAL FROM VIEW SELECT SUM(NUM_ORDER * PRICE_PER) AS ORDER_TOTAL FROM VIEW_ORDER_INFO; -- 3 GET ALL ORDERS SELECT C.CUSTOMER_ID, C.FNAME, C.LNAME, ORDERS.* FROM CUSTOMERS C, ORDERS WHERE C.CUSTOMER_ID = ORDERS.CUSTOMER_ID ORDER BY ORDERS.ORDER_ID; -- 4 GET ORDERS FROM CUSTTOMER_ID = 1003, TO SHOW MULTIPLE ORDERS FROM SAME CUSTOMER SELECT C.CUSTOMER_ID, C.FNAME, C.LNAME, ORDERS.* FROM CUSTOMERS C, ORDERS WHERE C.CUSTOMER_ID = ORDERS.CUSTOMER_ID AND ORDERS.ORDER_ID IN (SELECT ORDER_ID FROM ORDERS WHERE C.CUSTOMER_ID = 1003) ORDER BY ORDERS.ORDER_ID; -- 5 GET ALL ORDER TOTALS PER EACH ORDER IN DATABASE SELECT ORDERS.ORDER_ID, SUM(OLINE.NUM_ORDER * MP.PRICE) AS ORDER_TOTAL FROM MUSIC_PRODUCTS MP, ORDERS, ORDER_LINES OLINE WHERE ORDERS.ORDER_ID = OLINE.ORDER_ID AND OLINE.PRODUCT_ID = MP.PRODUCT_ID GROUP BY ORDERS.ORDER_ID ; -- SET LINESIZE SET LINESIZE 800 -- 6 GET ALL NON REPEATING COLUMNS ACROSS ALL TABLES TO SHOW INFORMATION REGARDING EACH CUSTOMER SELECT C.*, MP.*, ORDERS.ORDER_ID, ORDERS.ORDER_DATETIME, OLINE.ORDER_LINE_ID, OLINE.NUM_ORDER FROM CUSTOMERS C, MUSIC_PRODUCTS MP, ORDERS, ORDER_LINES OLINE WHERE C.CUSTOMER_ID = ORDERS.CUSTOMER_ID AND ORDERS.ORDER_ID = OLINE.ORDER_ID AND OLINE.PRODUCT_ID = MP.PRODUCT_ID; -- END