Access Training Materials – Cengage
The Language of SQL
SQL Statements and Data 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 are two prerequisites:
• Install Oracle Database Express Edition
• Create a database
Appendix C of "The Language of SQL" contains 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.
For each chapter in this book, you will find two sets of SQL statements:
• Setup Scripts
The setup scripts allow you to create the data needed to execute all statements in that chapter. 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 see a warning if you execute a DROP TABLE command and the table doesn't already exist. This warning can be ignored.
• SQL Statements from the Book
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.
Each setup script applies only to the SQL statements which immediately follow. For example, the statements shown for chapter 4 consist of a setup script, followed by the SQL statements found in that chapter. You don't need to execute the chapter 2 or 3 setup scripts in order to use the scripts in chapter 4.
The setup scripts in chapters 2, 6, 17, and 18 have been separated into multiple parts. This occurs because those chapters have situations where data is modified in some way, requiring additional setup scripts for subsequent SQL statments to work correctly.
All of the statements in a setup script can be executed all at once. However, if you should encounter any problems in executing a setup script, try executing the statements one at a time. In most cases, that will solve any problems you encounter.
There are no scripts or SQL statements for chapters 1, 16, 19 or 20.
The following is a list of chapters. Click on any of these links to go immediately to the SQL for that chapter.
Chapter 2
Chapter 3
Chapter 4
Chapter 5
Chapter 6
Chapter 7
Chapter 8
Chapter 9
Chapter 10
Chapter 11
Chapter 12
Chapter 13
Chapter 14
Chapter 15
Chapter 16
Chapter 17
Chapter 18
Chapter 2
Setup Script - Part 1:
DROP TABLE Customers;
CREATE TABLE Customers
(CustomerID INT NOT NULL,
FirstName VARCHAR2 (45) NULL,
LastName VARCHAR2 (45) NULL);
INSERT INTO Customers (CustomerID, FirstName, LastName)
VALUES (1, 'William', 'Smith');
INSERT INTO Customers (CustomerID, FirstName, LastName)
VALUES (2, 'Natalie', 'Lopez');
INSERT INTO Customers (CustomerID, FirstName, LastName)
VALUES (3, 'Brenda', 'Harper');
Statements from Book:
SELECT * FROM Customers;
SELECT *
FROM Customers;
SELECT LastName
FROM Customers;
SELECT
FirstName,
LastName
FROM Customers;
Setup Script - Part 2:
DROP TABLE Customers;
CREATE TABLE Customers
("Last Name" VARCHAR2 (45) NULL);
INSERT INTO Customers ("Last Name")
VALUES ('Smith');
INSERT INTO Customers ("Last Name")
VALUES ('Lopez');
INSERT INTO Customers ("Last Name")
VALUES ('Harper');
Statements from Book:
SELECT
"Last Name"
FROM Customers;
Chapter 3
Setup Script:
DROP TABLE Orders;
CREATE TABLE Orders
(OrderID INT NOT NULL,
FirstName VARCHAR2 (45) NULL,
LastName VARCHAR2 (45) NULL,
QuantityPurchased INT NULL,
PricePerItem FLOAT NULL);
INSERT INTO Orders (OrderID, FirstName, LastName, QuantityPurchased, PricePerItem)
VALUES (1, 'William', 'Smith', 4, 2.5);
INSERT INTO Orders (OrderID, FirstName, LastName, QuantityPurchased, PricePerItem)
VALUES (2, 'Natalie', 'Lopez', 10, 1.25);
INSERT INTO Orders (OrderID, FirstName, LastName, QuantityPurchased, PricePerItem)
VALUES (3, 'Brenda', 'Harper', 5, 4);
DROP TABLE Orders123;
CREATE TABLE Orders123
(LastName VARCHAR2 (45) NULL);
INSERT INTO Orders123 (LastName)
VALUES ('Smith');
Statements from Book:
SELECT
'First Name: ',
FirstName
FROM Orders;
SELECT
5,
FirstName
FROM Orders;
SELECT
OrderID,
QuantityPurchased,
PricePerItem,
QuantityPurchased * PricePerItem
FROM Orders;
SELECT
OrderID,
FirstName,
LastName,
FirstName || ' ' || LastName
FROM Orders;
SELECT
OrderID,
FirstName,
LastName,
FirstName || ' ' || LastName AS "Name"
FROM Orders;
SELECT
LastName
FROM Orders123 Orders;
SELECT
Orders.LastName
FROM Orders123 Orders;
Chapter 4
Setup Script:
DROP TABLE table1;
CREATE TABLE table1
(President VARCHAR2 (20) NULL);
INSERT INTO table1 (President)
VALUES ('George Washington ');
DROP TABLE Orders;
CREATE TABLE Orders
(OrderID INT NOT NULL,
FirstName VARCHAR2 (45) NULL,
LastName VARCHAR2 (45) NULL,
QuantityPurchased INT NULL,
PricePerItem FLOAT NULL);
INSERT INTO Orders (OrderID, FirstName, LastName, QuantityPurchased, PricePerItem)
VALUES (1, 'William', 'Smith', 4, 2.5);
INSERT INTO Orders (OrderID, FirstName, LastName, QuantityPurchased, PricePerItem)
VALUES (2, 'Natalie', 'Lopez', 10, 1.25);
INSERT INTO Orders (OrderID, FirstName, LastName, QuantityPurchased, PricePerItem)
VALUES (3, 'Brenda', 'Harper', 5, 4);
DROP TABLE Products;
CREATE TABLE Products
(ProductID INT NOT NULL,
Description VARCHAR2 (45) NULL,
Color VARCHAR2 (45) NULL);
INSERT INTO Products (ProductID, Description, Color)
VALUES (1, 'Chair A', 'Red');
INSERT INTO Products (ProductID, Description)
VALUES (2, 'Chair B');
INSERT INTO Products (ProductID, Description, Color)
VALUES (3, 'Lamp C', 'Green');
Statements from Book:
NOTE: Statements involving the LEFT and RIGHT functions are not available in Oracle.
SELECT
SUBSTR ('thewhitegoat', 4, 5) AS "The Answer"
FROM DUAL;
SELECT
LTRIM(' the apple') AS "The Answer"
FROM DUAL;
SELECT
OrderID,
FirstName,
LastName,
CONCAT (CONCAT (FirstName, ' '), LastName) AS "Name"
FROM Orders;
SELECT
UPPER ('Abraham Lincoln') AS "Convert to Uppercase",
LOWER ('ABRAHAM LINCOLN') AS "Convert to Lowercase"
FROM DUAL;
SELECT
SUBSTR (RTRIM (President), -10, 10) AS "Last Name"
FROM table1;
SELECT
SUBSTR ('George Washington', -10, 10)
FROM table1;
SELECT CURRENT_DATE
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;
NOTE: The statements involving the RAND and PI functions are not available in Oracle.
SELECT
'2009-04-11' AS "Original Date",
CAST('11-APR-2009' AS DATE) AS "Converted Date"
FROM DUAL;
SELECT
Description,
Color
FROM Products;
SELECT
Description,
NVL (Color, 'Unknown') AS "Color"
FROM Products;
Chapter 5
Setup Script:
DROP TABLE Customers;
CREATE TABLE Customers
(CustomerID INT NOT NULL,
FirstName VARCHAR2 (45) NULL,
LastName VARCHAR2 (45) NULL);
INSERT INTO Customers (CustomerID, FirstName, LastName) VALUES (1, 'William', 'Smith');
INSERT INTO Customers (CustomerID, FirstName, LastName) VALUES (2, 'Janet', 'Smith');
INSERT INTO Customers (CustomerID, FirstName, LastName) VALUES (3, 'Natalie', 'Lopez');
INSERT INTO Customers (CustomerID, FirstName, LastName) VALUES (4, 'Brenda', 'Harper');
DROP TABLE table1;
CREATE TABLE table1
(TableID INT NOT NULL,
CharacterData VARCHAR2 (45) NULL,
NumericData INT NULL);
INSERT INTO table1 (TableID, CharacterData, NumericData) VALUES (1, '23', 23);
INSERT INTO table1 (TableID, CharacterData, NumericData) VALUES (2, '5', 5);
INSERT INTO table1 (TableID, CharacterData) VALUES (3, 'Dog');
INSERT INTO table1 (TableID, NumericData) VALUES (4, -6);
Statements from Book:
SELECT
FirstName,
LastName
FROM Customers
ORDER BY LastName;
SELECT
FirstName,
LastName
FROM Customers
ORDER BY FirstName;
SELECT
FirstName,
LastName
FROM Customers
ORDER BY FirstName ASC;
SELECT
FirstName,
LastName
FROM Customers
ORDER BY FirstName DESC;
SELECT
FirstName,
LastName
FROM Customers
ORDER BY LastName, FirstName;
SELECT
CONCAT(CONCAT(LastName, ', '), FirstName) AS "Name"
FROM Customers
ORDER BY "Name";
SELECT
FirstName,
LastName
FROM Customers
ORDER BY CONCAT(LastName, FirstName);
SELECT NumericData
FROM table1
ORDER BY NumericData NULLS FIRST;
SELECT
NVL (NumericData, 0)
FROM table1
ORDER BY NVL(NumericData,0);
SELECT
CharacterData
FROM table1
ORDER BY CharacterData NULLS FIRST;
Chapter 6
Setup Script - Part 1:
DROP TABLE Products;
CREATE TABLE Products
(ProductID INT NOT NULL,
CategoryCode VARCHAR2 (45) NULL,
ProductDescription VARCHAR2 (45) NULL);
INSERT INTO Products (ProductID, CategoryCode, ProductDescription) VALUES (1, 'F', 'Apple');
INSERT INTO Products (ProductID, CategoryCode, ProductDescription) VALUES (2, 'F', 'Orange');
INSERT INTO Products (ProductID, CategoryCode, ProductDescription) VALUES (3, 'S', 'Mustard');
INSERT INTO Products (ProductID, CategoryCode, ProductDescription) VALUES (4, 'V', 'Carrot');
Statements from Book:
SELECT
CASE CategoryCode
WHEN 'F' THEN 'Fruit'
WHEN 'V' THEN 'Vegetable'
ELSE 'Other'
END AS "Category",
ProductDescription AS "Description"
FROM Products;
SELECT
CASE
WHEN CategoryCode = 'F' THEN 'Fruit'
WHEN CategoryCode = 'V' THEN 'Vegetable'
ELSE 'Other'
END AS "Category",
ProductDescription AS "Description"
FROM Products;
Setup Script - Part 2:
DROP TABLE Products;
CREATE TABLE Products
(ProductID INT NOT NULL,
Fruit VARCHAR2 (45) NULL,
Vegetable VARCHAR2 (45) NULL,
Spice VARCHAR2 (45) NULL,
ProductDescription VARCHAR2 (45) NULL);
INSERT INTO Products (ProductID, Fruit, Vegetable, Spice, ProductDescription) VALUES (1, 'X', ' ', ' ', 'Apple');
INSERT INTO Products (ProductID, Fruit, Vegetable, Spice, ProductDescription) VALUES (2, 'X', ' ', ' ', 'Orange');
INSERT INTO Products (ProductID, Fruit, Vegetable, Spice, ProductDescription) VALUES (3, ' ', ' ', 'X', 'Mustard');
INSERT INTO Products (ProductID, Fruit, Vegetable, Spice, ProductDescription) VALUES (4, ' ', 'X', ' ', 'Carrot');
Statements from Book:
SELECT
CASE
WHEN Fruit = 'X' THEN 'Fruit'
WHEN Vegetable = 'X' THEN 'Vegetable'
ELSE 'Other'
END AS "Category",
ProductDescription AS "Description"
FROM Products;
Chapter 7
Setup Script:
DROP TABLE Orders;
CREATE TABLE Orders
(OrderID INT NOT NULL,
FirstName VARCHAR2 (45) NULL,
LastName VARCHAR2 (45) NULL,
QuantityPurchased INT NULL,
PricePerItem FLOAT NULL);
INSERT INTO Orders (OrderID, FirstName, LastName, QuantityPurchased, PricePerItem) VALUES (1, 'William', 'Smith', 4, 2.5);
INSERT INTO Orders (OrderID, FirstName, LastName, QuantityPurchased, PricePerItem) VALUES (2, 'Natalie', 'Lopez', 10, 1.25);
INSERT INTO Orders (OrderID, FirstName, LastName, QuantityPurchased, PricePerItem) VALUES (3, 'Brenda', 'Harper', 5, 4);
DROP TABLE Books;
CREATE TABLE Books
(BookID INT NOT NULL,
Title VARCHAR2 (45) NULL,
Author VARCHAR2 (45) 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);
Statements from Book:
SELECT
FirstName,
LastName,
QuantityPurchased
FROM Orders
WHERE LastName = 'Harper';
SELECT
FirstName,
LastName,
QuantityPurchased
FROM Orders
WHERE QuantityPurchased = 5;
SELECT
FirstName,
LastName,
QuantityPurchased
FROM Orders
WHERE QuantityPurchased > 6;
SELECT
FirstName,
LastName
FROM Orders
WHERE LastName > 'K';
SELECT
Title AS "Book Title",
CurrentMonthSales AS "Quantity Sold"
FROM
(SELECT *
FROM Books
ORDER BY CurrentMonthSales DESC)
WHERE ROWNUM 8
OR PricePerItem > 3;
SELECT
CustomerName,
State,
QuantityPurchased
FROM Orders
WHERE State = 'IL'
OR State = 'CA'
AND QuantityPurchased > 8;
SELECT
CustomerName,
State,
QuantityPurchased
FROM Orders
WHERE (State = 'IL'
OR State = 'CA')
AND QuantityPurchased > 8;
SELECT
CustomerName,
State,
QuantityPurchased
FROM Orders
WHERE State = 'NY'
OR (State = 'IL'
AND (QuantityPurchased >= 3
AND QuantityPurchased 3);
SELECT
CustomerName,
State,
QuantityPurchased
FROM Orders
WHERE State 'IL'
OR QuantityPurchased = 5
AND QuantityPurchased = 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;
Chapter 11
Setup Script:
DROP TABLE Customers;
CREATE TABLE Customers
(CustomerID INT NOT NULL,
FirstName VARCHAR2 (45) NULL,
LastName VARCHAR2 (45) NULL);
INSERT INTO Customers (CustomerID, FirstName, LastName) VALUES (1, 'William', 'Smith');
INSERT INTO Customers (CustomerID, FirstName, LastName) VALUES (2, 'Natalie', 'Lopez');
INSERT INTO Customers (CustomerID, FirstName, LastName) VALUES (3, 'Brenda', 'Harper');
INSERT INTO Customers (CustomerID, FirstName, LastName) VALUES (4, 'Adam', 'Petrie');
DROP TABLE Orders;
CREATE TABLE Orders
(OrderID INT NOT NULL,
CustomerID INT NULL,
Quantity INT NULL,
PricePerItem FLOAT NULL);
INSERT INTO Orders (OrderID, CustomerID, Quantity, PricePerItem) VALUES (1, 1, 4, 2.50);
INSERT INTO Orders (OrderID, CustomerID, Quantity, PricePerItem) VALUES (2, 2, 10, 1.25);
INSERT INTO Orders (OrderID, CustomerID, Quantity, PricePerItem) VALUES (3, 2, 12, 1.50);
INSERT INTO Orders (OrderID, CustomerID, Quantity, PricePerItem) VALUES (4, 3, 5, 4);
Statements from Book:
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.Quantity AS "Qty",
O.PricePerItem AS "Price"
FROM Customers C
INNER JOIN Orders O
ON C.CustomerID = O.CustomerID;
Chapter 12
Setup Script:
DROP TABLE Customers;
CREATE TABLE Customers
(CustomerID INT NOT NULL,
FirstName VARCHAR2 (45) NULL,
LastName VARCHAR2 (45) NULL);
INSERT INTO Customers (CustomerID, FirstName, LastName) VALUES (1, 'William', 'Smith');
INSERT INTO Customers (CustomerID, FirstName, LastName) VALUES (2, 'Natalie', 'Lopez');
INSERT INTO Customers (CustomerID, FirstName, LastName) VALUES (3, 'Brenda', 'Harper');
INSERT INTO Customers (CustomerID, FirstName, LastName) VALUES (4, 'Adam', 'Petrie');
DROP TABLE Orders;
CREATE TABLE Orders
(OrderID INT NOT NULL,
CustomerID INT NULL,
OrderDate DATE NULL,
OrderAmount FLOAT NULL);
INSERT INTO Orders (OrderID, CustomerID, OrderDate, OrderAmount) VALUES (1, 1, TO_DATE('2009-09-01', 'YYYY/MM/DD'), 10);
INSERT INTO Orders (OrderID, CustomerID, OrderDate, OrderAmount) VALUES (2, 2, TO_DATE('2009-09-02', 'YYYY/MM/DD'), 12.5);
INSERT INTO Orders (OrderID, CustomerID, OrderDate, OrderAmount) VALUES (3, 2, TO_DATE('2009-10-03', 'YYYY/MM/DD'), 18);
INSERT INTO Orders (OrderID, CustomerID, OrderDate, OrderAmount) VALUES (4, 3, TO_DATE('2009-09-15', 'YYYY/MM/DD'), 20);
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('2009-09-02', 'YYYY/MM/DD'), 5);
INSERT INTO Refunds (RefundID, OrderID, RefundDate, RefundAmount) VALUES (2, 3, TO_DATE('2009-10-12', 'YYYY/MM/DD'), 18);
DROP TABLE Movies;
CREATE TABLE Movies
(MovieID INT NOT NULL,
MovieTitle VARCHAR2 (45) NULL,
Rating VARCHAR2 (45) NULL);
INSERT INTO Movies (MovieID, MovieTitle, Rating) VALUES (1, 'Sleepless in Seattle', 'PG');
INSERT INTO Movies (MovieID, MovieTitle, Rating) VALUES (2, 'Lost in America', 'R');
INSERT INTO Movies (MovieID, MovieTitle, Rating) VALUES (3, 'Bambi', 'G');
INSERT INTO Movies (MovieID, MovieTitle, Rating) VALUES (4, 'North by Northwest', 'Not Rated');
INSERT INTO Movies (MovieID, MovieTitle, Rating) VALUES (5, 'Forrest Gump', 'PG-13');
INSERT INTO Movies (MovieID, MovieTitle, Rating) VALUES (6, 'The Truman Show', 'PG');
DROP TABLE Ratings;
CREATE TABLE Ratings
(RatingID INT NOT NULL,
Rating VARCHAR2 (45) NULL,
RatingDescription VARCHAR2 (45) 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', 'No One 17 and Under Admitted');
Statements from Book:
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.CustomerID, Orders.OrderID, RefundID;
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.CustomerID, Orders.OrderID;
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 Refunds
RIGHT JOIN Orders
ON Orders.OrderID = Refunds.OrderID
RIGHT JOIN Customers
ON Customers.CustomerID = Orders.CustomerID;
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 (Refunds
RIGHT JOIN Orders
ON Orders.OrderID = Refunds.OrderID)
ON Customers.CustomerID = Orders.CustomerID;
SELECT
MovieTitle AS "Movie",
RatingDescription AS "Rating Description"
FROM Movies
FULL JOIN Ratings
ON Movies.Rating = Ratings.Rating
ORDER BY RatingDescription NULLS FIRST, MovieTitle NULLS FIRST;
Chapter 13
Setup Script:
DROP TABLE Personnel;
CREATE TABLE Personnel
(EmployeeID INT NOT NULL,
EmployeeName VARCHAR2 (45) NULL,
ManagerID INT NULL);
INSERT INTO Personnel (EmployeeID, EmployeeName) VALUES (1, 'Susan Ford');
INSERT INTO Personnel (EmployeeID, EmployeeName, ManagerID) VALUES (2, 'Harold Jenkins', 1);
INSERT INTO Personnel (EmployeeID, EmployeeName, ManagerID) VALUES (3, 'Jacqueline Baker', 1);
INSERT INTO Personnel (EmployeeID, EmployeeName, ManagerID) VALUES (4, 'Richard Fielding', 1);
INSERT INTO Personnel (EmployeeID, EmployeeName, ManagerID) VALUES (5, 'Carol Bland', 2);
INSERT INTO Personnel (EmployeeID, EmployeeName, ManagerID) VALUES (6, 'Janet Midling', 2);
INSERT INTO Personnel (EmployeeID, EmployeeName, ManagerID) VALUES (7, 'Andrew Brown', 3);
INSERT INTO Personnel (EmployeeID, EmployeeName, ManagerID) VALUES (8, 'Anne Nichol', 4);
INSERT INTO Personnel (EmployeeID, EmployeeName, ManagerID) VALUES (9, 'Bradley Cash', 4);
INSERT INTO Personnel (EmployeeID, EmployeeName, ManagerID) VALUES (10, 'David Sweet', 5);
DROP TABLE Customers;
CREATE TABLE Customers
(CustomerID INT NOT NULL,
FirstName VARCHAR2 (45) NULL,
LastName VARCHAR2 (45) NULL);
INSERT INTO Customers (CustomerID, FirstName, LastName) VALUES (1, 'William', 'Smith');
INSERT INTO Customers (CustomerID, FirstName, LastName) VALUES (2, 'Natalie', 'Lopez');
INSERT INTO Customers (CustomerID, FirstName, LastName) VALUES (3, 'Brenda', 'Harper');
INSERT INTO Customers (CustomerID, FirstName, LastName) VALUES (4, 'Adam', 'Petrie');
DROP TABLE Orders;
CREATE TABLE Orders
(OrderID INT NOT NULL,
CustomerID INT NULL,
OrderDate DATE NULL,
OrderAmount FLOAT NULL);
INSERT INTO Orders (OrderID, CustomerID, OrderDate, OrderAmount) VALUES (1, 1, TO_DATE('2009-09-01', 'YYYY/MM/DD'), 10);
INSERT INTO Orders (OrderID, CustomerID, OrderDate, OrderAmount) VALUES (2, 2, TO_DATE('2009-09-02', 'YYYY/MM/DD'), 12.5);
INSERT INTO Orders (OrderID, CustomerID, OrderDate, OrderAmount) VALUES (3, 2, TO_DATE('2009-10-03', 'YYYY/MM/DD'), 18);
INSERT INTO Orders (OrderID, CustomerID, OrderDate, OrderAmount) VALUES (4, 3, TO_DATE('2009-09-15', 'YYYY/MM/DD'), 20);
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('2009-09-02', 'YYYY/MM/DD'), 5);
INSERT INTO Refunds (RefundID, OrderID, RefundDate, RefundAmount) VALUES (2, 3, TO_DATE('2009-10-12', 'YYYY/MM/DD'), 18);
DROP VIEW CustomersOrdersRefunds;
DROP VIEW CustomersView;
Statements from Book:
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".EmployeeID;
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".EmployeeID;
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.CustomerID, Orders.OrderID, RefundID;
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
"First Name",
"Last Name",
"Order Date"
FROM CustomersOrdersRefunds
WHERE "Last Name" = 'Lopez';
CREATE VIEW CustomersView AS
SELECT
FirstName AS "First Name",
LastName AS "Last Name"
FROM Customers;
NOTE: The ALTER VIEW statement is not shown in Oracle.
DROP VIEW CustomersView;
Chapter 14
Setup Script:
DROP TABLE Customers;
CREATE TABLE Customers
(CustomerID INT NOT NULL,
CustomerName VARCHAR2 (45) NULL);
INSERT INTO Customers (CustomerID, CustomerName) VALUES (1, 'William Smith');
INSERT INTO Customers (CustomerID, CustomerName) VALUES (2, 'Natalie Lopez');
INSERT INTO Customers (CustomerID, CustomerName) VALUES (3, 'Brenda Harper');
INSERT INTO Customers (CustomerID, CustomerName) VALUES (4, 'Adam Petrie');
DROP TABLE Orders;
CREATE TABLE Orders
(OrderID INT NOT NULL,
CustomerID INT NULL,
OrderAmount FLOAT NULL,
OrderType VARCHAR2 (45) NULL);
INSERT INTO Orders (OrderID, CustomerID, OrderAmount, OrderType) VALUES (1, 1, 22.25, 'Cash');
INSERT INTO Orders (OrderID, CustomerID, OrderAmount, OrderType) VALUES (2, 2, 11.75, 'Credit');
INSERT INTO Orders (OrderID, CustomerID, OrderAmount, OrderType) VALUES (3, 2, 5, 'Credit');
INSERT INTO Orders (OrderID, CustomerID, OrderAmount, OrderType) VALUES (4, 2, 8, 'Cash');
INSERT INTO Orders (OrderID, CustomerID, OrderAmount, OrderType) VALUES (5, 3, 9.33, 'Credit');
INSERT INTO Orders (OrderID, CustomerID, OrderAmount, OrderType) VALUES (6, 3, 10.11, 'Credit');
Statements from Book:
SELECT
CustomerName AS "Customer Name",
NVL ("CashOrders".SumOfOrders, 0) AS "Total Cash Orders"
FROM Customers
LEFT JOIN
(SELECT
CustomerID,
SUM(OrderAmount) AS SumOfOrders
FROM Orders
WHERE OrderType = 'Cash'
GROUP BY CustomerID) "CashOrders"
ON Customers.CustomerID = "CashOrders".CustomerID
ORDER BY Customers.CustomerID;
SELECT
CustomerID,
SUM(OrderAmount) AS SumOfOrders
FROM Orders
WHERE OrderType = 'Cash'
GROUP BY CustomerID
SELECT
CustomerName AS "Customer Name",
SUM(OrderAmount) AS "Total Cash Orders"
FROM Customers
LEFT JOIN Orders
ON Customers.CustomerID = Orders.CustomerID
WHERE OrderType = 'Cash'
GROUP BY Customers.CustomerID, CustomerName
ORDER BY Customers.CustomerID;
SELECT CustomerName AS "Customer Name"
FROM Customers
WHERE CustomerID IN
(SELECT CustomerID
FROM Orders
WHERE OrderType = 'Cash');
SELECT CustomerName AS "Customer Name"
FROM Customers
INNER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID
WHERE OrderType = 'Cash'
GROUP BY Customers.CustomerID, Customers.CustomerName;
SELECT
CustomerName as "Customer Name"
FROM Customers
WHERE
(SELECT
SUM (OrderAmount)
FROM Orders
WHERE Customers.CustomerID = Orders.CustomerID) < 20;
SELECT
CustomerName as "Customer Name"
FROM Customers
LEFT JOIN Orders
ON Customers.CustomerID = Orders.CustomerID
GROUP BY Customers.CustomerID, CustomerName
HAVING SUM (OrderAmount) < 20;
SELECT
CustomerName AS "CustomerName"
FROM Customers
WHERE EXISTS
(SELECT *
FROM Orders
WHERE Customers.CustomerID = Orders.CustomerID);
SELECT
CustomerName AS "Customer Name"
FROM Customers
WHERE CustomerID IN
(SELECT CustomerID
FROM Orders);
SELECT
CustomerName AS "Customer Name"
FROM Customers
INNER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID
GROUP BY CustomerName
SELECT
CustomerName AS "Customer Name",
COUNT(OrderID) AS "Number of Orders"
FROM Customers
LEFT JOIN Orders
ON Customers.CustomerID = Orders.CustomerID
GROUP BY Customers.CustomerID, CustomerName
ORDER BY Customers.CustomerID;
SELECT
CustomerName AS "Customer Name",
(SELECT
COUNT(OrderID)
FROM Orders
WHERE Customers.CustomerID = Orders.CustomerID)
AS "Number of Orders"
FROM Customers
ORDER BY Customers.CustomerID;
Chapter 15
Setup Script:
DROP TABLE Orders;
CREATE TABLE Orders
(OrderID INT NOT NULL,
CustomerID INT NULL,
OrderDate DATE NULL,
OrderAmount FLOAT NULL);
INSERT INTO Orders (OrderID, CustomerID, OrderDate, "OrderAmount") VALUES (1, 1, TO_DATE('2009-10-13', 'YYYY/MM/DD'), 10);
INSERT INTO Orders (OrderID, CustomerID, OrderDate, OrderAmount) VALUES (2, 2, TO_DATE('2009-10-13', 'YYYY/MM/DD'), 8);
INSERT INTO Orders (OrderID, CustomerID, OrderDate, OrderAmount) VALUES (3, 2, TO_DATE('2009-12-05', 'YYYY/MM/DD'), 7);
INSERT INTO Orders (OrderID, CustomerID, OrderDate, OrderAmount) VALUES (4, 2, TO_DATE('2009-12-15', 'YYYY/MM/DD'), 21);
INSERT INTO Orders (OrderID, CustomerID, OrderDate, OrderAmount) VALUES (5, 3, TO_DATE('2009-12-28', 'YYYY/MM/DD'), 11);
DROP TABLE Returns;
CREATE TABLE Returns
(ReturnID INT NOT NULL,
CustomerID INT NULL,
ReturnDate DATE NULL,
ReturnAmount FLOAT NULL);
INSERT INTO Returns (ReturnID, CustomerID, ReturnDate, ReturnAmount) VALUES (1, 1, TO_DATE('2009-10-23', 'YYYY/MM/DD'), 2);
INSERT INTO Returns (ReturnID, CustomerID, ReturnDate, ReturnAmount) VALUES (2, 2, TO_DATE('2009-12-07', 'YYYY/MM/DD'), 7);
INSERT INTO Returns (ReturnID, CustomerID, ReturnDate, ReturnAmount) VALUES (3, 3, TO_DATE('2009-12-28', 'YYYY/MM/DD'), 3);
Statements from Book:
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
DISTINCT
OrderDate AS "Date"
FROM Orders
UNION ALL
SELECT
DISTINCT
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 16
NOTE: Oracle stored procedures are not covered in the book.
Chapter 17
Setup Script - Part 1:
DROP TABLE Customers;
CREATE TABLE Customers
(CustomerID INT NULL,
FirstName VARCHAR2 (45) NULL,
LastName VARCHAR2 (45) NULL,
State VARCHAR2 (45) NULL);
INSERT INTO Customers (CustomerID, FirstName, LastName, State) VALUES (1, 'William', 'Smith', 'IL');
INSERT INTO Customers (CustomerID, FirstName, LastName, State) VALUES (2, 'Natalie', 'Lopez', 'WI');
INSERT INTO Customers (CustomerID, FirstName, LastName, State) VALUES (3, 'Brenda', 'Harper', 'NV');
DROP TABLE CustomerTransactions;
CREATE TABLE CustomerTransactions
(CustomerID INT NOT NULL,
State VARCHAR2 (45) NULL,
Name1 VARCHAR2 (45) NULL,
Name2 VARCHAR2 (45) NULL);
INSERT INTO CustomerTransactions (CustomerID, State, Name1, Name2) VALUES (1, 'RI', 'Susan', 'Harris');
INSERT INTO CustomerTransactions (CustomerID, State, Name1, Name2) VALUES (2, 'DC', 'Michael', 'Blake');
INSERT INTO CustomerTransactions (CustomerID, State, Name1, Name2) VALUES (3, 'RI', 'Alan', 'Carter');
Statements from Book:
INSERT INTO Customers
(CustomerID, FirstName, LastName, State)
VALUES
(4, 'Virginia', 'Jones', 'OH');
INSERT INTO Customers
(CustomerID, FirstName, LastName, State)
VALUES
(5, 'Clark', 'Woodland', 'CA');
SELECT *
FROM Customers
ORDER BY CustomerID;
Setup Script - Part 2:
DROP TABLE Customers;
CREATE TABLE Customers
(CustomerID INT NULL,
FirstName VARCHAR2 (45) NULL,
LastName VARCHAR2 (45) NULL,
State VARCHAR2 (45) NULL);
INSERT INTO Customers (CustomerID, FirstName, LastName, State) VALUES (1, 'William', 'Smith', 'IL');
INSERT INTO Customers (CustomerID, FirstName, LastName, State) VALUES (2, 'Natalie', 'Lopez', 'WI');
INSERT INTO Customers (CustomerID, FirstName, LastName, State) VALUES (3, 'Brenda', 'Harper', 'NV');
Statements from Book:
INSERT INTO Customers
(CustomerID, State, LastName, FirstName)
VALUES
(4, 'OH', 'Jones', 'Virginia');
INSERT INTO Customers
(CustomerID, State, LastName, FirstName)
VALUES
(5, 'CA', 'Woodland', 'Clark');
SELECT *
FROM Customers
ORDER BY CustomerID;
INSERT INTO Customers
(CustomerID, FirstName, LastName)
VALUES
(6, 'Tom', 'Monroe');
SELECT *
FROM Customers
WHERE FirstName = 'Tom';
INSERT INTO Customers
(FirstName, LastName, State)
SELECT
Name1,
Name2,
State
FROM CustomerTransactions
WHERE State = 'RI';
SELECT *
FROM Customers
ORDER BY CustomerID;
SELECT
COUNT (*)
FROM Customers
WHERE State = 'RI';
DELETE
FROM Customers
WHERE State = 'RI';
TRUNCATE TABLE Customers;
Setup Script - Part 3:
DROP TABLE Customers;
CREATE TABLE Customers
(CustomerID INT NULL,
FirstName VARCHAR2 (45) NULL,
LastName VARCHAR2 (45) NULL,
State VARCHAR2 (45) NULL);
INSERT INTO Customers (CustomerID, FirstName, LastName, State) VALUES (1, 'William', 'Smith', 'IL');
INSERT INTO Customers (CustomerID, FirstName, LastName, State) VALUES (2, 'Natalie', 'Lopez', 'WI');
INSERT INTO Customers (CustomerID, FirstName, LastName, State) VALUES (3, 'Brenda', 'Harper', 'NV');
Statements from Book:
DELETE
FROM Customers;
Setup Script - Part 4:
DROP TABLE Customers;
CREATE TABLE Customers
(CustomerID INT NULL,
FirstName VARCHAR2 (45) NULL,
LastName VARCHAR2 (45) NULL,
State VARCHAR2 (45) NULL);
INSERT INTO Customers (CustomerID, FirstName, LastName, State) VALUES (1, 'William', 'Smith', 'IL');
INSERT INTO Customers (CustomerID, FirstName, LastName, State) VALUES (2, 'Natalie', 'Lopez', 'WI');
INSERT INTO Customers (CustomerID, FirstName, LastName, State) VALUES (3, 'Brenda', 'Harper', 'NV');
Statements from Book:
UPDATE Customers
SET FirstName = 'Bill',
LastName = 'Smythe'
WHERE CustomerID = 1;
SELECT *
FROM Customers
WHERE FirstName = 'Bill';
Setup Script - Part 5:
DROP TABLE Customers;
CREATE TABLE Customers
(CustomerID INT NOT NULL,
State VARCHAR2 (45) NULL,
Zip VARCHAR2 (45) NULL);
INSERT INTO Customers (CustomerID, State, Zip) VALUES (1, 'IL', '60089');
INSERT INTO Customers (CustomerID, State, Zip) VALUES (2, 'CA', '92802');
INSERT INTO Customers (CustomerID, State, Zip) VALUES (3, 'WI', '53718');
INSERT INTO Customers (CustomerID, State, Zip) VALUES (4, 'DC', '20024');
INSERT INTO Customers (CustomerID, State, Zip) VALUES (5, 'FL', '32801');
DROP TABLE CustomerTransactions;
CREATE TABLE CustomerTransactions
(TransactionID INT NOT NULL,
CustomerID INT NOT NULL,
State VARCHAR2 (45) NULL,
Zip VARCHAR2 (45) NULL);
INSERT INTO CustomerTransactions (TransactionID, CustomerID, State, Zip) VALUES (1, 4, 'MD', '20814');
INSERT INTO CustomerTransactions (TransactionID, CustomerID, State, Zip) VALUES (2, 1, 'IL', '60090');
INSERT INTO CustomerTransactions (TransactionID, CustomerID, State, Zip) VALUES (3, 5, 'FL', '32810');
INSERT INTO CustomerTransactions (TransactionID, CustomerID, State, Zip) VALUES (4, 2, 'NV', '89109');
INSERT INTO CustomerTransactions (TransactionID, CustomerID, State, Zip) VALUES (5, 3, 'WI', '53562');
Statements in Book:
UPDATE Customers
SET Customers.State =
(SELECT CustomerTransactions.State
FROM CustomerTransactions
WHERE CustomerTransactions.CustomerID = Customers.CustomerID),
Customers.Zip =
(SELECT CustomerTransactions.Zip
FROM CustomerTransactions
WHERE CustomerTransactions.CustomerID = Customers.CustomerID)
WHERE EXISTS
(SELECT *
FROM CustomerTransactions
WHERE CustomerTransactions.CustomerID = Customers.CustomerID);
SELECT *
FROM Customers
Order by CustomerID;
Chapter 18
Setup Script - Part 1:
DROP TABLE MyTable;
DROP TABLE RelatedTable;
CREATE TABLE RelatedTable
(FirstColumn INT NOT NULL PRIMARY KEY);
Statements in Book:
CREATE TABLE MyTable
(ColumnOne INT PRIMARY KEY NOT NULL,
ColumnTwo INT NOT NULL,
ColumnThree VARCHAR2 (25) NULL,
ColumnFour FLOAT DEFAULT 10 NULL,
CONSTRAINT "ForeignKey" FOREIGN KEY (ColumnTwo)
REFERENCES RelatedTable (FirstColumn) );
ALTER TABLE MyTable
DROP COLUMN ColumnThree;
DROP TABLE MyTable;
Setup Script - Part 2:
DROP TABLE MyTable;
CREATE TABLE MyTable
(ColumnOne INT PRIMARY KEY NOT NULL,
ColumnTwo INT NOT NULL,
ColumnThree VARCHAR2 (25) NULL,
ColumnFour FLOAT DEFAULT 10 NULL,
CONSTRAINT "ForeignKey" FOREIGN KEY (ColumnTwo)
REFERENCES RelatedTable (FirstColumn) );
Statements in Book:
CREATE INDEX Index2 ON MyTable (ColumnFour);
DROP INDEX Index2;
................
................
In order to avoid copyright disputes, this page is only a partial summary.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.
Related download
- access training materials cengage
- oracle database express edition
- oracle java application tutorial
- oracle imaging and process management services
- starting oracle with vmware le moyne college
- the oracle database commonly referred to as oracle rdbms
- installation guide for oracle 11g express edition apex
- microsoft word
Related searches
- free reading materials for kids
- free reading materials for children
- free materials for teaching esl
- free access 2016 training manual
- teachers training materials pdf
- customer service training materials pdf
- cengage higher education textbooks
- cengage financial accounting answer key
- cengage learning higher education
- hazardous materials technician training fema
- customer service training materials free
- leadership training materials free