SQL statement review

Create index on P_INDATE, Create composite index on V_CODE and P_CODE Delete the PROD_PRICEX index

1CREATE INDEX P_INDATEX ON PRODUCT(P_INDATE); 2CREATE INDEX VENPRODX ON PRODUCT(V_CODE,P_CODE); 3 DROP INDEX PROD_PRICEX;

ADDING PK AND FK WITH THE ALTER TABLE COMMAND

ALTER TABLE PART ADD PRIMARY KEY(P_CODE) ADD FOREIGN KEY (V_CODE) REFERENCES VENDOR;

ADD A COLUMN TO TABLE

ALTER TABLE PRODUCT ADD (P_SALECODE CHAR(1));

Change the V_CODE data type to CHAR

ALTER TABLE PRODUCT MODIFY (V_CODE CHAR(5));

DROPING A COLUMN FROM TABLE Only will work if column has no data or is not related in a foreign key relationship.

ALTER TABLE VENDOR DROP COLUMN V_ORDER;

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)

SEQUENCES

CREATE SEQUENCE CUS_CODE_SEQ START WITH 20010 NOCACHE; CREATE SEQUENCE INV_NUMBER_SEQ START WITH 4010 NOCACHE; SELECT * FROM USER_SEQUENCES;

Create a PART table with only using the code, description and price columns of PRODUCT

CREATE TABLE PART AS SELECT P_CODE AS PART_CODE, P_DESCRIPT AS PART_DESCRIPT, P_PRICE AS PART_PRICE, V_CODE FROM PRODUCT;

CREATING TABLE STRUCTURES

CREATE TABLE PRODUCT ( P_CODE VARCHAR2(10) P_INDATE DATE NOT NULL, LINE_UNITS NUMBER(9,2) DEFAULT 0.00 NOT NULL, P_DISCOUNT NUMBER(5,2) NOT NULL, FOREIGN KEY (P_CODE) REFERENCES PRODUCT(P_CODE), PRIMARY KEY (INV_NUMBER,LINE_NUMBER),

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;

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

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

DELETE TABLE ROWS

DELETE FROM PRODUCT WHERE P_CODE = 'BRT-345';

DROPING A TABLE

DROP TABLE PART;

INSERT INTO VENDOR

INSERT INTO VENDOR VALUES (21225,'Bryson, Inc.','Smithson','615','223-3234','TN','Y');

BETWEEN

SELECT * FROM PRODUCT WHERE P_PRICE BETWEEN 50.00 AND 100.00;

What product(s) have the highest inventory value?

SELECT * FROM PRODUCT WHERE P_QOH * P_PRICE = (SELECT MAX(P_QOH * P_PRICE) FROM PRODUCT)

in

SELECT * FROM PRODUCT WHERE V_CODE IN (21344, 24288);

List the products that are supplied by a vendor

SELECT * FROM PRODUCT WHERE V_CODE IS NOT NULL;

List all vendors but only if there are products to order (P_QOH <= P_MIN)

SELECT * FROM VENDOR WHERE EXISTS (SELECT * FROM PRODUCT WHERE P_QOH <= P_MIN);

List all vendors but only if there are products with the qty on hand less than double the min qty

SELECT * FROM VENDOR WHERE EXISTS (SELECT * FROM PRODUCT WHERE P_QOH < P_MIN*2);

List absolute values

SELECT 1.95, -1.93, ABS(1.95), ABS(-1.93) FROM DUAL;

What is the average product price?

SELECT AVG(P_PRICE) FROM PRODUCT;

COUNT(*) counts the number of rows returned

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

How many products with price < 10?

SELECT COUNT(*) FROM PRODUCT WHERE P_PRICE <= 10.00

How many vendors (unique vendors) have products with price < 10?

SELECT COUNT(DISTINCT V_CODE) FROM PRODUCT WHERE P_PRICE <= 10.00

How many vendors provide products?

SELECT COUNT(DISTINCT V_CODE) FROM PRODUCT

List all invoice data for customer number 10014

SELECT CUS_LNAME, INVOICE.INV_NUMBER, INV_DATE, P_DESCRIPT FROM CUSTOMER, INVOICE, LINE, PRODUCT WHERE CUSTOMER.CUS_CODE = INVOICE.CUS_CODE AND INVOICE.INV_NUMBER = LINE.INV_NUMBER AND LINE.P_CODE = PRODUCT.P_CODE AND CUSTOMER.CUS_CODE = 10014 ORDER BY INVOICE.INV_NUMBER

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

DISTINCT

SELECT DISTINCT V_CODE FROM PRODUCT;

LISTING UNIQUE VALUES (ROWS!)

SELECT DISTINCT V_STATE FROM VENDOR

SELF JOIN

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;

RECURSIVE QUERIES 注意是自己join自己哦 -- List all employees with their manager's name -- Using EMP table

SELECT E.EMP_MGR, M.EMP_LNAME,E.EMP_NUM, E.EMP_LNAME FROM EMP E, EMP M WHERE E.EMP_MGR=M.EMP_NUM ORDER BY E.EMP_MGR

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;

CONCATENATION List all employee names (concatenated):

SELECT EMP_LNAME || ', ' || EMP_FNAME AS NAME FROM EMPLOYEE;

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;

:get the employees current age

SELECT EMP_LNAME, EMP_FNAME, ((SYSDATE - EMP_DOB)/365) AS AGE FROM EMPLOYEE ORDER BY AGE;

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;

List all employees born in the 14th day of the month

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

List all employess born in 11

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 employess born in 1966

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

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

: SUBSTR List the first three characters of all employee's phone numbers

SELECT EMP_PHONE, SUBSTR(EMP_PHONE,1,3) FROM EMPLOYEE;

CROSS JOINS

SELECT INVOICE.INV_NUMBER, CUS_CODE, INV_DATE, P_CODE FROM INVOICE CROSS JOIN 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;

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

NATURAL JOIN

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

: LOWER List all employee names in all lowercase (concatenated)

SELECT LOWER(EMP_LNAME) || ', ' || LOWER(EMP_FNAME) AS NAME FROM EMPLOYEE;

SELECT SELECT WITH WHERE CLAUSE

SELECT P_CODE, P_DESCRIPT, P_INDATE, P_QOH, P_MIN, P_PRICE, P_DISCOUNT, V_CODE FROM PRODUCT; SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE FROM PRODUCT WHERE V_CODE = 21344;

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)

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)

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

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

aliases

SELECT P_CODE, P_INDATE, P_INDATE + 90 AS EXPDATE 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);

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;

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;

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

FULL OUTER JOIN

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

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;

AND, OR, NOT

SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE FROM PRODUCT WHERE V_CODE = 21344 OR V_CODE = 24288; SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE FROM PRODUCT WHERE P_PRICE < 50 AND P_INDATE > '15-JAN-2012'; SELECT * FROM PRODUCT WHERE NOT (V_CODE = 21344);

List the product description, price, vendor code, name, contact, area code and phone for each product

SELECT P_DESCRIPT, P_PRICE, VENDOR.V_CODE, V_NAME, V_CONTACT, V_AREACODE, V_PHONE FROM PRODUCT, VENDOR WHERE PRODUCT.V_CODE = VENDOR.V_CODE

ALIAS

SELECT P_DESCRIPT, P_PRICE, V_NAME, V_CONTACT, V_AREACODE, V_PHONE FROM PRODUCT P, VENDOR V WHERE P.V_CODE = V.V_CODE ORDER BY P_PRICE

List products with vendor data for products purchased after 15-JAN-2010

SELECT P_DESCRIPT, P_PRICE, V_NAME, V_CONTACT, V_AREACODE, V_PHONE FROM PRODUCT, VENDOR WHERE PRODUCT.V_CODE = VENDOR.V_CODE AND P_INDATE > '15-JAN-2012'

Using comparison operators on dates

SELECT P_DESCRIPT, P_QOH, P_MIN, P_PRICE, P_INDATE FROM PRODUCT WHERE P_INDATE >= '20-JAN-2012';

SELECT WITH ORDER BY

SELECT P_DESCRIPT, V_CODE, P_INDATE, P_PRICE FROM PRODUCT WHERE P_INDATE < '21-JAN-2012' AND P_PRICE <= 50.00 ORDER BY V_CODE, P_PRICE DESC

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;

What is the average price for each sale code?

SELECT P_SALECODE, AVG(P_PRICE) FROM PRODUCT GROUP BY P_SALECODE

: What is the minimum price for each sale code?

SELECT P_SALECODE, MIN(P_PRICE) FROM PRODUCT GROUP BY P_SALECODE

What is the minimum price for each sale code?

SELECT P_SALECODE, MIN(P_PRICE) FROM PRODUCT GROUP BY P_SALECODE

: How much is the total customer balance?

SELECT SUM(CUS_BALANCE) AS TOTBALANCE FROM CUSTOMER

How much is the total value of our product inventory?

SELECT SUM(P_QOH*P_PRICE) AS TOTVALUE FROM PRODUCT

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;

: UPPER List all employee names in all capitals (concatenated)

SELECT UPPER(EMP_LNAME) || ', ' || UPPER(EMP_FNAME) AS NAME FROM EMPLOYEE;

How many products each vendor provides?

SELECT V_CODE, COUNT(DISTINCT P_CODE) FROM PRODUCT GROUP BY V_CODE

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

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 cos

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

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

List the V_CODE and V_NAME of vendors that provide products

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

List the V_CODE and V_NAME of vendors that do not provide products

SELECT V_CODE, V_NAME FROM VENDOR WHERE V_CODE NOT IN (SELECT DISTINCT V_CODE FROM PRODUCT WHERE V_CODE IS NOT NULL);

:LIST ALL VENDORS THAT PROVIDE PRODUCTS?

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

%

SELECT V_NAME, V_CONTACT, V_AREACODE, V_PHONE FROM VENDOR WHERE UPPER(V_CONTACT) LIKE 'SMITH%';

ALIASES

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

UPDATE TABLE ROWS

UPDATE PRODUCT SET P_INDATE = '18-JAN-2012', P_PRICE = 17.99, P_MIN = 10 WHERE P_CODE = '13-Q2/P2';

UPDATE COMMANDS

UPDATE PRODUCT SET P_SALECODE = '2' WHERE P_CODE = '1546-QQ2'; UPDATE PRODUCT SET P_SALECODE = '1' WHERE P_INDATE >= '16-JAN-2012' AND P_INDATE < '10-FEB-2012'; UPDATE PRODUCT SET P_PRICE = P_PRICE*1.10 WHERE P_PRICE < 50.00;