ࡱ>  ^bjbjUU 0`??^hhTT&%g6xj&`$$$$$$$$&t)^$xx$$$$V#@m$@VT# $$0&%#x)()m$)m$$$$&%)h q: 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.  HYPERLINK \l "Chapter2" Chapter 2  HYPERLINK \l "Chapter3" Chapter 3  HYPERLINK \l "Chapter4" Chapter 4  HYPERLINK \l "Chapter5" Chapter 5  HYPERLINK \l "Chapter6" Chapter 6  HYPERLINK \l "Chapter7" Chapter 7  HYPERLINK \l "Chapter8" Chapter 8  HYPERLINK \l "Chapter9" Chapter 9  HYPERLINK \l "Chapter10" Chapter 10  HYPERLINK \l "Chapter11" Chapter 11  HYPERLINK \l "Chapter12" Chapter 12  HYPERLINK \l "Chapter13" Chapter 13  HYPERLINK \l "Chapter14" Chapter 14  HYPERLINK \l "Chapter15" Chapter 15  HYPERLINK \l "Chapter16" Chapter 16  HYPERLINK \l "Chapter17" Chapter 17  HYPERLINK \l "Chapter18" 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 <= 3; SELECT * FROM (SELECT Title AS "Book Title", CurrentMonthSales AS "Quantity Sold" FROM Books WHERE Author = 'Shakespeare' ORDER BY CurrentMonthSales DESC) WHERE ROWNUM <= 1; Chapter 8 Setup Script: DROP TABLE Orders; CREATE TABLE Orders (OrderID INT NOT NULL, CustomerName VARCHAR2 (45) NULL, State VARCHAR2 (45) NULL, QuantityPurchased INT NULL, PricePerItem FLOAT NULL); INSERT INTO Orders (OrderID, CustomerName, State, QuantityPurchased, PricePerItem) VALUES (1, 'William Smith', 'IL', 4, 2.5); INSERT INTO Orders (OrderID, CustomerName, State, QuantityPurchased, PricePerItem) VALUES (2, 'Natalie Lopez', 'CA', 10, 1.25); INSERT INTO Orders (OrderID, CustomerName, State, QuantityPurchased, PricePerItem) VALUES (3, 'Brenda Harper', 'NY', 5, 4); DROP TABLE Products; CREATE TABLE Products (ProductID INT NOT NULL, ProductDescription VARCHAR2 (45) NULL, Weight INT NULL); INSERT INTO Products (ProductID, ProductDescription) VALUES (1, 'Printer A'); INSERT INTO Products (ProductID, ProductDescription, Weight) VALUES (2, 'Printer B', 0); INSERT INTO Products (ProductID, ProductDescription, Weight) VALUES (3, 'Monitor C', 2); INSERT INTO Products (ProductID, ProductDescription, Weight) VALUES (4, 'Laptop D', 4); Statements from Book: SELECT CustomerName, QuantityPurchased FROM Orders WHERE QuantityPurchased > 3 AND QuantityPurchased < 7; SELECT CustomerName, QuantityPurchased, PricePerItem FROM Orders WHERE QuantityPurchased > 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 <= 10)); SELECT CustomerName, State, QuantityPurchased FROM Orders WHERE NOT State = 'NY'; SELECT CustomerName, State, QuantityPurchased FROM Orders WHERE State <> 'NY'; SELECT CustomerName, State, QuantityPurchased FROM Orders WHERE NOT (State = 'IL' OR State = 'NY'); SELECT CustomerName, State, QuantityPurchased FROM Orders WHERE State <> 'IL' AND State <> 'NY'; SELECT CustomerName, State, QuantityPurchased FROM Orders WHERE NOT (State = 'IL' AND QuantityPurchased > 3); SELECT CustomerName, State, QuantityPurchased FROM Orders WHERE State <> 'IL' OR QuantityPurchased <= 3; SELECT CustomerName, QuantityPurchased FROM Orders WHERE QuantityPurchased >= 5 AND QuantityPurchased <= 20; SELECT CustomerName, QuantityPurchased FROM Orders WHERE QuantityPurchased BETWEEN 5 AND 20; SELECT CustomerName, QuantityPurchased FROM Orders WHERE QuantityPurchased NOT BETWEEN 5 AND 20; SELECT CustomerName, State FROM Orders WHERE State = 'IL' OR State = 'NY'; SELECT CustomerName, State FROM Orders WHERE State IN ('IL', 'NY'); SELECT CustomerName, State FROM Orders WHERE State NOT IN ('IL', 'NY'); SELECT ProductDescription, Weight FROM Products WHERE Weight = 0; SELECT ProductDescription, Weight FROM Products WHERE Weight = 0 OR Weight IS NULL; SELECT ProductDescription, Weight FROM Products WHERE NVL (Weight, 0) = 0; SELECT ProductDescription, NVL (Weight, 0) AS Weight FROM Products WHERE Weight = 0 OR Weight IS NULL; Chapter 9 Setup Script: DROP TABLE Movies; CREATE TABLE Movies (MovieID INT NOT NULL, MovieTitle VARCHAR2 (45) NULL); INSERT INTO Movies (MovieID, MovieTitle) VALUES (1, 'Love Actually'); INSERT INTO Movies (MovieID, MovieTitle) VALUES (2, 'His Girl Friday'); INSERT INTO Movies (MovieID, MovieTitle) VALUES (3, 'Love and Death'); INSERT INTO Movies (MovieID, MovieTitle) VALUES (4, 'Sweet and Lowdown'); INSERT INTO Movies (MovieID, MovieTitle) VALUES (5, 'Everyone Says I Love You'); INSERT INTO Movies (MovieID, MovieTitle) VALUES (6, 'Down with Love'); INSERT INTO Movies (MovieID, MovieTitle) VALUES (7, 'One Hundred and One Dalmations'); DROP TABLE Actors; CREATE TABLE Actors (ActorID INT NOT NULL, FirstName VARCHAR2 (45) NULL, LastName VARCHAR2 (45) 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'); INSERT INTO Actors (ActorID, FirstName, LastName) VALUES (6, 'Julie', 'Andrews'); Statements from Book: SELECT MovieTitle AS Movie FROM Movies WHERE MovieTitle LIKE '%Love%'; SELECT MovieTitle AS Movie FROM Movies WHERE UPPER (MovieTitle) LIKE '%LOVE%'; SELECT MovieTitle AS Movie FROM Movies WHERE MovieTitle LIKE 'Love%'; SELECT 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'; NOTE: The remaining wildcards are not available in Oracle. SELECT SOUNDEX ('Smith') AS "Sound of Smith", SOUNDEX ('Smythe') AS "Sound of Smythe" FROM DUAL; NOTE: The statements with the DIFFERENCE function are not available in Oracle. Chapter 10 Setup Script: DROP TABLE SongTitles; CREATE TABLE SongTitles (SongID INT NOT NULL, Artist VARCHAR2 (45) NULL, Album VARCHAR2 (45) NULL, Title VARCHAR2 (45) NULL); INSERT INTO SongTitles (SongID, Artist, Album, Title) VALUES (1, 'The Beatles', 'Abbey Road', 'Come Together'); INSERT INTO SongTitles (SongID, Artist, Album, Title) VALUES (2, 'The Beatles', 'Abbey Road', 'Sun King'); INSERT INTO SongTitles (SongID, Artist, Album, Title) VALUES (3, 'The Beatles', 'Revolver', 'Yellow Submarine'); INSERT INTO SongTitles (SongID, Artist, Album, Title) VALUES (4, 'The Rolling Stones', 'Let It Bleed', 'Monkey Man'); INSERT INTO SongTitles (SongID, Artist, Album, Title) VALUES (5, 'The Rolling Stones', 'Flowers', 'Ruby Tuesday'); INSERT INTO SongTitles (SongID, Artist, Album, Title) VALUES (6, 'Paul McCartney', 'Ram', 'Smile Away'); DROP TABLE Fees; CREATE TABLE Fees (FeeID INT NOT NULL, Student VARCHAR2 (45) NULL, FeeType VARCHAR2 (45) NULL, Fee INT NULL); INSERT INTO Fees (FeeID, Student, FeeType, Fee) VALUES (1, 'George', 'Gym', 30); INSERT INTO Fees (FeeID, Student, FeeType, Fee) VALUES (2, 'George', 'Lunch', 10); INSERT INTO Fees (FeeID, Student, FeeType, Fee) VALUES (3, 'George', 'Trip', 8); INSERT INTO Fees (FeeID, Student, FeeType, Fee) VALUES (4, 'Janet', 'Gym', 30); INSERT INTO Fees (FeeID, Student, FeeType, Fee) VALUES (5, 'Alan', 'Lunch', 10); DROP TABLE Grades; CREATE TABLE Grades (GradeID INT NOT NULL, Student VARCHAR2 (45) NULL, GradeType VARCHAR2 (45) NULL, Grade DECIMAL NULL); INSERT INTO Grades (GradeID, Student, GradeType, Grade) VALUES (1, 'Susan', 'Quiz', 92); INSERT INTO Grades (GradeID, Student, GradeType, Grade) VALUES (2, 'Susan', 'Quiz', 95); INSERT INTO Grades (GradeID, Student, GradeType, Grade) VALUES (3, 'Susan', 'Homework', 84); INSERT INTO Grades (GradeID, Student, GradeType, Grade) VALUES (4, 'Kathy', 'Quiz', 62); INSERT INTO Grades (GradeID, Student, GradeType, Grade) VALUES (5, 'Kathy', 'Quiz', 81); INSERT INTO Grades (GradeID, Student, GradeType) VALUES (6, 'Kathy', 'Homework'); INSERT INTO Grades (GradeID, Student, GradeType, Grade) VALUES (7, 'Alec', 'Quiz', 58); INSERT INTO Grades (GradeID, Student, GradeType, Grade) VALUES (8, 'Alec', 'Quiz', 74); INSERT INTO Grades (GradeID, Student, GradeType, Grade) VALUES (9, 'Alec', 'Homework', 88); Statements from Book: 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; NOTE: The following statement intentionally errors SELECT GradeType AS "Grade Type", AVG (Grade) AS "Average Grade", Student AS "Student" 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 Student, GradeType 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; 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; 7:S  Dw01KLMVWYZt۸zdSzz hph/ 0JOJQJ^JaJ+jhph/ OJQJU^JaJhph/ OJQJ^JaJ%jhph/ OJQJU^JaJh/ CJOJQJ^JaJhDCJOJQJ^JaJ h:h/ CJOJQJ^JaJ#h:h/ 5CJOJQJ^JaJ#h:h/ 5CJOJQJ^JaJ#h:hc5CJOJQJ^JaJ789:STUS T q r    & FP^`Pgd/ m$ & FP^`Pgd/ gd/  G H }~xy/0XY & FP^`Pgd/ m$P^`Pgd/ gd/ tuv#$&'Aֶֶֶֶֶֶtֶֶ^ֶֶ+j?hph/ OJQJU^JaJ+jhph/ OJQJU^JaJ+jYhph/ OJQJU^JaJ+jhph/ OJQJU^JaJhph/ OJQJ^JaJ hph/ 0JOJQJ^JaJ%jhph/ OJQJU^JaJ+jshph/ OJQJU^JaJ#%&NOwx#$NOyzgd/ ABCLMOPjkluvxyֶֶֶֶֶֶtֶֶ^ֶֶ+jhph/ OJQJU^JaJ+j hph/ OJQJU^JaJ+jhph/ OJQJU^JaJ+j%hph/ OJQJU^JaJhph/ OJQJ^JaJ hph/ 0JOJQJ^JaJ%jhph/ OJQJU^JaJ+jhph/ OJQJU^JaJ#!"$%@ABLMOPklmwxz{ֶֶֶֶ~n~[nJ h77h/ 0JOJQJ^JaJ%jVh/ OJQJU^JaJjh/ OJQJU^JaJh/ OJQJ^JaJ+jhph/ OJQJU^JaJ+jlhph/ OJQJU^JaJhph/ OJQJ^JaJ hph/ 0JOJQJ^JaJ%jhph/ OJQJU^JaJ+jhph/ OJQJU^JaJŶ|f|TB#h:h/ 5CJOJQJ^JaJ#h:h/ 5CJOJQJ^JaJ+j@hph/ OJQJU^JaJ hph/ 0JOJQJ^JaJ+jhph/ OJQJU^JaJ%jhph/ OJQJU^JaJhph/ OJQJ^JaJh/ OJQJ^JaJjh/ OJQJU^JaJ h77h/ 0JOJQJ^JaJh/ 0JOJQJ^JaJ56Mg7WX@&gd/ gd/ Ne^tacy./~/2;<>Ln ###$+$,$$$% %ϽϽ{Ͻ#h:h/ >*CJOJQJ^JaJ h4h/ CJOJQJ^JaJh/ CJOJQJ^JaJ h:h/ CJOJQJ^JaJ#h:h/ 5CJOJQJ^JaJ#h:h/ 5CJOJQJ^JaJh:h/ 5OJQJ^J h:h/ CJOJQJ^JaJ0'3<LMNef|}$%I\]^tu}@&gd/ gd/ }1Mgh9cdgd/  ,-Oabcyz!./6@&gd/ gd/ 6?JTq~/01<=>LM`gd/ @&gd/ `au5RnX)gd/ )Bb}~   M l m n !!!#!P![!\!@&gd/ gd/ \!d!n!z!!!!!!"8"C"D"K"~""""""""""# # #@&gd/ gd/ 1$7$8$H$gd/  #'#2#3#M#X#Y#s#~#########$,$M$}$$$$$$$$@&gd/ 1$7$8$H$gd/ gd/ $$$$$$$ % % %%%0%1%H%b%%%%%%N&O&&&''' 1$7$8$H$gd/ gd/  % %%((++++++--/./11z2}2222288O;R;[;\;^;l;w??GGGGGGM(M&O,OcOOOPPPϽϽϽϽ{{ hbh/ CJOJQJ^JaJh/ CJOJQJ^JaJ#h:h/ >*CJOJQJ^JaJ h:h/ CJOJQJ^JaJ#h:h/ 5CJOJQJ^JaJ#h:h/ 5CJOJQJ^JaJh:h/ 5OJQJ^J h:h/ CJOJQJ^JaJ0'')'@'b'y'z'''((W(X(((((((((((((()@&gd/ 1$7$8$H$gd/ gd/ ))+),)4)@)I)X)p)q)y))))))))))** *>*M*^*_* 1$7$8$H$gd/ @&gd/ gd/ _*g*s*|*********++:+;+C+Q+]+++++++++gd/ @&gd/ 1$7$8$H$gd/ +++++,6,7,,,,,S-T---------.!.4.X.g.@&gd/ 1$7$8$H$gd/ gd/ g.h.o.t.....////.///D/E/[/t//////a0b000gd/ @&gd/ 1$7$8$H$gd/ 0J1K111111112'242G2k2z2{2|2222222222@&gd/ 1$7$8$H$gd/ gd/ 223.3H3I333B4C44444445.5K5L555 6!66677j7gd/ j7k777?8@88888888888999+969H9T9q9r9z99@&gd/ 1$7$8$H$gd/ gd/ 9999999999 : ::,:Q:W:a:l::::::::::@&gd/ 1$7$8$H$gd/ gd/ :;<;O;P;Q;\;];^;l;m;;;;;;;<<<<<====== 1$7$8$H$gd/ gd/ ===>>>h>i>>>??u?v?w??????????@@&@@&gd/ 1$7$8$H$gd/ gd/ &@3@?@[@p@q@y@@@@@@@@@@A AA*A>AOAjAkAsAAA@&gd/ 1$7$8$H$gd/ gd/ AAAAAABBBB'B9BEB]B^BfBuB}BBBBBBBBBB@&gd/ 1$7$8$H$gd/ gd/ BCCC!C0C8CJCVCjC}C~CCCCCCCCCCDD!D-DAD\D@&gd/ 1$7$8$H$gd/ gd/ \D]DeDtDDDDDDDDDE,E-E5EDEVEbEEEEEEEEEgd/ @&gd/ 1$7$8$H$gd/ EEEEEF%F&F.F=FCFOFpFqFyFFFFFFFFFFF G Ggd/ @&gd/ 1$7$8$H$gd/  GG*G1G?GZG[GcGxGGGGGGGGGGGGGG H!HAHBHH 1$7$8$H$gd/ gd/ @&gd/ HHHHIIdIeIIIIIVJWJjJkJJJJJJ"K#KxKyKKKL  gd/ gd/ LLlLmLLLMMM(M)M1MEMQMqMrMyMMMMMMMM N NN@&gd/ 1$7$8$H$gd/ gd/ N&N2NQNRNZNnNzNNNNNNNNNNNN O'O(OcOdOlOOO 1$7$8$H$gd/ gd/ @&gd/ OOOPPPP%P&P'P5P6PMPNPfP|PPPPP=Q>QQQRR  1$7$8$H$gd/ 1$7$8$H$gd/ gd/ P%P&P'P5PSSCYEY[Y\YYYYY#Z$ZuZvZ[[[[ \\\\\\\A]B]]]{^|^__aaaaaaeegggggrrjymyxyʼܮʼʮܼܼܼܼ h:h/ CJOJQJ^JaJ hbh/ CJOJQJ^JaJh/ CJOJQJ^JaJh:h/ 5OJQJ^J#h:h/ 5CJOJQJ^JaJ h:h/ CJOJQJ^JaJ#h:h/ 5CJOJQJ^JaJ7RRRSSpSqSSSSSSSSSCTDTTTTT:U;UUUUU  1$7$8$H$gd/ 1$7$8$H$gd/ UUUUVVVuVvVVV-W.WWWWW4X5XXXXXCYDYEY  1$7$8$H$gd/ 1$7$8$H$gd/ EY[Y\YcYmYtYYYYYYYYYYYYZ Z#Z$Z+ZOZ[ZuZvZ}Z@&gd/ 1$7$8$H$gd/ gd/ }ZZZZZ[[[?[K[i[j[q[[[[[[\ \\\0\O\[\n\\ 1$7$8$H$gd/ @&gd/ gd/ \\\\\\\]]-]A]B]I]d]z]]]]]]]^^6^B^^^{^gd/ @&gd/ 1$7$8$H$gd/ {^|^^^^^^^__ _6_Q_p_|________ `$`4`M`$a$gd/ gd/ @&gd/ 1$7$8$H$gd/ M`N`U`k`````````a)aHaTamaaaaaaaaaaa 1$7$8$H$gd/ @&gd/ gd/ aaabb9bWbbbc cacbccccccc d d:d;dddddJeKegd/ Keeeeeeeeefff'f-7ﰞﰞ{iZžh/ 5CJOJQJ^JaJ#h#)h/ 5CJOJQJ^JaJ#h:h/ >*CJOJQJ^JaJ h:h/ CJOJQJ^JaJ#h:h/ 5CJOJQJ^JaJ#h:h/ 5CJOJQJ^JaJ hbh/ CJOJQJ^JaJh/ CJOJQJ^JaJh:h/ 5OJQJ^J h:h/ CJOJQJ^JaJzzSzTzzz{{q{r{{{)|*|||||<}=}}}}}}}}~~gd/ ~v~w~~~()|}rs/gd/ /AWqrfgÃ5U܄ 1$7$8$H$gd/ @&gd/ gd/ !@q'Lqφ,-SZć gd/ @&gd/ 1$7$8$H$gd/  1@Q}ۈ܈ 'DEbjgd/ @&gd/  45Lf׊؊&'uv‹Ë֋׋/Ngd/ 1$7$8$H$gd/ @&gd/ NOnoʍˍ+,Ύ (4T`y 1$7$8$H$gd/ @&gd/ gd/ y͏ ,EYZaʐ;Z[đgd/ @&gd/ 1$7$8$H$gd/ đߑ(Tm̒ے=>Eetݓ@&gd/ 1$7$8$H$gd/ gd/ #2?IUє*<h~̕ە@&gd/ gd/ Dcdk$%&234BCVWkŗ 1$7$8$H$gd/ gd/ @&gd/ ŗƗAB34'(<=Rj,-&'(>?gd/ ?F[nʜޜ*+2FRX_tÝgd/ @&gd/ Ý͝ԝݝ,8BI^k|}ƞӞ8@&gd/ gd/ 78:Q 6˨ǪǬݬf}(< 01^ϾϾϾϾϾϾϾϾϾϾ{ݾϾϾϾ h:h/ CJOJQJ^JaJ#h:h/ 5CJOJQJ^JaJ#h:h/ 5CJOJQJ^JaJhcCJOJQJ^JaJ h:h/ CJOJQJ^JaJh:h/ 5OJQJ^J#h:h/ >*CJOJQJ^JaJ h#)h/ CJOJQJ^JaJ%89:QRhiџRS@Ac}̡͡5gd/ 1$7$8$H$gd/ 56  6`gȣϣ67M@&gd/ gd/ 1$7$8$H$gd/ MNe{ѤҤ782\cĦ˦gd/ 1$7$8$H$gd/ 1T[qr{קާ !*9NO 1$7$8$H$gd/ gd/ @&gd/ OV`oʨ˨'Eb}~IJ 1$7$8$H$gd/ gd/ @&gd/ ǪȪϪߪ'=[x_`ŬƬǬݬެgd/ 1$7$8$H$gd/ ެ12;Jdef}~ƭDEڮۮ% 1$7$8$H$gd/ gd/ @&gd/ %&pqүVW&'(<=N1$7$8$@&H$gd/ gd/ 1$7$8$H$gd/ Ne-kx۳ܳ 01EF_`gd/ 1$7$8$H$gd/ `zϴ ,N~յֵ.Skgd/ 1$7$8$H$gd/ ݶJK^gd/ .:p/ / =!"#$% sDyK  Chapter2sDyK  Chapter3sDyK  Chapter4sDyK  Chapter5sDyK  Chapter6sDyK  Chapter7sDyK  Chapter8sDyK  Chapter9uDyK  Chapter10uDyK  Chapter11uDyK  Chapter12uDyK  Chapter13uDyK  Chapter14uDyK  Chapter15uDyK  Chapter16uDyK  Chapter17uDyK  Chapter18j  OJPJQJ_HmH nH sH tH D`D +pXNormalCJ_HaJmH sH tH DA`D Default Paragraph FontRiR 0 Table Normal4 l4a (k ( 0No List ZZ sColorful Shading - Accent 3 ^m$6U@6 s Hyperlink >*B*phFVF 77FollowedHyperlink >*B*phPK![Content_Types].xmlj0Eжr(΢Iw},-j4 wP-t#bΙ{UTU^hd}㨫)*1P' ^W0)T9<l#$yi};~@(Hu* Dנz/0ǰ $ X3aZ,D0j~3߶b~i>3\`?/[G\!-Rk.sԻ..a濭?PK!֧6 _rels/.relsj0 }Q%v/C/}(h"O = C?hv=Ʌ%[xp{۵_Pѣ<1H0ORBdJE4b$q_6LR7`0̞O,En7Lib/SeеPK!kytheme/theme/themeManager.xml M @}w7c(EbˮCAǠҟ7՛K Y, e.|,H,lxɴIsQ}#Ր ֵ+!,^$j=GW)E+& 8PK!Ptheme/theme/theme1.xmlYOo6w toc'vuر-MniP@I}úama[إ4:lЯGRX^6؊>$ !)O^rC$y@/yH*񄴽)޵߻UDb`}"qۋJחX^)I`nEp)liV[]1M<OP6r=zgbIguSebORD۫qu gZo~ٺlAplxpT0+[}`jzAV2Fi@qv֬5\|ʜ̭NleXdsjcs7f W+Ն7`g ȘJj|h(KD- dXiJ؇(x$( :;˹! I_TS 1?E??ZBΪmU/?~xY'y5g&΋/ɋ>GMGeD3Vq%'#q$8K)fw9:ĵ x}rxwr:\TZaG*y8IjbRc|XŻǿI u3KGnD1NIBs RuK>V.EL+M2#'fi ~V vl{u8zH *:(W☕ ~JTe\O*tHGHY}KNP*ݾ˦TѼ9/#A7qZ$*c?qUnwN%Oi4 =3ڗP 1Pm \\9Mؓ2aD];Yt\[x]}Wr|]g- eW )6-rCSj id DЇAΜIqbJ#x꺃 6k#ASh&ʌt(Q%p%m&]caSl=X\P1Mh9MVdDAaVB[݈fJíP|8 քAV^f Hn- "d>znNJ ة>b&2vKyϼD:,AGm\nziÙ.uχYC6OMf3or$5NHT[XF64T,ќM0E)`#5XY`פ;%1U٥m;R>QD DcpU'&LE/pm%]8firS4d 7y\`JnίI R3U~7+׸#m qBiDi*L69mY&iHE=(K&N!V.KeLDĕ{D vEꦚdeNƟe(MN9ߜR6&3(a/DUz<{ˊYȳV)9Z[4^n5!J?Q3eBoCM m<.vpIYfZY_p[=al-Y}Nc͙ŋ4vfavl'SA8|*u{-ߟ0%M07%<ҍPK! ѐ'theme/theme/_rels/themeManager.xml.relsM 0wooӺ&݈Э5 6?$Q ,.aic21h:qm@RN;d`o7gK(M&$R(.1r'JЊT8V"AȻHu}|$b{P8g/]QAsم(#L[PK-![Content_Types].xmlPK-!֧6 +_rels/.relsPK-!kytheme/theme/themeManager.xmlPK-!Ptheme/theme/theme1.xmlPK-! ѐ' theme/theme/_rels/themeManager.xml.relsPK] ^`tA %Pxy7^\_abceo }6`)\! #$')_*+g.02j79:=&@AB\DE GHLNORUEY}Z\{^M`aKegimqtxz~/ Nyđŗ?Ý85MOެ%N`^]^`dfghijklmnpqrstuvwxyz{|}~0 L V Y u   # & B L O k u x  ! $ A L O l w z ^XXXXXXXXXXXXXXXXX8@0(  B S  ? _Hlt129600188 _Hlt132514029 _Hlt256524177 _Hlt256524178 _Hlt256524187 _Hlt256524196Chapter2Chapter3Chapter4Chapter5Chapter6Chapter7Chapter8Chapter9 Chapter10 Chapter11 Chapter12 Chapter13 Chapter14 Chapter15 Chapter16 Chapter17 Chapter18 q q 2#}*R3?HY_mq'- `@@@@@@  r r  < #*\3?%HY_xq 28`>?CDwx >>??$G$G&G&G'GbGcGcGdGdGGHKKTTgm`gm]`~8$QRAc%MfUev淨^0^`0OJQJo(hH^`OJQJo(hHop^p`OJQJo(hH@ ^@ `OJQJo(hH^`OJQJo(hHo^`OJQJo(hH^`OJQJo(hH^`OJQJo(hHoP^P`OJQJo(hH 0^`0OJQJo( ^`OJQJo(o p^p`OJQJo( @ ^@ `OJQJo( ^`OJQJo(o ^`OJQJo( ^`OJQJo( ^`OJQJo(o P^P`OJQJo( 0^`0OJQJo( ^`OJQJo(o p^p`OJQJo( @ ^@ `OJQJo( ^`OJQJo(o ^`OJQJo( ^`OJQJo( ^`OJQJo(o P^P`OJQJo(c%MUev~8R*        R*        R*        / cD^`@xh^@UnknownG*Ax Times New Roman5Symbol3. *Cx Arial7K@Cambria?= *Cx Courier New;WingdingsA BCambria Math"1hA&9lFF,2-Y>,2-Y>!4do:QH$Pdz2!xx Larry Rockoff Larry Rockoff   Oh+'0h  $ 0 <HPX`Larry Rockoff Normal.dotmLarry Rockoff30Microsoft Office Word@$e @4;@'>T-,2՜.+,D՜.+,, hp|  >Y  Title 8@ _PID_HLINKSAfL0 Chapter18L- Chapter17L* Chapter16L' Chapter15L$ Chapter14L! Chapter13L Chapter12L Chapter11L Chapter10D Chapter9E Chapter8J Chapter7K  Chapter6H  Chapter5I Chapter4N Chapter3O Chapter2  !"#$%&'()*+,-./0123456789:;<=>?@ABCDEFGHIJKLMNOPQRSTUVWXYZ[\]^_`abcdefghijklmnopqrstuvwxyz{|}~Root Entry FCWTData 1Table)WordDocument0`SummaryInformation(DocumentSummaryInformation8CompObjy  F'Microsoft Office Word 97-2003 Document MSWordDocWord.Document.89q