SQL Statements and Data for SQL Server

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.

Chapter 2

Chapter 3

Chapter4

Chapter 5

Chapter 6

Chapter 7

Chapter 8

Chapter 9

Chapter 10

Chapter 11

Chapter 12

Chapter 13

Chapter 14

Chapter 15

Chapter 16

Chapter 17

Chapter 18

Chapter 2

Setup Script - Part 1:

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)