SQL 4

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 WHERE CUS_CODE = 20010;

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

SELECT * FROM INVOICE WHERE INV_NUMBER = 4010;

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;
//dual双重表格 double table

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

: [TO_DATE]How many days are left to Christmas 2010?:

SELECT TO_DATE('25-Dec-2014','DD-MON-YYYY') - SYSDATE
FROM DUAL;

: [ADD_MONTHS]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);

[SYSDATE]

@ sysdate as currdate,round((sysdate-emp_dob)/365,0) as YEARs from employee order by

@

: 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

When using a group function and the GROUP BY clause, the individual column(s) in the SELECT must also appear in the GROUP BY.
The WHERE clause can still be used to restrict data before grouping.
The WHERE clause cannot be used to restrict groups.
A column alias cannot be used in a GROUP BY clause.
The GROUP BY column does not have to appear in a SELECT query.
When a column is used in the GROUP BY clause, the result is sorted in ascending order by that column by default. GROUP BY has an implied ORDER 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

[HAVING] 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

HAVING 用来限制group by

• 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

嵌套

ORDER BY clause cannot be used in a subquery.
where 用的很多
先测试subquery能够工作

: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;
//看每一个subquery返回值

: --- 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<P_MIN*2 
                AND VENDOR.V_CODE = PRODUCT.V_CODE);

Make sure tables are joined together properly
Execute subqueries on their own, make sure they are returning the correct result set
Add the where clause only after the tables are joined together properly
When using group by, make sure the group by statement works before adding the having clause
Add the order by clause last

Using a function as an argument in a WHERE clause can affect performance. 【like会影响效率
Indexes are often placed on table columns used for selection criteria to help Oracle identify records more efficiently.
If a query uses a function on a table column, it is changing its format.
This means Oracle cannot use the table column’s index.

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【 A∪B】

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 两个圆里都有的 A∩B

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