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.

Google Online Preview   Download