/* ADV SQL - SQL EXAMPLES 
 ADVSQL-TEXT.TXT
 These file contains sql commands used in the textbook.
 Theachers and students can use this file to "copy and paste"
 the SQL commands from this file to the "SQL>" command prompt (Oracle SQL Plus).
 This way, the class looses less time in typing (and error corrections!).
 Please be aware that date formats may differ among DBMS programs.
 See the tables in chapter 8 for the equivalent functions in each DBMS.
*/

/* NOTE TO ORACLE USERS:  
   You can use the following lines to set some columns display format options */

SET PAGESIZE 60
SET LINESIZE 132
 
COLUMN P_PRICE FORMAT 999.99
COLUMN P_DESCRIPT FORMAT A30 TRUNCATE
COLUMN V_NAME FORMAT A12 TRUNCATE

-- === JOINS

SELECT P_CODE, P_DESCRIPT, P_PRICE, V_NAME
FROM   PRODUCT, VENDOR
WHERE  PRODUCT.V_CODE = VENDOR.V_CODE;

-- --- CROSS JOINS (PRODUCT)
SELECT * FROM INVOICE CROSS JOIN LINE;

SELECT INVOICE.INV_NUMBER, CUS_CODE, INV_DATE, P_CODE
	FROM INVOICE CROSS JOIN LINE;

SELECT INVOICE.INV_NUMBER, CUS_CODE, INV_DATE, P_CODE
	FROM INVOICE, LINE;

-- --- NATURAL JOIN 
-- DOES NOT REQUIRE TABLE QUALIFIER

SELECT CUS_CODE, CUS_LNAME, INV_NUMBER, INV_DATE
FROM CUSTOMER NATURAL JOIN INVOICE;

SELECT INV_NUMBER, P_CODE, P_DESCRIPT, LINE_UNITS, LINE_PRICE
FROM INVOICE NATURAL JOIN LINE NATURAL JOIN PRODUCT;

-- --- JOIN USING (Not available in SQL Server or MS Access)

SELECT INV_NUMBER, P_CODE, P_DESCRIPT, LINE_UNITS, LINE_PRICE
FROM INVOICE JOIN LINE USING (INV_NUMBER) 
	JOIN PRODUCT USING (P_CODE);

-- --- JOIN ON 

SELECT INVOICE.INV_NUMBER, PRODUCT.P_CODE, P_DESCRIPT, LINE_UNITS, LINE_PRICE
FROM INVOICE JOIN LINE ON INVOICE.INV_NUMBER = LINE.INV_NUMBER
             JOIN PRODUCT ON LINE.P_CODE = PRODUCT.P_CODE;

SELECT E.EMP_MGR, M.EMP_LNAME, E.EMP_NUM, E.EMP_LNAME
FROM EMP E JOIN EMP M ON E.EMP_MGR = M.EMP_NUM
ORDER BY E.EMP_MGR;

-- --- OUTER JOINS
-- --- LEFT OUTER JOIN

SELECT P_CODE, VENDOR.V_CODE, V_NAME
FROM VENDOR LEFT JOIN PRODUCT ON VENDOR.V_CODE = PRODUCT.V_CODE;

-- --- RIGHT OUTER JOIN

SELECT P_CODE, VENDOR.V_CODE, V_NAME
FROM VENDOR RIGHT JOIN PRODUCT ON VENDOR.V_CODE = PRODUCT.V_CODE;

-- --- FULL OUTER JOIN

SELECT P_CODE, VENDOR.V_CODE, V_NAME
FROM VENDOR FULL JOIN PRODUCT ON VENDOR.V_CODE = PRODUCT.V_CODE;


-- === SUB-QUERIES
-- === EXAMPLE OF TYPICAL JOIN 
SELECT INV_NUMBER, INOVICE.CUS_CODE, CUS_LNAME, CUS_FNAME
FROM   CUSTOMER, INVOICE
WHERE  CUSTOMER.CUS_CODE = INVOICE.CUS_CODE;

-- SOME TIMES WE NEED TO PROCESS DATA BASED ON OTHER PROCESSED DATA
-- EXAMPLES OF SUB-QUERIES COVERED IN PREVIOUS CHAPTER
-- LIST ALL VENDORS THAT PROVIDE PRODUCTS?

SELECT V_CODE, V_NAME FROM VENDOR
WHERE V_CODE IN (SELECT V_CODE FROM PRODUCT);

-- --- WHERE SUB-QUERIES
-- LIST OF PRODUCTS WITH PRICE >= AVERAGE PRODUCT PRICE?
SELECT P_CODE, P_PRICE FROM PRODUCT
WHERE P_PRICE >= (SELECT AVG(P_PRICE) FROM PRODUCT);

-- LIST ALL CUSTOMERS WHO ORDERED THE PRODUCT "CLAW HAMMER"?
SELECT DISTINCT CUS_CODE, CUS_LNAME, CUS_FNAME
FROM CUSTOMER JOIN INVOICE USING (CUS_CODE)
              JOIN LINE USING (INV_NUMBER)
              JOIN PRODUCT USING (P_CODE)
WHERE P_CODE IN (SELECT P_CODE FROM PRODUCT WHERE P_DESCRIPT = 'Claw hammer');

-- ALTERNATIVE SYNTAX

SELECT DISTINCT CUS_CODE, CUS_LNAME, CUS_FNAME 
FROM CUSTOMER 	JOIN INVOICE USING (CUS_CODE)
		JOIN LINE USING (INV_NUMBER) 
		JOIN PRODUCT USING (P_CODE)
WHERE P_DESCRIPT = 'Claw hammer';


-- --- IN SUB-QUERIES
-- LIST ALL CUSTOMERS THAT PURCHASED ANY TYPE OF HAMMER OR ANY KIND OF SAW OR SAW BLADE?

SELECT DISTINCT CUS_CODE, CUS_LNAME, CUS_FNAME 
FROM CUSTOMER 	JOIN INVOICE USING (CUS_CODE)
		JOIN LINE USING (INV_NUMBER) 
		JOIN PRODUCT USING (P_CODE)
WHERE P_CODE IN (SELECT P_CODE FROM PRODUCT
		 WHERE P_DESCRIPT LIKE '%hammer%' OR P_DESCRIPT LIKE '%saw%');


-- --- HAVING SUB-QUERIES
-- LIST ALL PRODUCTS WITH A TOTAL QTY SOLD GREATER THAN THE AVERAGE QTY SOLD?

SELECT P_CODE, SUM(LINE_UNITS)
FROM LINE
GROUP BY P_CODE
HAVING SUM(LINE_UNITS) > (SELECT AVG(LINE_UNITS) FROM LINE);

-- --- ALL MULTI-ROW OPERAND SUB-QUERIES
-- LIST ALL PRODUCTS WITH A PRODUCT COST GREATER THAN ALL INDIVIDUAL PRODUCT COSTS OF PRODUCTS PROVIDED BY VENDORS IN FLORIDA?

SELECT P_CODE, P_QOH*P_PRICE
FROM PRODUCT
WHERE P_QOH*P_PRICE > ALL 
(SELECT P_QOH*P_PRICE FROM PRODUCT
	WHERE V_CODE IN (SELECT V_CODE FROM VENDOR WHERE V_STATE = 'FL'));

-- --- FROM SUB-QUERIES
-- LIST ALL CUSTOMER WHO PURCHASED PRODUCTS 13-Q2/P2 AND 23109-HB?

SELECT DISTINCT CUSTOMER.CUS_CODE, CUSTOMER.CUS_LNAME 
FROM CUSTOMER, 
(SELECT INVOICE.CUS_CODE 
FROM INVOICE NATURAL JOIN LINE WHERE P_CODE = '13-Q2/P2') CP1, (SELECT INVOICE.CUS_CODE 
FROM INVOICE NATURAL JOIN LINE WHERE P_CODE = '23109-HB') CP2
WHERE CUSTOMER.CUS_CODE = CP1.CUS_CODE AND
	   CP1.CUS_CODE = CP2.CUS_CODE;

-- --- ATTRIBUTE LIST SUB-QUERIES
-- List the the difference between each products price and the average product price

SELECT P_CODE, P_PRICE, (SELECT AVG(P_PRICE) FROM PRODUCT) AS AVGPRICE,
       P_PRICE-(SELECT AVG(P_PRICE) FROM PRODUCT) AS DIFF
FROM PRODUCT;

-- List the product code, total sales by product, and contribution by employee of each product sales?

SELECT P_CODE, SUM(LINE_UNITS*LINE_PRICE) AS SALES, 
       (SELECT COUNT(*) FROM EMPLOYEE) AS ECOUNT, 
       SUM(LINE_UNITS*LINE_PRICE)/(SELECT COUNT(*) FROM EMPLOYEE) AS CONTRIB
FROM LINE 
GROUP BY P_CODE;

OR

SELECT P_CODE, SALES, ECOUNT, SALES/ECOUNT AS CONTRIB
FROM (SELECT P_CODE, SUM(LINE_UNITS*LINE_PRICE) AS SALES,
	    (SELECT COUNT(*) FROM EMPLOYEE) AS ECOUNT
      FROM LINE GROUP BY P_CODE);


-- === CORRELATED QUERIES
-- List all product sales for which the units sold is greater than the average units sold for that product

SELECT INV_NUMBER, P_CODE, LINE_UNITS
FROM LINE LS
WHERE LS.LINE_UNITS > 
	(SELECT AVG(LINE_UNITS) 
		FROM LINE LA
		WHERE LA.P_CODE = LS.P_CODE);

-- Add a correlated in-line sub-query to list the average units sold per product
SELECT INV_NUMBER, P_CODE, LINE_UNITS, 
       (SELECT AVG(LINE_UNITS) FROM LINE LX WHERE LX.P_CODE = LS.P_CODE) AS AVG
FROM LINE LS
WHERE LS.LINE_UNITS >
		 ( SELECT AVG(LINE_UNITS)
			FROM LINE LA
			WHERE LA.P_CODE = LS.P_CODE);

-- CORRELATED QUERY WITH EXISTS
-- List all customers who has made purchases?

SELECT CUS_CODE, CUS_LNAME, CUS_FNAME
FROM CUSTOMER 
WHERE EXISTS (SELECT CUS_CODE FROM INVOICE
			WHERE INVOICE.CUS_CODE = CUSTOMER.CUS_CODE);

-- List all vendors to contact for products with a qty on hand <= double P_MIN?
SELECT V_CODE, V_NAME FROM VENDOR
WHERE EXISTS (
		SELECT * FROM PRODUCT
			WHERE P_QOH<P_MIN*2 
				AND VENDOR.V_CODE = PRODUCT.V_CODE);


-- === SQL FUNCTIONS
-- DATE/TIME FUNCTIONS

-- List all employess born in 1966
-- MS Access and SQL Server
SELECT EMP_LNAME, EMP_FNAME, EMP_DOB, YEAR(EMP_DOB) AS YEAR
FROM EMPLOYEE
WHERE YEAR(EMP_DOB) = 1966;

-- Oracle
SELECT EMP_LNAME, EMP_FNAME, EMP_DOB, TO_CHAR(EMP_DOB,'YYYY') AS YEAR
FROM EMPLOYEE
WHERE TO_CHAR(EMP_DOB,'YYYY') = '1966';


-- List all employess born in November
-- MS Access and SQL Server
SELECT EMP_LNAME, EMP_FNAME, EMP_DOB, MONTH(EMP_DOB) AS MONTH
FROM EMPLOYEE
WHERE MONTH(EMP_DOB)= 11;

-- Oracle
SELECT EMP_LNAME, EMP_FNAME, EMP_DOB, TO_CHAR(EMP_DOB,'MM') AS MONTH
FROM EMPLOYEE
WHERE TO_CHAR(EMP_DOB,'MM') = '11';


-- List all employees born in the 14th day of the month
-- MS Access and SQL Server
SELECT EMP_LNAME, EMP_FNAME, EMP_DOB, DAY(EMP_DOB) AS DAY
FROM EMPLOYEE	
WHERE DAY(EMP_DOB)=14;

-- Oracle
SELECT EMP_LNAME, EMP_FNAME, EMP_DOB, TO_CHAR(EMP_DOB,'DD') AS DAY
FROM EMPLOYEE
WHERE TO_CHAR(EMP_DOB,'DD') = '14';


-- Oracle
-- TO_DATE

-- List the approximate age of the employees on the company's 10th anniversary date (11/25/2014)
SELECT EMP_LNAME, EMP_FNAME, EMP_DOB, '11/25/2016' AS ANIV_DATE, (TO_DATE('11/25/2016','MM/DD/YYYY') - EMP_DOB)/365 AS YEARS 
FROM EMPLOYEE
ORDER BY YEARS;

-- How many days between thanksgiving and Christmas 2014?
SELECT TO_DATE('2016/12/25','YYYY/MM/DD') - TO_DATE('NOVEMBER 25, 2016','MONTH DD, YYYY') 
FROM DUAL;

-- How many days are left to Christmas 2014?--
SELECT TO_DATE('25-Dec-2016','DD-MON-YYYY') - SYSDATE
FROM DUAL;

-- List all products with their expiration date (two years from the purchase date).
SELECT P_CODE, P_INDATE, ADD_MONTHS(P_INDATE,24)
FROM PRODUCT
ORDER BY ADD_MONTHS(P_INDATE,24);

-- List all employees that were hired within the last 7 days of a month.
SELECT EMP_LNAME, EMP_FNAME, EMP_HIRE_DATE
FROM EMPLOYEE
WHERE EMP_HIRE_DATE >= LAST_DAY(EMP_HIRE_DATE)-7;


-- === NUMERIC FUNCTIONS

-- ABS
-- List absolute values
SELECT 1.95, -1.93, ABS(1.95), ABS(-1.93) FROM DUAL;

-- ROUND
-- List the product prices rounded to one and zero decimal places
SELECT P_CODE, P_PRICE, ROUND(P_PRICE,1) AS PRICE1, ROUND(P_PRICE,0) AS PRICE0
FROM PRODUCT;

-- TRUNC
-- List the product price rounded to one and zero decimal places and truncated.
SELECT P_CODE, P_PRICE, 
                ROUND(P_PRICE,1) AS PRICE1,
                ROUND(P_PRICE,0) AS PRICE0,
                TRUNC(P_PRICE,0) AS PRICEX
FROM PRODUCT;

-- CEIL AND FLOOR
-- List the product price, smallest integer greater than or equal to the product price, and the largest integer equal or less than the product price.
SELECT P_PRICE, CEIL(P_PRICE), FLOOR(P_PRICE)
FROM PRODUCT;

-- === STRING FUNCTIONS
-- CONCATENATION
--List all employee names (concatenated)
SELECT EMP_LNAME || ', ' || EMP_FNAME AS NAME
FROM EMPLOYEE;

-- UPPER 
-- List all employee names in all capitals (concatenated)
SELECT UPPER(EMP_LNAME) || ', ' || UPPER(EMP_FNAME) AS NAME
FROM EMPLOYEE;

-- LOWER
-- List all employee names in all lowercase (concatenated)
SELECT LOWER(EMP_LNAME) || ', ' || LOWER(EMP_FNAME) AS NAME
FROM EMPLOYEE;

-- SUBSTR
-- List the first three characters of all employees phone numbers
SELECT EMP_PHONE, SUBSTR(EMP_PHONE,1,3)
FROM EMPLOYEE;

-- Generate a list of employee user ids using the first character of first name and first 7 characters of last name
SELECT EMP_FNAME, EMP_LNAME, 
       SUBSTR(EMP_FNAME,1,1) || SUBSTR(EMP_LNAME,1,7)
FROM EMPLOYEE;

-- LENGTH
List all employees last names and the length of their names, ordered descended by last name length
SELECT EMP_LNAME, LENGTH(EMP_LNAME) AS NAMESIZE
FROM EMPLOYEE
ORDER BY NAMESIZE DESC;

-- === CONVERSION FUNCTIONS
-- TO_CHAR
-- List all product prices, quantity on hand and percent discount and total inventory cost using formatted values
SELECT P_CODE, TO_CHAR(P_PRICE,'$999.99') AS PRICE,
       TO_CHAR(P_QOH,'9,999.99') AS QUANTITY,
       TO_CHAR(P_DISCOUNT, '0.99') AS DISC,
       TO_CHAR(P_PRICE*P_QOH, '$99,999.99') AS TOTAL_COST
FROM PRODUCT;

-- List all employees date of birth using different date formats
SELECT EMP_LNAME, EMP_DOB, TO_CHAR(EMP_DOB, 'DAY, MONTH DD, YYYY') AS "DATE OF BIRTH"
FROM EMPLOYEE;

SELECT EMP_LNAME, EMP_DOB, TO_CHAR(EMP_DOB, 'YYYY/MM/DD') AS "DATE OF BIRTH"
FROM EMPLOYEE;

-- TO_NUMBER
-- USEFUL WHEN IMPORTING DATA IN TEXT FILES TO A DATABASE
SELECT TO_NUMBER('-123.99', 'S999.99'), TO_NUMBER(' 99.78-','B999.99MI')
FROM DUAL;


-- === UNION
-- ===
SELECT CUS_LNAME, CUS_FNAME, CUS_INITIAL, CUS_AREACODE, CUS_PHONE FROM CUSTOMER
UNION
SELECT CUS_LNAME, CUS_FNAME, CUS_INITIAL, CUS_AREACODE, CUS_PHONE FROM CUSTOMER_2;


-- UNION ALL
SELECT CUS_LNAME, CUS_FNAME, CUS_INITIAL, CUS_AREACODE, CUS_PHONE FROM CUSTOMER
UNION ALL
SELECT CUS_LNAME, CUS_FNAME, CUS_INITIAL, CUS_AREACODE, CUS_PHONE FROM CUSTOMER_2;


-- === INTERSECT
SELECT CUS_LNAME, CUS_FNAME, CUS_INITIAL, CUS_AREACODE, CUS_PHONE FROM CUSTOMER
INTERSECT
SELECT CUS_LNAME, CUS_FNAME, CUS_INITIAL, CUS_AREACODE, CUS_PHONE FROM CUSTOMER_2;

SELECT CUS_CODE FROM CUSTOMER WHERE CUS_AREACODE = '615'
INTERSECT
SELECT DISTINCT CUS_CODE FROM INVOICE;


-- === MINUS
SELECT CUS_LNAME, CUS_FNAME, CUS_INITIAL, CUS_AREACODE, CUS_PHONE FROM CUSTOMER
MINUS
SELECT CUS_LNAME, CUS_FNAME, CUS_INITIAL, CUS_AREACODE, CUS_PHONE FROM CUSTOMER_2;

SELECT CUS_LNAME, CUS_FNAME, CUS_INITIAL, CUS_AREACODE, CUS_PHONE FROM CUSTOMER_2
MINUS
SELECT CUS_LNAME, CUS_FNAME, CUS_INITIAL, CUS_AREACODE, CUS_PHONE FROM CUSTOMER;

SELECT CUS_CODE FROM CUSTOMER WHERE CUS_AREACODE = '615'
MINUS
SELECT DISTINCT CUS_CODE FROM INVOICE;


-- ALTERNATIVE SYNTAX
-- ALTERNATIVE SYNTAX FOR INTERSECT
SELECT CUS_CODE FROM CUSTOMER 
WHERE CUS_AREACODE = '615' AND
      CUS_CODE IN (SELECT DISTINCT CUS_CODE FROM INVOICE);

-- ALTERNATIVE SYNTAX FOR MINUS
SELECT CUS_CODE FROM CUSTOMER 
WHERE CUS_AREACODE = '615' AND
      CUS_CODE NOT IN (SELECT DISTINCT CUS_CODE FROM INVOICE);


-- === VIEWS
-- ===
-- Q: Create a view to list all products with price greater than 50?
CREATE VIEW PRICEGT50 AS
    SELECT	P_DESCRIPT, P_QOH, P_PRICE 
    FROM	PRODUCT
    WHERE	P_PRICE > 50.00

SELECT * FROM PRICEGT50;

-- Q: Create a view to list all products to order, that is the quantity on hand is less that the minimum qty plus 10.
CREATE VIEW PROD_TO_ORDER AS
    SELECT P_CODE, P_DESCRIPT, P_QOH, P_PRICE 
      FROM PRODUCT
     WHERE P_QOH < (P_MIN +10)

SELECT * FROM PROD_TO_ORDER


-- Q: Create a view to show the total product cost and quantity on hand statistics grouped by vendor.
CREATE VIEW PROD_STATS AS
   SELECT V_CODE, 
          SUM(P_QOH * P_PRICE) AS TOTCOST,
          MAX(P_QOH) AS MAXQTY,
          MIN(P_QOH) AS MINQTY,
          AVG(P_QOH) AS AVGQTY
   FROM   PRODUCT
   GROUP  BY V_CODE;

SELECT * FROM PROD_STATS

	  
-- === SEQUENCES

CREATE SEQUENCE CUS_CODE_SEQ START WITH 20010 NOCACHE;
CREATE SEQUENCE INV_NUMBER_SEQ START WITH 4010 NOCACHE;

SELECT * FROM USER_SEQUENCES;

INSERT INTO CUSTOMER
VALUES (CUS_CODE_SEQ.NEXTVAL, 'Connery', 'Sean', NULL, '615', '898-2007', 0.00);

SELECT * FROM CUSTOMER WHERE CUS_CODE = 20010;

INSERT INTO INVOICE
VALUES (INV_NUMBER_SEQ.NEXTVAL, 20010, SYSDATE);

SELECT * FROM INVOICE WHERE INV_NUMBER = 4010;

INSERT INTO LINE
VALUES (INV_NUMBER_SEQ.CURRVAL, 1,'13-Q2/P2', 1, 14.99);

INSERT INTO LINE
VALUES (INV_NUMBER_SEQ.CURRVAL, 2,'23109-HB', 1, 9.95);

SELECT * FROM LINE WHERE INV_NUMBER = 4010;

COMMIT;

SELECT * FROM CUSTOMER;
SELECT * FROM INVOICE;
SELECT * FROM LINE;

DELETE FROM INVOICE WHERE INV_NUMBER = 4010;
DELETE FROM CUSTOMER WHERE CUS_CODE = 20010;

COMMIT;

-- USE DROP SEQUENCE sequence_name to delete a sequence.
DROP SEQUENCE CUS_CODE_SEQ;
DROP SEQUENCE INV_NUMBER_SEQ;

-- CREATE SEQUENCES AGAIN AS THEY WILL BE USED IN STORED PROCEDURES
CREATE SEQUENCE CUS_CODE_SEQ START WITH 20010 NOCACHE;
CREATE SEQUENCE INV_NUMBER_SEQ START WITH 4010 NOCACHE;

