Lesson 1: Retrieving Data | Microsoft SQL Server 7 Data Warehousing Technical Support Training Kit

In this lesson, you will learn how to retrieve data from tables using the SELECT statement. You will also learn how to specify columns and rows and then format the results using the SELECT statement.

After this lesson, you will be able to:

Write SELECT statements to retrieve data from tables

Write queries that sort values and eliminate duplicates in result sets and format result sets to improve readability

Estimated lesson time: 120 minutes

Using the SELECT Statement

Use the SELECT statement to retrieve data. The partial syntax for using the SELECT statement is as follows:

 SELECT [ALL | DISTINCT] <select_list> FROM [[database_name.][owner_name].]{ table_name } [AS] alias WHERE <search_condition>

Use the following parts of the SELECT statement to specify the columns and rows that you want returned from a table:

The select list specifies the columns to be returned.

The FROM clause specifies the table from which columns and rows are returned.

The WHERE clause specifies the rows to return. The search condition in the WHERE clause restricts the rows that are retrieved by using conditional and logical operators.

Specifying Columns

You can retrieve particular columns from a table by listing them in the select list. The select list contains the columns, expressions, or keywords to select. The select list can include one or more of the following items:

 <select_list> :: =   { *  | { table_name | table_alias }.*   | [{ table_name | table_alias }.]    { column_name | expression | IDENTITYCOL | ROWGUIDCOL }    [ [AS] column_alias ]  | column_alias = expression   } [,Ön]

When you specify columns to retrieve, consider the following facts and guidelines:

The select list retrieves and displays the columns in the specified order.

Separate the column names with commas. Do not place a comma after the last column name.

Use an asterisk (*) in the select list to retrieve all columns from a table.

Example

This example retrieves the EmployeeID, LastName, FirstName, and Title columns of all employees from the Employees table in the Northwind database.

USE Northwind SELECT EmployeeID, LastName, FirstName, Title FROM Employees

Result

 EmployeeID  LastName             FirstName  Title                            ----------- -------------------- ---------- ------------------------------ 1           Davolio              Nancy      Sales Representative  2           Fuller               Andrew     Vice President, Sales  3           Leverling            Janet      Sales Representative  4           Peacock              Margaret   Sales Representative  5           Buchanan             Steven     Sales Manager  6           Suyama               Michael    Sales Representative  7           King                 Robert     Sales Representative  8           Callahan             Laura      Inside Sales Coordinator  9           Dodsworth            Anne       Sales Representative  (9 row(s) affected)

Exercise 1: To Select Specific Columns

In this exercise, you will write and execute a SELECT statement that retrieves the title and title_no columns from the title table in the library database.

To write a SELECT statement that retrieves specific columns

Open SQL Server Query Analyzer, and log in to the (local) server with Microsoft Windows NT authentication.

Choose library in the DB list to make library the current database.

Write and execute a SELECT statement that retrieves the title and title_no columns from the title table.

SELECT title, title_no   FROM title

Save the SELECT statement in the ANSI file format with a .SQL extension.

Save the result set with a .RPT extension. (Hint: To save results, click in the results pane before you select Save on the File menu.)

Your results should look similar to the following partial result set.

 title                                                 title_no      --------------------------------------------------------------   Last of the Mohicans                                  1  The Village Watch-Tower                               2  Self Help; Conduct & Perseverance                     3    Julius Caesar's Commentaries on the Gallic War        49  Frankenstein                                          50  (50 row(s) affected)

Using the FROM Clause to Specify a Source Table

The FROM clause of the SELECT statement specifies the name of the table from which rows will be retrieved. If the table is in the current database and the table is owned by the dbo user ID, you can use just the table name; otherwise, specify the database and owner names.

Examples

In this example, the current database is set to Northwind, and then the SELECT statement selects three columns from the Employees table in the current database. The Employees table is owned by db_owner, so the owner does not need to be specified as part of the table name.

 USE Northwind  SELECT EmployeeID, LastName, FirstName   FROM Employees

In this example, the SELECT statement selects three columns from the Employees table, explicitly specifying the database and owner names. The current database does not need to be Northwind when this SELECT statement is executed.

 SELECT EmployeeID, LastName, FirstName   FROM Northwind.dbo.Employees

In this example, the SELECT statement selects three columns from the Employees table, explicitly specifying the Northwind database. Because the Employees table is owned by dbo, the owner does not need to be specified as part of the table name, but the periods must still be used to specify the owner name placeholder in the table name. The current database does not need to be Northwind when this SELECT statement is executed.

 SELECT EmployeeID, LastName, FirstName   FROM Northwind..Employees

Using a Table Alias

You can specify an alias for a table in the FROM clause of the SELECT statement. An alias allows you to refer to the table by its alias in other parts of the SELECT statement. A table alias is not necessary when you are selecting rows from a single table, but you will make extensive use of table aliases when you use more than one table. You will learn about selecting rows from multiple tables in Appendix D, "Querying Multiple Tables."

Example

In this example, the Employees table in the Northwind database is given the alias emp. The alias is used in the select list and in the WHERE clause of the SELECT statement. You will learn about the WHERE clause in the next section.

 SELECT emp.EmployeeID, emp.FirstName, emp.LastName   FROM Northwind..Employees AS emp  WHERE emp.EmployeeID = 1

Using the WHERE Clause to Specify Rows

The WHERE clause of the SELECT statement restricts the number of rows that are returned.

Using the WHERE clause, you can also retrieve specific rows based on a given search condition. Only rows that match the search condition in the WHERE clause are returned by the SELECT statement. The search condition in the WHERE clause can contain an unlimited list of predicates (expressions that return a value of TRUE, FALSE, or UNKNOWN). The following syntax shows how multiple predicates are combined with the AND or OR logical operators:

    <search_condition> ::=     [NOT] <predicate> [{AND | OR} [NOT] <predicate>] [, ...n]

The following syntax shows the expressions that can be used in the predicates:

 <predicate> ::=  {  expression { = | <> | != | > | >= | !> | < | <= | !< } expression  | string_expression [NOT] LIKE string_expression           [ESCAPE 'escape_character']  | expression [NOT] BETWEEN expression AND expression | expression [NOT] IN (expression [, n]) | expression IS [NOT] NULL }

When you specify rows with the WHERE clause, consider the following facts and guidelines:

Place single quotation marks around all char, nchar, varchar, nvarchar, text, datetime, and smalldatetime data.

Use positive rather than negative predicates, because negative predicates slow the speed of data retrieval.

Whenever possible, use a WHERE clause to limit the number of rows that are returned, rather than returning all rows from a table. Returning fewer rows of data improves performance, especially when you use the SELECT * statement.

Example

This example retrieves the EmployeeID, LastName, FirstName, and Title columns from the Employees table for the employee with an employee ID of 5.

 USE Northwind  SELECT EmployeeID, LastName, FirstName, Title  FROM Employees  WHERE EmployeeID = 5

Result

 EmployeeID  LastName             FirstName  Title                            ----------- -------------------- ---------- ------------------------------   5           Buchanan             Steven     Sales Manager (1 row(s) affected)

The WHERE Clause Search Condition

When you specify the search condition in the WHERE clause, use any of the following types of conditional operators:

Description	Conditional Operators
Comparison operators	=, <>, !=, >, >=, !>, <, <=, and !<
String comparisons	LIKE and NOT LIKE
Range comparisons	BETWEEN and NOT BETWEEN
List comparisons	IN and NOT IN
Unknown values	IS NULL and IS NOT NULL

Using Comparison Operators

Use comparison operators to compare the values in a table to a specified value or expression. Comparison operators compare columns or variables of compatible data types. Avoid the use of negative comparisons. They slow data retrieval because all rows in a table are evaluated.

The comparison operators are listed in the following table:

Operator	Description
=	Equal to
< >	Not equal to
!=	Not equal to (not SQL-92 standard)
>	Greater than
<	Less than
>=	Greater than or equal to
<=	Less than or equal to
!>	Not greater than (not SQL-92 standard)
!<	Not less than (not SQL-92 standard)

Example 1

This example retrieves the last name and city of employees who reside in the United States from the Employees table.

 USE Northwind  SELECT LastName, City  FROM Employees  WHERE Country = 'USA'

Result

 LastName             City              -------------------- ---------------   Davolio              Seattle  Fuller               Tacoma  Leverling            Kirkland Peacock              Redmond  Callahan             Seattle  (5 row(s) affected)

Example 2

This example retrieves from the Orders table the values in the OrderID and CustomerID columns with order dates that are older than 7/15/96.

 USE Northwind  SELECT OrderID, CustomerID  FROM Orders  WHERE OrderDate < '7/15/96'

Result

 OrderID     CustomerID   ----------- ----------   10248       VINET  10249       TOMSP  10250       HANAR  10251       VICTE  10252       SUPRD  10253       HANAR  10254       CHOPS  10255       RICSU  (8 row(s) affected)

Exercise 2: To Select Rows by Using a Comparison Operator

In this exercise, you will write and execute a SELECT statement that retrieves data from specific rows by using a WHERE clause with a comparison operator.

To write a SELECT statement that uses a WHERE clause

Write and execute a SELECT statement that retrieves the title column of title number 10 from the title table in the library database.

You can execute the sp_help system stored procedure for the title table to find the correct column names.

 USE library  SELECT title   FROM title   WHERE title_no = 10

Your result should look similar to the following result set:

 title                                                             ---------------------------------------------------------------   The Night-Born  (1 row(s) affected)

Using String Comparisons

You can use the LIKE operator in combination with wildcard characters to select rows by comparing character values from the rows to a pattern string specified with the LIKE operator. When you use the LIKE operator, consider the following facts:

If you want to do an exact string comparison, you will get much better performance using a comparison operator rather than the LIKE operator; for example, use Country = USA rather than Country LIKE USA .

All characters in the pattern string are significant, including leading and trailing blank spaces.

LIKE can be used only with data of the char, nchar, varchar, nvarchar, or date-time data types.

Types of Wildcard Characters

Use the four wildcard characters listed in the following table to form your character string search criteria:

Wildcard	Description
%	Any string of zero or more characters
_ (underscore)	Any single character
[]	Any single character within the specified range (for example, [s-w]) or set (for example, [aeiou])
[^]	Any single character not within the specified range (for example, [^s-w]) or set (for example, [^aeiou])

Examples of the Use of Wildcard Characters

The following table lists examples of the wildcards used with the LIKE operator to find matching names:

Comparison	Returns
LIKE BR%	Every name beginning with the letters BR
LIKE %een	Every name ending with the letters een
LIKE %en%	Every name containing the letters en
LIKE _en	Every three-letter name ending in the letters en
LIKE [CK]%	Every name beginning with the letter C or K
LIKE [S-V]ing	Every four-letter name ending in the letters ing and beginning with any single letter from S to V
LIKE M[^c]%	Every name beginning with the letter M that does not have the letter c as the second letter

Example

This example retrieves companies from the Customers table that have the word restaurant in their company names.

 USE Northwind  SELECT CompanyName  FROM Customers  WHERE CompanyName LIKE '%Restaurant%'

Result

 CompanyName                                ----------------------------------------   GROSELLA-Restaurante  Lonesome Pine Restaurant  Tortuga Restaurante  (3 row(s) affected

Case Sensitivity for String Comparisons

When you use the LIKE operator or the comparison operators to compare string values, the comparison is case sensitive if the SQL Server is using a case- sensitive sort order. The comparison is not case sensitive if the SQL Server is using a case-insensitive sort order (the default).

The following table gives examples of various string comparisons and shows whether the comparison will return a match when the SQL Server is using a case-sensitive sort order or when the SQL Server is using a case-insensitive sort order.

Value	Comparison	Case-Sensitive Sort Order	Case-Insensitive Sort Order
Smith	= Smith	Match	Match
Smith	= smith	No match	Match
Smith	LIKE Sm%	Match	Match
Smith	LIKE SM%	No match	Match

Exercise 3: To Select Rows by Using a Character String Comparison

In this exercise, you will write and execute a SELECT statement that retrieves data from specific rows that contain a character string similar to another character string.

To write a SELECT statement that uses character comparisons

Write and execute a SELECT statement that retrieves the title number and title from the title table in the library database for all rows that contain the character string "Adventures" in the title. Use the LIKE operator in your query.

 USE library  SELECT title_no, title   FROM title   WHERE title LIKE ('%Adventures%')

Your result should look similar to the following result set:

 title_no    title        ----------- --------------------------------------------------   26          The Adventures of Robin Hood  44          Adventures of Huckleberry Finn (2 row(s) affected)

Retrieving a Range of Values

Use the BETWEEN operator in the WHERE clause to retrieve rows that are within a specified range of values. When you use the BETWEEN operator, consider the following facts and guidelines:

SQL Server includes the end values in the result set.

Use the BETWEEN operator, rather than a predicate that includes the AND operator, with two comparison operators (> = x AND < = y). However, to search for an exclusive range in which the returned rows do not contain the end values, use a predicate that includes the AND operator with two comparison operators (> x AND < y).

Use the NOT BETWEEN operator to retrieve rows outside of the specified range. Be aware, however, that negative conditions slow data retrieval.

Be careful when using the BETWEEN operator with date and time values, because midnight is the end point for the ending date value. No data with a time (other than midnight) on the ending date will be returned.

For example, if you want to retrieve a list of invoices entered between 1/4/98 and 1/5/98, only invoices from 1/4/98 are returned if times are stored in the invoice dates. If you want to retrieve all invoices from 1/4/98 and 1/5/98, you must use the AND operator with two comparison operators (InvoiceDate >= 1/4/98 AND InvoiceDate <= 1/5/98 ). This search condition includes the entire 48-hour period from midnight on 1/4/98 to just before midnight on 1/6/98.

Example 1

This example retrieves the product name and unit price of all products with a unit price between $10.00 and $14.00. Notice that the result set includes the end values.

 USE Northwind  SELECT ProductName, UnitPrice  FROM Products  WHERE UnitPrice BETWEEN 10 AND 14

Result

 ProductName                               UnitPrice               ---------------------------------------- ---------------------   Aniseed Syrup                                10.0000  Sir Rodney's Scones                          10.0000  NuNuCa Nuß-Nougat-Creme                      14.0000  Gorgonzola Telino                            12.5000  Sasquatch Ale                                14.0000  Singaporean Hokkien Fried Mee                14.0000  Spegesild                                    12.0000  Chocolade                                    12.7500  Escargots de Bourgogne                       13.2500  Laughing Lumberjack Lager                    14.0000  Scottish Longbreads                          12.5000  Longlife Tofu                                10.0000  Original Frankfurter grüne Soße              13.0000  (13 row(s) affected)

Example 2

This example retrieves the product name and unit price of all products with a unit price between $10.00 and $14.00. Notice that the result set excludes the end values.

 USE Northwind  SELECT ProductName, UnitPrice  FROM Products  WHERE (UnitPrice > 10) AND (UnitPrice < 14)

Result

 ProductName                               UnitPrice               ---------------------------------------- ---------------------   Gorgonzola Telino                            12.5000  Spegesild                                    12.0000  Chocolade                                    12.7500  Escargots de Bourgogne                       13.2500  Scottish Longbreads                          12.5000  Original Frankfurter grüne Soße              13.0000  (6 row(s) affected)

Using a List of Values as Search Criteria

Use the IN operator in the WHERE clause to retrieve rows that match a specified list of values. When you use the IN operator, consider the following guidelines:

Use either the IN operator or a series of predicates that are connected with an OR operator; SQL Server resolves them in the same way, returning identical result sets.

Do not include the NULL value in the list. A NULL value in the list evaluates to the comparison = NULL. This may return unpredictable result sets.

Use the NOT IN operator to retrieve rows that are not in your list of values. Remember, however, that negative conditions slow data retrieval.

Example 1

This example returns the companies from the Suppliers table that are located in Japan or Italy.

 USE Northwind  SELECT CompanyName, Country  FROM Suppliers  WHERE Country IN ('Japan', 'Italy')

Result

 CompanyName                                 Country           ---------------------------------------- ---------------   Tokyo Traders                               Japan  Mayumi's                                    Japan  Formaggi Fortini s.r.l.                     Italy  Pasta Buttini s.r.l.                        Italy  (4 row(s) affected)

Example 2

This example also returns the companies from the Suppliers table that are located in Japan or Italy. Notice that rather than using the IN operator, two predicates that use the equal comparison operator are joined by the OR operator. The result set is identical to the result set in Example 1.

 USE Northwind  SELECT CompanyName, Country  FROM Suppliers  WHERE Country = 'Japan' OR Country = 'Italy'

Result

 CompanyName                                 Country           ---------------------------------------- ---------------   Tokyo Traders                               Japan  Mayumi's                                    Japan  Formaggi Fortini s.r.l.                     Italy  Pasta Buttini s.r.l.                        Italy  (4 row(s) affected)

Exercise 4: To Select Rows by Using a Range

In this exercise, you will write and execute a SELECT statement that retrieves data from specific rows by using a WHERE clause with a range.

To write a SELECT statement that uses a range in the WHERE clause

Write and execute a SELECT statement that retrieves the member numbers and assessed fines from the loanhist table in the library database for all members who have had fines between $8.00 and $9.00, inclusive.

You can execute the sp_help system-stored procedure for the loanhist table to find the correct column names.

 USE library  SELECT member_no, fine_assessed   FROM loanhist   WHERE (fine_assessed BETWEEN $8.00 AND $9.00)

Your result should look similar to the following partial result set.

 member_no fine_assessed           --------- ---------------------   7399      9.0000  7399      9.0000  7399      9.0000    969       9.0000  969       9.0000  (286 row(s) affected)

Exercise 5: To Select Rows by Using a List of Values

In this exercise, you will write and execute a SELECT statement that retrieves data from specific rows by using a WHERE clause that contains a list of values.

To write a SELECT statement that uses a list in the WHERE clause

Write and execute a SELECT statement that retrieves the title number and author from the title table in the library database for all books authored by Charles Dickens or Jane Austen. Use the IN operator as part of the SELECT statement.

 USE library  SELECT author, title_no   FROM title   WHERE author IN ('Charles Dickens','Jane Austen')

Your result should look similar to the following result set.

 author                             title_no      ------------------------------- -----------   Jane Austen                        27  Charles Dickens                    30  Charles Dickens                    31  Jane Austen                        41  Jane Austen                        43  (5 row(s) affected)

Retrieving Unknown Values

A column has a null value if no value is entered during data entry and no default values are defined for that column. A null value is not the same as zero (a numerical value) or a blank (a character value). For example, if a column value recorded the destination of your vacation last year, possible values and their meanings are as follows:

Value	Meaning
Cape Town, South Africa	You went to Cape Town, South Africa, on your vacation last year.
''	You did not have a vacation last year.
NULL	The destination of your vacation last year is unknown.

Use the IS NULL operator to retrieve rows for which information is missing from a specified column. When you retrieve rows that contain unknown values, consider the following facts and guidelines:

Null values fail all comparisons because they do not evaluate equally with one another.

You define whether columns allow null values in the CREATE TABLE statement.

Use the IS NOT NULL operator to retrieve rows that have known values in the specified columns.

Example

This example retrieves a list of companies from the Suppliers table for which the fax column contains a null value.

 USE Northwind  SELECT CompanyName, Fax  FROM Suppliers  WHERE Fax IS NULL

Result

 CompanyName                                         Fax                        ---------------------------------------- ------------------------   Exotic Liquids                                      NULL  New Orleans Cajun Delights                          NULL  Tokyo Traders                                       NULL  Cooperativa de Quesos 'Las Cabras'                  NULL  Mayumi's                                            NULL  Specialty Biscuits, Ltd.                            NULL  Refrescos Americanas LTDA                           NULL  Heli Süßwaren GmbH & Co. KG                         NULL  Plutzer Lebensmittelgroßmärkte AG                   NULL  Norske Meierier                                     NULL  Bigfoot Breweries                                   NULL  Svensk Sjöföda AB                                   NULL  Leka Trading                                        NULL  Karkki Oy                                           NULL  Ma Maison                                           NULL  Escargots Nouveaux                                  NULL  (16 row(s) affected)

Using Logical Operators

Use the logical operators AND and OR to combine a series of predicates and to refine query processing. Use the logical NOT operator to negate the value of a predicate. The results of a query may vary depending on the grouping and ordering of the predicates.

When you use logical operators, consider the following guidelines:

Use the AND operator to retrieve rows that meet all of the search criteria.

Use the OR operator to retrieve rows that meet any of the search criteria.

Use the NOT operator to negate the expression that follows the operator.

Using Parentheses

Use parentheses when you have two or more expressions as the search criteria. Using parentheses allows you to

Group expressions

Change the order of evaluation

Make expressions more readable

Order of Predicates

When you use more than one logical operator in a statement, consider the following facts:

SQL Server evaluates the NOT operator first, followed by the AND operator and then the OR operator.

The precedence order is from left to right if all operators in an expression are of the same level.

Example 1

The following example retrieves all products with product names that begin with the letter T or have a product identification number of 60 and that have a price greater than $16.00.

 USE Northwind  SELECT ProductID, ProductName, SupplierID, UnitPrice  FROM Products  WHERE (ProductName LIKE 'T%' OR ProductID = 60) AND     (UnitPrice > 16.00)

Result

 ProductID   ProductName                 SupplierID    UnitPrice               ----------- -------------------------- ----------- ----------------- 14          Tofu                                 6    23.2500  29          Thüringer Rostbratwurst             12   123.7900  60          Camembert Pierrot                   28    34.0000  62          Tarte au sucre                      29    49.3000  (4 row(s) affected)

Example 2

The following example retrieves products with product names that begin with the letter T or that have a product identification number of 60 and a price greater than $16.00. Compare the query in Example 1 to that in Example 2. Notice that because the expressions are grouped differently, the queries are processed differently and return different result sets.

 USE Northwind  SELECT ProductID, ProductName, SupplierID, UnitPrice  FROM Products  WHERE (ProductName LIKE 'T%') OR      (ProductID = 60 AND UnitPrice > 16.00)

Result

 ProductID   ProductName                   SupplierID    UnitPrice               ----------- ---------------------------- ----------- -----------------   60          Camembert Pierrot                     28     34.0000  54          Tourtière                             25      7.4500  62          Tarte au sucre                        29     49.3000  23          Tunnbröd                               9      9.0000  19          Teatime Chocolate Biscuits             8      9.2000  14          Tofu                                   6     23.2500  29          Thüringer Rostbratwurst               12    123.7900  (7 row(s) affected)

Exercise 6: To Select Rows That Contain Null Values by Using a WHERE Clause That Contains Logical Operators

In this exercise, you will write and execute a SELECT statement that retrieves data from specific rows by using a WHERE clause that uses logical operators to combine predicates.

To write a SELECT statement that uses logical operators in the WHERE clause

Write and execute a SELECT statement that retrieves the member number, assessed fine, and fine that has been paid for loans in the loanhist table in the library database for members who have unpaid fines. Retrieve rows that have a fine entered in the fine_assessed column and that have either null values in the fine_paid column or non-null values in the fine_paid column that are smaller than the value in the fine_assessed column.

 USE library  SELECT member_no, fine_assessed, fine_paid   FROM loanhist   WHERE (fine_assessed IS NOT NULL) AND               (fine_paid IS NULL OR fine_paid < fine_assessed)

Your result should look similar to the following result set.

 member_no fine_assessed         fine_paid               --------- --------------------- ---------------------   7399      9.0000                NULL  7399      9.0000                NULL  7399      9.0000                NULL    6201      1.0000                NULL  6201      1.0000                NULL  (650 row(s) affected)

Formatting Result Sets

You can improve the readability of a result set by sorting the order in which the result set is listed, eliminating any duplicates, changing column names to column aliases, or using literals to replace result set values. These formatting options do not change the data, only the presentation of it.

Sorting Data

Use the ORDER BY clause to sort rows in the result set in ascending or descending order. When you use the ORDER BY clause, consider the following facts and guidelines:

When SQL Server is installed, a sort order is specified. The sort order is a set of rules that determines how SQL Server sorts and compares character data. Various sort orders are available, but only one sort order can be used on the SQL Server. Changing the sort order for a server is a major operation. Execute the sp_helpsort system-stored procedure to determine the sort order that is in use on your SQL Server.

SQL Server does not guarantee an order in the result set unless the order is specified with an ORDER BY clause.

SQL Server sorts in ascending order by default.

Columns that are included in the ORDER BY clause do not have to appear in the select list.

The total size of the columns specified in the ORDER BY clause cannot exceed 8060 bytes.

You can sort by column names, computed values, or expressions.

In the ORDER BY clause, you can refer to columns by their names, aliases, or positions in the select list. The columns are evaluated in the same way and return the same result set.

To sort the values of a column in descending order, specify the DESC keyword after the column reference in the column list of the ORDER BY clause. You can specify the ASC keyword to sort the values of a column in ascending order, but it is not necessary, because ASC is the default.

Do not use an ORDER BY clause on text or image columns.

Example 1

This example retrieves the product ID, product name, category ID, and unit price of each product from the Products table. By default, the result set is ordered by category ID in ascending order, and within each category the rows are ordered by unit price in descending order.

 USE Northwind  SELECT ProductID, ProductName, CategoryID, UnitPrice  FROM Products  ORDER BY CategoryID, UnitPrice DESC

Result

 ProductID  ProductName                       CategoryID  UnitPrice         ---------- --------------------------------- ---------- ------------------   38          Côte de Blaye                         1      263.5000  43          Ipoh Coffee                           1       46.0000  2           Chang                                 1       19.0000  1           Chai                                  1       18.0000  46          Spegesild                             8       12.0000  41          Jack's New England Clam Chowder       8        9.6500  45          Rogede sild                           8        9.5000  13          Konbu                                 8        6.0000  (77 row(s) affected)

Example 2

This example is similar to Example 1. The only difference is that the numbers that follow the ORDER BY clause indicate the position of columns in the select list. SQL Server resolves both queries in the same way, returning the same result set.

 USE Northwind  SELECT ProductID, ProductName, CategoryID, UnitPrice  FROM Products   ORDER BY 3, 4 DESC

Result

 ProductID  ProductName                        CategoryID      UnitPrice         ---------- --------------------------------- ---------- ------------------   38          Côte de Blaye                             1     263.5000  43          Ipoh Coffee                               1      46.0000  2           Chang                                     1      19.0000  1           Chai                                      1      18.0000    46          Spegesild                                 8      12.0000  41          Jack's New England Clam Chowder           8       9.6500  45          Rogede sild                               8       9.5000  13          Konbu                                     8       6.0000  (77 row(s) affected)

Exercise 7: To Sort Data in a Query

In this exercise, you will write and execute a query that retrieves the titles from the title table in the library database and lists them in alphabetical order.

To write a SELECT statement that uses the ORDER BY clause to sort the query results

Write and execute a query that retrieves a sorted list of all titles from the title table.

 USE library  SELECT title   FROM title   ORDER BY title

Your result should look similar to the following partial result set.

 title                                                             ---------------------------------------------------------------   A Tale of Two Cities  Adventures of Huckleberry Finn  Ballads of a Bohemian Candide    War and Peace  Wayfarers  (50 row(s) affected)

Eliminating Duplicates

If you require a list of unique values, use the DISTINCT clause to eliminate duplicate rows in the result set. When you use the DISTINCT clause, consider the following facts:

All rows that meet the search condition specified in the WHERE clause of the SELECT statement are returned in the result set, unless you have specified the DISTINCT clause.

The combination of values in the select list determines distinctiveness.

Rows that contain any unique combination of values are retrieved and returned in the result set.

The DISTINCT clause sorts the result set in random order, unless you have included an ORDER BY clause.

If you specify a DISTINCT clause, the ORDER BY clause may include only the columns listed in the select list of the SELECT statement.

Example 1

This example retrieves all rows from the Suppliers table but displays each country name only once.

 USE Northwind  SELECT DISTINCT Country  FROM Suppliers  ORDER BY Country

Result

 Country           ---------------   Australia  Brazil  Canada  Denmark  Finland  France  Germany  Italy  Japan  Netherlands  Norway  Singapore  Spain  Sweden  UK  USA  (16 row(s) affected)

Example 2

This example does not specify the DISTINCT clause. All rows from the Suppliers table are retrieved and listed in ascending order. Notice that all instances of each country are displayed.

 USE Northwind  SELECT Country  FROM Suppliers  ORDER BY Country

Result

 Country           ---------------   Australia  Australia  Brazil  Canada  Canada    USA USA  (29 row(s) affected)

Exercises 8: To Eliminate Duplicate Rows from the Result Set

In this exercise, you will write and execute a query on the adult table in the library database that returns only unique combinations of cities and states in your result set.

To write a SELECT statement that uses the DISTINCT clause

Write and execute a query that retrieves all of the unique pairs of cities and states from the adult table. You should receive only one row in the result set for each city and state pair.

 USE library  SELECT DISTINCT city, state   FROM adult

Your result should look similar to the following partial result set.

 city            state   --------------- -----   Salt Lake City     UT  Atlanta            GA  Tallahassee        FL    Austin             TX  Charleston         WV  (23 row(s) affected)

Changing Column Names

Create more readable column names by using the AS keyword to replace default column names with aliases in the select list, as shown in the following syntax.

{ column_name | expression } AS column_alias

When you change column names, consider the following facts and guidelines:

By default, columns that are based on expressions do not have column names. Use column aliases to give names to columns that are based on expressions.

Place single quotation marks around column aliases that contain blank spaces or that do not conform to SQL Server object-naming conventions.

You can include up to 128 characters in a column alias.

You can use column aliases in the ORDER BY clause of the SELECT statement, but you cannot use column aliases in the WHERE, GROUP BY, or HAVING clauses of the SELECT statement.

Example

This example retrieves a list of employees from the Employees table. Column aliases are specified for the calculated Name column and the EmployeeID column. Notice that the Employee ID: alias is enclosed in single quotation marks because it contains a blank space. No alias is used for the Title column.

 USE Northwind  SELECT FirstName + ' ' + LastName AS Name,                EmployeeID AS 'Employee ID:',                Title  FROM Employees

Result

 Name                         Employee ID: Title                            ---------------------------- ------------ ------------------------------   Nancy Davolio                1            Sales Representative  Andrew Fuller                2            Vice President, Sales  Janet Leverling              3            Sales Representative  Margaret Peacock             4            Sales Representative  Steven Buchanan              5            Sales Manager  Michael Suyama               6            Sales Representative  Robert King                  7            Sales Representative  Laura Callahan               8            Inside Sales Coordinator  Anne Dodsworth               9            Sales Representative    (9 row(s) affected)

Exercise 9: To Compute Data, Return Computed Values, and Use a Column Alias

In this exercise, you will write and execute a query that returns data about loans for which fines are owed. You will add a calculated column called double fine that shows double the outstanding fine for each loan.

To write a SELECT statement that computes a value and uses a column alias

Write and execute a query that retrieves the member_no, isbn, and fine_assessed columns from the loanhist table for all rows that have a non-null value in the fine_assessed column.

Create a computed column that contains the value of the fine_assessed column multiplied by two.

Use the column alias double fine for the computed column. Enclose the column alias within single quotation marks because it does not conform to the SQL Server object-naming conventions.

 USE library  SELECT member_no, isbn, fine_assessed,                 (fine_assessed * 2) AS 'double fine'  FROM loanhist   WHERE (fine_assessed IS NOT NULL)

Your result should look similar to the following result set.

 member_no isbn        fine_assessed         double fine             --------- ----------- -------------------- --------------------   7399      101         9.0000                18.0000  7399      101         9.0000                18.0000  7399      101         9.0000                18.0000    6201      850         1.0000                 2.0000  6201      850         1.0000                 2.0000  (1066 row(s) affected)

Exercise 10: To Format the Result Set of a Column by Using String Functions

In this exercise, you will write and execute a query that lists all members in the member table in the library database who have a last name Anderson. You will create a calculated column that displays e-mail names that consist of each appropriate member s first name, middle initial, and first two letters of the last name.

To write a SELECT statement that uses string functions to format the results

Write and execute a query that generates a single column that contains the firstname, middleinitial, and lastname columns from the member table for all members with the last name Anderson.

Use the column alias email_name.

Modify the query to return a list of e-mail names with the member s first name, middle initial, and first two letters of the last name in lowercase characters. Use the SUBSTRING function to retrieve part of a string column. Use the LOWER function to return the result in lowercase characters. Use the concatenation (+) operator to concatenate the character strings.

 USE library  SELECT       LOWER(firstname + middleinitial + SUBSTRING(lastname, 1, 2))      AS email_name  FROM member  WHERE lastname = 'Anderson'

Your result should look similar to the following partial result set.

 email_name           ------------------   amyaan  angelaaan  brianaan  clairaan  thomman  williamman  (390 row(s) affected)

Using Literals

Literals are letters, numerals, or symbols that are used as literal values in a result set. You can include literals in the select list to make result sets more readable.

Example

This example retrieves a list of employees from the Employees table. Notice that the text "ID number": precedes the EmployeeID column in the result set.

 USE Northwind  SELECT FirstName, LastName,                 'ID number:', EmployeeID  FROM Employees

Result

 FirstName  LastName                         EmployeeID    ---------- -------------------- ---------- -----------   Nancy      Davolio                         ID number: 1  Andrew     Fuller                          ID number: 2  Janet      Leverling                       ID number: 3  Margaret   Peacock                         ID number: 4  Steven     Buchanan                        ID number: 5  Michael    Suyama                          ID number: 6  Robert     King                            ID number: 7  Laura      Callahan                        ID number: 8  Anne       Dodsworth                       ID number: 9  (9 row(s) affected)

Exercise 11: To Format the Result Set of a Column by Using Literals

In this exercise, you will format the result set of a query for readability by using the CAST function and string literals.

To write a SELECT statement that uses literals in the results

Write and execute a query that retrieves the title and title_no columns from the title table in the library database. Your result set should be a single column with the following format:

 The title is: Poems, title number 7

This query returns a single column based on an expression that concatenates four elements:

The title is: string constant

title column

,title number string constant

title_no column

Use the CAST function to format the title_no column and the concatenation (+) operator to concatenate the character strings. Give the calculated column an alias of Title String.

 USE library  SELECT 'The title is: ' + title + ', title number ' +                 CAST(title_no AS char(6)) AS 'Title String'  FROM title

Your result should look similar to the following partial result set.

 Title String                                                                                         --------------------------------------------------------------- The title is: Last of the Mohicans, title number 1 The title is: The Village Watch-Tower, title number 2 The title is: Self Help; Conduct & Perseverance, title number 3 The title is: Julius Caesar's Commentaries on the Gallic War, title number 49 The title is: Frankenstein, title number 50  (50 row(s) affected)

Lesson Summary

The SELECT statement is used to retrieve data from SQL Server tables. The SELECT statement has a large number of clauses that are used to determine which data is retrieved and how the data is formatted in the result set. The FROM clause specifies the tables from which data is retrieved. The WHERE clause is used to specify a search condition that determines which rows are retrieved from the tables specified in the FROM clause.