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)
/* Create an index in V_AREACODE and gather statistics */
CREATE INDEX QOV_NDX1 ON QOVENDOR(V_AREACODE) ANALYZE TABLE QOVENDOR COMPUTE STATISTICS
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 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
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
Comments
Comments are used to explain sections of SQL statements, or to prevent execution of SQL statements. --Select all: SELECT * FROM Customers; SELECT * FROM Customers -- WHERE City='Berlin'; /*Select all the columns of all the records in the Customers table:*/ SELECT * FROM Customers;
DELETE Syntax
DELETE FROM Customers WHERE CustomerName='Alfreds Futterkiste';
DELETE TABLE ROWS
DELETE FROM PRODUCT WHERE P_CODE = 'BRT-345';
DROPING A TABLE
DROP TABLE PART;
/* Second Explain Plan */
EXPLAIN PLAN FOR SELECT * FROM QOVENDOR WHERE V_NAME LIKE 'B%' ORDER BY V_AREACODE
FROM
FROM table_name
INSERT INTO SELECT Examples
INSERT INTO Customers (CustomerName, City, Country) SELECT SupplierName, City, Country FROM Suppliers; INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country) SELECT SupplierName, ContactName, Address, City, PostalCode, Country FROM Suppliers; INSERT INTO Customers (CustomerName, City, Country) SELECT SupplierName, City, Country FROM Suppliers WHERE Country='Germany';
INSERT INTO VENDOR
INSERT INTO VENDOR VALUES (21225,'Bryson, Inc.','Smithson','615','223-3234','TN','Y');
INSERT INTO SELECT Syntax
INSERT INTO table2 SELECT * FROM table1 WHERE condition; INSERT INTO table2 (column1, column2, column3, ...) SELECT column1, column2, column3, ... FROM table1 WHERE condition;
INSERT INTO Syntax
INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...); INSERT INTO table_name VALUES (value1, value2, value3, ...);
Another UNION Example
SELECT 'Customer' As Type, ContactName, City, Country FROM Customers UNION SELECT 'Supplier', ContactName, City, Country FROM Suppliers;
SELECT INTO Syntax
SELECT * INTO newtable [IN externaldb] FROM oldtable WHERE condition; SELECT column1, column2, column3, ... INTO newtable [IN externaldb] FROM oldtable WHERE condition;
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);
LIMIT (MySQL)
SELECT * FROM Customers LIMIT 3; SELECT * FROM Customers WHERE Country='Germany' LIMIT 3;
ORDER BY DESC
SELECT * FROM Customers ORDER BY Country DESC; SELECT * FROM Customers ORDER BY Country ASC, CustomerName DESC;
ROWNUM (Oracle)
SELECT * FROM Customers WHERE ROWNUM <= 3; SELECT * FROM Customers WHERE Country='Germany' AND ROWNUM <= 3;
/ * Display the plan -> uses Full Table Scan! */
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY)
/* Display the access plan */
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY)
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);
SELECT
SELECT *, SELECT column1, column2 FROM table_name;
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;
AVG() Example
SELECT AVG(Price) FROM Products;
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
HAVING Examples
SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country HAVING COUNT(CustomerID) > 5; SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country HAVING COUNT(CustomerID) > 5 ORDER BY COUNT(CustomerID) DESC;
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
COUNT() Example
SELECT COUNT(ProductID) FROM Products;
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;
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;
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;
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
UNION Example (only distinct values)
SELECT City FROM Customers UNION SELECT City FROM Suppliers ORDER BY City;
UNION ALL Example (duplicate values also)
SELECT City FROM Customers UNION ALL SELECT City FROM Suppliers ORDER BY City;
UNION With WHERE
SELECT City, Country FROM Customers WHERE Country='Germany' UNION SELECT City, Country FROM Suppliers WHERE Country='Germany' ORDER BY City;
UNION ALL With WHERE
SELECT City, Country FROM Customers WHERE Country='Germany' UNION ALL SELECT City, Country FROM Suppliers WHERE Country='Germany' ORDER BY City;
Alias for Columns Examples
SELECT CustomerID as ID, CustomerName AS Customer FROM Customers; SELECT CustomerName AS Customer, ContactName AS [Contact Person] FROM Customers;
(Address, PostalCode, City and Country)
SELECT CustomerName, Address + ', ' + PostalCode + ' ' + City + ', ' + Country AS Address FROM Customers;
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;
More HAVING Examples
SELECT Employees.LastName, COUNT(Orders.OrderID) AS NumberOfOrders FROM (Orders INNER JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID) GROUP BY LastName HAVING COUNT(Orders.OrderID) > 10; Try it Yourself » SELECT Employees.LastName, COUNT(Orders.OrderID) AS NumberOfOrders FROM Orders INNER JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID WHERE LastName = 'Davolio' OR LastName = 'Fuller' GROUP BY LastName HAVING COUNT(Orders.OrderID) > 25;
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;
MAX() Example
SELECT MAX(Price) AS LargestPrice FROM Products;
MIN() Example
SELECT MIN(Price) AS SmallestPrice FROM Products;
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
ANY Examples
SELECT ProductName FROM Products WHERE ProductID = ANY (SELECT ProductID FROM OrderDetails WHERE Quantity = 10); SELECT ProductName FROM Products WHERE ProductID = ANY (SELECT ProductID FROM OrderDetails WHERE Quantity > 99);
: 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
SUM() Example
SELECT SUM(Quantity) FROM OrderDetails;
GROUP BY With JOIN Example
SELECT Shippers.ShipperName, COUNT(Orders.OrderID) AS NumberOfOrders FROM Orders LEFT JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID GROUP BY ShipperName;
SQL EXISTS Examples
SELECT SupplierName FROM Suppliers WHERE EXISTS (SELECT ProductName FROM Products WHERE SupplierId = Suppliers.supplierId AND Price < 20); SELECT SupplierName FROM Suppliers WHERE EXISTS (SELECT ProductName FROM Products WHERE SupplierId = Suppliers.supplierId AND Price = 22);
/* CUBE EXAMPLE */
SELECT TM_MONTH, P_CODE, SUM(SALE_UNITS*SALE_PRICE) AS TOTSALES FROM DWDAYSALESFACT NATURAL JOIN DWPRODUCT NATURAL JOIN DWTIME GROUP BY CUBE (TM_MONTH, P_CODE) ORDER BY TM_MONTH, P_CODE;
TOP (SQL)
SELECT TOP 3 * FROM Customers; SELECT TOP 3 * FROM Customers WHERE Country='Germany';
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
/* ROLLUP EXAMPLE */
SELECT V_CODE, P_CODE, SUM(SALE_UNITS*SALE_PRICE) AS TOTSALES FROM DWDAYSALESFACT NATURAL JOIN DWPRODUCT NATURAL JOIN DWVENDOR GROUP BY ROLLUP (V_CODE, P_CODE) ORDER BY V_CODE, P_CODE;
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%';
ORDER BY Syntax
SELECT column1, column2, ... FROM table_name ORDER BY column1, column2, ... ASC|DESC;
EXISTS Syntax
SELECT column_name(s) FROM table_name WHERE EXISTS (SELECT column_name FROM table_name WHERE condition);
ALL Syntax
SELECT column_name(s) FROM table_name WHERE column_name operator ALL (SELECT column_name FROM table_name WHERE condition); Note: The operator must be a standard comparison operator (=, <>, !=, >, >=, <, or <=).
ANY Syntax
SELECT column_name(s) FROM table_name WHERE column_name operator ANY (SELECT column_name FROM table_name WHERE condition);
HAVING Syntax
SELECT column_name(s) FROM table_name WHERE condition GROUP BY column_name(s) HAVING condition ORDER BY column_name(s);
GROUP BY Syntax
SELECT column_name(s) FROM table_name WHERE condition GROUP BY column_name(s) ORDER BY column_name(s); SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country; SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country ORDER BY COUNT(CustomerID) DESC;
UNION Syntax
SELECT column_name(s) FROM table1 UNION SELECT column_name(s) FROM table2;
UNION ALL Syntax
SELECT column_name(s) FROM table1 UNION ALL SELECT column_name(s) FROM table2;
ALIASES
SELECT inv_number, inv_date, c.cus_code, cus_lname FROM invoice i, customer c WHERE c.cus_code = i.cus_code;
SQL Statements order
SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY (sweaty feat will give horrible odors)
SQL Aliases
SQL aliases are used to give a table, or a column in a table, a temporary name. Aliases are often used to make column names more readable. An alias only exists for the duration of the query.
ALL Example
The ALL operator returns TRUE if all of the subquery values meet the condition. SELECT ProductName FROM Products WHERE ProductID = ALL (SELECT ProductID FROM OrderDetails WHERE Quantity = 10);
ANY and ALL Operators
The ANY and ALL operators are used with a WHERE or HAVING clause. The ANY operator returns true if any of the subquery values meet the condition. The ALL operator returns true if all of the subquery values meet the condition.
ANY
The ANY operator returns TRUE if any of the subquery values meet the condition.
SQL COUNT(), AVG() and SUM() Functions
The COUNT() function returns the number of rows that matches a specified criteria. The AVG() function returns the average value of a numeric column. The SUM() function returns the total sum of a numeric column.
DELETE
The DELETE statement is used to delete existing records in a table.
EXISTS Operator
The EXISTS operator is used to test for the existence of any record in a subquery. The EXISTS operator returns true if the subquery returns one or more records.
GROUP BY Statement
The GROUP BY statement is often used with aggregate functions (COUNT, MAX, MIN, SUM, AVG) to group the result-set by one or more columns.
HAVING Clause
The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions.
INSERT INTO SELECT Statement
The INSERT INTO SELECT statement copies data from one table and inserts it into another table. INSERT INTO SELECT requires that data types in source and target tables match The existing records in the target table are unaffected
INSERT INTO
The INSERT INTO statement is used to insert new records in a table.
MIN() and MAX()
The MIN() function returns the smallest value of the selected column. The MAX() function returns the largest value of the selected column.
ORDER BY
The ORDER BY keyword is used to sort the result-set in ascending or descending order. The ORDER BY keyword sorts the records in ascending order by default. To sort the records in descending order, use the DESC keyword.
DISTINCT
The SELECT DISTINCT statement is used to return only distinct (different) values.
SELECT INTO Statement
The SELECT INTO statement copies data from one table into a new table.
TOP, LIMIT or ROWNUM
The SELECT TOP clause is used to specify the number of records to return. The SELECT TOP clause is useful on large tables with thousands of records. Returning a large number of records can impact on performance.
UNION Operator
The UNION operator is used to combine the result-set of two or more SELECT statements. Each SELECT statement within UNION must have the same number of columns The columns must also have similar data types The columns in each SELECT statement must also be in the same order
UPDATE
The UPDATE statement is used to modify the existing records in a table.
WHERE
The WHERE clause is used to filter records. The WHERE clause is used to extract only those records that fulfill a specified condition. WHERE Country='Mexico';
Alias for Tables Example
The following SQL statement selects all the orders from the customer with CustomerID=4 (Around the Horn). We use the "Customers" and "Orders" tables, and give them the table aliases of "c" and "o" respectively (Here we use aliases to make the SQL shorter): SELECT o.OrderID, o.OrderDate, c.CustomerName FROM Customers AS c, Orders AS o WHERE c.CustomerName="Around the Horn" AND c.CustomerID=o.CustomerID;
UPDATE Syntax
UPDATE Customers SET ContactName = 'Alfred Schmidt', City= 'Frankfurt' WHERE CustomerID = 1;
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;
