SQL Statements and Data for SQL Server

The Language of SQLSQL Statements and Data for SQL ServerHow to Use This DocumentThis 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 databaseAppendix 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 ScriptsThe setup scripts allow you to create the data needed to execute all statements in thatchapter. These scripts consist of CREATE TABLE commands to create the tables andINSERT statements to insert data into those tables. There are also DROP TABLEstatements 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 BookEach statement in the book is shown in the correct syntax for Microsoft SQL Server. If aparticular 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.Chapter 2Chapter 3Chapter 4Chapter 5Chapter 6Chapter 7Chapter 8Chapter 9Chapter 10Chapter 11Chapter 12Chapter 13Chapter 14Chapter 15Chapter 16Chapter 17Chapter 18Chapter 2Setup Script - Part 1:IF EXISTS(SELECT * FROM sys.objectsWHERE OBJECT_ID = OBJECT_ID (N'Customers')AND TYPE IN (N'U'))DROP TABLE CustomersGOCREATE 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 CustomersSELECT *FROM CustomersSELECT LastNameFROM CustomersSELECTFirstName,LastNameFROM CustomersSetup Script - Part 2:IF EXISTS(SELECT * FROM sys.objectsWHERE OBJECT_ID = OBJECT_ID (N'Customers')AND TYPE IN (N'U'))DROP TABLE CustomersGOCREATE 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 CustomersChapter 3Setup Script:IF EXISTS(SELECT * FROM sys.objectsWHERE OBJECT_ID = OBJECT_ID (N'Orders')AND TYPE IN (N'U'))DROP TABLE OrdersGOCREATE 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.objectsWHERE OBJECT_ID = OBJECT_ID (N'Orders123')AND TYPE IN (N'U'))DROP TABLE Orders123GOCREATE TABLE Orders123(LastName VARCHAR(45) NOT NULL)INSERT INTO Orders123 (Lastname)VALUES ('Smith')Statements from Book:SELECT'First Name: ',FirstNameFROM OrdersSELECT5,FirstNameFROM OrdersSELECTOrderID,QuantityPurchased,PricePerItem,QuantityPurchased * PricePerItem FROM OrdersSELECTOrderID,FirstName,LastName,FirstName + ' ' + LastNameFROM OrdersSELECTOrderID,FirstName,LastName,FirstName + ' ' + LastName AS 'Name' FROM OrdersSELECTLastNameFROM Orders123 AS Orders SELECTstNameFROM Orders123 AS OrdersChapter 4Setup Script:IF EXISTS(SELECT * FROM sys.objectsWHERE OBJECT_ID = OBJECT_ID (N'table1')AND TYPE IN (N'U'))DROP TABLE table1GOCREATE TABLE table1(President VARCHAR(20) NULL)INSERT INTO table1 (President)VALUES ('George Washington ')IF EXISTS(SELECT * FROM sys.objectsWHERE OBJECT_ID = OBJECT_ID (N'Orders')AND TYPE IN (N'U'))DROP TABLE OrdersGOCREATE 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.objectsWHERE OBJECT_ID = OBJECT_ID (N'Products')AND TYPE IN (N'U'))DROP TABLE ProductsGOCREATE 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:SELECTLEFT ('sunlight',3) AS 'The Answer'SELECTRIGHT ('sunlight',5) AS 'The Answer'SELECTRIGHT (President,10) AS 'Last Name'FROM table1SELECTSUBSTRING ('thewhitegoat', 4, 5) AS 'The Answer' SELECTLTRIM (' the apple') AS 'The Answer'SELECTUPPER ('Abraham Lincoln') AS 'Convert to Uppercase', LOWER ('ABRAHAM LINCOLN') AS 'Convert to Lowercase' SELECTRIGHT (RTRIM (President),10) AS 'Last Name'FROM table1SELECTRIGHT ('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' SELECTDescription,ColorFROM ProductsSELECTDescription,ISNULL (Color, 'Unknown') AS 'Color'FROM ProductsChapter 5Setup Script:IF EXISTS(SELECT * FROM sys.objectsWHERE OBJECT_ID = OBJECT_ID (N'Customers')AND TYPE IN (N'U'))DROP TABLE CustomersGOCREATE 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.objectsWHERE OBJECT_ID = OBJECT_ID (N'table1')AND TYPE IN (N'U'))DROP TABLE table1GOCREATE 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:SELECTFirstName,LastNameFROM CustomersORDER BY LastNameSELECTFirstName,LastNameFROM CustomersORDER BY FirstNameSELECTFirstName,LastNameFROM CustomersORDER BY FirstName ASCSELECTFirstName,LastNameFROM CustomersORDER BY FirstName DESC SELECTFirstName,LastNameFROM CustomersORDER BY LastName, FirstName SELECTLastName + ', ' + FirstName AS 'Name' FROM CustomersORDER BY NameSELECTFirstName,LastNameFROM CustomersORDER BY LastName + FirstName SELECT NumericDataFROM table1ORDER BY NumericDataSELECTISNULL (NumericData, 0)FROM table1ORDER BY ISNULL (NumericData,0) SELECTCharacterDataFROM table1ORDER BY CharacterDataChapter 6Setup Script - Part 1:IF EXISTS(SELECT * FROM sys.objectsWHERE OBJECT_ID = OBJECT_ID (N'Products')AND TYPE IN (N'U'))DROP TABLE ProductsGOCREATE 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:SELECTCASE CategoryCodeWHEN 'F' THEN 'Fruit'WHEN 'V' THEN 'Vegetable'ELSE 'Other'END AS 'Category',ProductDescription AS 'Description'FROM ProductsSELECTCASEWHEN CategoryCode = 'F' THEN 'Fruit'WHEN CategoryCode = 'V' THEN 'Vegetable'ELSE 'Other'END AS 'Category',ProductDescription AS 'Description'FROM ProductsSetup Script - Part 2:IF EXISTS(SELECT * FROM sys.objectsWHERE OBJECT_ID = OBJECT_ID (N'Products')AND TYPE IN (N'U'))DROP TABLE ProductsGOCREATE 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:SELECTCASEWHEN Fruit = 'X' THEN 'Fruit'WHEN Vegetable = 'X' THEN 'Vegetable'ELSE 'Other'END AS 'Category',ProductDescription AS 'Description'FROM ProductsChapter 7Setup Script:IF EXISTS(SELECT * FROM sys.objectsWHERE OBJECT_ID = OBJECT_ID (N'Orders')AND TYPE IN (N'U'))DROP TABLE OrdersGOCREATE 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.objectsWHERE OBJECT_ID = OBJECT_ID (N'Books')AND TYPE IN (N'U'))DROP TABLE BooksGOCREATE 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:SELECTFirstName,LastName,QuantityPurchasedFROM OrdersWHERE LastName = 'Harper'SELECTFirstName,LastName,QuantityPurchasedFROM OrdersWHERE QuantityPurchased = 5SELECTFirstName,LastName,QuantityPurchasedFROM OrdersWHERE QuantityPurchased > 6SELECTFirstName,LastNameFROM OrdersWHERE LastName > 'K'SELECTTOP 3Title AS 'Book Title',CurrentMonthSales AS 'Quantity Sold'FROM BooksORDER BY CurrentMonthSales DESCSELECTTOP 1Title AS 'Book Title',CurrentMonthSales AS 'Quantity Sold'FROM BooksWHERE Author = 'Shakespeare'ORDER BY CurrentMonthSales DESCChapter 8Setup Script:IF EXISTS(SELECT * FROM sys.objectsWHERE OBJECT_ID = OBJECT_ID (N'Orders')AND TYPE IN (N'U'))DROP TABLE OrdersGOCREATE 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.objectsWHERE OBJECT_ID = OBJECT_ID (N'Products')AND TYPE IN (N'U'))DROP TABLE ProductsGOCREATE 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:SELECTCustomerName,QuantityPurchasedFROM OrdersWHERE QuantityPurchased > 3 AND QuantityPurchased < 7 SELECTCustomerName, QuantityPurchased, PricePerItemFROM OrdersWHERE QuantityPurchased > 8 OR PricePerItem > 3SELECTCustomerName,State,QuantityPurchasedFROM OrdersWHERE State = 'IL'OR State = 'CA'AND QuantityPurchased > 8 SELECTCustomerName,State,QuantityPurchasedFROM OrdersWHERE (State = 'IL'OR State = 'CA')AND QuantityPurchased > 8 SELECTCustomerName,State,QuantityPurchasedFROM OrdersWHERE State = 'NY'OR (State = 'IL'AND (QuantityPurchased >= 3 AND QuantityPurchased <= 10)) SELECTCustomerName,State,QuantityPurchasedFROM OrdersWHERE NOT State = 'NY' SELECTCustomerName,State,QuantityPurchasedFROM OrdersWHERE State <> 'NY' SELECTCustomerName,State,QuantityPurchasedFROM OrdersWHERE NOT (State = 'IL'OR State = 'NY')SELECTCustomerName,State,QuantityPurchasedFROM OrdersWHERE State <> 'IL'AND State <> 'NY'SELECTCustomerName,State,QuantityPurchasedFROM OrdersWHERE NOT (State = 'IL'AND QuantityPurchased > 3)SELECTCustomerName,State,QuantityPurchasedFROM OrdersWHERE State <> 'IL'OR QuantityPurchased <= 3SELECTCustomerName,QuantityPurchasedFROM OrdersWHERE QuantityPurchased >= 5AND QuantityPurchased <= 20SELECTCustomerName,QuantityPurchasedFROM OrdersWHERE QuantityPurchased BETWEEN 5 AND 20 SELECTCustomerName,QuantityPurchasedFROM OrdersWHERE QuantityPurchased NOT BETWEEN 5 AND 20 SELECTCustomerName,StateFROM OrdersWHERE State = 'IL'OR State = 'NY'SELECTCustomerName,StateFROM OrdersWHERE State IN ('IL', 'NY') SELECTCustomerName,StateFROM OrdersWHERE State NOT IN ('IL', 'NY') SELECT ProductDescription,WeightFROM ProductsWHERE Weight = 0SELECT ProductDescription,WeightFROM ProductsWHERE Weight = 0OR Weight IS NULLSELECT ProductDescription,WeightFROM ProductsWHERE ISNULL (Weight, 0) = 0 SELECT ProductDescription,ISNULL (Weight, 0) AS 'Weight' FROM ProductsWHERE Weight = 0OR Weight IS NULLChapter 9Setup Script:IF EXISTS(SELECT * FROM sys.objectsWHERE OBJECT_ID = OBJECT_ID (N'Movies')AND TYPE IN (N'U'))DROP TABLE MoviesGOCREATE 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.objectsWHERE OBJECT_ID = OBJECT_ID (N'Actors')AND TYPE IN (N'U'))DROP TABLE ActorsGOCREATE 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: SELECTMovieTitle AS 'Movie'FROM MoviesWHERE MovieTitle LIKE '%LOVE%' SELECTMovieTitle AS 'Movie'FROM MoviesWHERE MovieTitle LIKE 'LOVE%' SELECTMovieTitle AS 'Movie'FROM MoviesWHERE MovieTitle LIKE '%LOVE' SELECTMovieTitle AS 'Movie'FROM MoviesWHERE MovieTitle LIKE '% LOVE %' SELECTFirstName,LastNameFROM ActorsWHERE FirstName LIKE '_ARY' SELECTFirstName,LastNameFROM ActorsWHERE FirstName LIKE 'J_N' SELECTFirstName,LastNameFROM ActorsWHERE FirstName LIKE '[CM]ARY' SELECTFirstName,LastNameFROM ActorsWHERE FirstName LIKE '[^CG]ARY' SELECTFirstName,LastNameFROM ActorsWHERE FirstName LIKE '%ARY%' AND FirstName NOT LIKE '[MG]ARY' SELECTSOUNDEX ('Smith') AS 'Sound of Smith',SOUNDEX ('Smythe') AS 'Sound of Smythe'SELECTDIFFERENCE ('Smith', 'Smythe') AS 'The Difference' SELECTFirstName,LastNameFROM ActorsWHERE DIFFERENCE (FirstName, 'John') = 4 SELECTFirstName,LastName,DIFFERENCE (FirstName, 'John') AS 'Difference Value', SOUNDEX (FirstName) AS 'Soundex Value'FROM ActorsChapter 10Setup Script:IF EXISTS(SELECT * FROM sys.objectsWHERE OBJECT_ID = OBJECT_ID (N'SongTitles')AND TYPE IN (N'U'))DROP TABLE SongTitlesGOCREATE 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.objectsWHERE OBJECT_ID = OBJECT_ID (N'Fees')AND TYPE IN (N'U'))DROP TABLE FeesGOCREATE 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.objectsWHERE OBJECT_ID = OBJECT_ID (N'Grades')AND TYPE IN (N'U'))DROP TABLE GradesGOCREATE 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:SELECTDISTINCTArtistFROM SongTitlesORDER BY ArtistSELECTDISTINCTArtist,AlbumFROM SongTitlesORDER BY Artist, AlbumSELECTSUM (Fee) AS 'Total Gym Fees'FROM FeesWHERE FeeType = 'Gym'SELECTAVG (Grade) AS 'Average Quiz Score'FROM GradesWHERE GradeType = 'Quiz'SELECTAVG (Grade) AS 'Average Quiz Score',MIN (Grade) AS 'Minimum Quiz Score',MAX (Grade) AS 'Maximum Quiz Score'FROM GradesWHERE GradeType = 'Quiz'SELECTCOUNT (*) AS 'Count of Homework Rows'FROM GradesWHERE GradeType = 'Homework'SELECTCOUNT (Grade) AS 'Count of Homework Scores' FROM GradesWHERE GradeType = 'Homework'SELECTCOUNT (DISTINCT FeeType) AS 'Number of Fee Types' FROM FeesSELECTGradeType AS 'Grade Type',AVG (Grade) AS 'Average Grade'FROM GradesGROUP BY GradeTypeORDER BY GradeTypeNOTE: The following statement intentionally errors. SELECTGradeType AS 'Grade Type',AVG (Grade) AS 'Average Grade',Student AS 'Student'FROM GradesGROUP BY GradeTypeORDER BY GradeTypeSELECTGradeType AS 'Grade Type',Student AS 'Student',AVG (Grade) AS 'Average Grade'FROM GradesGROUP BY GradeType, StudentORDER BY GradeType, StudentSELECTGradeType AS 'Grade Type',Student AS 'Student',AVG (Grade) AS 'Average Grade'。