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

SQL Statements for MySQL



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



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

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 are two prerequisites:

1) Install MySQL Community Server
2) Install MySQL Workbench

Appendix B of "The Language of SQL" has instructions on how do these installs and create a database.

To use any of the SQL statements in this document, simply copy the desired statements into MySQL Workbench.

For each chapter in this book, you will find two sets of SQL statements:

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 MySQL. If a particular statement doesn't apply to MySQL, then it isn't shown. There are no SQL statements for chapters 1, 19 or 20.



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



-- Customers table:

DROP TABLE IF EXISTS 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 IF EXISTS 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 IF EXISTS 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 IF EXISTS 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 IF EXISTS 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 IF EXISTS 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 IF EXISTS 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 IF EXISTS 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 IF EXISTS 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 IF EXISTS 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 IF EXISTS 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 IF EXISTS 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 IF EXISTS 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 IF EXISTS 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 IF EXISTS 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 IF EXISTS 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);

-- Inventory table:

DROP TABLE IF EXISTS 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);

-- Orders table:

DROP TABLE IF EXISTS 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, '2016-09-01', 10);

INSERT INTO Orders (OrderID, CustomerID, OrderDate, OrderAmount) VALUES (2, 2, '2016-09-02', 12.5);

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

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

-- Refunds table:

DROP TABLE IF EXISTS 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, '2016-09-02', 5);

INSERT INTO Refunds (RefundID, OrderID, RefundDate, RefundAmount) VALUES (2, 3, '2016-09-18', 18);

-- SizeInventory table:

DROP TABLE IF EXISTS 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 IF EXISTS 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 IF EXISTS SpecialDates;

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

INSERT INTO SpecialDates (LastProcessDate, CurrentFiscalYear, CurrentFiscalQuarter) VALUES ('2016-09-15', 2016, 'Q3');

-- Personnel table:

DROP TABLE IF EXISTS 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 IF EXISTS 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 IF EXISTS 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 IF EXISTS 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, '2016-09-10', 2);

INSERT INTO Returns (ReturnID, CustomerID, ReturnDate, ReturnAmount) VALUES (2, 2, '2016-09-15', 15);

INSERT INTO Returns (ReturnID, CustomerID, ReturnDate, ReturnAmount) VALUES (3, 3, '2016-09-28', 3);

-- Clients table:

DROP TABLE IF EXISTS Clients;

CREATE TABLE Clients
(ClientID INT NOT NULL AUTO_INCREMENT,
FirstName VARCHAR(30) NULL,
LastName VARCHAR(30) NULL,
State VARCHAR(10) NULL,
PRIMARY KEY (ClientID));

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 IF EXISTS 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 IF EXISTS 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 IF EXISTS 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 IF EXISTS 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,
CONCAT (FirstName, ' ', LastName)
FROM Sales;

SELECT
SalesID,
FirstName,
LastName,
CONCAT (FirstName, ' ', LastName) AS 'Name'
FROM Sales;

SELECT
SalesID,
FirstName,
LastName,
CONCAT (FirstName, ' ', LastName) Name
FROM Sales;



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



SELECT 
LEFT('sunlight',3) AS 'The Answer';

SELECT 
RIGHT('sunlight',5) AS 'The Answer';

SELECT 
SUBSTRING('thewhitegoat', 4, 5) AS 'The Answer';

SELECT 
LTRIM('          the apple') AS 'The Answer';

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

SELECT NOW();

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

SELECT DATE_FORMAT('2017-05-06', '%d');

SELECT DATE_FORMAT('2017-05-06', '%u');

SELECT DATE_FORMAT('2017-05-06', '%w') + 1;

SELECT DATEDIFF('2017-08-14', '2017-07-08');

SELECT ROUND(712.863, 3);

SELECT ROUND(712.863, 2);

SELECT ROUND(712.863, 1);

SELECT ROUND(712.863, 0);

SELECT ROUND(712.863, -1);

SELECT ROUND(712.863, -2);

SELECT PI( );

SELECT ROUND(PI( ), 2);

SELECT POWER(5,2) AS '5 Squared';

SELECT POWER(25,.5) AS 'Square Root of 25';

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

SELECT 
Description, 
Weight
FROM Products;

SELECT 
Description,
IFNULL(CAST(Weight AS VARCHAR), '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 
CONCAT(LastName, ', ', FirstName) AS 'Name'
FROM Salespeople
ORDER BY Name;

SELECT 
FirstName, 
LastName
FROM Salespeople
ORDER BY CONCAT(LastName, FirstName);

SELECT NumericData
FROM TableForSort
ORDER BY NumericData;

SELECT 
IFNULL(NumericData, 0) AS 'NumericData'
FROM TableForSort
ORDER BY IFNULL(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 Books
ORDER BY CurrentMonthSales DESC
LIMIT 3;

SELECT 
Title AS 'Book Title',
CurrentMonthSales AS 'Quantity Sold'
FROM Books
WHERE Author = 'Shakespeare'
ORDER BY CurrentMonthSales DESC
LIMIT 1;

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

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

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

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

SELECT 
FirstName,
LastName
FROM Actors
WHERE FirstName LIKE '_ARY';

SELECT 
FirstName,
LastName
FROM Actors
WHERE 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 IFNULL(Weight, 0) = 0;

SELECT
Description,
IFNULL(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;



- - - - - - — - - - - - - - - - - - - -
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 Category, Subcategory WITH ROLLUP;



- - - - - - — - - - - - - - - - - - - -
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 AS C
INNER JOIN Orders AS 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
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 AS Employees
INNER JOIN Personnel AS Managers
ON Employees.ManagerID = Managers.EmployeeID
ORDER BY Employees.EmployeeName;

SELECT
Employees.EmployeeName AS 'Employee Name',
Managers.EmployeeName AS 'Manager Name'
FROM Personnel AS Employees
LEFT JOIN Personnel AS 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';

CREATE VIEW CustomersView AS
SELECT
FirstName AS 'First Name',
LastName as 'Last Name'
FROM Customers;

ALTER VIEW CustomersView AS
SELECT
FirstName AS 'First Name',
LastName as 'Last Name',
CustomerID AS 'Cust ID'
FROM Customers;

DROP VIEW CustomersView;

DROP VIEW CustomersOrdersRefunds;



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



SELECT
UserName AS 'User Name',
IFNULL (CashTransactions.TotalCash, 0) AS 'Total Cash'
FROM Users
LEFT JOIN
(SELECT
UserID,
SUM(TransactionAmount) AS 'TotalCash'
FROM Transactions
WHERE TransactionType = 'Cash'
GROUP BY UserID) AS 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',
IFNULL(CashTransactions.TotalCash, 0) AS 'Total Cash'
FROM Users
LEFT JOIN
(SELECT
UserID,
SUM(TransactionAmount) AS 'TotalCash'
FROM Transactions
WHERE TransactionType = 'Cash'
GROUP BY UserID) AS 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;



- - - - - - — - - - - - - - - - - - - -
CHAPTER 16 SQL STATEMENTS
- - - - - - — - - - - - - - - - - - - -



DELIMITER $$
CREATE PROCEDURE ProcedureOne ( )
BEGIN
SELECT *
FROM Customers;
END$$
DELIMITER ;

DELIMITER $$
CREATE PROCEDURE CustomerProcedure
(CustID INT)
BEGIN
SELECT *
FROM Customers
WHERE CustomerID = CustID;
END$$
DELIMITER ;

CALL ProcedureOne

CALL CustomerProcedure (2);

DROP PROCEDURE ProcedureOne

DROP PROCEDURE CustomerProcedure



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



INSERT INTO Clients
(FirstName, LastName, State)
VALUES
('Claudia', 'Davis', 'OH'),
('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';

UPDATE Clients
SET LastName = 'Barrow',
State = 'WI'
WHERE ClientID = 1;



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



CREATE TABLE MyTable
(ColumnOne INT AUTO_INCREMENT PRIMARY KEY NOT NULL,
ColumnTwo INT NULL,
ColumnThree VARCHAR(25) NULL,
ColumnFour FLOAT NULL DEFAULT 10,
CONSTRAINT 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
ON MyTable;

DROP TABLE MyTable;