Question

Prepare and execute each of the queries listed using the "Adventureworks2012" database in SQL:

Server: http://msftdbprodsamples.codeplex.com/releases/view/93587

When all of your queries are complete, cut and paste the SQL Syntax into a word document. In order to see what the column names are, you need to click on the table and then Columns to see the field names. Make sure to include column headings that make sense in the queries (use the as “Field Name” after the field selected).

Multi-table Queries (each query will require the access of at least 2 tables, maybe more):

6. Write a query to find out how many people have a Seattle address. List the Address ID, Address Lines (Street and Apt number if available), City, StateProvince ID, Name, and Business Entity ID. Use the Person.Address, Person.AddressType, and Person.BusinessEntityAddress tables. Sort by Address ID. You should get 141 records.

7. Write a query to find out the minimum order quantity greater than 100 for all vendors. List the vendor name, account number, credit rating, last receipt cost, maximum order quantity, and minimum order quantity. Soft by minimum order quantity and then by maximum order quantity. Use the Purchasing.ProductVendor and Purchasing.Vendor tables. You should get 38 records.

8. Write a query to find out how many Vista credit cards expire in 2008 or more recent (2009, 2010, etc.). List the Credit Card ID, Card Type, Card Number, Expiration Month, Expiration Year, Sales Order ID, and Customer ID. Sort by expiration year, and then expiration month. Use the Sales.Creditcard and Sales.SalesOrderHeader tables. You should get 1,708 records.

9. Write a query to find out how many Vista credit cards expire in 2008 or more recent (2009, 2010, etc.) from customer 11151, 11223, and 11750. List the Credit Card ID, Card Type, Card Number, Expiration Month, Expiration Year, Sales Order ID, and Customer ID. Order by Customer ID. Use the Sales.Creditcard and Sales.SalesOrderHeader tables. You should get 33 records.

10. Write a query to find out how many products had a scrapped quantity greater than 20 and an order quantity greater than 2000 . List the Product ID, Product Name, Product Number, Work Order ID, Order Quantity, Scrapped Quantity, and Scrap Reason ID. Sort by Scrapped Quantity, and then by Product ID. You should get 56 records.

Answer 1

A. Using CONTAINS with <simple_term>

The following example finds all products with a price of $80.99 that contain the word "Mountain".

USE AdventureWorks2012

GO

SELECT Name, ListPrice

FROM Production.Product

WHERE ListPrice = 80.99

   AND CONTAINS(Name, 'Mountain')

GO

B. Using FREETEXT to search for words containing specified character values

The following example searches for all documents containing the words related to vital, safety, components.

USE AdventureWorks2012

GO

SELECT Title

FROM Production.Document

WHERE FREETEXT (Document, 'vital safety components')

GO

In This Topic

Overview of the Full-Text Functions (CONTAINSTABLE and FREETEXTTABLE)

The CONTAINSTABLE and FREETEXTTABLE functions are referenced like a regular table name in the FROM clause of a SELECT statement. They return a table of zero, one, or more rows that match the full-text query. The returned table contains only rows of the base table that match the selection criteria specified in the full-text search condition of the function.

Note

For information about the syntax and arguments of these functions, see CONTAINSTABLE (Transact-SQL) and FREETEXTTABLE (Transact-SQL).

Queries using one of these functions return a relevance ranking value (RANK) and full-text key (KEY) for each row, as follows:

• KEY column

The KEY column returns unique values of the returned rows. The KEY column can be used to specify selection criteria.

• RANK column

The RANK column returns a rank value for each row that indicates how well the row matched the selection criteria. The higher the rank value of the text or document in a row, the more relevant the row is for the given full-text query. Note that different rows can be ranked identically. You can limit the number of matches to be returned by specifying the optional top_n_by_rank parameter. For more information, see Limit Search Results with RANK.

When using either of these functions, you must specify the base table that is to be full-text searched. As with the predicates, you can specify a single column, a list of columns, or all columns in the table to be searched, and optionally, the language whose resources will be used by given full-text query.

CONTAINSTABLE is useful for the same kinds of matches as CONTAINS, and FREETEXTTABLE is useful for the same kinds of matches as FREETEXT. For more information, see Overview of the Full-Text Predicates (CONTAINS and FREETEXT), earlier in this topic. When running queries that use the CONTAINSTABLE and FREETEXTTABLE functions you must explicitly join rows that are returned with the rows in the SQL Server base table.

Typically, the result of CONTAINSTABLE or FREETEXTTABLE needs to be joined with the base table. In such cases, you need to know the unique key column name. This column, which occurs in every full-text enabled table, is used to enforce unique rows for the table (the unique key column). For more information, see Manage Full-Text Indexes.

In This Topic

Examples

A. Using CONTAINSTABLE

The following example returns the description ID and description of all products for which the Description column contain the word "aluminum" near either the word "light" or the word "lightweight." Only rows with a rank value of 2 or higher are returned.

USE AdventureWorks2012

GO

SELECT FT_TBL.ProductDescriptionID,

   FT_TBL.Description, 

   KEY_TBL.RANK

FROM Production.ProductDescription AS FT_TBL INNER JOIN

   CONTAINSTABLE (Production.ProductDescription,

      Description, 

      '(light NEAR aluminum) OR

      (lightweight NEAR aluminum)'

   ) AS KEY_TBL

   ON FT_TBL.ProductDescriptionID = KEY_TBL.[KEY]

WHERE KEY_TBL.RANK > 2

ORDER BY KEY_TBL.RANK DESC;

GO

B. Using FREETEXTTABLE

The following example extends a FREETEXTTABLE query to return the highest ranked rows first and to add the ranking of each row to the select list. To specify the query, you must know that ProductDescriptionID is the unique key column for the ProductDescription table.

USE AdventureWorks2012

GO

SELECT KEY_TBL.RANK, FT_TBL.Description

FROM Production.ProductDescription AS FT_TBL 

     INNER JOIN

     FREETEXTTABLE(Production.ProductDescription, Description,

                    'perfect all-around bike') AS KEY_TBL

     ON FT_TBL.ProductDescriptionID = KEY_TBL.[KEY]

ORDER BY KEY_TBL.RANK DESC

GO

Here is an extension of the same query that only returns rows with a rank value of 10 or greater:

USE AdventureWorks2012

GO

SELECT KEY_TBL.RANK, FT_TBL.Description

FROM Production.ProductDescription AS FT_TBL 

     INNER JOIN

     FREETEXTTABLE(Production.ProductDescription, Description,

                    'perfect all-around bike') AS KEY_TBL

     ON FT_TBL.ProductDescriptionID = KEY_TBL.[KEY]

WHERE KEY_TBL.RANK >= 10

ORDER BY KEY_TBL.RANK DESC

GO

In This Topic

Using Boolean operators – AND, OR, and NOT – in CONTAINS and CONTAINSTABLE

The CONTAINS predicate and CONTAINSTABLE function use the same search conditions. Both support combining several search terms by using Boolean operators—AND, OR, AND NOT—to perform logical operations. You could use AND, for example, to find rows that contain both "latte" and "New York-style bagel". You can use AND NOT, for example, to find the rows that contain "bagel" but do not contain "cream cheese".

Note

In contrast, FREETEXT and FREETEXTTABLE treat the Boolean terms as words to be searched.

For information about combining CONTAINS with other predicates that use the logical operators AND, OR, and NOT, see Search Condition (Transact-SQL).

Example

The following example uses the ProductDescription table of the AdventureWorks2012 database. The query uses the CONTAINS predicate to search for descriptions in which the description ID is not equal to 5 and the description contains both the word "Aluminum" and the word "spindle." The search condition uses the AND Boolean operator.

USE AdventureWorks2012

GO

SELECT Description

FROM Production.ProductDescription

WHERE ProductDescriptionID <> 5 AND

   CONTAINS(Description, 'aluminum AND spindle')

GO

In This Topic

Additional Considerations for Full-Text Queries

When writing full-text queries also consider the following:

• The LANGUAGE option

Many query terms depend heavily on word-breaker behavior. To ensure that you are using the correct word breaker (and stemmer) and thesaurus file, we recommend that you specify the LANGUAGE option. For more information, see Choose a Language When Creating a Full-Text Index.

• Stopwords

When defining a full-text query, the Full-Text Engine discards stopwords (also called noise words) from the search criteria. Stopwords are words such as "a," "and," "is," or "the," that can occur frequently but that typically do not help when searching for particular text. Stopwords are listed in a stoplist. Each full-text index is associated with a specific stoplist, which determines what stopwords are omitted from the query or the index at indexing time. For more information, see Configure and Manage Stopwords and Stoplists for Full-Text Search.

• The thesaurus

FREETEXT and FREETEXTTABLE queries use the thesaurus by default. CONTAINS and CONTAINSTABLE support an optional THESAURUS argument.

• Case sensitivity

Full-text search queries are case-insensitive. However, in Japanese, there are multiple phonetic orthographies in which the concept of orthographic normalization is akin to case insensitivity (for example, kana = insensitivity). This type of orthographic normalization is not supported.

In This Topic

Querying varbinary(max) and xml Columns

If a varbinary(max), varbinary, or xml column is full-text indexed, it can be queried using the full-text predicates (CONTAINS and FREETEXT) and functions (CONTAINSTABLE and FREETEXTTABLE), like any other full-text indexed column.

Important

Full-text search also works with image columns. However, the image data type will be removed in a future version of SQL Server. Avoid using this data type in new development work, and plan to modify applications that currently use it. Use the varbinary(max) data type instead.

varbinary(max) or varbinary data

A single varbinary(max) or varbinary column can store many types of documents. SQL Server supports any document type for which a filter is installed and available in the operative system. The document type of each document is identified by the file extension of the document. For example, for a .doc file extension, full-text search uses the filter that supports Microsoft Word documents. For a list of available document types, query the sys.fulltext_document_types catalog view.

Note that the Full-Text Engine can leverage existing filters that are installed in the operating system. Before you can use operating-system filters, word breakers, and stemmers, you must load them in the server instance, as follows:

EXEC sp_fulltext_service @action='load_os_resources', @value=1

To create a full-text index on a varbinary(max) column, the Full-Text Engine needs access to the file extensions of the documents in thevarbinary(max) column. This information must be stored in a table column, called a type column, that must be associated with thevarbinary(max) column in the full-text index. When indexing a document, the Full-Text Engine uses the file extension in the type column to identify which filter to use.

In This Topic

xml data

An xml data type column stores only XML documents and fragments, and only the XML filter is used for the documents. Therefore, a type column is unnecessary. On xml columns, the full-text index indexes the content of the XML elements, but ignores the XML markup. Attribute values are full-text indexed unless they are numeric values. Element tags are used as token boundaries. Well-formed XML or HTML documents and fragments containing multiple languages are supported.

For more information about querying on an xml column, see Use Full-Text Search with XML Columns.

In This Topic

Supported Forms of Query Terms

This section summarizes the support provided for each form of query by the full-text predicates and rowset-valued functions.

Note
For the syntax a given query term, click the corresponding links in Supported by column of the following table.
Query-term form	Description	Supported by
One or more specific words or phrases (simple term)	In full-text search, a word (or token) is a string whose boundaries are identified by appropriate word breakers, following the linguistic rules of the specified language. A valid phrase consists of multiple words, with or without any punctuation marks between them. For example, "croissant" is a word, and "café au lait" is a phrase. Words and phrases such as these are called simple terms. For more information, see Searching for Specific word or Phrase (Simple Term), later in this topic.	CONTAINS andCONTAINSTABLElook for an exact match for the phrase. FREETEXT andFREETEXTTABLEbreak up the phrase into separate words.
A word or a phrase where the words begin with specified text (prefix term)	A prefix term refers to a string that is affixed to the front of a word to produce a derivative word or an inflected form. For a single prefix term, any word starting with the specified term will be part of the result set. For example, the term "auto*" matches "automatic", "automobile", and so forth. For a phrase, each word within the phrase is considered to be a prefix term. For example, the term "auto tran*" matches "automatic transmission" and "automobile transducer", but it does not match "automatic motor transmission". For more information, see Performing Prefix Searches (Prefix Term), later in this topic.	CONTAINS andCONTAINSTABLE
Inflectional forms of a specific word (generation term—inflectional)	The inflectional forms are the different tenses and conjugations of a verb or the singular and plural forms of a noun. For example, search for the inflectional form of the word "drive". If various rows in the table include the words "drive", "drives", "drove", "driving", and "driven", all would be in the result set because each of these can be inflectionally generated from the word drive. For more information, see Searching for the Inflectional Form of a Specific Word (Generation Term), later in this topic.	FREETEXT andFREETEXTTABLElook for inflectional terms of all specified words by default. CONTAINS andCONTAINSTABLEsupport an optional INFLECTIONAL argument.
Synonymous forms of a specific word (generation term—thesaurus)	A thesaurus defines user-specified synonyms for terms. For example, if an entry, "{car, automobile, truck, van}", is added to a thesaurus, you can search for the thesaurus form of the word "car". All rows in the table queried that include the words "automobile", "truck", "van", or "car", appear in the result set because each of these words belong to the synonym expansion set containing the word "car". For information about the structure of thesaurus files, see Configure and Manage Thesaurus Files for Full-Text Search.	FREETEXT andFREETEXTTABLEuse the thesaurus by default. CONTAINS andCONTAINSTABLEsupport an optional THESAURUS argument.
A word or phrase close to another word or phrase (proximity term)	A proximity term indicates words or phrases that are near to each other., You can also specify the maximum number of non-search terms that separate the first and last search terms. In addition, you can search for words or phrases in any order, or in the order in which you specify them. For example, you want to find the rows in which the word "ice" is near the word "hockey" or in which the phrase "ice skating" is near the phrase "ice hockey". For more information, see Search for Words Close to Another Word with NEAR.	CONTAINS andCONTAINSTABLE
Words or phrases using weighted values (weighted term)	A weighting value that indicates the degree of importance for each word and phrase within a set of words and phrases. A weight value of 0.0 is the lowest, and a weight value of 1.0 is the highest. For example, in a query searching for multiple terms, you can assign each search word a weight value indicating its importance relative to the other words in the search condition. The results for this type of query return the most relevant rows first, according to the relative weight you have assigned to search words. The result sets contain documents or rows containing any of the specified terms (or content between them); however, some results will be considered more relevant than others because of the variation in the weighted values associated with different searched terms. For more information, see Searching for Words or Phrases Using Weighted Values (Weighted Term), later in this topic.	CONTAINSTABLE

In This Topic

Searching for Specific Word or Phrase (Simple Term)

You can use CONTAINS, CONTAINSTABLE, FREETEXT, or FREETEXTTABLE to search a table for a specific phrase. For example, if you want to search the ProductReview table in the AdventureWorks2012 database to find all comments about a product with the phrase "learning curve", you could use the CONTAINS predicate as follows:

USE AdventureWorks2012

GO

SELECT Comments

FROM Production.ProductReview

WHERE CONTAINS(Comments, '"learning curve"')

GO

The search condition, in this case "learning curve", can be quite complex and can be composed of one or more terms

In This Topic

Performing Prefix Searches (Prefix Term)

You can use CONTAINS or CONTAINSTABLE to search for words or phrases with a specified prefix. All entries in the column that contain text beginning with the specified prefix are returned. For example, to search for all rows that contain the prefix top-, as in topple, topping, andtop. The query looks like this:

USE AdventureWorks2012

GO

SELECT Description, ProductDescriptionID

FROM Production.ProductDescription

WHERE CONTAINS (Description, '"top*"' )

GO

All text that matches the text specified before the asterisk (*) is returned. If the text and asterisk are not delimited by double quotation marks, as in CONTAINS (DESCRIPTION, 'top*'), full-text search does not consider the asterisk to be a wildcard..

When the prefix term is a phrase, each token making up the phrase is considered a separate prefix term. All rows that have words beginning with the prefix terms will be returned. For example, the prefix term "light bread*" will find rows with text of "light breaded," "lightly breaded," or "light bread," but it will not return "lightly toasted bread".

In This Topic

Searching for Inflectional Forms of a Specific Word (Generation Term)

You can use CONTAINS, CONTAINSTABLE, FREETEXT, or FREETEXTTABLE to search for all the different tenses and conjugations of a verb or both the singular and plural forms of a noun (an inflectional search) or for synonymous forms of a specific word (a thesaurus search).

The following example searches for any form of "foot" ("foot", "feet", and so on) in the Comments column of the ProductReview table in theAdventureWorks database.

USE AdventureWorks2012

GO

SELECT Comments, ReviewerName

FROM Production.ProductReview

WHERE CONTAINS (Comments, 'FORMSOF(INFLECTIONAL, "foot")')

GO

Note

Full-text search uses stemmers, which allow you to search for the different tenses and conjugations of a verb, or both the singular and plural forms of a noun. For more information about stemmers, see Configure and Manage Word Breakers and Stemmers for Search.

In This Topic

Searching for Words or Phrases Using Weighted Values (Weighted Term)

You can use CONTAINSTABLE to search for words or phrases and specify a weighting value. Weight, measured as a number from 0.0 through 1.0, indicates the importance of each word and phrase within a set of words and phrases. A weight of 0.0 is the lowest, and a weight of 1.0 is the highest.

The following example shows a query that searches for all customer addresses, using weights, in which any text beginning with the string "Bay" has either "Street" or "View". The results give a higher rank to those rows that contain more of the words specified.

USE AdventureWorks2012

GO

SELECT AddressLine1, KEY_TBL.RANK 

FROM Person.Address AS Address INNER JOIN

CONTAINSTABLE(Person.Address, AddressLine1, 'ISABOUT ("Bay*", 

         Street WEIGHT(0.9), 

         View WEIGHT(0.1)

         ) ' ) AS KEY_TBL

ON Address.AddressID = KEY_TBL.[KEY]

ORDER BY KEY_TBL.RANK DESC

GO

The code sample below shows some sample comments.

Code Sample:

SimpleSelects/Demos/Comments.sql

1 2 3 4 5 6 7

-- Single-line comment /*     Multi-line comment used in:          -SQL Server          -Oracle          -MySQL */

Whitespace and Semi-colons

Whitespace is ignored in SQL statements. Multiple statements are separated with semi-colons. The two statements in the sample below are equally valid.

Code Sample:

SimpleSelects/Demos/WhiteSpace.sql

1 2 3 4

SELECT * FROM Employees; SELECT * FROM Employees;

Case Sensitivity

SQL is not case sensitive. It is common practice to write reserved words in all capital letters. User-defined names, such as table names and column names may or may not be case sensitive depending on the operating system used.

SELECTing All Columns in All Rows

The following syntax is used to retrieve all columns in all rows of a table.

Syntax

1 2 3 4 5 6 7

SELECT table.* FROM table;     -- OR SELECT * FROM table;

Code Sample:

SimpleSelects/Demos/SelectAll.sql

1 2 3

--Retrieve all columns in the Region table SELECT * FROM Region;

The above SELECT statement will return the following results:

As you can see, the Region table has only two columns, RegionID and RegionDescription, and four rows.

Exploring the Tables

Duration: 10 to 20 minutes.

In this exercise, you will explore all the data in the Northwind database by selecting all the rows of each of the tables.

1.     Using your SQL editor, execute the necessary SQL queries to select all columns of all rows from the tables below.

2.     The number of records that should be returned is indicated in parentheses next to the table name.

A.    Categories (8)

B.    Customers (91)

C.    Employees (9)

D.    Orders (830)

E.    Products (77)

F.    Shippers (3)

G.    Suppliers (29)

Solution:

SimpleSelects/Solutions/SelectAll.sql

SELECTing Specific Columns

The following syntax is used to retrieve specific columns in all rows of a table.

Syntax

1 2 3 4 5 6 7

SELECT table_name.column_name, table_name.column_name FROM table;     -- OR SELECT column, column FROM table;

Code Sample:

SimpleSelects/Demos/SelectCols.sql

1 2 3 4 5

/* Select the FirstName and LastName columns from the Employees table. */ SELECT FirstName, LastName FROM Employees;

The above SELECT statement will return the following results:

SELECTing Specific Columns

Duration: 5 to 15 minutes.

In this exercise, you will practice selecting specific columns from tables in the Northwind database.

1.     Select CategoryName and Description from the Categories table.

2.     Select ContactName, CompanyName, ContactTitle and Phone from the Customers table.

3.     Select EmployeeID, Title, FirstName, LastName, and Region from the Employees table.

4.     Select RegionID and RegionDescription from the Region table.

5.     Select CompanyName, Fax, Phone and HomePage from the Suppliers table.

Solution:

SimpleSelects/Solutions/SelectCols.sql

Sorting Records

The ORDER BY clause of the SELECT statement is used to sort records.

Sorting By a Single Column

To sort by a single column, simply name that column in the ORDER BY clause.

Syntax

1 2 3

SELECT column, column FROM table ORDER BY column;

Note that columns in the ORDER BY clause do not have to appear in the SELECT clause.

Code Sample:

SimpleSelects/Demos/OrderBy1.sql

1 2 3 4 5 6 7 8

/*     Select the FirstName and LastName columns from the Employees table.     Sort by LastName. */ SELECT FirstName, LastName FROM Employees ORDER BY LastName;

The above SELECT statement will return the following results:

Sorting By Multiple Columns

To sort by multiple columns, comma-delimit the column names in the ORDER BY clause.

Syntax

1 2 3

SELECT column, column FROM table ORDER BY column, column;

Code Sample:

SimpleSelects/Demos/OrderBy2.sql

1 2 3 4 5 6 7 8

/* Select the Title, FirstName and LastName columns from the Employees table. Sort first by Title and then by LastName. */ SELECT Title, FirstName, LastName FROM Employees ORDER BY Title, LastName;

The above SELECT statement will return the following results:

Sorting By Column Position

It is also possible to sort tables by the position of a column in the SELECT list. To do so, specify the column numbers in the ORDER BY clause.

Syntax

1 2 3

SELECT column, column FROM table ORDER BY column_position, column_position;

Code Sample:

SimpleSelects/Demos/OrderBy3.sql

1 2 3 4 5 6 7 8

/* Select the Title, FirstName and LastName columns from the Employees table. Sort first by Title (position 1) and then by LastName (position 3). */ SELECT Title, FirstName, LastName FROM Employees ORDER BY 1,3;

The above SELECT statement will return the same results as the previous query:

Ascending and Descending Sorts

By default, when an ORDER BY clause is used, records are sorted in ascending order. This can be explicitly specified with the ASC keyword. To sort records in descending order, use the DESC keyword.

Syntax

1 2 3

SELECT column, column FROM table ORDER BY column_position DESC, column_position ASC;

Code Sample:

SimpleSelects/Demos/OrderBy4.sql

1 2 3 4 5 6 7 8 9

/*     Select the Title, FirstName and LastName columns from the Employees table.     Sort first by Title in ascending order and then by LastName     in descending order. */ SELECT Title, FirstName, LastName FROM Employees ORDER BY Title ASC, LastName DESC;

The above SELECT statement will return the following results:

Sorting Results

Duration: 5 to 15 minutes.

In this exercise, you will practice sorting results in SELECT statements.

1.     Select CategoryName and Description from the Categories table sorted by CategoryName.

2.     Select ContactName, CompanyName, ContactTitle, and Phone from the Customers table sorted by Phone.

3.     Create a report showing employees' first and last names and hire dates sorted from newest to oldest employee.

4.     Create a report showing Northwind's orders sorted by Freight from most expensive to cheapest. Show OrderID,OrderDate, ShippedDate, CustomerID, and Freight.

5.     Select CompanyName, Fax, Phone, HomePage and Country from the Suppliers table sorted by Country in descending order and then by CompanyName in ascending order.

6.     Create a list of employees showing title, first name, and last name. Sort by Title in ascending order and then byLastName in descending order.

Solution:

SimpleSelects/Solutions/Sorting.sql

The WHERE Clause and Operator Symbols

The WHERE clause is used to retrieve specific rows from tables. The WHERE clause can contain one or more conditions that specify which rows should be returned.

Syntax

1 2 3

SELECT column, column FROM table WHERE conditions;

The following table shows the symbolic operators used in WHERE conditions.

SQL Symbol Operators
Operator	Description
=	Equals
<>	Not Equal
>	Greater Than
<	Less Than
>=	Greater Than or Equal To
<=	Less Than or Equal To

Note that non-numeric values (e.g, dates and strings) in the WHERE clause must be enclosed in single quotes. Examples are shown below.

Checking for Equality

Code Sample:

SimpleSelects/Demos/Where-Equal.sql

1 2 3 4 5 6 7 8

/* Create a report showing the title and the first and last name of all sales representatives. */ SELECT Title, FirstName, LastName FROM Employees WHERE Title = 'Sales Representative';

The above SELECT statement will return the following results:

Checking for Inequality

Code Sample:

SimpleSelects/Demos/Where-NotEqual.sql

1 2 3 4 5 6 7 8

/* Create a report showing the first and last name of all employees excluding sales representatives. */ SELECT FirstName, LastName FROM Employees WHERE Title <> 'Sales Representative';

The above SELECT statement will return the following results:

Checking for Greater or Less Than

The less than (<) and greater than (>) signs are used to compare numbers, dates, and strings.

Code Sample:

SimpleSelects/Demos/Where-GreaterThanOrEqual.sql

1 2 3 4 5 6 7 8

/* Create a report showing the first and last name of all employees whose last names start with a letter in the last half of the alphabet. */ SELECT FirstName, LastName FROM Employees WHERE LastName >= 'N';

The above SELECT statement will return the following results:

Checking for NULL

When a field in a row has no value, it is said to be NULL. This is not the same as having an empty string. Rather, it means that the field contains no value at all. When checking to see if a field is NULL, you cannot use the equals sign (=); rather, use the IS NULL expression.

Code Sample:

SimpleSelects/Demos/Where-Null.sql

1 2 3 4 5 6 7 8

/* Create a report showing the first and last names of all employees whose region is unspecified. */ SELECT FirstName, LastName FROM Employees WHERE Region IS NULL;

The above SELECT statement will return the following results:

Code Sample:

SimpleSelects/Demos/Where-NotNull.sql

1 2 3 4 5 6 7 8

/* Create a report showing the first and last names of all employees who have a region specified. */ SELECT FirstName, LastName FROM Employees WHERE Region IS NOT NULL;

The above SELECT statement will return the following results:

WHERE and ORDER BY

When using WHERE and ORDER BY together, the WHERE clause must come before the ORDER BY clause.

Code Sample:

SimpleSelects/Demos/Where-OrderBy.sql

1 2 3 4 5 6 7 8 9 10

/* Create a report showing the first and last name of all employees whose last names start with a letter in the last half of the alphabet. Sort by LastName in descending order. */ SELECT FirstName, LastName FROM Employees WHERE LastName >= 'N' ORDER BY LastName DESC;

The above SELECT statement will return the following results:

Using the WHERE clause to check for equality or inequality

Duration: 5 to 15 minutes.

In this exercise, you will practice using the WHERE clause to check for equality and inequality.

1.     Create a report showing all the company names and contact names of Northwind's customers in Buenos Aires.

2.     Create a report showing the product name, unit price and quantity per unit of all products that are out of stock.

3.     Create a report showing the order date, shipped date, customer id, and freight of all orders placed on May 19, 1997.

·         Oracle users will have to use following date format: 'dd-mmm-yyyy' (e.g, '19-May-1997').

·         MySQL users will have to use following date format: 'yyyy-mm-dd' (e.g, '1997-05-19').

4.     Create a report showing the first name, last name, and country of all employees not in the United States.

Solution:

SimpleSelects/Solutions/EqualityAndInequality.sql

Using the WHERE clause to check for greater or less than

Duration: 5 to 15 minutes.

In this exercise, you will practice using the WHERE clause to check for values greater than or less than a specified value.

1.     Create a report that shows the employee id, order id, customer id, required date, and shipped date of all orders that were shipped later than they were required.

2.     Create a report that shows the city, company name, and contact name of all customers who are in cities that begin with "A" or "B."

3.     Create a report that shows all orders that have a freight cost of more than $500.00.

4.     Create a report that shows the product name, units in stock, units on order, and reorder level of all products that are up for reorder.

Solution:

SimpleSelects/Solutions/GreaterThanLessThan.sql

Checking for NULL

Duration: 5 to 15 minutes.

In this exercise, you will practice selecting records with fields that have NULL values.

1.     Create a report that shows the company name, contact name and fax number of all customers that have a fax number.

2.     Create a report that shows the first and last name of all employees who do not report to anybody.

Solution:

SimpleSelects/Solutions/Null.sql

Using WHERE and ORDER BY Together

Duration: 5 to 15 minutes.

In this exercise, you will practice writing SELECT statements that use both WHERE and ORDER BY.

1.     Create a report that shows the company name, contact name and fax number of all customers that have a fax number. Sort by company name.

2.     Create a report that shows the city, company name, and contact name of all customers who are in cities that begin with "A" or "B." Sort by contact name in descending order.

Solution:

SimpleSelects/Solutions/Where-OrderBy.sql

The WHERE Clause and Operator Words

The following table shows the word operators used in WHERE conditions.

SQL Word Operators
Operator	Description
BETWEEN	Returns values in an inclusive range
IN	Returns values in a specified subset
LIKE	Returns values that match a simple pattern
NOT	Negates an operation

The BETWEEN Operator

The BETWEEN operator is used to check if field values are within a specified inclusive range.

Code Sample:

SimpleSelects/Demos/Where-Between.sql

1 2 3 4 5 6 7 8 9 10 11 12 13 14

/* Create a report showing the first and last name of all employees whose last names start with a letter between "J" and "M". */ SELECT FirstName, LastName FROM Employees WHERE LastName BETWEEN 'J' AND 'M'; -- The above SELECT statement is the same as the one below. SELECT FirstName, LastName FROM Employees WHERE LastName >= 'J' AND LastName <= 'M';

The above SELECT statements will both return the following results:

Note that a person with the last name "M" would be included in this report, but a person's whose last name starts with "M" would not.

The IN Operator

The IN operator is used to check if field values are included in a specified comma-delimited list.

Code Sample:

SimpleSelects/Demos/Where-In.sql

1 2 3 4 5 6 7 8 9 10 11 12 13 14

/* Create a report showing the title of courtesy and the first and last name of all employees whose title of courtesy is "Mrs." or "Ms.". */ SELECT TitleOfCourtesy, FirstName, LastName FROM Employees WHERE TitleOfCourtesy IN ('Ms.','Mrs.'); -- The above SELECT statement is the same as the one below SELECT TitleOfCourtesy, FirstName, LastName FROM Employees WHERE TitleOfCourtesy = 'Ms.' OR TitleOfCourtesy = 'Mrs.';

The above SELECT statements will both return the following results:

The LIKE Operator

The LIKE operator is used to check if field values match a specified pattern.

The Percent Sign (%)

The percent sign (%) is used to match any zero or more characters.

Code Sample:

SimpleSelects/Demos/Where-Like1.sql

1 2 3 4 5 6 7 8

/* Create a report showing the title of courtesy and the first and last name of all employees whose title of courtesy begins with "M". */ SELECT TitleOfCourtesy, FirstName, LastName FROM Employees WHERE TitleOfCourtesy LIKE 'M%';

The above SELECT statement will return the following results:

The Underscore (_)

The underscore (_) is used to match any single character.

Code Sample:

SimpleSelects/Demos/Where-Like2.sql

1 2 3 4 5 6 7 8 9

/* Create a report showing the title of courtesy and the first and last name of all employees whose title of courtesy begins with "M" and is followed by any character and a period (.). */ SELECT TitleOfCourtesy, FirstName, LastName FROM Employees WHERE TitleOfCourtesy LIKE 'M_.';

The above SELECT statement will return the following results:

Wildcards and Performance

Using wildcards can slow down performance, especially if they are used at the beginning of a pattern. You should use them sparingly.

The NOT Operator

The NOT operator is used to negate an operation.

Code Sample:

SimpleSelects/Demos/Where-Not.sql

1 2 3 4 5 6 7 8

/* Create a report showing the title of courtesy and the first and last name of all employees whose title of courtesy is not "Ms." or "Mrs.". */ SELECT TitleOfCourtesy, FirstName, LastName FROM Employees WHERE NOT TitleOfCourtesy IN ('Ms.','Mrs.');

The above SELECT statement will return the following results:

More SELECTs with WHERE

Duration: 5 to 15 minutes.

In this exercise, you will practice writing SELECT statements that use WHERE with word operators.

1.     Create a report that shows the first and last names and birth date of all employees born in the 1950s.

2.     Create a report that shows the product name and supplier id for all products supplied by Exotic Liquids, Grandma Kelly's Homestead, and Tokyo Traders. Hint: you will need to first do a separate SELECT on the Suppliers table to find the supplier ids of these three companies.

3.     Create a report that shows the shipping postal code, order id, and order date for all orders with a ship postal code beginning with "02389".

4.     Create a report that shows the contact name and title and the company name for all customers whose contact title does not contain the word "Sales".

Solution:

SimpleSelects/Solutions/WordOperators.sql

Checking Multiple Conditions

AND

AND can be used in a WHERE clause to find records that match more than one condition.

Code Sample:

SimpleSelects/Demos/Where-And.sql

1 2 3 4 5 6 7 8 9

/* Create a report showing the first and last name of all sales representatives whose title of courtesy is "Mr.". */ SELECT FirstName, LastName FROM Employees WHERE Title = 'Sales Representative'     AND TitleOfCourtesy = 'Mr.';

The above SELECT statement will return the following results:

OR