--: ADVSQL-TEXT.TXT - SQL EXAMPLES 

--: 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 Query Analyzer in SQL Server.
--: This way, the class loses less time in typing (and error corrections!).
--: Please be aware that date formats may differ among DBMS programs.
--: Most commands here are for SQL Server or MS ACCESS where indicated.
 


-- === 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;


-- 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);


-- === JOINS

-- --- 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;

-- --- 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
-- 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);

-- LIST OF PRODUCTS WITH PRICE >= AVERAGE PRODUCT PRICE?

SELECT P_CODE, P_PRICE FROM PRODUCT
WHERE P_PRICE >= (SELECT AVG(P_PRICE) FROM PRODUCT);


-- --- WHERE SUB-QUERIES
-- LIST ALL CUSTOMERS WHO ORDERED THE PRODUCT "CLAW HAMMER"?

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

-- ALTERNATIVE SYNTAX

SELECT DISTINCT CUSTOMER.CUS_CODE, CUS_LNAME, CUS_FNAME
FROM CUSTOMER JOIN INVOICE ON CUSTOMER.CUS_CODE = INVOICE.CUS_CODE
              JOIN LINE ON INVOICE.INV_NUMBER = LINE.INV_NUMBER
              JOIN PRODUCT ON LINE.P_CODE = PRODUCT.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 CUSTOMER.CUS_CODE, CUS_LNAME, CUS_FNAME
FROM CUSTOMER JOIN INVOICE ON CUSTOMER.CUS_CODE = INVOICE.CUS_CODE
              JOIN LINE ON INVOICE.INV_NUMBER = LINE.INV_NUMBER
              JOIN PRODUCT ON LINE.P_CODE = PRODUCT.P_CODE
WHERE PRODUCT.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 JOIN LINE ON INVOICE.INV_NUMBER = LINE.INV_NUMBER WHERE P_CODE = '13-Q2/P2') CP1, 
    (SELECT INVOICE.CUS_CODE FROM INVOICE JOIN LINE ON INVOICE.INV_NUMBER = LINE.INV_NUMBER WHERE P_CODE = '23109-HB') CP2
WHERE CUSTOMER.CUS_CODE = CP1.CUS_CODE 
AND   CP1.CUS_CODE = CP2.CUS_CODE;

-- USING VIRTUAL TABLES IN FROM CLAUSE
CREATE VIEW CP1 AS 
SELECT INVOICE.CUS_CODE FROM INVOICE JOIN LINE ON INVOICE.INV_NUMBER = LINE.INV_NUMBER WHERE P_CODE = '13-Q2/P2'
CREATE VIEW CP2 AS
SELECT INVOICE.CUS_CODE FROM INVOICE JOIN LINE ON INVOICE.INV_NUMBER = LINE.INV_NUMBER WHERE P_CODE = '23109-HB'
SELECT DISTINCT CUSTOMER.CUS_CODE, CUSTOMER.CUS_LNAME 
FROM CUSTOMER JOIN CP1 ON CUSTOMER.CUS_CODE = CP1.CUS_CODE JOIN CP2 ON 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/2004)
SELECT EMP_LNAME, EMP_FNAME, EMP_DOB, '11/25/2008' AS ANIV_DATE, (TO_DATE('11/25/1998','MM/DD/YYYY') - EMP_DOB)/365 AS YEARS 
FROM EMPLOYEE
ORDER BY YEARS;

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

-- How many days are left to Christmas 2008?:
SELECT TO_DATE('25-Dec-2008','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;

--SQL Server
--DATE

-- List the approximate age of the employees on the company's 10th anniversary date -- (11/25/2008)
SELECT EMP_LNAME, EMP_FNAME, EMP_DOB, '11/25/2008' AS ANIV_DATE, DATEDIFF(DAY, '11/25/2008',EMP_DOB)/365 AS YEARS 
FROM EMPLOYEE
ORDER BY YEARS;

-- How many days between thanksgiving and Christmas 2008?
SELECT DATEDIFF(DAY,'11/25/2008','12/25/2008')

-- How many days are left to Christmas 2008?:
SELECT DATEDIFF(DAY, CURRENT_TIMESTAMP, '12/25/2008')

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


-- === NUMERIC FUNCTIONS

-- ABS
-- List absolute values
SELECT 1.95, -1.93, ABS(1.95), ABS(-1.93)

-- 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;

-- 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, CEILING(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, SUBSTRING(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, 
       SUBSTRING(EMP_FNAME,1,1) + SUBSTRING(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, LEN(EMP_LNAME) AS NAMESIZE
FROM EMPLOYEE
ORDER BY NAMESIZE DESC;

-- === CONVERSION FUNCTIONS
-- CAST
-- List all product prices, quantity on hand and percent discount and total inventory cost 
-- as text instead of numeric data types
SELECT P_CODE, CAST(P_PRICE AS VARCHAR(8)) AS PRICE,
       CAST(P_QOH AS VARCHAR(4)) AS QUANTITY,
       CAST(P_DISCOUNT AS VARCHAR(3)) AS DISC,
       CAST(P_PRICE*P_QOH AS VARCHAR(10)) AS TOTAL_COST
FROM PRODUCT;


-- List all employees date of birth using different date formats
SELECT EMP_LNAME, EMP_DOB, CAST(EMP_DOB as varchar(11)) AS "DATE OF BIRTH"
FROM EMPLOYEE;


-- === SEQUENCES
-- SQL Server does not use SEQUENCES. Instead, there is a column attribute IDENTITY that
-- specifies a column will use a sequencial value.

BEGIN;

ALTER TABLE CUSTOMER ADD CUS_CODE_SEQ INT IDENTITY(20010,1);
ALTER TABLE INVOICE ADD INV_NUMBER_SEQ INT IDENTITY(4010,1);
ALTER TABLE LINE ADD INV_NUMBER_SEQ INT IDENTITY(4010,1);

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

INSERT INTO INVOICE
VALUES (1009, 10010, CURRENT_TIMESTAMP);

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

INSERT INTO LINE
VALUES (1009, 2,'23109-HB', 1, 9.95);

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;
