SQL 5

: 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.

Most commands here are for ORACLE or MS ACCESS where indicated.

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

SEQUENCES

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

SELECT * FROM USER_SEQUENCES;
SELECT * FROM CUSTOMER;
INSERT INTO CUSTOMER
VALUES (CUS_CODE_SEQ.NEXTVAL, 'Connery', 'Sean', NULL, '615', '898-2007', 0.00);
//select*from customer
//rollback 将会回到Insert之前
//NEXTVAL:给next value that dosen't insert 
SELECT * FROM CUSTOMER WHERE CUS_CODE = 20010;
SELECT * FROM INVOICE;
INSERT INTO INVOICE
VALUES (INV_NUMBER_SEQ.NEXTVAL, 20010, SYSDATE);
SELECT * FROM INVOICE WHERE INV_NUMBER = 4010;
COMMIT;
//如果rollback会发什么？
//
SELECT * FROM LINE;
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;
ROLLBACK;
SELECT * FROM USER_SEQUENCES
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;

=== JOINS
SELECT * FROM invoice;

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

/* How do I rewrite this using a natural join? */ SELECT i.inv_number, inv_date, p_code, line_units, line_price, c.cus_code, cus_lname FROM invoice i, customer c, line l WHERE c.cus_code = i.cus_code AND l.inv_number = i.inv_number;

--- JOIN USING

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;

/* How could I rewrite the above statement without the join on clauses? Will I get the same result? */

: --- OUTER JOINS

--- LEFT OUTER JOIN
SELECT count() FROM VENDOR;
SELECT count() FROM PRODUCT;

/* 11 /
/ 16 */

/* which vendors are not associated with any products in the product table? /
/ which vendors have not supplied products? */
SELECT P_CODE, VENDOR.V_CODE, V_NAME
FROM VENDOR LEFT JOIN PRODUCT ON VENDOR.V_CODE = PRODUCT.V_CODE;

/* 19 */

--- RIGHT OUTER JOIN

/* Which products are not associated with any vendors in the vendor table? /
/ Which products don't have a vendor? */
SELECT P_CODE, VENDOR.V_CODE, V_NAME
FROM VENDOR RIGHT JOIN PRODUCT ON VENDOR.V_CODE = PRODUCT.V_CODE;

/* 16 */

--- FULL OUTER JOIN
/* Show me all the vendors that don't have any products and all the products that don't have any vendors */
SELECT P_CODE, VENDOR.V_CODE, V_NAME
FROM VENDOR FULL JOIN PRODUCT ON VENDOR.V_CODE = PRODUCT.V_CODE;

/* 21 - Why 21 and not 27? */

: --- SELF JOIN

SELECT EMP_MGR, EMP_LNAME, EMP_NUM, EMP_LNAME
FROM EMP;

SELECT count(*) FROM EMPLOYEE;

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;

/* How many managers are there? */

--- CROSS JOINS
/* 8, 18*/
select count() from invoice;
select count() from line;

select count(*) from invoice cross join line;

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;

: === ALIASES
/* What's wrong with this statement? */

SELECT inv_number, inv_date, cus_code, cus_lname
FROM invoice, customer
WHERE cus_code = cus_code;

SELECT inv_number, inv_date, c.cus_code, cus_lname
FROM invoice i, customer c
WHERE c.cus_code = i.cus_code;

SELECT i.inv_number, inv_date, p_code, line_units, line_price, c.cus_code, cus_lname
FROM invoice i, customer c, line l
WHERE c.cus_code = i.cus_code AND
l.inv_number = i.inv_number;

/* What's wrong with this statement? */

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

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

: === SQL FUNCTIONS

=== CHARACTER 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 employee’s 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 employee’s 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;

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

=== DATE/TIME FUNCTIONS

List all employess born in 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';

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

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/2010)
SELECT EMP_LNAME, EMP_FNAME, EMP_DOB, '11/25/2012' AS ANIV_DATE, (TO_DATE('11/25/2012','MM/DD/YYYY') - EMP_DOB)/365 AS YEARS
FROM EMPLOYEE
ORDER BY YEARS;

:get the employees current age
SELECT EMP_LNAME, EMP_FNAME, ((SYSDATE - EMP_DOB)/365) AS AGE
FROM EMPLOYEE
ORDER BY AGE;

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

How many days are left to Christmas 2010?:
SELECT TO_DATE('25-Dec-2014','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, LAST_DAY(EMP_HIRE_DATE)-7 AS LASTDAY7
FROM EMPLOYEE
WHERE EMP_HIRE_DATE >= LAST_DAY(EMP_HIRE_DATE)-7;

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

== AGGREGATE FUNCTIONS: COUNT, MIN, MAX, SUM AND AVG
== COUNT
- Q: How many vendors provide products?
- COUNT(column) counts the not null values in column
SELECT COUNT(DISTINCT V_CODE) FROM PRODUCT

• COUNT() counts the number of rows returned
  SELECT COUNT() FROM (SELECT DISTINCT V_CODE FROM PRODUCT)

SELECT COUNT(*) FROM (SELECT DISTINCT V_CODE FROM PRODUCT WHERE V_CODE IS NOT NULL)

• Q: How many vendors (unique vendors) have products with price < 10?
  SELECT COUNT(DISTINCT V_CODE)
  FROM PRODUCT
  WHERE P_PRICE <= 10.00

• Q: How many products with price < 10?
  SELECT COUNT(*) FROM PRODUCT
  WHERE P_PRICE <= 10.00

== MAX AND MIN

SELECT MAX(P_PRICE) FROM PRODUCT

SELECT MIN(P_PRICE) FROM PRODUCT

• Q: What product(s) have a price equal to the maximum product price?
  SELECT P_CODE, P_DESCRIPT, P_PRICE
  FROM PRODUCT
  WHERE P_PRICE = (SELECT MAX(P_PRICE) FROM PRODUCT)

• Q: What product(s) have the highest inventory value?
  SELECT *
  FROM PRODUCT
  WHERE P_QOH * P_PRICE = (SELECT MAX(P_QOH * P_PRICE) FROM PRODUCT)

== SUM
- Q: How much is the total customer balance?
SELECT SUM(CUS_BALANCE) AS TOTBALANCE FROM CUSTOMER

• Q: How much is the total value of our product inventory?
  SELECT SUM(P_QOH*P_PRICE) AS TOTVALUE
  FROM PRODUCT

== AVG
- Q: What is the average product price?
SELECT AVG(P_PRICE) FROM PRODUCT;

• Q: What products have a price that exceeds the average product price?
  SELECT P_CODE, P_DESCRIPT, P_QOH, P_PRICE, V_CODE
  FROM PRODUCT
  WHERE P_PRICE > (SELECT AVG(P_PRICE) FROM PRODUCT)
  ORDER BY P_PRICE DESC

== GROUP BY
- Q: What is the minimum price for each sale code?
SELECT P_SALECODE, MIN(P_PRICE)
FROM PRODUCT
GROUP BY P_SALECODE

• Q: What is the average price for each sale code?
  SELECT P_SALECODE, AVG(P_PRICE)
  FROM PRODUCT
  GROUP BY P_SALECODE

• The following will generate an error

• GROUP BY must be used with aggregation functions
  SELECT V_CODE, P_CODE, P_DESCRIPT,P_PRICE
  FROM PRODUCT
  GROUP BY V_CODE

• How many products each vendor provides?
  SELECT V_CODE, COUNT(DISTINCT P_CODE)
  FROM PRODUCT
  GROUP BY V_CODE

:== GROUP BY WITH HAVING CLAUSE
-Q: List the number of products by vendor with the average price, include only the rows with price below 10.00.
SELECT V_CODE, COUNT(DISTINCT P_CODE), AVG(P_PRICE)
FROM PRODUCT
GROUP BY V_CODE

SELECT V_CODE, COUNT(DISTINCT P_CODE), AVG(P_PRICE)
FROM PRODUCT
GROUP BY V_CODE
HAVING AVG(P_PRICE) < 10

• Q: The following SQL command will:
• 1 - Aggregate the total cost of products group by vendor
• 2 - Select only the rows having a total cost greater than 500
• 3 - List the results in descending order by total cost

SELECT V_CODE, SUM(P_QOH * P_PRICE) AS TOTCOST
FROM PRODUCT
GROUP BY V_CODE
HAVING (SUM(P_QOH * P_PRICE)>500)
ORDER BY SUM(P_QOH * P_PRICE) DESC

: === SUB-QUERIES

=== EXAMPLE OF TYPICAL JOIN
SELECT INV_NUMBER, INVOICE.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);

/* Another way to write this using distinct? How is this different? */

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

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

: --- 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%');

: --- 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 product’s 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;

: Why is this wrong?
SELECT P_CODE, P_PRICE, (SELECT P_PRICE FROM PRODUCT) AS AVGPRICE,
P_PRICE-(SELECT 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 AND VENDOR.V_CODE = PRODUCT.V_CODE);

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

DoubleClick
Google Analytics