The Language of SQL, 2nd Edition
ISBN 978-0-13-465825-4
Larry Rockoff
Copyright (c) 2017 by Pearson Education, Inc.


SQL Statements for Oracle



- - - - - - — - - - - - - - - - - - - -
HOW TO USE THIS DOCUMENT
- - - - - - — - - - - - - - - - - - - -



This document contains all the SQL statements in "The Language of SQL" in the syntax of Oracle.

Additionally, this document allows you to create the same data used in the book. This will allow you to execute the SQL statements and see the same results.

There is one prerequisite:

1) Install Oracle Database Express Edition

Appendix C of "The Language of SQL" has instructions on how do the install and create a database.

To use any of the SQL statements in this document, simply copy the desired statements into Oracle Database Express.

What follows is:

1) A Setup Script

The setup script allow you to create the data needed to execute all statements in the book. These scripts consist of CREATE TABLE commands to create the tables and INSERT statements to insert data into those tables. There are also DROP TABLE statements which delete the tables if that table already exists. You will need to create a database prior to executing this script. After creating a database, copy the entire script into a query window and execute.

2) SQL Statements from the Book

Listed by chapter, each statement in the book is shown in the correct syntax for Oracle. If a particular statement doesn't apply to Oracle, then it isn't shown. There are no scripts or SQL statements for chapters 1, 16, 19, or 20.



- - - - - - — - - - - - - - - - - - - -
SETUP SCRIPT
- - - - - - — - - - - - - - - - - - - -



-- Customers table:

DROP TABLE Customers;

CREATE TABLE Customers
(CustomerID INT NOT NULL,
FirstName VARCHAR(30) NULL,
LastName VARCHAR(30) NULL);

INSERT INTO Customers (CustomerID, FirstName, LastName)
VALUES (1, 'Sara', 'Davis');

INSERT INTO Customers (CustomerID, FirstName, LastName)
VALUES (2, 'Rumi', 'Shah');

INSERT INTO Customers (CustomerID, FirstName, LastName)
VALUES (3, 'Paul', 'Johnson');

INSERT INTO Customers (CustomerID, FirstName, LastName)
VALUES (4, 'Samuel', 'Martinez');

-- Sales table:

DROP TABLE Sales;

CREATE TABLE Sales
(SalesID INT NOT NULL,
FirstName VARCHAR(30) NULL,
LastName VARCHAR(30) NULL,
QuantityPurchased INT NULL,
PricePerItem FLOAT NULL);

INSERT INTO Sales (SalesID, FirstName, LastName, QuantityPurchased, PricePerItem)
VALUES (1, 'Andrew', 'Li', 4, 2.5);

INSERT INTO Sales (SalesID, FirstName, LastName, QuantityPurchased, PricePerItem)
VALUES (2, 'Carol', 'White', 10, 1.25);

INSERT INTO Sales (SalesID, FirstName, LastName, QuantityPurchased, PricePerItem)
VALUES (3, 'James', 'Carpenter', 5, 4);

-- Products table:

DROP TABLE Products;

CREATE TABLE Products
(ProductID INT NOT NULL,
Description VARCHAR(30) NULL,
Weight INT NULL);

INSERT INTO Products (ProductID, Description)
VALUES (1, 'Printer A');

INSERT INTO Products (ProductID, Description, Weight)
VALUES (2, 'Printer B', 0);

INSERT INTO Products (ProductID, Description, Weight)
VALUES (3, 'Monitor C', 2);

INSERT INTO Products (ProductID, Description, Weight)
VALUES (3, 'Laptop D', 4);

-- Salespeople table:

DROP TABLE Salespeople;

CREATE TABLE Salespeople
(SalespersonID INT NOT NULL,
FirstName VARCHAR(30) NULL,
LastName VARCHAR(30) NULL);

INSERT INTO Salespeople (SalespersonID, FirstName, LastName)
VALUES (1, 'Gregory', 'Brown');

INSERT INTO Salespeople (SalespersonID, FirstName, LastName)
VALUES (2, 'Carla', 'Brown');

INSERT INTO Salespeople (SalespersonID, FirstName, LastName)
VALUES (3, 'Natalie', 'Lopez');

INSERT INTO Salespeople (SalespersonID, FirstName, LastName)
VALUES (4, 'Connie', 'King');

-- TableForSort table:

DROP TABLE TableForSort;

CREATE TABLE TableForSort
(TableID INT NOT NULL,
CharacterData VARCHAR(30) NULL,
NumericData INT NULL);

INSERT INTO TableForSort (TableID, CharacterData, NumericData)
VALUES (1, '23', 23);

INSERT INTO TableForSort (TableID, CharacterData, NumericData)
VALUES (2, '5', 5);

INSERT INTO TableForSort (TableID, CharacterData)
VALUES (3, 'Dog');

INSERT INTO TableForSort (TableID, NumericData)
VALUES (4, -6);

-- Books table:

DROP TABLE Books;

CREATE TABLE Books
(BookID INT NOT NULL,
Title VARCHAR(30) NULL,
Author VARCHAR (30) NULL,
CurrentMonthSales INT NULL);

INSERT INTO Books (BookID, Title, Author, CurrentMonthSales) VALUES (1, 'Pride and Prejudice', 'Austen', 15);

INSERT INTO Books (BookID, Title, Author, CurrentMonthSales) VALUES (2, 'Animal Farm', 'Orwell', 7);

INSERT INTO Books (BookID, Title, Author, CurrentMonthSales) VALUES (3, 'Merchant of Venice', 'Shakespeare', 5);

INSERT INTO Books (BookID, Title, Author, CurrentMonthSales) VALUES (4, 'Romeo and Juliet', 'Shakespeare', 8);

INSERT INTO Books (BookID, Title, Author, CurrentMonthSales) VALUES (5, 'Oliver Twist', 'DIckens', 3);

INSERT INTO Books (BookID, Title, Author, CurrentMonthSales) VALUES (6, 'Candide', 'Voltaire', 9);

INSERT INTO Books (BookID, Title, Author, CurrentMonthSales) VALUES (7, 'The Scarlet Letter', 'Hawthorne', 12);

INSERT INTO Books (BookID, Title, Author, CurrentMonthSales) VALUES (8, 'Hamlet', 'Shakespeare', 2);

-- Movies table:

DROP TABLE Movies;

CREATE TABLE Movies
(MovieID INT NOT NULL,
MovieTitle VARCHAR(30) NULL,
Rating VARCHAR(10) NULL);

INSERT INTO Movies (MovieID, MovieTitle, Rating) VALUES (1, 'Love Actually', 'R');

INSERT INTO Movies (MovieID, MovieTitle, Rating) VALUES (2, 'North by Northwest', 'Not Rated');

INSERT INTO Movies (MovieID, MovieTitle, Rating) VALUES (3, 'Love and Death', 'PG');

INSERT INTO Movies (MovieID, MovieTitle, Rating) VALUES (4, 'The Truman Show', 'PG');

INSERT INTO Movies (MovieID, MovieTitle, Rating) VALUES (5, 'Everyone Says I Love You', 'R');

INSERT INTO Movies (MovieID, MovieTitle, Rating) VALUES (6, 'Down with Love', 'PG-13');

INSERT INTO Movies (MovieID, MovieTitle, Rating) VALUES (7, 'Finding Nemo', 'G');

-- Actors table:

DROP TABLE Actors;

CREATE TABLE Actors
(ActorID INT NOT NULL,
FirstName VARCHAR(30) NULL,
LastName VARCHAR(30) NULL);

INSERT INTO Actors (ActorID, FirstName, LastName) VALUES (1, 'Cary', 'Grant');

INSERT INTO Actors (ActorID, FirstName, LastName) VALUES (2, 'Mary', 'Steenburgen');

INSERT INTO Actors (ActorID, FirstName, LastName) VALUES (3, 'Jon', 'Voight');

INSERT INTO Actors (ActorID, FirstName, LastName) VALUES (4, 'Dustin', 'Hoffman');

INSERT INTO Actors (ActorID, FirstName, LastName) VALUES (5, 'John', 'Wayne');

INSERT INTO Actors (ActorID, FirstName, LastName) VALUES (6, 'Gary', 'Cooper');

-- Purchases table:

DROP TABLE Purchases;

CREATE TABLE Purchases
(PurchaseID INT NOT NULL,
CustomerName VARCHAR(30) NULL,
State VARCHAR (5) NULL,
QuantityPurchased INT NULL,
PricePerItem FLOAT NULL);

INSERT INTO Purchases (PurchaseID, CustomerName, State, QuantityPurchased, PricePerItem) VALUES (1, 'Andrew Li', 'IL', 4, 2.5);

INSERT INTO Purchases (PurchaseID, CustomerName, State, QuantityPurchased, PricePerItem) VALUES (1, 'Carol White', 'CA', 10, 1.25);

INSERT INTO Purchases (PurchaseID, CustomerName, State, QuantityPurchased, PricePerItem) VALUES (1, 'James Carpenter', 'NY', 5, 4);

-- Groceries table:

DROP TABLE Groceries;

CREATE TABLE Groceries
(GroceryID INT NOT NULL,
CategoryCode VARCHAR(5) NULL,
Description VARCHAR (30) NULL);

INSERT INTO Groceries (GroceryID, CategoryCode, Description) VALUES (1, 'F', 'Apple');

INSERT INTO Groceries (GroceryID, CategoryCode, Description) VALUES (2, 'F', 'Orange');

INSERT INTO Groceries (GroceryID, CategoryCode, Description) VALUES (3, 'S', 'Mustard');

INSERT INTO Groceries (GroceryID, CategoryCode, Description) VALUES (4, 'V', 'Carrot');

INSERT INTO Groceries (GroceryID, CategoryCode, Description) VALUES (5, 'B', 'Water');

-- GroceryCategories table:

DROP TABLE GroceryCategories;

CREATE TABLE GroceryCategories
(GroceryID INT NOT NULL,
Fruit VARCHAR(5) NULL,
Vegetable VARCHAR (5) NULL,
Spice VARCHAR (5) NULL,
Beverage VARCHAR (5) NULL,
Description VARCHAR (30) NULL);

INSERT INTO GroceryCategories (GroceryID, Fruit, Vegetable, Spice, Beverage, Description) VALUES (1, 'X', ' ', ' ', ' ', 'Apple');

INSERT INTO GroceryCategories (GroceryID, Fruit, Vegetable, Spice, Beverage, Description) VALUES (2, 'X', ' ', ' ', ' ', 'Orange');

INSERT INTO GroceryCategories (GroceryID, Fruit, Vegetable, Spice, Beverage, Description) VALUES (3, ' ', ' ', 'X', ' ', 'Mustard');

INSERT INTO GroceryCategories (GroceryID, Fruit, Vegetable, Spice, Beverage, Description) VALUES (4, ' ', 'X', ' ', ' ', 'Carrot');

INSERT INTO GroceryCategories (GroceryID, Fruit, Vegetable, Spice, Beverage, Description) VALUES (5, ' ', ' ', ' ', 'X', 'Water');

-- NorthAmerica table:

DROP TABLE NorthAmerica;

CREATE TABLE NorthAmerica
(CityID INT NOT NULL,
Country VARCHAR(5) NULL,
State VARCHAR (5) NULL,
Province VARCHAR (5) NULL,
City VARCHAR (30) NULL);

INSERT INTO NorthAmerica (CityID, Country, State, Province, City) VALUES (1, 'US', 'VT', ' ', 'Burlington');

INSERT INTO NorthAmerica (CityID, Country, State, Province, City) VALUES (2, 'CA', ' ', 'QU', 'Montreal');

INSERT INTO NorthAmerica (CityID, Country, State, Province, City) VALUES (3, 'US', 'CO', ' ', 'Denver');

INSERT INTO NorthAmerica (CityID, Country, State, Province, City) VALUES (4, 'US', 'CO', ' ', 'Boulder');

INSERT INTO NorthAmerica (CityID, Country, State, Province, City) VALUES (5, 'CA', ' ', 'AB', 'Edmonton');

-- CustomerList table:

DROP TABLE CustomerList;

CREATE TABLE CustomerList
(CustomerID INT NOT NULL,
Sex VARCHAR(5) NULL,
Age INT NULL,
Income INT NULL);

INSERT INTO CustomerList (CustomerID, Sex, Age, Income) VALUES (1, 'M', 55, 80000);

INSERT INTO CustomerList (CustomerID, Sex, Age, Income) VALUES (2, 'F', 25, 65000);

INSERT INTO CustomerList (CustomerID, Sex, Age, Income) VALUES (3, 'M', 35, 40000);

INSERT INTO CustomerList (CustomerID, Sex, Age, Income) VALUES (4, 'F', 42, 90000);

INSERT INTO CustomerList (CustomerID, Sex, Age, Income) VALUES (5, 'F', 27, 25000);

-- SongTitles table:

DROP TABLE SongTitles;

CREATE TABLE SongTitles
(SongID INT NOT NULL,
Artist VARCHAR(30) NULL,
Album VARCHAR(30) NULL,
Title VARCHAR(30) NULL);

INSERT INTO SongTitles (SongID, Artist, Album, Title) VALUES (1, 'The Beatles', 'Revolver', 'Yellow Submarine');

INSERT INTO SongTitles (SongID, Artist, Album, Title) VALUES (2, 'The Beatles', 'Revolver', 'Eleanor Rigby');

INSERT INTO SongTitles (SongID, Artist, Album, Title) VALUES (3, 'The Beatles', 'Abbey Road', 'Here Comes the Sun');

INSERT INTO SongTitles (SongID, Artist, Album, Title) VALUES (4, 'The Rolling Stones', 'Beggars Banquet', 'Sympathy for the Devil');

INSERT INTO SongTitles (SongID, Artist, Album, Title) VALUES (5, 'The Rolling Stones', 'Let It Bleed', 'Gimme Shelter');

INSERT INTO SongTitles (SongID, Artist, Album, Title) VALUES (6, 'Paul McCartney', 'Ram', 'Too Many People');

-- Fees table:

DROP TABLE Fees;

CREATE TABLE Fees
(FeeID INT NOT NULL,
Student VARCHAR(30) NULL,
FeeType VARCHAR(30) NULL,
Fee INT NULL);

INSERT INTO Fees (FeeID, Student, FeeType, Fee) VALUES (1, 'Jose', 'Gym', 30);

INSERT INTO Fees (FeeID, Student, FeeType, Fee) VALUES (2, 'Jose', 'Lunch', 10);

INSERT INTO Fees (FeeID, Student, FeeType, Fee) VALUES (3, 'Jose', 'Trip', 8);

INSERT INTO Fees (FeeID, Student, FeeType, Fee) VALUES (4, 'Rama', 'Gym', 30);

INSERT INTO Fees (FeeID, Student, FeeType, Fee) VALUES (5, 'Julie', 'Lunch', 10);

-- Grades table:

DROP TABLE Grades;

CREATE TABLE Grades
(GradeID INT NOT NULL,
Student VARCHAR(30) NULL,
GradeType VARCHAR(30) NULL,
Grade DECIMAL NULL,
YearInSchool INT NULL);

INSERT INTO Grades (GradeID, Student, GradeType, Grade, YearInSchool) VALUES (1, 'Isabella', 'Quiz', 92, 7);

INSERT INTO Grades (GradeID, Student, GradeType, Grade, YearInSchool) VALUES (2, 'Isabella', 'Quiz', 95, 7);

INSERT INTO Grades (GradeID, Student, GradeType, Grade, YearInSchool) VALUES (3, 'Isabella', 'Homework', 84, 7);

INSERT INTO Grades (GradeID, Student, GradeType, Grade, YearInSchool) VALUES (4, 'Hailey', 'Quiz', 62, 8);

INSERT INTO Grades (GradeID, Student, GradeType, Grade, YearInSchool) VALUES (5, 'Hailey', 'Quiz', 81, 8);

INSERT INTO Grades (GradeID, Student, GradeType, YearInSchool) VALUES (6, 'Hailey', 'Homework', 8);

INSERT INTO Grades (GradeID, Student, GradeType, Grade, YearInSchool) VALUES (7, 'Peter', 'Quiz', 58, 7);

INSERT INTO Grades (GradeID, Student, GradeType, Grade, YearInSchool) VALUES (8, 'Peter', 'Quiz', 74, 7);

INSERT INTO Grades (GradeID, Student, GradeType, Grade, YearInSchool) VALUES (9, 'Peter', 'Homework', 88, 7);

-- Stocks table:

DROP TABLE Stocks;

CREATE TABLE Stocks
(StockSymbol VARCHAR(10) NOT NULL,
StockName VARCHAR(30) NULL,
Exchange VARCHAR(10) NULL,
PriceEarningsRatio INT NULL);

INSERT INTO Stocks (StockSymbol, StockName, Exchange, PriceEarningsRatio) VALUES ('AAPL', 'Apple Inc', 'NASDAQ', 14);

INSERT INTO Stocks (StockSymbol, StockName, Exchange, PriceEarningsRatio) VALUES ('AMZN', 'Amazon.com Inc', 'NASDAQ', 489);

INSERT INTO Stocks (StockSymbol, StockName, Exchange, PriceEarningsRatio) VALUES ('DIS', 'The Walt Disney Company', 'NYSE', 21);

INSERT INTO Stocks (StockSymbol, StockName, Exchange, PriceEarningsRatio) VALUES ('GE', 'General Electric Company', 'NYSE', 18);

INSERT INTO Stocks (StockSymbol, StockName, Exchange, PriceEarningsRatio) VALUES ('GOOG', 'Alphabet Inc', 'NASDAQ', 30);

INSERT INTO Stocks (StockSymbol, StockName, Exchange, PriceEarningsRatio) VALUES ('HSY', 'The Hershey Company', 'NYSE', 26);

INSERT INTO Stocks (StockSymbol, StockName, Exchange, PriceEarningsRatio) VALUES ('KRFT', 'Kraft Foods Inc', 'NYSE', 12);

INSERT INTO Stocks (StockSymbol, StockName, Exchange, PriceEarningsRatio) VALUES ('KO', 'The Coca-Cola Company', 'NYSE', 21);

INSERT INTO Stocks (StockSymbol, StockName, Exchange, PriceEarningsRatio) VALUES ('MCD', 'McDonalds Corporation', 'NYSE', 18);

INSERT INTO Stocks (StockSymbol, StockName, Exchange, PriceEarningsRatio) VALUES ('MMM', '3M Company', 'NYSE', 20);

INSERT INTO Stocks (StockSymbol, StockName, Exchange, PriceEarningsRatio) VALUES ('MSFT', 'Microsoft Corporation', 'NASDAQ', 15);

INSERT INTO Stocks (StockSymbol, StockName, Exchange, PriceEarningsRatio) VALUES ('ORCL', 'Oracle Corporation', 'NASDAQ', 17);

INSERT INTO Stocks (StockSymbol, StockName, Exchange, PriceEarningsRatio) VALUES ('SBUX', 'Starbucks Corporation', 'NASDAQ', 357);

INSERT INTO Stocks (StockSymbol, StockName, Exchange, PriceEarningsRatio) VALUES ('WBA', 'Walgreens Boots Alliance Inc', 'NYSE', 24);

INSERT INTO Stocks (StockSymbol, StockName, Exchange, PriceEarningsRatio) VALUES ('WMT', 'Wal-Mart Stores Inc', 'NYSE', 15);

-- Inventory table:

DROP TABLE Inventory;

CREATE TABLE Inventory
(InventoryID INT NOT NULL,
Category VARCHAR(30) NULL,
Subcategory VARCHAR(30) NULL,
Product VARCHAR(30) NULL,
Quantity INT NULL);

INSERT INTO Inventory (InventoryID, Category, Subcategory, Product, Quantity) VALUES (1, 'Furniture', 'Chair', 'Red Armchair', 3);

INSERT INTO Inventory (InventoryID, Category, Subcategory, Product, Quantity) VALUES (2, 'Furniture', 'Chair', 'Green Armchair', 2);

INSERT INTO Inventory (InventoryID, Category, Subcategory, Product, Quantity) VALUES (3, 'Furniture', 'Desk', 'Blue Computer Desk', 4);

INSERT INTO Inventory (InventoryID, Category, Subcategory, Product, Quantity) VALUES (4, 'Paper', 'Copy', 'White Copy Paper', 5);

INSERT INTO Inventory (InventoryID, Category, Subcategory, Product, Quantity) VALUES (5, 'Paper', 'Copy', 'Pink Copy Paper', 2);

INSERT INTO Inventory (InventoryID, Category, Subcategory, Product, Quantity) VALUES (6, 'Paper', 'Notebook', 'White Notebook Paper', 4);

-- SalesSumary table:

DROP TABLE SalesSummary;

CREATE TABLE SalesSummary
(SalesDate DATE NULL,
CustomerID INT NULL,
State VARCHAR(10) NULL,
Channel VARCHAR(10) NULL,
SalesAmount INT NULL);

INSERT INTO SalesSummary (SalesDate, CustomerID, State, Channel, SalesAmount) VALUES (TO_DATE('2017-04-01', 'YYYY/MM/DD'), 101, 'NY', 'Internet', 50);

INSERT INTO SalesSummary (SalesDate, CustomerID, State, Channel, SalesAmount) VALUES (TO_DATE('2017-04-01', 'YYYY/MM/DD'), 102, 'NY', 'Retail', 30);

INSERT INTO SalesSummary (SalesDate, CustomerID, State, Channel, SalesAmount) VALUES (TO_DATE('2017-04-01', 'YYYY/MM/DD'), 103, 'VT', 'Internet', 120);

INSERT INTO SalesSummary (SalesDate, CustomerID, State, Channel, SalesAmount) VALUES (TO_DATE('2017-04-02', 'YYYY/MM/DD'), 145, 'VT', 'Retail', 90);

INSERT INTO SalesSummary (SalesDate, CustomerID, State, Channel, SalesAmount) VALUES (TO_DATE('2017-04-02', 'YYYY/MM/DD'), 180, 'NY', 'Retail', 300);

INSERT INTO SalesSummary (SalesDate, CustomerID, State, Channel, SalesAmount) VALUES (TO_DATE('2017-04-02', 'YYYY/MM/DD'), 181, 'VT', 'Internet', 130);

INSERT INTO SalesSummary (SalesDate, CustomerID, State, Channel, SalesAmount) VALUES (TO_DATE('2017-04-02', 'YYYY/MM/DD'), 182, 'NY', 'Internet', 520);

INSERT INTO SalesSummary (SalesDate, CustomerID, State, Channel, SalesAmount) VALUES (TO_DATE('2017-04-02', 'YYYY/MM/DD'), 184, 'NY', 'Retail', 80);

-- Orders table:

DROP TABLE Orders;

CREATE TABLE Orders
(OrderID INT NOT NULL,
CustomerID INT NOT NULL,
OrderDate DATE NULL,
OrderAmount FLOAT NULL);

INSERT INTO Orders (OrderID, CustomerID, OrderDate, OrderAmount) VALUES (1, 1, TO_DATE('2016-09-01', 'YYYY/MM/DD'), 10);

INSERT INTO Orders (OrderID, CustomerID, OrderDate, OrderAmount) VALUES (2, 2, TO_DATE('2016-09-02', 'YYYY/MM/DD'), 12.5);

INSERT INTO Orders (OrderID, CustomerID, OrderDate, OrderAmount) VALUES (3, 2, TO_DATE('2016-09-03', 'YYYY/MM/DD'), 18);

INSERT INTO Orders (OrderID, CustomerID, OrderDate, OrderAmount) VALUES (4, 3, TO_DATE('2016-09-15', 'YYYY/MM/DD'), 20);

-- Refunds table:

DROP TABLE Refunds;

CREATE TABLE Refunds
(RefundID INT NOT NULL,
OrderID INT NULL,
RefundDate DATE NULL,
RefundAmount FLOAT NULL);

INSERT INTO Refunds (RefundID, OrderID, RefundDate, RefundAmount) VALUES (1, 1, TO_DATE('2016-09-02', 'YYYY/MM/DD'), 5);

INSERT INTO Refunds (RefundID, OrderID, RefundDate, RefundAmount) VALUES (2, 3, TO_DATE('2016-09-18', 'YYYY/MM/DD'), 18);

-- Ratings table:

DROP TABLE Ratings;

CREATE TABLE Ratings
(RatingID INT NOT NULL,
Rating VARCHAR(10) NULL,
RatingDescription VARCHAR(30) NULL);

INSERT INTO Ratings (RatingID, Rating, RatingDescription) VALUES (1, 'G', 'General Audiences');

INSERT INTO Ratings (RatingID, Rating, RatingDescription) VALUES (2, 'PG', 'Parental Guidance Suggested');

INSERT INTO Ratings (RatingID, Rating, RatingDescription) VALUES (3, 'PG-13', 'Parents Strongly Cautioned');

INSERT INTO Ratings (RatingID, Rating, RatingDescription) VALUES (4, 'R', 'Restricted');

INSERT INTO Ratings (RatingID, Rating, RatingDescription) VALUES (5, 'NC-17', 'Under 17 Not Admitted');

-- SizeInventory table:

DROP TABLE SizeInventory;

CREATE TABLE SizeInventory
(SizeID INT NOT NULL,
"Size" VARCHAR(10) NULL);

INSERT INTO SizeInventory (SizeID, "Size") VALUES (1, 'Small');

INSERT INTO SizeInventory (SizeID, "Size") VALUES (2, 'Medium');

INSERT INTO SizeInventory (SizeID, "Size") VALUES (3, 'Large');

-- ColorInventory table:

DROP TABLE ColorInventory;

CREATE TABLE ColorInventory
(ColorID INT NOT NULL,
Color VARCHAR(10) NULL);

INSERT INTO ColorInventory (ColorID, Color) VALUES (1, 'Red');

INSERT INTO ColorInventory (ColorID, Color) VALUES (2, 'Blue');

INSERT INTO ColorInventory (ColorID, Color) VALUES (3, 'Green');

INSERT INTO ColorInventory (ColorID, Color) VALUES (4, 'Yellow');

-- SpecialDates table:

DROP TABLE SpecialDates;

CREATE TABLE SpecialDates
(LastProcessDate DATE NULL,
CurrentFiscalYear INT NULL,
CurrentFiscalQuarter VARCHAR(10) NULL);

INSERT INTO SpecialDates (LastProcessDate, CurrentFiscalYear, CurrentFiscalQuarter) VALUES (TO_DATE('2016-09-15', 'YYYY/MM/DD'), 2016, 'Q3');

-- Personnel table:

DROP TABLE Personnel;

CREATE TABLE Personnel
(EmployeeID INT NOT NULL,
EmployeeName VARCHAR(30) NULL,
ManagerID INT NULL);

INSERT INTO Personnel (EmployeeID, EmployeeName) VALUES (1, 'Susan Carter');

INSERT INTO Personnel (EmployeeID, EmployeeName, ManagerID) VALUES (2, 'Li Wang', 1);

INSERT INTO Personnel (EmployeeID, EmployeeName, ManagerID) VALUES (3, 'Robert Baker', 1);

INSERT INTO Personnel (EmployeeID, EmployeeName, ManagerID) VALUES (4, 'Scott Fielding', 1);

INSERT INTO Personnel (EmployeeID, EmployeeName, ManagerID) VALUES (5, 'Carla Bender', 2);

INSERT INTO Personnel (EmployeeID, EmployeeName, ManagerID) VALUES (6, 'Janet Brown', 2);

INSERT INTO Personnel (EmployeeID, EmployeeName, ManagerID) VALUES (7, 'Jules Moreau', 3);

INSERT INTO Personnel (EmployeeID, EmployeeName, ManagerID) VALUES (8, 'Amy Adamson', 4);

INSERT INTO Personnel (EmployeeID, EmployeeName, ManagerID) VALUES (9, 'Jaideep Singh', 4);

INSERT INTO Personnel (EmployeeID, EmployeeName, ManagerID) VALUES (10, 'Amelia Williams', 5);

-- Users table:

DROP TABLE Users;

CREATE TABLE Users
(UserID INT NOT NULL,
UserName VARCHAR (30) NULL);

INSERT INTO Users (UserID, UserName)
VALUES (1, 'Cecilia Rodriguez');

INSERT INTO Users (UserID, UserName)
VALUES (2, 'Elaine Bundy');

INSERT INTO Users (UserID, UserName)
VALUES (3, 'Rakesh Gupta');

INSERT INTO Users (UserID, UserName)
VALUES (4, 'April Waters');

-- Transactions table:

DROP TABLE Transactions;

CREATE TABLE Transactions
(TransactionID INT NOT NULL,
UserID INT NULL,
TransactionAmount FLOAT NULL,
TransactionType VARCHAR (10) NULL);

INSERT INTO Transactions (TransactionID, UserID, TransactionAmount, TransactionType)
VALUES (1, 1, 22.25, 'Cash');

INSERT INTO Transactions (TransactionID, UserID, TransactionAmount, TransactionType)
VALUES (2, 2, 11.75, 'Credit');

INSERT INTO Transactions (TransactionID, UserID, TransactionAmount, TransactionType)
VALUES (3, 2, 5, 'Credit');

INSERT INTO Transactions (TransactionID, UserID, TransactionAmount, TransactionType)
VALUES (4, 2, 8, 'Cash');

INSERT INTO Transactions (TransactionID, UserID, TransactionAmount, TransactionType)
VALUES (5, 3, 9.33, 'Credit');

INSERT INTO Transactions (TransactionID, UserID, TransactionAmount, TransactionType)
VALUES (6, 3, 10.11, 'Credit');

-- Returns table:

DROP TABLE Returns;

CREATE TABLE Returns
(ReturnID INT NOT NULL,
CustomerID INT NOT NULL,
ReturnDate DATE NULL,
ReturnAmount FLOAT NULL);

INSERT INTO Returns (ReturnID, CustomerID, ReturnDate, ReturnAmount) VALUES (1, 1, TO_DATE('2016-09-10', 'YYYY/MM/DD'), 2);

INSERT INTO Returns (ReturnID, CustomerID, ReturnDate, ReturnAmount) VALUES (2, 2, TO_DATE('2016-09-15', 'YYYY/MM/DD'), 15);

INSERT INTO Returns (ReturnID, CustomerID, ReturnDate, ReturnAmount) VALUES (3, 3, TO_DATE('2016-09-28', 'YYYY/MM/DD'), 3);

-- Clients table:

DROP TABLE Clients;

CREATE TABLE Clients
(FirstName VARCHAR(30) NULL,
LastName VARCHAR(30) NULL,
State VARCHAR(10) NULL);

INSERT INTO Clients (FirstName, LastName, State) VALUES ('Joyce', 'Bentley', 'TN');

INSERT INTO Clients (FirstName, LastName, State) VALUES ('Miguel', 'Ramirez', 'PA');

INSERT INTO Clients (FirstName, LastName, State) VALUES ('Ellen', 'Baker', 'OR');

-- NewClients table:

DROP TABLE NewClients;

CREATE TABLE NewClients
(State VARCHAR(10) NULL,
Name1 VARCHAR(30) NULL,
Name2 VARCHAR(30) NULL);

INSERT INTO NewClients (State, Name1, Name2) VALUES ('RI', 'Roberto', 'Harris');

INSERT INTO NewClients (State, Name1, Name2) VALUES ('PA', 'Beata', 'Kowalski');

INSERT INTO NewClients (State, Name1, Name2) VALUES ('RI', 'Galina', 'Melnyk');

-- Vendors table:

DROP TABLE Vendors;

CREATE TABLE Vendors
(VendorID INT NOT NULL,
State VARCHAR(10) NULL,
Zip VARCHAR(10) NULL);

INSERT INTO Vendors (VendorID, State, Zip) VALUES (1, 'NY', '10605');

INSERT INTO Vendors (VendorID, State, Zip) VALUES (2, 'FL', '33431');

INSERT INTO Vendors (VendorID, State, Zip) VALUES (3, 'CA', '94704');

INSERT INTO Vendors (VendorID, State, Zip) VALUES (4, 'CO', '80302');

INSERT INTO Vendors (VendorID, State, Zip) VALUES (5, 'WY', '83001');

-- VendorTransactions table:

DROP TABLE VendorTransactions;

CREATE TABLE VendorTransactions
(TransactionID INT NOT NULL,
VendorID INT NULL,
State VARCHAR(10) NULL,
Zip VARCHAR(10) NULL);

INSERT INTO VendorTransactions (TransactionID, VendorID, State, Zip) VALUES (1, 1, 'NJ', '07030');

INSERT INTO VendorTransactions (TransactionID, VendorID, State, Zip) VALUES (2, 2, 'FL', '33139');

INSERT INTO VendorTransactions (TransactionID, VendorID, State, Zip) VALUES (3, 5, 'OR', '97401');

-- RelatedTable table:

DROP TABLE RelatedTable;

CREATE TABLE RelatedTable
(FirstColumn INT NOT NULL PRIMARY KEY);



- - - - - - — - - - - - - - - - - - - -
CHAPTER 2 SQL STATEMENTS
- - - - - - — - - - - - - - - - - - - -



SELECT * FROM Customers;

SELECT *
FROM Customers;

SELECT
-- this is the first comment
FirstName,
LastName -- this is a second comment
FROM Customers;

SELECT
/* this is the first comment */
FirstName,
LastName /* this is a second comment
this is still part of the second comment
this is the end of the second comment */
FROM Customers;

SELECT LastName
FROM Customers;

SELECT 
FirstName, 
LastName
FROM Customers;



- - - - - - — - - - - - - - -
CHAPTER 3 SQL STATEMENTS
- - - - - - — - - - - - - - -



SELECT
'First Name:',
FirstName
FROM Sales;

SELECT
5,
FirstName
FROM Sales;

SELECT
SalesID,
QuantityPurchased,
PricePerItem,
QuantityPurchased * PricePerItem
FROM Sales;

SELECT
SalesID,
FirstName,
LastName,
FirstName || ' ' || LastName
FROM Sales;

SELECT
SalesID,
FirstName,
LastName,
FirstName || ' ' || LastName AS "Name"
FROM Sales;

SELECT
SalesID,
FirstName,
LastName,
FirstName || ' ' || LastName Name
FROM Sales;



- - - - - - — - - - - - - - - - - - - -
CHAPTER 4 SQL STATEMENTS
- - - - - - — - - - - - - - - - - - - -



SELECT 
SUBSTR('thewhitegoat', 4, 5) AS "The Answer"
FROM DUAL;

SELECT 
LTRIM('          the apple') AS "The Answer"
FROM DUAL;

SELECT 
UPPER('Abraham Lincoln') AS "Convert to Uppercase",
LOWER('ABRAHAM LINCOLN') AS "Convert to Lowercase"
FROM DUAL;

SELECT CURRENT_DATE
FROM DUAL;

SELECT DATE_FORMAT('2017-05-06', '%m')
FROM DUAL;

SELECT ROUND(712.863, 3)
FROM DUAL;

SELECT ROUND(712.863, 2)
FROM DUAL;

SELECT ROUND(712.863, 1)
FROM DUAL;

SELECT ROUND(712.863, 0)
FROM DUAL;

SELECT ROUND(712.863, -1)
FROM DUAL;

SELECT ROUND(712.863, -2)
FROM DUAL;

SELECT POWER(5,2) AS "5 Squared"
FROM DUAL;

SELECT POWER(25,.5) AS "Square Root of 25"
FROM DUAL;

SELECT 
'2017-04-11' AS 'Original Date',
CAST('2017-04-11' AS DATE) AS "Converted Date";

SELECT 
Description, 
Weight
FROM Products;

SELECT 
Description,
NVL(CAST(Weight AS CHAR), 'Unknown') AS "Weight"
FROM Products;

 

- - - - - - — - - - - - - - - - - - - -
CHAPTER 5 SQL STATEMENTS
- - - - - - — - - - - - - - - - - - - -



SELECT 
FirstName, 
LastName
FROM Salespeople
ORDER BY LastName;

SELECT 
FirstName, 
LastName
FROM Salespeople
ORDER BY FirstName;

SELECT 
FirstName, 
LastName
FROM Salespeople
ORDER BY FirstName ASC;

SELECT 
FirstName, 
LastName
FROM Salespeople
ORDER BY FirstName DESC;

SELECT 
FirstName, 
LastName
FROM Salespeople
ORDER BY LastName, FirstName;

SELECT 
LastName || ', ' || FirstName AS "Name"
FROM Salespeople
ORDER BY LastName || ', ' || FirstName;

SELECT 
FirstName, 
LastName
FROM Salespeople
ORDER BY LastName || FirstName;

SELECT NumericData
FROM TableForSort
ORDER BY NumericData;

SELECT 
NVL(NumericData, 0) AS "NumericData"
FROM TableForSort
ORDER BY NVL(NumericData,0);

SELECT 
CharacterData
FROM TableForSort
ORDER BY CharacterData;



- - - - - - — - - - - - - - - - - - - -
CHAPTER 6 SQL STATEMENTS
- - - - - - — - - - - - - - - - - - - -



SELECT 
FirstName, 
LastName, 
QuantityPurchased
FROM Sales
WHERE LastName = 'Carpenter';

SELECT 
FirstName, 
LastName, 
QuantityPurchased
FROM Sales
WHERE QuantityPurchased = 5;

SELECT 
FirstName, 
LastName, 
QuantityPurchased
FROM Sales
WHERE QuantityPurchased > 6;

SELECT 
FirstName, 
LastName
FROM Sales
WHERE LastName > 'K';

SELECT 
Title AS "Book Title",
CurrentMonthSales AS "Quantity Sold"	
FROM
(SELECT *
FROM Books
ORDER BY CurrentMonthSales DESC)
WHERE ROWNUM <= 3;

SELECT *
FROM
(SELECT 
Title AS "Book Title",
CurrentMonthSales AS "Quantity Sold"
FROM Books
WHERE Author = 'Shakespeare'
ORDER BY CurrentMonthSales DESC)
WHERE ROWNUM <= 1;

SELECT 
MovieTitle AS "Movie"
FROM Movies
WHERE UPPER(MovieTitle) LIKE '%LOVE%';

SELECT 
MovieTitle AS "Movie"
FROM Movies
WHERE UPPER(MovieTitle) LIKE 'LOVE%';

SELECT 
MovieTitle AS "Movie"
FROM Movies
WHERE UPPER(MovieTitle) LIKE '%LOVE';

SELECT 
MovieTitle AS "Movie"
FROM Movies
WHERE MovieTitle LIKE '% LOVE %';

SELECT 
FirstName,
LastName
FROM Actors
WHERE UPPER(FirstName) LIKE '_ARY';

SELECT 
FirstName,
LastName
FROM Actors
WHERE UPPER(FirstName) LIKE 'J_N';



- - - - - - — - - - - - - - - - - - - -
CHAPTER 7 SQL STATEMENTS
- - - - - - — - - - - - - - - - - - - -



SELECT
CustomerName,
QuantityPurchased
FROM Purchases
WHERE QuantityPurchased > 3
AND QuantityPurchased < 7;

SELECT
CustomerName,
QuantityPurchased,
PricePerItem
FROM Purchases
WHERE QuantityPurchased > 8
OR PricePerItem > 3;

SELECT
CustomerName,
State,
QuantityPurchased
FROM Purchases
WHERE State = 'IL'
OR State = 'CA'
AND QuantityPurchased > 8;

SELECT
CustomerName,
State,
QuantityPurchased
FROM Purchases
WHERE (State = 'IL'
OR State = 'CA')
AND QuantityPurchased > 8;

SELECT
CustomerName,
State,
QuantityPurchased
FROM Purchases
WHERE State = 'NY'
OR (State = 'IL'
AND (QuantityPurchased >= 3
AND QuantityPurchased <= 10));

SELECT
CustomerName,
State,
QuantityPurchased
FROM Purchases
WHERE NOT State = 'NY';

SELECT
CustomerName,
State,
QuantityPurchased
FROM Purchases
WHERE State <> 'NY';

SELECT
CustomerName,
State,
QuantityPurchased
FROM Purchases
WHERE NOT (State = 'IL'
OR State = 'NY');

SELECT
CustomerName,
State,
QuantityPurchased
FROM Purchases
WHERE State <> 'IL'
AND State <> 'NY';

SELECT
CustomerName,
State,
QuantityPurchased
FROM Purchases
WHERE NOT (State = 'IL'
AND QuantityPurchased > 3);

SELECT
CustomerName,
State,
QuantityPurchased
FROM Purchases
WHERE State <> 'IL'
OR QuantityPurchased <= 3;

SELECT
CustomerName,
QuantityPurchased
FROM Purchases
WHERE QuantityPurchased >= 5
AND QuantityPurchased <= 20;

SELECT
CustomerName,
QuantityPurchased
FROM Purchases
WHERE QuantityPurchased BETWEEN 5 AND 20;

SELECT
CustomerName,
QuantityPurchased
FROM Purchases
WHERE QuantityPurchased NOT BETWEEN 5 AND 20;

SELECT
CustomerName,
State
FROM Purchases
WHERE State = 'IL'
OR State = 'NY';

SELECT
CustomerName,
State
FROM Purchases
WHERE State IN ('IL', 'NY');

SELECT
CustomerName,
State
FROM Purchases
WHERE State NOT IN ('IL', 'NY');

SELECT
Description,
Weight
FROM Products
WHERE Weight = 0;

SELECT
Description,
Weight
FROM Products
WHERE Weight = 0
OR Weight IS NULL;

SELECT
Description,
Weight
FROM Products
WHERE NVL(Weight, 0) = 0;

SELECT
Description,
NVL(Weight, 0) AS "Weight"
FROM Products
WHERE Weight = 0
OR Weight IS NULL;



- - - - - - — - - - - - - - - - - - - -
CHAPTER 8 SQL STATEMENTS
- - - - - - — - - - - - - - - - - - - -



SELECT
CASE CategoryCode
WHEN 'F' THEN 'Fruit'
WHEN 'V' THEN 'Vegetable'
ELSE 'Other'
END AS "Category",
Description
FROM Groceries;

SELECT
CASE
WHEN CategoryCode = 'F' THEN 'Fruit'
WHEN CategoryCode = 'V' THEN 'Vegetable'
ELSE 'Other'
END AS "Category",
Description
FROM Groceries;

SELECT
CASE
WHEN Fruit = 'X' THEN 'Fruit'
WHEN Vegetable = 'X' THEN 'Vegetable'
ELSE 'Other'
END AS "Category",
Description
FROM GroceryCategories;

SELECT *
FROM NorthAmerica
ORDER BY
Country,
CASE Country
WHEN 'US' THEN State
WHEN 'CA' THEN Province
ELSE State
END,
City;

SELECT *
FROM CustomerList
WHERE Income >
CASE
WHEN Sex = 'M' AND Age >= 50 THEN 75000
WHEN Sex = 'F' AND Age >= 35 THEN 60000
ELSE 50000
END;



- - - - - - — - - - - - - - - - - - - -
CHAPTER 9 SQL STATEMENTS
- - - - - - — - - - - - - - - - - - - -



SELECT
DISTINCT
Artist
FROM SongTitles
ORDER BY Artist;

SELECT
DISTINCT
Artist,
Album
FROM SongTitles
ORDER BY Artist, Album;

SELECT
SUM(Fee) AS "Total Gym Fees"
FROM Fees
WHERE FeeType = 'Gym';

SELECT
AVG(Grade) AS "Average Quiz Score"
FROM Grades
WHERE GradeType = 'Quiz';

SELECT
AVG(Grade) AS "Average Quiz Score",
MIN(Grade) AS "Minimum Quiz Score",
MAX(Grade) AS "Maximum Quiz Score"
FROM Grades
WHERE GradeType = 'Quiz';

SELECT
COUNT(*) AS "Count of Homework Rows"
FROM Grades
WHERE GradeType = 'Homework';

SELECT
COUNT(Grade) AS "Count of Homework Scores"
FROM Grades
WHERE GradeType = 'Homework';

SELECT
COUNT(DISTINCT FeeType) AS "Number of Fee Types"
FROM Fees;

SELECT
GradeType AS "Grade Type",
AVG(Grade) AS "Average Grade"
FROM Grades
GROUP BY GradeType
ORDER BY GradeType;

SELECT
GradeType AS "Grade Type",
Student AS "Student",
AVG(Grade) AS "Average Grade"
FROM Grades
GROUP BY GradeType, Student
ORDER BY GradeType, Student;

SELECT
GradeType AS "Grade Type",
Student AS "Student",
AVG(Grade) AS "Average Grade"
FROM Grades
GROUP BY GradeType, Student
ORDER BY Student, GradeType;

SELECT
Student AS "Student",
GradeType AS "Grade Type",
AVG(Grade) AS "Average Grade"
FROM Grades
GROUP BY GradeType, Student
ORDER BY Student, GradeType;

SELECT
Student AS "Student",
GradeType AS "Grade Type",
Grade AS "Grade"
FROM Grades
WHERE GradeType = 'Quiz'
AND Grade >= 70
ORDER BY Student, Grade;

SELECT
Student AS "Student",
AVG(Grade) AS "Average Quiz Grade"
FROM Grades
WHERE GradeType = 'Quiz'
GROUP BY Student
HAVING AVG(Grade) >= 70
ORDER BY Student;

SELECT
Student AS "Student",
GradeType AS 'Grade Type',
AVG(Grade) AS "Average Grade"
FROM Grades
WHERE GradeType = 'Quiz'
GROUP BY Student, GradeType
HAVING AVG(Grade) >= 70
ORDER BY Student;

SELECT
CASE CategoryCode
WHEN 'F' THEN 'Fruit'
WHEN 'V' THEN 'Vegetable'
ELSE 'Other'
END AS "Category",
COUNT(*) AS "Count"
FROM Groceries
GROUP BY
CASE CategoryCode
WHEN 'F' THEN 'Fruit'
WHEN 'V' THEN 'Vegetable'
ELSE 'Other'
END;
 
SELECT
Student AS "Student",
YearInSchool AS "Year in School",
GradeType AS "Grade Type",
AVG(Grade) AS "Average Grade"
FROM Grades
WHERE GradeType = 'Quiz'
GROUP BY Student, YearInSchool, GradeType
HAVING AVG(Grade) >=
CASE 
WHEN YearInSchool = 7 THEN 70
WHEN YearInSchool = 8 THEN 75
ELSE 80 END
ORDER BY Student;

SELECT
ROW_NUMBER() OVER (ORDER BY PriceEarningsRatio) AS "Row",
StockSymbol AS "Symbol",
StockName AS "Name",
Exchange AS "Exchange",
PriceEarningsRatio AS "PE Ratio"
FROM Stocks
ORDER BY PriceEarningsRatio;

SELECT
ROW_NUMBER() OVER (ORDER BY PriceEarningsRatio) AS "Row",
RANK() OVER (ORDER BY PriceEarningsRatio) AS "Rank",
DENSE_RANK() OVER (ORDER BY PriceEarningsRatio) AS "Dense Rank",
StockSymbol AS "Symbol",
PriceEarningsRatio AS "PE Ratio"
FROM Stocks
ORDER BY PriceEarningsRatio;

SELECT
NTILE(4) OVER (ORDER BY PriceEarningsRatio) AS "Quartile",
NTILE(10) OVER (ORDER BY PriceEarningsRatio) AS "Decile",
StockSymbol AS "Symbol",
PriceEarningsRatio AS "PE Ratio"
FROM Stocks
ORDER BY PriceEarningsRatio;

SELECT
Exchange AS "Exchange",
ROW_NUMBER() OVER (PARTITION BY Exchange ORDER BY PriceEarningsRatio) 
AS "Exchange Rank",
StockSymbol AS "Symbol",
StockName AS "Name",
PriceEarningsRatio AS "PE Ratio"
FROM Stocks
ORDER BY Exchange, PriceEarningsRatio;

SELECT
Exchange AS "Exchange",
NTILE(4) OVER (PARTITION BY Exchange ORDER BY PriceEarningsRatio) 
AS "Quartile",
StockSymbol AS "Symbol",
PriceEarningsRatio AS "PE Ratio"
FROM Stocks
ORDER BY Exchange, PriceEarningsRatio;



- - - - - - — - - - - - - - - - - - - -
CHAPTER 10 SQL STATEMENTS
- - - - - - — - - - - - - - - - - - - -



SELECT 
Category,
Subcategory,
SUM(Quantity) AS "Quantity"
FROM Inventory
GROUP BY Category, Subcategory
ORDER BY Category, Subcategory;

SELECT 
Category,
Subcategory,
SUM(Quantity) AS "Quantity"
FROM Inventory
GROUP BY ROLLUP(Category, Subcategory);

SELECT
Category,
Subcategory,
SUM(Quantity) AS "Quantity"
FROM Inventory
GROUP BY ROLLUP(Category, Subcategory)
ORDER BY Category, Subcategory;

SELECT
Category,
Subcategory,
SUM(Quantity) AS "Quantity",
GROUPING(Category) AS "Category Grouping",
GROUPING(Subcategory) AS "Subcategory Grouping"
FROM Inventory
GROUP BY ROLLUP(Category, Subcategory);

SELECT
NVL(Category,'') AS "Category",
NVL(Subcategory, '') AS "Subcategory",
SUM(Quantity) AS "Quantity",
CASE WHEN GROUPING(Category) = 1 then 'Grand Total'
WHEN GROUPING(Subcategory) = 1 then 'Subtotal'
ELSE ' ' END AS "Subtotal/Total"
FROM Inventory
GROUP BY ROLLUP(Category, Subcategory);

SELECT
CASE
WHEN GROUPING(Category) = 1 THEN 'GRAND TOTAL'
WHEN GROUPING(Subcategory) = 1 THEN 'SUBTOTAL'
ELSE NVL(Category,'') END AS "Category",
NVL(Subcategory, '') AS "Subcategory",
SUM(Quantity) AS "Quantity"
FROM Inventory
GROUP BY ROLLUP(Category, Subcategory);

SELECT 
State,
Channel,
SUM(SalesAmount) AS "Sales Amount"
FROM SalesSummary
GROUP BY State, Channel
ORDER BY State, Channel;

SELECT 
State,
Channel,
SUM(SalesAmount) AS "Sales Amount"
FROM SalesSummary
GROUP BY CUBE(State, Channel)
ORDER BY State, Channel;

SELECT 
State,
Channel,
SUM(SalesAmount) AS "Sales Amount",
GROUPING(State) AS "State Grouping",
GROUPING(Channel) AS "Channel Grouping"
FROM SalesSummary
GROUP BY CUBE(State, Channel)
ORDER BY State, Channel;

SELECT
NVL(State,' ') AS "State",
NVL(Channel, ' ') AS "Channel",
SUM(SalesAmount) AS "Sales Amount",
CASE WHEN GROUPING(State) = 1
AND GROUPING(Channel) = 1 then 'Grand Total'
WHEN GROUPING(State) = 1 
AND GROUPING(Channel) = 0 then 'Channel Subtotal'
WHEN GROUPING(State) = 0
AND GROUPING(Channel) = 1 then 'State Subtotal'
ELSE ' ' END AS "Subtotal/Total"
FROM SalesSummary
GROUP BY CUBE(State, Channel)
ORDER BY
CASE 
WHEN GROUPING(State) = 0 and GROUPING(Channel) = 0 then 1
WHEN GROUPING(State) = 0 and GROUPING(Channel) = 1 then 2
WHEN GROUPING(State) = 1 and GROUPING(Channel) = 0 then 3
ELSE 4
END;

SELECT 
State,
Channel,
SUM(SalesAmount) AS "Sales Amount"
FROM SalesSummary
GROUP BY State, Channel
ORDER BY State, Channel;

SELECT
SalesDate, 
State, 
Channel, 
SUM(SalesAmount) AS Total
FROM SalesSummary
GROUP BY SalesDate, State, Channel
ORDER BY SalesDate, State, Channel;

SELECT * FROM
(SELECT SalesDate, Channel, State, SalesAmount FROM SalesSummary) 
PIVOT (SUM(SalesAmount) FOR State IN ('NY', 'VT'))
ORDER BY SalesDate;

SELECT * FROM
(SELECT Channel, SalesDate, State, SalesAmount FROM SalesSummary)
PIVOT (SUM(SalesAmount) FOR State IN ('NY', 'VT'))
ORDER BY Channel;

SELECT * FROM
(SELECT SalesDate, State, Channel, SalesAmount FROM SalesSummary)
PIVOT (SUM(SalesAmount) FOR Channel IN ('Internet', 'Retail'))
ORDER BY SalesDate;



- - - - - - — - - - - - - - - - - - - -
CHAPTER 11 SQL STATEMENTS
- - - - - - — - - - - - - - - - - - - -



SELECT * 
FROM Customers
INNER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID;

SELECT *
FROM Orders
INNER JOIN Customers
ON Orders.CustomerID = Customers.CustomerID;

SELECT *
FROM Customers, Orders
WHERE Customers.CustomerID = Orders.CustomerID;

SELECT
C.CustomerID AS "Cust ID",
C.FirstName AS "First Name", 
C.LastName AS "Last Name",
O.OrderID AS "Order ID",
O.OrderDate AS "Date",
O.OrderAmount AS "Amount"
FROM Customers C
INNER JOIN Orders O
ON C.CustomerID = O.CustomerID;



- - - - - - — - - - - - - - - - - - - -
CHAPTER 12 SQL STATEMENTS
- - - - - - — - - - - - - - - - - - - -



SELECT
Customers.FirstName AS "First Name",
Customers.LastName AS "Last Name", 
Orders.OrderDate AS "Order Date",
Orders.OrderAmount AS "Order Amt",
Refunds.RefundDate AS "Refund Date",
Refunds.RefundAmount AS "Refund Amt"
FROM Customers
LEFT JOIN Orders
ON Customers.CustomerID = Orders.CustomerID
LEFT JOIN Refunds
ON Orders.OrderID = Refunds.OrderID
ORDER BY Customers.LastName, Customers.FirstName, Orders.OrderDate;

SELECT
Customers.FirstName AS "First Name",
Customers.LastName AS "Last Name", 
Orders.OrderDate AS "Order Date",
Orders.OrderAmount AS "Order Amt"
FROM Customers
LEFT JOIN Orders
ON Customers.CustomerID = Orders.CustomerID
LEFT JOIN Refunds
ON Orders.OrderID = Refunds.OrderID
WHERE Orders.OrderID IS NOT NULL
AND Refunds.RefundID IS NULL
ORDER BY Customers.LastName, Customers.FirstName, Orders.OrderDate;

SELECT
RatingDescription AS "Rating Description",
MovieTitle AS "Movie"
FROM Movies
FULL JOIN Ratings
ON Movies.Rating = Ratings.Rating
ORDER BY RatingDescription, MovieTitle;

SELECT
"Size",
Color
FROM SizeInventory
CROSS JOIN ColorInventory;

SELECT
"Size",
Color
FROM SizeInventory, ColorInventory;

SELECT
OrderID AS "Order ID",
OrderDate AS "Date",
OrderAmount AS "Amount"
FROM Orders
CROSS JOIN SpecialDates
WHERE OrderDate = LastProcessDate;



- - - - - - — - - - - - - - - - - - - -
CHAPTER 13 SQL STATEMENTS
- - - - - - — - - - - - - - - - - - - -



SELECT
Employees.EmployeeName AS "Employee Name",
Managers.EmployeeName AS "Manager Name"
FROM Personnel Employees
INNER JOIN Personnel Managers
ON Employees.ManagerID = Managers.EmployeeID
ORDER BY Employees.EmployeeName;

SELECT
Employees.EmployeeName AS "Employee Name",
Managers.EmployeeName AS "Manager Name"
FROM Personnel Employees
LEFT JOIN Personnel Managers
ON Employees.ManagerID = Managers.EmployeeID
ORDER BY Employees.EmployeeName;

SELECT
Customers.FirstName AS "First Name",
Customers.LastName AS "Last Name", 
Orders.OrderDate AS "Order Date",
Orders.OrderAmount AS "Order Amt",
Refunds.RefundDate AS "Refund Date",
Refunds.RefundAmount AS "Refund Amt"
FROM Customers
LEFT JOIN Orders
ON Customers.CustomerID = Orders.CustomerID
LEFT JOIN Refunds
ON Orders.OrderID = Refunds.OrderID
ORDER BY Customers.LastName, Customers.FirstName, Orders.OrderDate;

CREATE VIEW CustomersOrdersRefunds AS
SELECT
Customers.FirstName AS "First Name",
Customers.LastName AS "Last Name", 
Orders.OrderDate AS "Order Date",
Orders.OrderAmount AS "Order Amt",
Refunds.RefundDate AS "Refund Date",
Refunds.RefundAmount AS "Refund Amt"
FROM Customers
LEFT JOIN Orders
ON Customers.CustomerID = Orders.CustomerID
LEFT JOIN Refunds
ON Orders.OrderID = Refunds.OrderID;

SELECT *
FROM CustomersOrdersRefunds;

SELECT *
FROM CustomersOrdersRefunds
ORDER BY "Last Name", "First Name", "Order Date";

SELECT
"First Name",
"Last Name",
"Order Date"
FROM CustomersOrdersRefunds
WHERE "Last Name" = 'Shah';

DROP VIEW CustomersOrdersRefunds;



- - - - - - — - - - - - - - - - - - - -
CHAPTER 14 SQL STATEMENTS
- - - - - - — - - - - - - - - - - - - -



SELECT
UserName AS "User Name",
NVL(CashTransactions.TotalCash, 0) AS "Total Cash"
FROM Users
LEFT JOIN
(SELECT
UserID,
SUM(TransactionAmount) AS TotalCash
FROM Transactions
WHERE TransactionType = 'Cash'
GROUP BY UserID) CashTransactions
ON Users.UserID = CashTransactions.UserID
ORDER BY Users.UserID;

SELECT
UserID,
SUM(TransactionAmount) AS "TotalCash"
FROM Transactions
WHERE TransactionType = 'Cash'
GROUP BY UserID;

SELECT
UserName AS "User Name",
SUM(TransactionAmount) AS "Total Cash Transactions"
FROM Users
LEFT JOIN Transactions
ON Users.UserID = Transactions.UserID
WHERE TransactionType = 'Cash'
GROUP BY Users.UserID, Users.UserName
ORDER BY Users.UserID;

SELECT UserName AS "User Name"
FROM Users
WHERE UserID IN 
(SELECT UserID
FROM Transactions
WHERE TransactionType = 'Cash');

SELECT UserName AS "User Name"
FROM Users
INNER JOIN Transactions
ON Users.UserID = Transactions.UserID
WHERE TransactionType = 'Cash'
GROUP BY Users.UserName;

SELECT 
UserName AS "User Name"
FROM Users
WHERE
(SELECT
SUM(TransactionAmount)
FROM Transactions
WHERE Users.UserID = Transactions.UserID)
< 20;

SELECT 
UserName AS "User Name"
FROM Users
LEFT JOIN Transactions
ON Users.UserID = Transactions.UserID
GROUP BY Users.UserID, Users.UserName
HAVING SUM(TransactionAmount) < 20;

SELECT 
UserName AS "User Name"
FROM Users
WHERE EXISTS
(SELECT *
FROM Transactions
WHERE Users.UserID = Transactions.UserID);

SELECT 
UserName AS "User Name"
FROM Users
WHERE UserID IN
(SELECT UserID
FROM Transactions);

SELECT 
UserName AS "User Name"
FROM Users
INNER JOIN Transactions
ON Users.UserID = Transactions.UserID
GROUP BY UserName;

SELECT 
UserName AS "User Name",
COUNT(TransactionID) AS "Number of Transactions"
FROM Users
LEFT JOIN Transactions
ON Users.UserID = Transactions.UserID
GROUP BY Users.UserID, Users.UserName
ORDER BY Users.UserID;

SELECT 
UserName AS "User Name",
(SELECT
COUNT(TransactionID)
FROM Transactions
WHERE Users.UserID = Transactions.UserID)
AS "Number of Transactions"
FROM Users
ORDER BY Users.UserID;

SELECT
UserName AS "User Name",
NVL(CashTransactions.TotalCash, 0) AS "Total Cash"
FROM Users
LEFT JOIN
(SELECT
UserID,
SUM(TransactionAmount) AS TotalCash
FROM Transactions
WHERE TransactionType = 'Cash'
GROUP BY UserID) CashTransactions
ON Users.UserID = CashTransactions.UserID
ORDER BY Users.UserID;

WITH CashTransactions AS
(SELECT
UserID,
SUM(TransactionAmount) as TotalCash
FROM Transactions
WHERE TransactionType = 'Cash'
GROUP BY UserID)
SELECT
UserName AS "User Name",
NVL(CashTransactions.TotalCash, 0) AS "Total Cash"
FROM Users
LEFT JOIN CashTransactions
ON Users.UserID = CashTransactions.UserID
ORDER BY Users.UserID;



- - - - - - — - - - - - - - - - - - - -
CHAPTER 15 SQL STATEMENTS
- - - - - - — - - - - - - - - - - - - -



SELECT
OrderDate AS "Date",
'Order' AS "Type",
OrderAmount AS "Amount"
FROM Orders
WHERE CustomerID = 2
UNION
SELECT
ReturnDate as "Date",
'Return' AS "Type",
ReturnAmount AS "Amount"
FROM Returns
WHERE CustomerID = 2
ORDER BY "Date";

SELECT
OrderDate AS "Date"
FROM Orders
UNION
SELECT
ReturnDate as "Date"
FROM Returns
ORDER BY "Date";

SELECT
OrderDate AS "Date"
FROM Orders
UNION ALL
SELECT
ReturnDate as "Date"
FROM Returns
ORDER BY "Date";

SELECT
OrderDate AS "Date"
FROM Orders
INTERSECT
SELECT
ReturnDate as "Date"
FROM Returns
ORDER BY "Date";

SELECT
OrderDate AS "Date"
FROM Orders
MINUS
SELECT
ReturnDate as "Date"
FROM Returns
ORDER BY "Date";



- - - - - - — - - - - - - - - - - - - -
CHAPTER 17 SQL STATEMENTS
- - - - - - — - - - - - - - - - - - - -



INSERT INTO Clients
(FirstName, LastName, State)
VALUES
('Claudia', 'Davis', 'OH');

INSERT INTO Clients
(FirstName, LastName, State)
VALUES
('Ingrid', 'Krause’, 'CA');

INSERT INTO Clients
(FirstName, LastName)
VALUES
('Deepak', 'Gupta');

INSERT INTO Clients
(FirstName, LastName, State)
SELECT
Name1,
Name2,
State
FROM NewClients
WHERE State = 'RI';

DELETE
FROM Clients
WHERE State = 'RI';

SELECT
COUNT (*)
FROM Clients
WHERE State = 'RI';

UPDATE Vendors
SET Vendors.State = 
(SELECT VendorTransactions.State
FROM VendorTransactions
WHERE Vendors.VendorID = VendorTransactions.VendorID),
Vendors.Zip = 
(SELECT VendorTransactions.Zip
FROM VendorTransactions
WHERE Vendors.VendorID = VendorTransactions.VendorID)
WHERE EXISTS
(SELECT *
FROM VendorTransactions
WHERE Vendors.VendorID = VendorTransactions.VendorID);



- - - - - - — - - - - - - - - - - - - -
CHAPTER 18 SQL STATEMENTS
- - - - - - — - - - - - - - - - - - - -



CREATE TABLE MyTable
(ColumnOne INT PRIMARY KEY NOT NULL,
ColumnTwo INT NULL,
ColumnThree VARCHAR(25) NULL,
ColumnFour FLOAT DEFAULT 10 NULL,
CONSTRAINT "ForeignKey" FOREIGN KEY(ColumnTwo)
REFERENCES RelatedTable (FirstColumn)
ON DELETE SET NULL);

ALTER TABLE MyTable
DROP COLUMN ColumnThree;

CREATE INDEX Index2
ON MyTable(ColumnFour);

DROP INDEX Index2;

DROP TABLE MyTable;