ITSS3300 SQL Resources - 2020 Spring

Welcome! This post includes my video tutorial of hands-on SQL on Oracle Live SQL for ITSS3300 - 2020 Spring.

The pdf and following codes come from the slides and sample script I used in class.

SQL Example Codes

/* Example: How To Comment */
/* Multi line comment
   This is the 
   first SQL statement exmaple */
SELECT PROD_ID, PROD_CATEGORY, PROD_SUB_CATEGORY -- Single line comment
FROM PRODUCT;

/* Example: How To Create Table*/
CREATE TABLE ORDERS (
Order_ID NUMBER(5),
Order_Date DATE,
Order_Priority VARCHAR2(20),
Ord_Id VARCHAR2(10) );

/* Example: How to Create Table with Constraint */
CREATE TABLE ORDERS (
Order_ID NUMBER(5) PRIMARY KEY NOT NULL,
Order_Date DATE,
Order_Priority VARCHAR2(20),
Ord_Id VARCHAR2(10) );

/*Example: How To Drop Table*/
DROP TABLE ORDERS;

/* Example: INSERT INTO */
INSERT INTO PRODUCT (Prod_id, Prod_Category, Prod_Sub_Category)
VALUES ('Prod_1', 'OFFICE SUPPLIES', 'STORAGE & ORGANIZATION');

INSERT INTO PRODUCT (Prod_id, Prod_Sub_Category, Prod_Category)
VALUES ('Prod_1', 'STORAGE & ORGANIZATION', 'OFFICE SUPPLIES');

INSERT INTO PRODUCT 
VALUES ('Prod_1', 'OFFICE SUPPLIES', 'STORAGE & ORGANIZATION');

/* Example: UPDATE */
UPDATE PRODUCT
SET Prod_Sub_Category = 'FURNITURE & LIGHTING'
WHERE Prod_id = 'Prod_1';
/* What if no WHERE? */
UPDATE PRODUCT
SET Prod_Sub_Category = 'FURNITURE & LIGHTING';

/* Example: SELECT Prod_id FROM PRODUCT*/
SELECT Prod_id
FROM PRODUCT;
/* Select all columns from PRODUCT*/
SELECT *
FROM PRODUCT;

/*Example: Rename Sales as New_Sales*/
SELECT Mkt_Id, 1.5 * Sales
FROM MARKET;

SELECT Mkt_Id, 1.5 * Sales New_Sales
FROM MARKET;

/*Example: SELECT + FROM + WHERE + Logic Condition*/
SELECT * FROM MARKET
WHERE Prod_Id = 'Prod_1' AND Order_Quantity > 30;

SELECT * FROM MARKET
WHERE Prod_Id = 'Prod_1' OR Prod_Id = 'Prod_10';

SELECT * FROM MARKET
WHERE NOT (Prod_Id = 'Prod_1'); -- Equivalent <>

/*Example: Get min and max of sales, average order quantity and number of records for Prod_1*/
SELECT MIN(Sales) Min_Sales, MAX(Sales) Max_Sales, AVG(Order_Quantity) Avg_Ord_Q, COUNT(*)
FROM MARKET
WHERE Prod_Id = 'Prod_1';

/* Example: ORDER BY Sales */
SELECT * FROM MARKET
WHERE Prod_Id = 'Prod_1' AND Order_Quantity > 10
ORDER BY Sales DESC;

/*Example: Get Prod_Id and their corresponding number and average profit. Order records by average profit descendingly */
SELECT Prod_Id, COUNT(*), AVG(Profit) AVG_Profit
FROM MARKET
WHERE Order_Quantity > 10
GROUP BY Prod_Id
ORDER BY AVG_Profit DESC;

/* Example: HAVING */
SELECT Prod_Id, COUNT(*), AVG(Profit) AVG_Profit
FROM MARKET
WHERE Order_Quantity > 10
GROUP BY Prod_Id
HAVING COUNT(*) > 5 OR AVG(Profit) > 0
ORDER BY AVG_Profit DESC;
/* Example: HAVING */
SELECT Prod_Id, AVG(Profit) AVG_Profit
FROM MARKET
WHERE Order_Quantity > 10
GROUP BY Prod_Id
HAVING COUNT(*) > 5 OR AVG(Profit) > 0
ORDER BY AVG_Profit DESC;

/* Example: JOIN */
SELECT ORDERS.Order_ID, ORDERS.Order_Priority, SHIPPING.Ship_Mode
FROM ORDERS
JOIN SHIPPING ON ORDERS.Order_ID = SHIPPING.Order_ID
WHERE ORDERS.Order_Priority = 'LOW'; 

/* Example: Query two tables via JOIN */
SELECT ORDERS.Order_ID, ORDERS.Order_Priority, SHIPPING.Ship_Mode
FROM ORDERS
JOIN SHIPPING ON ORDERS.Order_ID = SHIPPING.Order_ID;
/* Example: Query two tables via = syntax */
SELECT ORDERS.Order_ID, ORDERS.Order_Priority, SHIPPING.Ship_Mode
FROM ORDERS, SHIPPING
WHERE ORDERS.Order_ID = SHIPPING.Order_ID;

And you also can download the slides and sample script from this link.

What’s more, here are some learning resources for your reference:

1. Books

   • Date, C. J. An Introduction to Database Systems (8th edition).

   • Ullman, J. D. (2007). A first Course in Database Systems. Pearson Education India.

   • Beaulieu, A. (2009). Learning SQL: Master SQL Fundamentals. O’Reilly Media, Inc.

2. Online Website

   • SQL in tutorialspoint.com

   • w3school SQL Tutorial

   • SQLZOO

   • SQLBolt

3. Video Courses

   • Khan Academy

thumbnail by Taylor Vick