ࡱ>  Kbjbj|:|: $PPKCCCCCWWWWWTWo(jg6xj&`(((((((){,^(Cxx(CC)(BCC((r&TCC'kQW>'( ?(0o(L',,'C' :   The Language of SQL SQL Statements and Data for SQL Server How to Use This Document This document contains all the SQL statements in "The Language of SQL" in the syntax of Microsoft SQL Server. 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 three prerequisites: Install SQL Server Express 2008 Install SQL Server Management Studio Create a database Appendix A of "The Language of SQL" contains instructions on how do these installs and create a database. To use any of the SQL statements in this document, simply copy the desired statements into SQL Management Studio. 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. Note that you will see a GO command after each DROP TABLE statement. The GO serves the purpose of forcing the DROP TABLE to execute prior to the subsequent CREATE TABLE. SQL Statements from the Book Each statement in the book is shown in the correct syntax for Microsoft SQL Server. If a particular statement doesn't apply to SQL Server, 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, 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: IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID (N'Customers') AND TYPE IN (N'U')) DROP TABLE Customers GO CREATE TABLE Customers (CustomerID INT NOT NULL, FirstName VARCHAR(45) NULL, Lastname VARCHAR(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: IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID (N'Customers') AND TYPE IN (N'U')) DROP TABLE Customers GO CREATE TABLE Customers ([Last Name] VARCHAR(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: IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID (N'Orders') AND TYPE IN (N'U')) DROP TABLE Orders GO CREATE TABLE Orders (OrderID INT NOT NULL, FirstName VARCHAR(45) NULL, Lastname VARCHAR(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) IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID (N'Orders123') AND TYPE IN (N'U')) DROP TABLE Orders123 GO CREATE TABLE Orders123 (LastName VARCHAR(45) NOT 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 AS Orders SELECT Orders.LastName FROM Orders123 AS Orders Chapter 4 Setup Script: IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID (N'table1') AND TYPE IN (N'U')) DROP TABLE table1 GO CREATE TABLE table1 (President VARCHAR(20) NULL) INSERT INTO table1 (President) VALUES ('George Washington ') IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID (N'Orders') AND TYPE IN (N'U')) DROP TABLE Orders GO CREATE TABLE Orders (OrderID INT NOT NULL, FirstName VARCHAR(45) NULL, LastName VARCHAR(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) IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID (N'Products') AND TYPE IN (N'U')) DROP TABLE Products GO CREATE TABLE Products (ProductID INT NOT NULL, Description VARCHAR(45) NULL, Color VARCHAR(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: SELECT LEFT ('sunlight',3) AS 'The Answer' SELECT RIGHT ('sunlight',5) AS 'The Answer' SELECT RIGHT (President,10) AS 'Last Name' FROM table1 SELECT SUBSTRING ('thewhitegoat', 4, 5) AS 'The Answer' SELECT LTRIM (' the apple') AS 'The Answer' SELECT UPPER ('Abraham Lincoln') AS 'Convert to Uppercase', LOWER ('ABRAHAM LINCOLN') AS 'Convert to Lowercase' SELECT RIGHT (RTRIM (President),10) AS 'Last Name' FROM table1 SELECT RIGHT ('George Washington', 10) SELECT GETDATE () SELECT DATEPART (month, '7/2/2009') SELECT DATEPART (day, '7/2/2009') SELECT DATEPART (week, '7/2/2009') SELECT DATEPART (weekday, '7/2/2009') SELECT DATEDIFF (day, '2009-07-08', '2009-08-14') SELECT DATEDIFF (week, '2009-07-08', '2009-08-14') SELECT DATEDIFF (month, '2009-07-08', '2009-08-14') SELECT DATEDIFF (year, '2009-07-08', '2009-08-14') SELECT ROUND (712.863, 3) SELECT ROUND (712.863, 2) SELECT ROUND (712.863, 1) SELECT ROUND (712.863, 0) SELECT ROUND (712.863, -1) SELECT ROUND (712.863, -2) SELECT RAND ( ) SELECT RAND (100) SELECT PI ( ) SELECT ROUND (PI ( ), 2) SELECT '2009-04-11' AS 'Original Date', CAST('2009-04-11' AS DATETIME) AS 'Converted Date' SELECT Description, Color FROM Products SELECT Description, ISNULL (Color, 'Unknown') AS 'Color' FROM Products Chapter 5 Setup Script: IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID (N'Customers') AND TYPE IN (N'U')) DROP TABLE Customers GO CREATE TABLE Customers (CustomerID INT NOT NULL, FirstName VARCHAR(45) NULL, LastName VARCHAR (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') IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID (N'table1') AND TYPE IN (N'U')) DROP TABLE table1 GO CREATE TABLE table1 (TableID INT NOT NULL, CharacterData VARCHAR(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 LastName + ', ' + FirstName AS 'Name' FROM Customers ORDER BY Name SELECT FirstName, LastName FROM Customers ORDER BY LastName + FirstName SELECT NumericData FROM table1 ORDER BY NumericData SELECT ISNULL (NumericData, 0) FROM table1 ORDER BY ISNULL (NumericData,0) SELECT CharacterData FROM table1 ORDER BY CharacterData Chapter 6 Setup Script - Part 1: IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID (N'Products') AND TYPE IN (N'U')) DROP TABLE Products GO CREATE TABLE Products (ProductID INT NOT NULL, CategoryCode VARCHAR(45) NULL, ProductDescription VARCHAR (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: IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID (N'Products') AND TYPE IN (N'U')) DROP TABLE Products GO CREATE TABLE Products (ProductID INT NOT NULL, Fruit VARCHAR(45) NULL, Vegetable VARCHAR (45) NULL, Spice VARCHAR (45) NULL, ProductDescription VARCHAR (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: IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID (N'Orders') AND TYPE IN (N'U')) DROP TABLE Orders GO CREATE TABLE Orders (OrderID INT NOT NULL, FirstName VARCHAR(45) NULL, LastName VARCHAR (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) IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID (N'Books') AND TYPE IN (N'U')) DROP TABLE Books GO CREATE TABLE Books (BookID INT NOT NULL, Title VARCHAR(45) NULL, Author VARCHAR (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 TOP 3 Title AS 'Book Title', CurrentMonthSales AS 'Quantity Sold' FROM Books ORDER BY CurrentMonthSales DESC SELECT TOP 1 Title AS 'Book Title', CurrentMonthSales AS 'Quantity Sold' FROM Books WHERE Author = 'Shakespeare' ORDER BY CurrentMonthSales DESC Chapter 8 Setup Script: IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID (N'Orders') AND TYPE IN (N'U')) DROP TABLE Orders GO CREATE TABLE Orders (OrderID INT NOT NULL, CustomerName VARCHAR (45) NULL, State VARCHAR (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) IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID (N'Products') AND TYPE IN (N'U')) DROP TABLE Products GO CREATE TABLE Products (ProductID INT NOT NULL, ProductDescription VARCHAR(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 ISNULL (Weight, 0) = 0 SELECT ProductDescription, ISNULL (Weight, 0) AS 'Weight' FROM Products WHERE Weight = 0 OR Weight IS NULL Chapter 9 Setup Script: IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID (N'Movies') AND TYPE IN (N'U')) DROP TABLE Movies GO CREATE TABLE Movies (MovieID INT NOT NULL, MovieTitle VARCHAR(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') IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID (N'Actors') AND TYPE IN (N'U')) DROP TABLE Actors GO CREATE TABLE Actors (ActorID INT NOT NULL, FirstName VARCHAR(45) NULL, LastName VARCHAR(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 (7, 'Julie', 'Andrews') Statements from Book: SELECT MovieTitle AS 'Movie' FROM Movies WHERE MovieTitle LIKE '%LOVE%' SELECT MovieTitle AS 'Movie' FROM Movies WHERE MovieTitle LIKE 'LOVE%' SELECT MovieTitle AS 'Movie' FROM Movies WHERE MovieTitle LIKE '%LOVE' SELECT MovieTitle AS 'Movie' FROM Movies WHERE MovieTitle LIKE '% LOVE %' SELECT FirstName, LastName FROM Actors WHERE FirstName LIKE '_ARY' SELECT FirstName, LastName FROM Actors WHERE FirstName LIKE 'J_N' SELECT FirstName, LastName FROM Actors WHERE FirstName LIKE '[CM]ARY' SELECT FirstName, LastName FROM Actors WHERE FirstName LIKE '[^CG]ARY' SELECT FirstName, LastName FROM Actors WHERE FirstName LIKE '%ARY%' AND FirstName NOT LIKE '[MG]ARY' SELECT SOUNDEX ('Smith') AS 'Sound of Smith', SOUNDEX ('Smythe') AS 'Sound of Smythe' SELECT DIFFERENCE ('Smith', 'Smythe') AS 'The Difference' SELECT FirstName, LastName FROM Actors WHERE DIFFERENCE (FirstName, 'John') = 4 SELECT FirstName, LastName, DIFFERENCE (FirstName, 'John') AS 'Difference Value', SOUNDEX (FirstName) AS 'Soundex Value' FROM Actors Chapter 10 Setup Script: IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID (N'SongTitles') AND TYPE IN (N'U')) DROP TABLE SongTitles GO CREATE TABLE SongTitles (SongID INT NOT NULL, Artist VARCHAR(45) NULL, Album VARCHAR(45) NULL, Title VARCHAR(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') IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID (N'Fees') AND TYPE IN (N'U')) DROP TABLE Fees GO CREATE TABLE Fees (FeeID INT NOT NULL, Student VARCHAR(45) NULL, FeeType VARCHAR(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) IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID (N'Grades') AND TYPE IN (N'U')) DROP TABLE Grades GO CREATE TABLE Grades (GradeID INT NOT NULL, Student VARCHAR(45) NULL, GradeType VARCHAR(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: IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID (N'Customers') AND TYPE IN (N'U')) DROP TABLE Customers GO CREATE TABLE Customers (CustomerID INT NOT NULL, FirstName VARCHAR(45) NULL, LastName VARCHAR (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') IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID (N'Orders') AND TYPE IN (N'U')) DROP TABLE Orders GO CREATE TABLE Orders (OrderID INT NOT NULL, CustomerID INT NULL, Quantity INT NULL, PricePerItem real 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 AS C INNER JOIN Orders AS O ON C.CustomerID = O.CustomerID Chapter 12 Setup Script: IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID (N'Customers') AND TYPE IN (N'U')) DROP TABLE Customers GO CREATE TABLE Customers (CustomerID INT NOT NULL, FirstName VARCHAR(45) NULL, LastName VARCHAR (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') IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID (N'Orders') AND TYPE IN (N'U')) DROP TABLE Orders GO 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, '2009-09-01', 10) INSERT INTO Orders (OrderID, CustomerID, OrderDate, OrderAmount) VALUES (2, 2, '2009-09-02', 12.5) INSERT INTO Orders (OrderID, CustomerID, OrderDate, OrderAmount) VALUES (3, 2, '2009-10-03', 18) INSERT INTO Orders (OrderID, CustomerID, OrderDate, OrderAmount) VALUES (4, 3, '2009-09-15', 20) IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID (N'Refunds') AND TYPE IN (N'U')) DROP TABLE Refunds GO 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, '2009-09-02', 5) INSERT INTO Refunds (RefundID, OrderID, RefundDate, RefundAmount) VALUES (2, 3, '2009-10-12', 18) IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID (N'Movies') AND TYPE IN (N'U')) DROP TABLE Movies GO CREATE TABLE Movies (MovieID INT NOT NULL, MovieTitle VARCHAR(45) NULL, Rating VARCHAR(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') IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID (N'Ratings') AND TYPE IN (N'U')) DROP TABLE Ratings GO CREATE TABLE Ratings (RatingID INT NOT NULL, Rating VARCHAR(45) NULL, RatingDescription VARCHAR(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, MovieTitle Chapter 13 Setup Script: IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID (N'Personnel') AND TYPE IN (N'U')) DROP TABLE Personnel GO CREATE TABLE Personnel (EmployeeID INT NOT NULL, EmployeeName VARCHAR(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) IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID (N'Customers') AND TYPE IN (N'U')) DROP TABLE Customers GO CREATE TABLE customers (CustomerID INT NOT NULL, FirstName VARCHAR(45) NULL, LastName VARCHAR (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') IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID (N'Orders') AND TYPE IN (N'U')) DROP TABLE Orders GO 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, '2009-09-01', 10) INSERT INTO Orders (OrderID, CustomerID, OrderDate, OrderAmount) VALUES (2, 2, '2009-09-02', 12.5) INSERT INTO Orders (OrderID, CustomerID, OrderDate, OrderAmount) VALUES (3, 2, '2009-10-03', 18) INSERT INTO Orders (OrderID, CustomerID, OrderDate, OrderAmount) VALUES (4, 3, '2009-09-15', 20) IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID (N'Refunds') AND TYPE IN (N'U')) DROP TABLE Refunds GO 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, '2009-09-02', 5) INSERT INTO Refunds (RefundID, OrderID, RefundDate, RefundAmount) VALUES (2, 3, '2009-10-12', 18) IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID (N'CustomersOrdersRefunds') AND TYPE IN (N'V')) DROP VIEW CustomersOrdersRefunds GO IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID (N'CustomersView') AND TYPE IN (N'V')) DROP VIEW CustomersView GO Statements from Book: SELECT Employees.EmployeeName AS 'Employee Name', Managers.EmployeeName AS 'Manager Name' FROM Personnel AS Employees INNER JOIN Personnel AS Managers ON Employees.ManagerID = Managers.EmployeeID ORDER BY Employees.EmployeeID SELECT Employees.EmployeeName AS 'Employee Name', Managers.EmployeeName AS 'Manager Name' FROM Personnel AS Employees LEFT JOIN Personnel AS Managers ON Employees.ManagerID = Managers.EmployeeID ORDER BY Employees.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 ALTER VIEW CustomersView AS SELECT LEFT (FirstName,1) AS 'Initial', LastName AS 'Last Name' FROM Customers NOTE: The following statement verifies the above modification. SELECT * FROM CustomersView DROP VIEW CustomersView Chapter 14 Setup Script: IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID (N'Customers') AND TYPE IN (N'U')) DROP TABLE Customers GO CREATE TABLE Customers (CustomerID INT NOT NULL, CustomerName VARCHAR(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') IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID (N'Orders') AND TYPE IN (N'U')) DROP TABLE Orders GO CREATE TABLE Orders (OrderID INT NOT NULL, CustomerID INT NULL, OrderAmount FLOAT NULL, OrderType VARCHAR (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', ISNULL (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) AS 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: IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID (N'Orders') AND TYPE IN (N'U')) DROP TABLE Orders GO 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, '2009-10-13', 10) INSERT INTO Orders (OrderID, CustomerID, OrderDate, OrderAmount) VALUES (2, 2, '2009-10-13', 8) INSERT INTO Orders (OrderID, CustomerID, OrderDate, OrderAmount) VALUES (3, 2, '2009-12-05', 7) INSERT INTO Orders (OrderID, CustomerID, OrderDate, OrderAmount) VALUES (4, 2, '2009-12-15', 21) INSERT INTO Orders (OrderID, CustomerID, OrderDate, OrderAmount) VALUES (5, 3, '2009-12-28', 11) IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID (N'Returns') AND TYPE IN (N'U')) DROP TABLE Returns GO 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, '2009-10-23', 2) INSERT INTO Returns (ReturnID, CustomerID, ReturnDate, ReturnAmount) VALUES (2, 2, '2009-12-07', 7) INSERT INTO Returns (ReturnID, CustomerID, ReturnDate, ReturnAmount) VALUES (3, 3, '2009-12-28', 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 EXCEPT SELECT ReturnDate AS 'Date' FROM Returns ORDER BY Date Chapter 16 Setup Script: IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID (N'Customers') AND TYPE IN (N'U')) DROP TABLE Customers GO CREATE TABLE Customers (CustomerID INT NOT NULL, CustomerName VARCHAR(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') IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID (N'ProcedureOne') AND TYPE IN (N'P')) DROP PROCEDURE ProcedureOne GO IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID (N'CustomerProcedure') AND TYPE IN (N'P')) DROP PROCEDURE CustomerProcedure GO Statements from Book: NOTE: The following statements need to be executed one at a time. CREATE PROCEDURE ProcedureOne AS BEGIN SELECT * FROM Customers; END CREATE PROCEDURE CustomerProcedure (@CustID INT) AS BEGIN SELECT * FROM Customers WHERE CustomerID = @CustID; END EXEC ProcedureOne EXEC CustomerProcedure @CustID = 2 ALTER PROCEDURE CustomerProcedure (@CustID INT) AS BEGIN SELECT TOP 5 * FROM Customers WHERE CustomerID = @CustID; END DROP PROCEDURE CustomerProcedure Chapter 17 Setup Script - Part 1: IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID (N'Customers') AND TYPE IN (N'U')) DROP TABLE Customers GO CREATE TABLE Customers (CustomerID INT identity NOT NULL, FirstName VARCHAR (45) NULL, LastName VARCHAR (45) NULL, State VARCHAR (45) NULL, PRIMARY KEY (CustomerID) ) INSERT INTO Customers (FirstName, LastName, State) VALUES ('William', 'Smith', 'IL') INSERT INTO Customers (FirstName, LastName, State) VALUES ('Natalie', 'Lopez', 'WI') INSERT INTO Customers (FirstName, LastName, State) VALUES ('Brenda', 'Harper', 'NV') IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID (N'CustomerTransactions') AND TYPE IN (N'U')) DROP TABLE CustomerTransactions GO CREATE TABLE CustomerTransactions (CustomerID INT NOT NULL, State VARCHAR (45) NULL, Name1 VARCHAR (45) NULL, Name2 VARCHAR (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 (FirstName, LastName, State) VALUES ('Virginia', 'Jones', 'OH'), ('Clark', 'Woodland', 'CA') SELECT * FROM Customers ORDER BY CustomerID Setup Script - Part 2: IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID (N'Customers') AND TYPE IN (N'U')) DROP TABLE Customers GO CREATE TABLE Customers (CustomerID INT IDENTITY NOT NULL, FirstName VARCHAR (45) NULL, LastName VARCHAR (45) NULL, State VARCHAR (45) NULL, PRIMARY KEY (CustomerID) ) INSERT INTO Customers (FirstName, LastName, State) VALUES ('William', 'Smith', 'IL') INSERT INTO Customers (FirstName, LastName, State) VALUES ('Natalie', 'Lopez', 'WI') INSERT INTO Customers (FirstName, LastName, State) VALUES ('Brenda', 'Harper', 'NV') Statements from Book: INSERT INTO Customers (State, LastName, FirstName) VALUES ('OH', 'Jones', 'Virginia'), ('CA', 'Woodland', 'Clark') SELECT * FROM Customers ORDER BY CustomerID INSERT INTO Customers (FirstName, LastName) VALUES ('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: IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID (N'Customers') AND TYPE IN (N'U')) DROP TABLE Customers GO CREATE TABLE Customers (CustomerID INT identity NOT NULL, FirstName VARCHAR (45) NULL, LastName VARCHAR (45) NULL, State VARCHAR (45) NULL, PRIMARY KEY (CustomerID) ) INSERT INTO Customers (FirstName, LastName, State) VALUES ('William', 'Smith', 'IL') INSERT INTO Customers (FirstName, LastName, State) VALUES ('Natalie', 'Lopez', 'WI') INSERT INTO Customers (FirstName, LastName, State) VALUES ('Brenda', 'Harper', 'NV') Statements from Book: DELETE FROM Customers Setup Script - Part 4: IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID (N'Customers') AND TYPE IN (N'U')) DROP TABLE Customers GO CREATE TABLE Customers (CustomerID INT IDENTITY NOT NULL, FirstName VARCHAR (45) NULL, LastName VARCHAR (45) NULL, State VARCHAR (45) NULL, PRIMARY KEY (CustomerID) ) INSERT INTO Customers (FirstName, LastName, State) VALUES ('William', 'Smith', 'IL') INSERT INTO Customers (FirstName, LastName, State) VALUES ('Natalie', 'Lopez', 'WI') INSERT INTO Customers (FirstName, LastName, State) VALUES ('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: IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID (N'Customers') AND TYPE IN (N'U')) DROP TABLE Customers GO CREATE TABLE Customers (CustomerID INT NOT NULL, State VARCHAR (45) NULL, Zip VARCHAR (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') IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID (N'CustomerTransactions') AND TYPE IN (N'U')) DROP TABLE CustomerTransactions GO CREATE TABLE CustomerTransactions (TransactionID INT NOT NULL, CustomerID INT NOT NULL, State VARCHAR (45) NULL, Zip VARCHAR (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 from 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: IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID (N'MyTable') AND TYPE IN (N'U')) DROP TABLE MyTable GO IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID (N'RelatedTable') AND TYPE IN (N'U')) DROP TABLE RelatedTable GO CREATE TABLE RelatedTable (FirstColumn INT IDENTITY(1,1) NOT NULL PRIMARY KEY) Statements from Book: CREATE TABLE MyTable (ColumnOne INT IDENTITY (1,1) PRIMARY KEY NOT NULL, ColumnTwo INT NOT NULL REFERENCES RelatedTable(FirstColumn), ColumnThree VARCHAR (25) NULL, ColumnFour FLOAT NULL DEFAULT (10)) ALTER TABLE MyTable DROP COLUMN ColumnThree DROP TABLE MyTable Setup Script - Part 2: IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID (N'MyTable') AND TYPE IN (N'U')) DROP TABLE MyTable GO CREATE TABLE MyTable (ColumnOne INT IDENTITY (1,1) PRIMARY KEY NOT NULL, ColumnTwo INT NOT NULL REFERENCES RelatedTable(FirstColumn), ColumnThree VARCHAR (25) NULL, ColumnFour FLOAT NULL DEFAULT (10)) Statements from Book: CREATE INDEX Index2 ON MyTable (ColumnFour) DROP INDEX Index2 ON MyTable ;>W ( M *,۸waPww h&Th:c0JOJQJ^JaJ+jh&Th:cOJQJU^JaJh&Th:cOJQJ^JaJ%jh&Th:cOJQJU^JaJ hh:cCJOJQJ^JaJh:cCJOJQJ^JaJ h}#h:cCJOJQJ^JaJ#h}#h:c5CJOJQJ^JaJ#h}#h:c5CJOJQJ^JaJ#h}#h65CJOJQJ^JaJ;<=>WXYe f H I    & FP^`Pgd:cgd:c     +, & FP^`Pgd:cP^`Pgd:cgd:c & FP^`Pgd:c   345>?AB\]^ghjkֶֶֶֶֶֶtֶֶ^ֶֶ+j?h&Th:cOJQJU^JaJ+jh&Th:cOJQJU^JaJ+jYh&Th:cOJQJU^JaJ+jh&Th:cOJQJU^JaJh&Th:cOJQJ^JaJ h&Th:c0JOJQJ^JaJ%jh&Th:cOJQJU^JaJ+jsh&Th:cOJQJU^JaJ#@Aij:;ef<=gd:c ,-.89;<WXYcdfgֶֶֶֶֶֶtֶֶ^ֶֶ+jh&Th:cOJQJU^JaJ+j h&Th:cOJQJU^JaJ+jh&Th:cOJQJU^JaJ+j%h&Th:cOJQJU^JaJh&Th:cOJQJ^JaJ h&Th:c0JOJQJ^JaJ%jh&Th:cOJQJU^JaJ+jh&Th:cOJQJU^JaJ#./0:;=>Yֶֶֶֶֶֶtֶֶ^ֶֶ+jh&Th:cOJQJU^JaJ+jVh&Th:cOJQJU^JaJ+jh&Th:cOJQJU^JaJ+jlh&Th:cOJQJU^JaJh&Th:cOJQJ^JaJ h&Th:c0JOJQJ^JaJ%jh&Th:cOJQJU^JaJ+jh&Th:cOJQJU^JaJ#YZ[efghrstx$&HֶsesZsesIs h}#h:cCJOJQJ^JaJh:c5OJQJ^Jh:cCJOJQJ^JaJh}#h:c5OJQJ^J h}#h:cCJOJQJ^JaJ#h}#h:c5CJOJQJ^JaJ#h}#h:c5CJOJQJ^JaJh&Th:cOJQJ^JaJ h&Th:c0JOJQJ^JaJ%jh&Th:cOJQJU^JaJ+j@h&Th:cOJQJU^JaJ=grst  :Vqr Wvwxgd:cx &'1Lw 1$7$8$H$gd:c@&gd:cgd:cFG}~#7ILgd:c 1$7$8$H$gd:cHK9;QR"#~""X#Z#p#((((()!)]+`+,,/////2050%2;2334466D7F7P7R7`777R:U: >">8>ϯϯϯϯ#h}#h:c>*CJOJQJ^JaJ#h}#h:c5CJOJQJ^JaJh}#h:c5OJQJ^J#h}#h:c5CJOJQJ^JaJ h}#h:cCJOJQJ^JaJh:cCJOJQJ^JaJ>LMax7^_-RS]x(9gd:c9:;QRZkv",GST@&gd:cgd:cT[doy"#-Hpgd:c@&gd:c  1Ym   n !!f!!gd:c!!!!!!""""6"T"l"m"""""##:#X#Y#Z#p#q#y###@&gd:cgd:c######$$ $>$?$G$u$v$~$$$$$%(%)%0%P%Q%c%d%%%gd:c@&gd:c%%%%%%%)&*&]&^&&&&&&&&&''2'3'N'O'j'k'{'|'gd:c|'''''''''(((+(1(?(@(H(U(z(((((((((@&gd:c 1$7$8$H$gd:cgd:c(((( ))")#):)T)p))))):*;******+8+L+^+a+b+v+gd:cv+++++,,_,`,,,,,,,,,---/-0-8-D-M-\-o-@&gd:c 1$7$8$H$gd:cgd:co-p-x------------. ..%.B.C.K.q...... 1$7$8$H$gd:c@&gd:cgd:c......./////;/[/\/d/r/~///////////gd:c@&gd:c 1$7$8$H$gd:c/ 00306070M0f00001 1f1g111#2$2%2;2<2C2U2k222@&gd:c 1$7$8$H$gd:cgd:c2222222 323?3R3v3333333334444/4H4`4@&gd:c 1$7$8$H$gd:cgd:c`4}4444.5/555666666666666757C7D7E7P7@&gd:c 1$7$8$H$gd:cgd:cP7Q7R7`7a7k7777777788;8W8p8q888h9i9999:.:gd:c 1$7$8$H$gd:c.:B:S:V:W:j::::::<;=;;;<<<<<<K=L=== >!>"> 1$7$8$H$gd:cgd:c">8>9>A>M>X>j>v>>>>>>>>>>>> ??(?D?E?M?Y?b?@&gd:c 1$7$8$H$gd:cgd:cb?n??????????@ @ @E@P@m@@@@@@@@@@@ 1$7$8$H$gd:c@&gd:cgd:c8>@@@@@@A ACCpEEMMMMM@NCNQQSSWWWWWWsXvX[[0\;\s^v^ bb$b%b]b^bbbbb;c*CJOJQJ^JaJ#h}#h:c5CJOJQJ^JaJ#h}#h:c5CJOJQJ^JaJ h}#h:cCJOJQJ^JaJ:@@ AA!A"A6AMAmAAAAA9B:BBB5C6C@C[CCCCCCCCDgd:cDDDdDeDDDEEnEoEpEEEEEEEEEEE FF+F7FSF@&gd:c 1$7$8$H$gd:cgd:cSFgFhFpFFFFFFFFFFFGG G4GEG_G`GhGwGGGGG@&gd:c 1$7$8$H$gd:cgd:cGGGGGHHH-H9HPHQHYHhHpHHHHHHHHHHH I I@&gd:c 1$7$8$H$gd:cgd:c II!I)I;IGI[ImInIvIIIIIIIIIIIJJ0JJJKJSJbJ 1$7$8$H$gd:cgd:c@&gd:cbJtJJJJJJJJJKK!K0KBKNK{K|KKKKKKKKKK@&gd:c 1$7$8$H$gd:cgd:cKKKLLL&L,L8LXLYLaLvL}LLLLLLLLLLLLMM@&gd:c 1$7$8$H$gd:cgd:cM%MBMCMKM`MMMMMMMMMMMMMMN/NANDNENYNpNN@&gd:c 1$7$8$H$gd:cgd:cNNNNOObOcOOOOODPEPPPPPPPQQQ'Q>QZQuQvQ  gd:cgd:cvQQQRRhRiRRR S SZS[SSSSSSSSSTTT-T9TWT@&gd:c 1$7$8$H$gd:cgd:cWTXT`TvTTTTTTTTTTU UU1U2U:UEUNUZUuUvU~UUUgd:c@&gd:c 1$7$8$H$gd:cUUUUUUUUVVVV#V/VLVmVnVvVVVVVWW WWW@&gd:c 1$7$8$H$gd:cgd:cW*WSWTW\WgWqWWWWWWWWWWWXXJX^XtXwXxXXX@&gd:c 1$7$8$H$gd:cgd:cXXXXX_Y`YYY;Z*CJOJQJ^JaJ#h}#h:c5CJOJQJ^JaJ h}#h:cCJOJQJ^JaJ#h}#h:c5CJOJQJ^JaJ h;Qh:cCJOJQJ^JaJ7zeeeeeeeff f&f01Sg '(/Z 1$7$8$H$gd:c@&gd:cgd:c =eΑ<^˒ڒ)Mgd:c@&gd:c 1$7$8$H$gd:cؓ@e֔ 23:HUb~ەgd:c@&gd:c'H`op̖͖)Th} 1$7$8$H$gd:c@&gd:cgd:cїҗ mn-Ui{~יST@&gd:cgd:cTlm̛͛-./EFMnɜ՜@p@&gd:cgd:cŝѝ'P_p'6J^j͟ 1$7$8$H$gd:c@&gd:cgd:c͟HIPpߠ'Sáҡߡ@&gd:cgd:c 1$7$8$H$gd:c%&-M\pȢڢ$Ejy*@&gd:cgd:c*2AM}¤ΤϤФޤߤ,@RUVjå 1$7$8$H$gd:c@&gd:cgd:cåĥ%&IJѧ!$%:Rg}`agd:caũƩǩݩީ %1FLSi}ƪǪΪ+@&gd:cgd:c+,3<P\fmv«Ϋث߫+7>EZg@&gd:cgd:cguvw (BabKLV 1$7$8$H$gd:c@&gd:cgd:cVqϮҮӮݮ+?`cde{|ݯ@&gd:c 1$7$8$H$gd:cgd:c(69?HWswxҰ#'(I 1$7$8$H$gd:c1$7$8$@&H$gd:cIJKWXYpq{ձ(EazAB 1$7$8$H$gd:cgd:cWXYp&)8;ٹ?VϻһŽ>A*kKͼͼͼͼͼͼͼͼͼͼۼͼͼͼ#h}#h:c5CJOJQJ^JaJh:cCJOJQJ^JaJ h}#h:cCJOJQJ^JaJh}#h:c5OJQJ^J#h}#h:c>*CJOJQJ^JaJ#h}#h:c5CJOJQJ^JaJ:'*+Mg^ǵ8 1$7$8$H$gd:cgd:c8?]yzʶ$9<=Uxʷ;gd:c@&gd:c 1$7$8$H$gd:c;<5<Zvwҹٹ@&gd:cgd:c 1$7$8$H$gd:c3PW^ekźƺͺ׺#$>?Vgd:c@&gd:c 1$7$8$H$gd:cVWa|лӻԻ+G`{|ѼҼ'(}~ 1$7$8$H$gd:cgd:cŽƽн*?BCZ}Ͼ@A 1$7$8$H$gd:cgd:c 1$7$8$H$gd:c.BWXap 7Qj 1$7$8$H$gd:c@&gd:cgd:c_`OcYvwgd:c 1$7$8$H$gd:c'DE*+<SvYfp1$7$8$@&H$gd:cgd:c 1$7$8$H$gd:c'Bk3 1$7$8$H$gd:cgd:c3hjk'KL`xy4hgd:c 1$7$8$H$gd:ch-.Kgd:c ":p:c/ =!"#$%sDyK  Chapter2sDyK  Chapter3sDyK  Chapter4sDyK  Chapter5sDyK  Chapter6sDyK  Chapter7sDyK  Chapter8sDyK  Chapter9uDyK  Chapter10uDyK  Chapter11uDyK  Chapter12uDyK  Chapter13uDyK  Chapter14uDyK  Chapter15uDyK  Chapter16uDyK  Chapter17uDyK  Chapter188XV~ OJPJQJ_HmH nH sH tH D`D +pXNormalCJ_HaJmH sH tH DA`D Default Paragraph FontRiR Table Normal4 l4a (k ( No List N@N sLight List - Accent 5 ^m$6U@6 la Hyperlink >*B*phFVF laFollowedHyperlink >*B*phPK!K[Content_Types].xmlj0Eжr(΢]yl#!MB;BQޏaLSWyҟ^@ Lz]__CdR{`L=r85v&mQ뉑8ICX=H"Z=&JCjwA`.Â?U~YkG/̷x3%o3t\&@w!H'"v0PK!֧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!\theme/theme/theme1.xmlYOoE#F{o'NDuر i-q;N3' G$$DAč*iEP~wq4;{o?g^;N:$BR64Mvsi-@R4Œ mUb V*XX! cyg$w.Q "@oWL8*Bycjđ0蠦r,[LC9VbX*x_yuoBL͐u_. DKfN1엓:+ۥ~`jn[Zp֖zg,tV@bW/Oټl6Ws[R?S֒7 _כ[֪7 _w]ŌShN'^Bxk_[dC]zOլ\K=.:@MgdCf/o\ycB95B24S CEL|gO'sקo>W=n#p̰ZN|ӪV:8z1f؃k;ڇcp7#z8]Y / \{t\}}spķ=ʠoRVL3N(B<|ݥuK>P.EMLhɦM .co;əmr"*0#̡=6Kր0i1;$P0!YݩjbiXJB5IgAФ޲a6{P g֢)҉-Ìq8RmcWyXg/u]6Q_Ê5H Z2PU]Ǽ"GGFbCSOD%,p 6ޚwq̲R_gJSbj9)ed(w:/ak;6jAq11_xzG~F<:ɮ>O&kNa4dht\?J&l O٠NRpwhpse)tp)af] 27n}mk]\S,+a2g^Az )˙>E G鿰L7)'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-!K[Content_Types].xmlPK-!֧6 1_rels/.relsPK-!kytheme/theme/themeManager.xmlPK-!\theme/theme/theme1.xmlPK-! ѐ' theme/theme/_rels/themeManager.xml.relsPK] K YH8>IeOWKhkmnos =xL9T!#%|'(v+o-./2`4P7.:">b?@DSFG IbJKMNvQWTUWX(\^,bcze^g(ijm9pru)xb{$69M0T͟*åa+gVI8;V3hKijlpqrtuvwxyz{|}~   4 > A ] g j   - 8 ; X c f    / : = Z e KXXXXXXXXXXXXXXXXX8@0(  B S  ? _Hlt255833329 _Hlt255833283 _Hlt255833344 _Hlt129600188 _Hlt129960324 _Hlt255833352Chapter2Chapter3Chapter4Chapter5Chapter6Chapter7Chapter8Chapter9 Chapter10 Chapter11 Chapter12 Chapter13 Chapter14 Chapter15 Chapter16 Chapter17 Chapter18   h  'F/8EOrb!i|ÜxLM@@@@@@    r  'P/8EO}b,i|ΜWMMMM |z}ÕĕQTUUVV # ΦѦ_b&)8;ϳҳ>AM I2Z~8$QRAc%MfUev淨^80^8`0o(. ^`hH. pL^p`LhH. @ ^@ `hH. ^`hH. L^`LhH. ^`hH. ^`hH. PL^P`LhH.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~8 I2M|        R*        R*        R*        :cM@K@UnknownGTimes New Roman5Symbol3 Arial7Cambria?1M Courier New;Wingdings 1'h(;8.eۡ3RYL3Ud4dKK#QH?'dz2 Larry Rockoff Larry Rockoff     Oh+'0l  ( 4 @LT\d'Larry Rockoff Normal.dotmLarry Rockoff46Microsoft Macintosh Word@Ff@%@xIQ3eۡ ՜.+,D՜.+,, hp|  'YR  Title 8@ _PID_HLINKS'Af<L0 X Chapter183L- X Chapter172L* X Chapter161L' X Chapter150L$ X Chapter147L! X Chapter136L X Chapter125L X Chapter114L X Chapter10D X Chapter9E X Chapter8J X Chapter7K  X Chapter6H  X Chapter5I X Chapter4N X Chapter3O X Chapter2  !"#$%&'()*+,-./0123456789:;<=>?@ABCDEFGHIJKLMNOPQRSTUVWXYZ[\]^_`abcdefghijklmnopqrstuvwxyz{|}~Root Entry FT2kQData 1Table,WordDocument$SummaryInformation(DocumentSummaryInformation8CompObj` F Microsoft Word 97-2004 DocumentNB6WWord.Document.8