Teach Yourself Microsoft SQL Server 6.5 in 21 Days

Previous chapterNext chapterContents


Day 10

Data Retrieval


On Day 9, "Importing and Exporting Data," you learned how to transfer data to and from your SQL Server. Now that you have information in your database, it is time to learn how to extract just the data you want. You are going to begin the day with a simple SELECT statement and learn how to grab specific columns. You then will expand on this simple SELECT with data-manipulation techniques and data-conversion techniques. You will finish the day with a discussion on selecting specific rows and eliminating duplicate information. Day 10, "Data Retrieval," and Day 11, "Subqueries and Data Correlation," go hand in hand. After you build a good understanding of how the SELECT statement works, you will examine more complex data-retrieval techniques.

Simple SELECT Statements

You can create queries to retrieve information from your database by using the SQL Query Tool in the Enterprise Manager, ISQL/w, ISQL, MSQuery, other tools, and third-party utilities. In this section, you will look at using the SELECT statement to retrieve rows and columns from tables in your database.

The SELECT statement consists of three basic components: SELECT, FROM, and WHERE. The basic syntax follows:

SELECT column_list
FROM table_list
WHERE search_criteria

The SELECT portion of the statement specifies the columns you want to retrieve. The FROM clause specifies the table(s) from which the columns are to be retrieved. The WHERE clause can be used to limit the amount of data returned by your query.

The complete syntax for the SELECT statement follows:

SELECT [ALL | DISTINCT] column_list
[INTO [new_table_name]]
[FROM {table_name | view_name}[(optimizer_hints)]
    [[, {table_name2 | view_name2}[(optimizer_hints)]
    [..., {table_name16 | view_name16}[(optimizer_hints)]]]
[WHERE clause]
[GROUP BY clause]
[HAVING clause]
[ORDER BY clause]
[COMPUTE clause]
[FOR BROWSE]


NOTE: These optimizer hints are beyond the scope of this course. You can gather more information about these hints from Microsoft SQL Server 6.5 Unleashed or Microsoft SQL Server 6.5 DBA Survival Guide--both published by Macmillan Computer Publishing.

SELECT * FROM table_name is the most basic of all queries. When you use an asterisk (*) for column_list, it retrieves all columns from the table.

In the pubs database, for example, you can run this query to select all columns and rows from the authors table:

SELECT *
FROM employee
emp_id    fname    minit lname    job_id job_lvl pub_id hire_date   
------    -----    ----- -----    ------ ------- ------ ---------
PMA42628M Paolo    M     Accorti  13      35      877   Aug 27 1992 12:00AM    
PSA89086M Pedro    S     Afonso   14      89     1389   Dec 24 1990 12:00AM  
VPA30890F Victoria P     Ashworth  6     140      877   Sep 13 1990 12:00AM       
H-B39728F Helen          Bennett                       
L-B31947F Lesley         Brown                         
F-C16315M Francisco      Chang                     
GHT50241M Gary     H     Thomas   9      170      736    Aug  9 1998 12:00AM
DBT39435M Daniel   B     Tonini   11     70       877    Jan  1 1990 12:00AM
(43 row(s) affected)

To select specific columns from a table, you must designate the columns in column_list. Each column must be separated by a comma (,). You should not place a comma after the final column.

SELECT column_name [, column_name...]
FROM table_name
SELECT fname, lname, emp_id
FROM employee
fname    		lname     		emp_id
-----              ------          ------
Paolo    		Accorti    		PMA42628M
Pedro    		Afonso     		PSA89086M
Victoria  	Ashworth    	     VPA30890F
Helen     	Bennett     		H-B39728F
Lesley     	Brown     		L-B31947F
Francisco  	Chang     		F-C16315M
. . .                     				. . .
. . .        		. . .
. . .        		. . .
Gary     		Thomas     		GHT50241M
Daniel     		Tonini     		DBT39435M
(43 row(s) affected)

ANALYSIS: This query selects the first name, last name, and employee ID for each employee in the employee table.

When you execute a query with SELECT *, the column order is the same as the column order specified in the CREATE TABLE statement. When you select columns from a table, the column_list order does not have to be the same as the table column order. You can rearrange the column order in your query output by rearranging the columns in column_list.

You can take the previous query and rearrange the column order. The same information is returned, but it is displayed in a different column order.

SELECT emp_id, lname, fname
FROM employee
emp_id        lname     		fname
------        ------       ------
PMA42628M     Accorti    	Paolo
PSA89086M     Afonso     	Pedro
VPA30890F     Ashworth     Victoria
H-B39728F     Bennett     	Helen
L-B31947F     Brown     		Lesley
F-C16315M     Chang     		Francisco
. . .                     			. . .
. . .         . . .
. . .         . . .
GHT50241M     Thomas        Gary
DBT39435M     Tonini        Daniel
(43 row(s) affected)

Changing Column Headings

When the results of a query are displayed, the column headings are the names used in column_list. Instead of using column headings, such as lname and fname, you can produce more readable column headings, such as FirstName and LastName, by aliasing the column headings. You can alias column headings using SQL Server 6.5 syntax or ANSI SQL syntax.


NOTE: SQL Server 6.5 supports both the ANSI '92 SQL syntax standard as well as its own Microsoft SQL Server 6.5 flavor.

You can alias columns with SQL Server syntax in two ways:

SELECT column_heading = column_name
FROM table_name

or

SELECT column_name  [AS] column_heading
FROM table_name

You can rewrite your query by using the following SQL Server 6.5 syntax:

SELECT EmployeeID = emp_id, LastName = lname, FirstName = fname
FROM employee

You also can rewrite your query by using ANSI SQL syntax:

SELECT emp_id AS EmployeeID,  lname AS LastName,  fname AS FirstName
FROM employee

Both queries have the same result:

EmployeeID    LastName    FirstName
----------    --------    ---------
PMA42628M     Accorti     Paolo
PSA89086M     Afonso      Pedro
VPA30890F     Ashworth    Victoria
H-B39728F     Bennett     Helen
L-B31947F     Brown       Lesley
F-C16315M     Chang       Francisco
. . .                     . . .
. . .         . . .
. . .         . . .
GHT50241M     Thomas      Gary
DBT39435M     Tonini      Daniel
(43 row(s) affected)


NOTE: If the alias you used has spaces or is a SQL Server keyword, you must enclose the alias in single quotation marks.
Here is an example using spaces:

SELECT lname AS `Last Name',  fname AS `First Name'
FROM employee

Here is an example using a SQL keyword:

SELECT `count' = Count(*)
FROM employee



Using Literals

You also can use literals to make output more readable. A literal is a string surrounded by single quotation marks included in column_list and displayed as another column in the query result. It looks a lot like creating a label next to your column of information.

The syntax for including a literal value follows:

SELECT `literal'  [, `literal'...]

***SELECT fname, lname, `Employee ID:', emp_id
FROM employee
fname    lname        emp_id
-----    ------       -------
Paolo    Accorti      Employee ID:    PMA42628M
Pedro    Afonso       Employee ID:    PSA89086M
Victoria Ashworth     Employee ID:    VPA30890F
Helen    Bennett      Employee ID:    H-B39728F
. . .                 . . .
. . .                 . . .
. . .                 . . .
Gary     Thomas       Employee ID:    GHT50241M
Daniel   Tonini       Employee ID:    DBT39435M
(43 row(s) affected)

ANALYSIS: This query returns first name, last name, a column containing the literal string Employee ID:, and the employee ID for all employees in the employee table.

Manipulating Data

You can manipulate data in your query results to produce new columns that display computed values, new string values, converted dates, and more. Your query results can be manipulated using arithmetic operators, mathematical functions, string functions, datetime functions, and system functions. You also can use the CONVERT function to convert from one datatype to another for easier data manipulation.

Arithmetic Operators

You can use arithmetic operators on the following datatypes: int, smallint, tinyint, numeric, decimal, float, real, money, and smallmoney. Table 10.1 shows the arithmetic operators and the datatypes you can use with them.

Table 10.1. Datatypes and arithmetic operations.

Addition Subtraction Division Mult. Modulo
Datatype + - / * %
decimal yes yes yes yes no
float yes yes yes yes no
int yes yes yes yes yes
money yes yes yes yes no
numeric yes yes yes yes no
real yes yes yes yes no
smallint yes yes yes yes yes
smallmoney yes yes yes yes no
tinyint yes yes yes yes yes


NOTE: Because modulo is the integer remainder from the division of two integers, it can be used only with the int datatypes.

Operator Precedence

With arithmetic operations, two levels of precedence exist: datatype precedence and operator precedence.

Datatype precedence is used when arithmetic operations are performed on different datatypes. When you use different datatypes, the smaller datatype is converted to the higher datatype. If you multiply a smallint by an int, for example, the result is an int. The only exception to this rule is when using the money datatype, in which case the result always is of the datatype money.

Operator precedence is used when multiple operators are used. Operators follow the normal rules for operator precedence in which modulo always is evaluated first, followed by multiplication and division, followed by addition and subtraction, as read from right to left.

As with normal arithmetic operations, you can change the order of precedence by placing expressions in parentheses. The innermost expression (the deepest-nested expression) is evaluated first. You also can use parentheses to make the arithmetic operation more readable.

For example,

5 + 5 * 5 = 30 (The multiplication is done first.)

but

(5 + 5) * 5 = 50 (The nested expression is evaluated first.)

Mathematical Functions

Mathematical functions enable you to perform commonly needed operations on mathematical data.

You can return mathematical data by using the following syntax:

SELECT function_name(parameters)

Table 10.2 lists the mathematical functions, their parameters, and their results. These examples include such operations as finding the absolute value, finding trigonometric functions, deriving square roots, and raising values to an exponential power. Table 10.3 shows some additional examples.

Table 10.2. Mathematical functions.

Function Results
ABS(numeric_expr) Absolute value
ACOS, ASIN, Angle in radians whose cosine, sine, or tangent is a
ATAN, ATN2(float_expr) floating-point value
COS, SIN, COT, TAN(float_expr) Cosine, sine, or tangent of the angle (in radians)
CEILING(numeric_expr) Smallest integer greater than or equal to the specified value
DEGREES(numeric_expr) Conversion from radians to degrees
EXP(float_expr) Exponential value of specified value
FLOOR(numeric_expr) Largest integer less than or equal to the specified value
LOG(float_expr) Natural log
LOG10(float_expr) Base-10 log
PI() Constant 3.141592653589793
POWER(numeric_expr,y) Value of numeric_expr to the power of y
RADIANS(numeric_expr) Conversion from degrees to radians
RAND([seed]) Random float number between 0 and 1
ROUND(numeric_expr,len) Numeric_exp rounded to the specified length; length in an integer value
SIGN(numeric_expr) Positive, negative, or zero
SQRT(float_expr) Square root of the specified value

Table 10.3. Mathematical functions and results.

Statement Result
SELECT SQRT(9) 3.0
SELECT ROUND(1234.56, 0) 1235
SELECT ROUND(1234.56, 1) 1234.60
SELECT ROUND($1234.56, 1) 1,234.60
SELECT POWER (2,8) 256.0
SELECT FLOOR(1332.39) 1332
SELECT ABS(-365) 365


TIP: When using mathematical functions with monetary datatypes, you always should precede the datatype with a dollar sign ($). Otherwise, the value is treated as a numeric with a scale of 4.

String Functions

When dealing with character information, you can use various string functions to manipulate the data (see Table 10.4). Most string functions manipulate only char and varchar datatypes; therefore, other datatypes first must be converted. You can return character data by using the following syntax:

SELECT function_name(parameters)

Table 10.4. String functions.

Function Result
+(expression, expression) Concatenates two or more character strings
ASCII(char_expr) ASCII code value of the leftmost character
CHAR(integer_expr) Character equivalent of an ASCII code value
CHARINDEX(pattern, expression) Returns the starting position of a specified pattern
DIFFERENCE(char_expr1, char_exr2) Compares two strings and evaluates their similarity; returns a value from 0 to 4, 4 being the best match
LOWER(char_expr) Converts to lowercase
LTRIM(char_expr) Returns data without leading blanks
PATINDEX(`%pattern%', expression) Returns the starting position of the first occurrence in expression
REPLICATE(char_expr, integer_expr) Repeats char_expr integer_expr number of times
REVERSE(char_expr) Returns the reverse of char_expr
RIGHT(char_expr, integer_expr) Returns the character string starting integer_expr characters from the right
RTRIM(char_expr) Returns data without trailing blanks
SOUNDEX(char_expr) Returns a four-digit (SOUNDEX) code to evaluate the similarity of two character strings
SPACE(integer_expr) Returns a string of repeated spaces equal to integer_expr
STR(float_expr[,length[,decimal]]) Returns character data converted from numeric data; length is the total length and decimal is the number of spaces to the right of the decimal
STUFF(char_expr1, start, length, char_expr2) Deletes length characters from char_expr1 at start and inserts char_expr2 at start
SUBSTRING(expression, start, length) Returns part of a character or binary string
UPPER(char_expr) Converts to uppercase

Take a look at the following examples.

You can submit the following to return a column called Name, which is a concatenation of last name, first initial, and employee ID:

SELECT lname + `, ` + SUBSTRING(fname,1,1) + `.'
AS Name, emp_id as EmployeeID
FROM employee
Name           EmployeeID
-----          -----------
Accorti, P.    PMA42628M
Afonso, P.     PSA89086M
Ashworth, V.   VPA30890F
Bennett, H.    H-B39728F
.    .
.    .
.    .
Sommer, M.     MFS52347M
Thomas, G.     GHT50241M
Tonini, D.     DBT39435M
(43 row(s) affected)

Table 10.5 lists some more examples of string functions.

Table 10.5. More string functions.

Statement Result
SELECT ASCII(`G') 71
SELECT LOWER(`ABCDE') abcde
SELECT PATHINDEX(`%BC%','ABCDE') 2
SELECT RIGHT(`ABCDE',3) CDE
SELECT REVERSE(`ABCDE') EDCBA

Date Functions

You can manipulate datetime values with date functions. Date functions can be used in the column_list, the WHERE clause, or wherever an expression can be used. Use the following syntax for date functions:

SELECT date_function (parameters)

Datetime values passed as parameters must be enclosed in single quotation marks or double quotation marks. Some functions take a parameter known as a datepart. Table 10.6 lists the datepart values and their abbreviations.

Table 10.6. Datepart values.

Datepart Abbreviation Values
day dd 1-31
day of year dy 1-366
hour hh 0-23
millisecond ms 0-999
minute mi 0-59
month mm 1-12
quarter qq 1-4
second ss 0-59
week wk 0-51
weekday dw 1-7
year yy 1753-9999

Table 10.7 lists the date functions, their parameters, and their results. Table 10.8 shows some date function examples.

Table 10.7. Date functions.

Function Results
DATEADD(datepart, number, date) Adds the number of dateparts to the date
DATEDIFF(datepart, date1, date2) Specifies the number of dateparts between two dates
DATENAME(datepart, date) Returns the ASCII value for the specified datepart for the date listed
DATEPART(datepart, date) Returns an integer value for the specified datepart for the date listed
GETDATE() Specifies the current date and time in internal format

Table 10.8. Date function examples.

Function Results
SELECT DATEDIFF(mm, '1/1/97', '12/31/99') 35
SELECT GETDATE() Apr 29, 1997 2:10AM
SELECT DATEADD(mm, 6, `1/1/97') Jul 1, 1997 2:10AM
SELECT DATEADD(mm -5, `10/6/97') May 6, 1997 2:10AM

Now take a look at a more complex query that involves many of the pieces you have learned so far.

SELECT emp_id AS EmployeeID,  lname + `, ` + SUBSTRING(fname,1,1) + `.' AS Name,`Has been employed for `, DATEDIFF(year, hire_date, getdate()), ` years.'
FROM employee
EmployeeID    Name
----------    -----
PMA42628M    Accorti, P.    Has been employed for     5    years.
PSA89086M    Afonso, P.     Has been employed for     7    years.
VPA30890F    Ashworth, V.   Has been employed for     7    years.
H-B39728F    Bennett, H.    Has been employed for     8    years.
.    .    .    .    .
.    .    .    .    .
.    .    .    .    .
MFS52347M    Sommer, M.    Has been employed for     7    years.
GHT50241M    Thomas, G.    Has been employed for     9    years.
DBT39435M    Tonini, D.    Has been employed for     7    years.
(43 row(s) affected)

System Functions

You can use a number of built-in system functions to query the system tables. You can return system data by using the following syntax:

SELECT function_name(parameters)

You can use system functions in the column_list, WHERE clause, and anywhere else an expression can be used.

Table 10.9 lists the system functions, parameters, and results.

Table 10.9. The system functions.

Function Results
COALESCE(expression1, Returns the first non-null
expression2,...expressionN) expression
COL_NAME(table_id, column_id) Returns column name
COL_LENGTH(`table_name',`column_name') Returns column length
DATALENGTH(`expression') Returns actual length of expression of any datatype
DB_ID([`database_name']) Returns database ID
DB_NAME([database_id]) Returns database name
GETANSINULL([`database_name']) Returns default nullability of the database
HOST_ID() Returns host process ID
HOST_NAME() Returns host computer name
IDENT_INCR(`table_name') Returns increment value specified during creation of identity column
IDENT_SEED(`table_name') Returns seed value specified during creation of identity column
INDEX_COL(`table_name', index_id, key_id) Returns indexed column name
ISDATE(variable | column_name) Checks for a valid date format; returns 1 if valid--otherwise, returns 0
ISNULL(expression, value) Returns specified value in place of null
ISNUMERIC(variable | column_name) Checks for a valid numeric format; returns 1 if valid--otherwise, returns 0
NULLIF(expression1, expression2) Returns NULL if expression1 = expression2
OBJECT_ID(`object_name'') Returns database object ID
OBJECT_NAME(object_id') Returns database object name
STATS_DATE(table_id, index_id) Returns the date at which index statistics were updated
SUSER_ID([`server_username']) Returns server user's ID
SUSER_NAME([server_id]) Returns server user's name
USER_ID([`username']) Returns database user's ID
USER_NAME([user_id]) Returns database user's name

This query uses two system functions to return the name of the second column of the employee table.

SELECT COL_NAME(OBJECT_ID(`employee'),2)
fname

(1 row(s) affected)

Data Conversion

Because many functions require data in a certain format or datatype, you might find it necessary to convert from one datatype to another. You use the CONVERT function to modify your datatypes; you can use CONVERT anywhere expressions are allowed. The CONVERT statement has the following syntax:

CONVERT(datatype[(length)], expression [,style])

Table 10.10 lists the CONVERT style parameters associated with their standards and the format of the output.

Table 10.10. Using the CONVERT function.

Style Without Century yy Style With Century yyyy Standard Date Format Output
- 0 or 100 Default mon dd yyyy hh:mi AM (or PM)
1 101 USA mm/dd/yy
2 102 ANSI yy.mm.dd
3 103 British/French dd/mm/yy
4 104 German dd.mm.yy
5 105 Italian dd-mm-yy
6 106 - dd mon yy
7 107 - mon dd, yy
8 108 - hh:mi:ss
- 9 or 109 Default + milliseconds mon dd, yyyy hh:mi:ss:ms AM (or PM)
10 110 USA mm-dd-yy
11 111 Japan yy/mm/dd
12 112 ISO yymmdd
- 13 or 113 Europe default
+ milliseconds
dd mon yyyy hh:mi:ss:ms(24h)
14 114 - hh:mi:ss:ms(24h)

You can submit the following query to convert the current date to a character string of eight and a date style of ANSI:

SELECT CONVERT(CHAR(8),GETDATE(),2)

--------
97.07.06
(1 row(s) affected)
SELECT emp_id AS EmployeeID,  lname + `, ` + SUBSTRING(fname,1,1) + `.'
AS Name, `Has been employed for ` + CONVERT(CHAR(2),
(DATEDIFF(year, hire_date, getdate()))) + ` years.'
FROM employee
EmployeeID   Name
----------   ----
PMA42628M    Accorti, P.    Has been employed for 5  years.
PSA89086M    Afonso, P.     Has been employed for 7  years.
VPA30890F    Ashworth, V.   Has been employed for 7  years.
H-B39728F    Bennett, H.    Has been employed for 8  years.
.    .    .
.    .    .
.    .    .
MFS52347M    Sommer, M.    Has been employed for 7  years.
GHT50241M    Thomas, G.    Has been employed for 9  years.
DBT39435M    Tonini, D.    Has been employed for 7  years.
(43 row(s)affected)

ANALYSIS: This example is built on the query you ran in the section "Date Functions," earlier in this chapter. In this example, you combined the last three columns into one column by using the CONVERT function and string concatenation.

Choosing Rows

You have looked at various ways to retrieve, format, and manipulate the columns in the result set of a query. Now you will learn how to specify which rows to retrieve based on search conditions. You can do this by using the WHERE clause of your SELECT statement. Search conditions include comparison operators, ranges, lists, string matching, unknown values, combinations, and negations of these conditions.

The basic syntax for specifying which rows to retrieve follows:

SELECT column_list
FROM table_list
WHERE search_conditions

Comparison Operators

You can implement the search conditions by using comparison operators (see Table 10.11). You can select rows by comparing column values to a certain expression or value. Expressions can contain constants, column names, functions, or nested subqueries. If you are comparing two different character datatypes (such as char and varchar), or you are comparing date datatypes (such as datetime and smalldatetime), these must be enclosed in single quotation marks. Double quotation marks are acceptable, but single quotation marks maintain ANSI compliance.

The syntax for the WHERE clause using comparison operators follows:

SELECT column_list
FROM table_list
WHERE column_name comparison_operator expression

Table 10.11. Available comparison operators.

Operator Description
= Equal to
> Greater than
< Less than
>= Greater than or equal to
<= Less than or equal to
<> Not equal to (preferred)
!= Not equal to
!> Not greater than
!< Not less than
() Order of precedence

Here's an example:

SELECT emp_id, lname, fname
FROM employee
WHERE pub_id = `0877'
emp_id       lname      fname
-------      ------     ------
PMA42628M    Accorti    Paolo
VPA30890F    Ashworth   Victoria
H-B39728F    Bennett    Helen
.    .    .
.    .    .
.    .    .
M-R38834F    Rance     Martine
DBT39435M    Tonini    Daniel
(10 row(s) affected)

ANALYSIS: This query returns the employee ID, last name, and first name for all employees employed by the publisher with a pub_id of 0877.

Ranges

You can retrieve rows based on a range of values using the BETWEEN keyword. As with the comparison operator, if you are specifying ranges based on character datatypes (such as char and varchar) or date datatypes (such as datetime and smalldatetime), these must be enclosed in single quotation marks.

The syntax for the WHERE clause using comparisons follows:

SELECT column_list
FROM table_list
WHERE column_name [NOT] BETWEEN expression AND expression
SELECT lname, emp_id
FROM employee
WHERE hire_date BETWEEN `10/1/92' AND `12/31/92'
lname      emp_id
-----      -------
Josephs    KFJ64308F
Paolino    MAP77183M
(2 row(s) affected)

This query returns the last name and employee ID for all employees hired between 10/1/92 and 12/31/92.

Lists

You can retrieve rows with values that match those in a list by using the IN keyword. If you are specifying ranges based on character datatypes (such as char and varchar) or date types (such as datetime and smalldatetime), you must enclose these types in single quotation marks.

The syntax for the WHERE clause using comparisons follows:

SELECT column_list
FROM table_list
WHERE [NOT] column_name [NOT] IN (value_list)

If you want to find employees who work for publishers with a pub_id of 0877 or 9999, you submit the following query:

SELECT emp_id, lname, fname
FROM employee
WHERE pub_id IN (`0877', `9999')
emp_id       lname      fname
------       -----      -----
PMA42628M    Accorti    Paolo
VPA30890F    Ashworth   Victoria
H-B39728F    Bennett    Helen
.    .    .
.    .    .
.    .    .
A-R89858F    Roulet    Annette
DBT39435M    Tonini    Daniel
(17 row(s) affected)

You also can retrieve rows not in the list by using the NOT operator. If you want to find all employees who do not work for publishers with a pub_id of 0877 or 9999, you can submit the following query:

SELECT emp_id, lname, fname
FROM employee
WHERE pub_id NOT IN (`0877', `9999')
emp_id    lname                          fname
------    -----                          ------
PSA89086M Afonso                         Pedro
F-C16315M Chang                          Francisco
PTC11962M Cramer                         Philip
A-C71970F Cruz                           Aria
AMD15433F Devon                          Ann
.......
CGS88322F Schmitt                        Carine
MAS70474F Smith                          Margaret
HAS54740M Snyder                         Howard
MFS52347M Sommer                         Martin
GHT50241M Thomas                         Gary
(26 row(s) affected)


TIP: Try using positive search conditions whenever possible. Avoid using NOT, because the query optimizer does not recognize negative search conditions. In other words, SQL Server has to do a lot more work to return your result set when you use NOT. You could rewrite the preceding query by using BETWEEN and AND statements.

Character Strings

You can retrieve rows based on portions of character strings by using the LIKE keyword. LIKE is used with char, varchar, text, datetime, and smalldatetime data. You also can use four wildcard characters in the form of regular expressions.

The syntax for the WHERE clause using the LIKE keyword follows:

SELECT column_list
FROM table_list
WHERE column_name  [NOT] LIKE `string'

The available wildcards follow: % A string of zero or more characters

_ A single character

[] A single character within the specified range [^] A single character not within the specified range When you use the LIKE clause, make sure that you enclose the wildcard and/or characters in single quotation marks.

Take a look at the following examples.

You can submit the following query to return the title_id and title of all books with "computer" anywhere in the title from the title table.

SELECT title_id, title
FROM titles
WHERE  title LIKE `%computer%'
title_id     title
--------     -----
BU1111       Cooking with Computers: Surreptitious Balance Sheets
BU2075       You Can Combat Computer Stress!
BU7832       Straight Talk About Computers
MC3026       The Psychology of Computer Cooking
PS1372       Computer Phobic AND Non-Phobic Individuals: Behavior
             Variations
(5 row(s) affected)

You can submit the following query to return the au_id, au_lname, and au_fname of all authors whose names begin with B or M from the authors table.

SELECT au_id, au_lname, au_fname
FROM authors
WHERE au_lname LIKE `[BM]%'
au_id           au_lname              au_fname
-----           --------              --------
409-56-7008    	    Bennet                 Abraham
648-92-1872     Blotchet-Halls        Reginald
724-80-9391     MacFeather            Stearns
893-72-1158     McBadden              Heather
(4 row(s) affected)

Unknown Values

What is an unknown or null value? A null value is not the same as a blank character string, and it is not the same as a 0 when dealing with numeric data. A null occurs when a value is not assigned to a field. In such cases, a null fails all comparisons to blanks, zeros, and other nulls (when using the greater than (>) or less than (<) comparison operators). So how do you find rows based on null values? You can discriminate between rows in your tables containing null values by using the IS NULL and IS NOT NULL keywords.

The syntax for the WHERE clause using the IS NULL and IS NOT NULL operators follows:

SELECT column_list
FROM table_list
WHERE column_name IS [NOT] NULL

Examine the following examples.

You can submit this query to find all books that have no sales:

SELECT title_id, title
FROM titles
WHERE ytd_sales IS NULL

or

SELECT title_id, title
FROM titles
WHERE ytd_sales = NULL
title_id    title
--------    -----
--------    -----
MC3026    	  The Psychology of Computer Cooking
PC9999       Net Etiquette
(2 row(s) affected)

In contrast to the preceding query, you can use the IS NOT NULL clause to find all books that do have ytd_sales values by submitting the following query.

SELECT title_id, title
FROM titles
WHERE ytd_sales IS NOT NULL

or

SELECT title_id, title
FROM titles
WHERE ytd_sales <> NULL
title_id     title
--------     -----
BU1032       The Busy Executive's Database Guide
BU1111       Cooking with Computers: Surreptitious Balance Sheets
BU2075       You Can Combat Computer Stress!
BU7832       Straight Talk About Computers
.    .
.    .
.    .
TC3218       Onions, Leeks, and Garlic: Cooking Secrets of the Mediter-			  ranean
TC4203       Fifty Years in Buckingham Palace Kitchens
TC7777       Sushi, Anyone?
(16 row(s) affected)

Retrieving Rows Using Multiple Criteria

You have looked at selecting rows based on specific values, ranges, lists, string comparisons, and unknown values. Now, you'll look at retrieving rows using multiple search criteria.

You can combine multiple search criteria using the logical operators AND, OR, and NOT. Using AND and OR operators enables you to join two or more expressions. AND returns results when all conditions are true. OR returns results when any of the conditions are true.

When more than one of the logical operators are used in the WHERE clause, the order of precedence can be significant. NOT is followed by AND and then OR.

Here is the syntax for a WHERE clause using multiple criteria:

SELECT column_list
FROM table_list
WHERE [NOT] expression {AND|OR} [NOT] expression


NOTE: If you are using arithmetic operators joined by logical operators, arithmetic operators are processed first. Of course, you can always change the order of precedence by using parentheses.

Take a look at some examples.

Query 1

Consider the following: You want to retrieve the title_id, title, and price for all books that have a publisher ID of 0877 or computer in the title, and for which the price is NOT NULL.

SELECT title_id, title, price, pub_id
FROM titles
WHERE title LIKE `%computer%' OR pub_id  = `0877' AND price IS NOT NULL
title_id  title    					price    pub_id
--------  -----                                    ------   ------
BU1111    Cooking with Computers: Surreptitious    11.95    1389           Balance Sheets 
BU2075    You Can Combat Computer Stress!    		2.99     736
BU7832    Straight Talk About Computers    		19.99    1389
MC2222    Silicon Valley Gastronomic Treats    		19.99    877
MC3021    The Gourmet Microwave    			2.99     877
MC3026    The Psychology of Computer Cooking    	(null)   877
PS1372    Computer Phobic AND Non-Phobic  	       Individuals: Behavior Variations  		21.59    877
TC3218    Onions, Leeks, and Garlic: Cooking  	       Secrets of the Mediterranean 		20.95    877
TC4203    Fifty Years in Buckingham Palace 	       Kitchens    					11.95    877
TC7777    Sushi, Anyone?    				14.99    877
(10 row(s) affected)

Query 2

Now run the query again and see whether you can get rid of that null value in your price field:

SELECT title_id, title, price, pub_id
FROM titles
WHERE (title LIKE `%computer%' OR pub_id  = `0877') AND price IS NOT NULL
title_id  title    					price    pub_id
--------  -----                                    -----    ------
BU1111    Cooking with Computers: Surreptitious  	       Balance Sheets  				11.95    1389
BU2075    You Can Combat Computer Stress!    		 2.99    736
BU7832    Straight Talk About Computers    		19.99    1389
MC2222    Silicon Valley Gastronomic Treats    	19.99    877
MC3021    The Gourmet Microwave    			 2.99    877
PS1372    Computer Phobic AND Non-Phobic  	       Individuals: Behavior Variations  		21.59    877
TC3218    Onions, Leeks, and Garlic: Cooking  	       Secrets of the Mediterranean  		20.95    877
TC4203     Fifty Years in Buckingham Palace  	        Kitchens    				11.95    877
TC7777     Sushi, Anyone?    				14.99    877
(9 row(s)affected)

ANALYSIS: Note that Query 2 returns the desired results by changing the order of precedence.

Eliminating Duplicate Information

When selecting certain information from a table, you may receive duplicate rows of information. You can eliminate duplicates by using the DISTINCT clause in the SELECT portion of the SELECT statement. If you do not specify the DISTINCT clause, all rows that meet the WHERE clause criteria are returned.

The syntax for the DISTINCT clause follows:

SELECT DISTINCT column_list
FROM table_name
WHERE search_conditions

Distinctness is determined by the combination of all the columns in column_list. Null values are treated as duplicates of each other; therefore, only one NULL is returned.

Table 10.12 shows some SELECT statements with and without the DISTINCT clause. Query 1 lists states in which authors live without listing duplicates. Query 2 lists cities authors live in without listing duplicates. Query 3 lists distinct combinations of cities and states.

Table 10.12. Each query shows distinct results.

Query 1 Query 2 Query 3
SELECT DISTINCT SELECT DISTINCT SELECT DISTINCT
state city city, state
FROM authors FROM authors FROM authors
STATE CITY CITY and State
CA Ann Arbor Ann Arbor, MI
IN Berkeley Berkeley, CA
KS Corvallis Corvallis, OR
MD Covelo Covelo, CA
MI Gary Gary, IN
OR Lawrence Lawrence, KS
TN Menlo Park Menlo Park, CA
UT Nashville Nashville, TN
Oakland Oakland, CA
Palo Alto Palo Alto, CA
Rockville Rockville, MD
Salt Lake City Salt Lake City, UT
San Francisco San Francisco, CA
San Jose San Jose, CA
Vacaville Vacaville, CA
Walnut Creek Walnut Creek, CA

If you just want to list the different cities where authors live, why not just use Query 2? It gives you the same cities as Query 3. To answer that question, suppose that two authors in your database live in Portland. If you run Query 2, it returns Portland as one of the distinct values. However, one author lives in Portland, Oregon and the other lives in Portland, Maine. Obviously, these are two distinct locations, so submitting Query 3 using the DISTINCT combination of city and state returns both Portland, OR and Portland, ME.

Notice that the results in all three queries are sorted. This is not by chance. The values are sorted first so that the first value can be compared to the next value to make it easier to remove duplicate values. Also note that if you have multiple columns in the DISTINCT clause, the results are sorted in the order of your column_list.

Take a closer look at Query 3:

SELECT DISTINCT city, state
FROM authors

If the results returned Portland, OR and Portland, ME, it would look like this:

city                     state
----                     ------
Portland                 ME
Portland                 OR
(2 row(s) affected)

Sorting Data Using the ORDER BY Clause

You can sort your query results by using the ORDER BY clause in your SELECT statement.

The basic syntax for using the ORDER BY clause follows:

SELECT column_list
FROM table_list
[ORDER BY column_name | column_list_number [ASC|DESC]]

You can have up to 16 columns in your ORDER BY list. You also can specify column names or use the ordinal number of the columns in column_list.

Both these queries return the same ordered result sets:

Query 1

SELECT title_id, au_id
FROM titleauthor
ORDER BY title_id, au_id

Query 2

SELECT title_id, au_id
FROM titleauthor
ORDER BY 1, 2

You can use column names and ordinal numbers together in the ORDER BY clause. You also can specify whether you want the results sorted in ascending (ASC) or descending (DESC) order. If you do not specify ASC or DESC, ASC is used.

If you are sorting results based on a column that has null values, and ASC order is used, the rows containing nulls are displayed first.

When using the ORDER BY clause, the sort order of your SQL Server can make a difference in your result sets. The default sort order for SQL Server is dictionary order and not case sensitive. If your SQL Server is using a sort order that is case sensitive, it can affect the overall query response time and the ordering of your result sets. This is because a capital A is not considered the same as a lowercase a when you are using a case-sensitive sort order.

To find out what your server's current sort order is, you can execute the system-stored procedure sp_helpsort.


NOTE: You cannot use the ORDER BY clause on columns that are of text or image datatypes. This is true for text datatypes, because they are stored in a different location on your database and can range from 0 Gig to 2.15 Gig. Attempting to sort on a field this size is not allowed in SQL Server. Image datatypes also are stored in their own separate 2KB data pages and are not sortable.

Summary

In this chapter, you built a foundation for data retrieval using the SELECT statement. You then learned how to change your column headings and add string literals to your output.

You expanded on your understanding of the SELECT statement using arithmetic, mathematical, string, and date functions. You then learned about system functions to further manipulate your data.

Many times, the data you want to work with is not expressed in the format and datatype you must use. You learned how to use data conversion to alter your data from one datatype to another.

You then continued expanding on the SELECT statement with a discussion and examples of choosing different rows of information by applying comparison operators, ranges of values, lists, and character strings.

You learned how to eliminate rows with null values. You also learned how to select DISTINCT rows of information.

You finished the chapter by learning how to sort your data using the ORDER BY clause.

Q&A

Q It looks like my SELECT statements can get pretty long. Is there a limit to how big they can be?

A
Yes, queries are limited to 64KB.

Q Where can I find SELECT statements?


A
SELECT statements often are found in front-end applications such as Visual Basic and PowerBuilder. You also can find SELECT statements embedded in stored procedures, triggers, events, Alerts, and many other locations in your SQL Server.

Q Can I nest SELECT statements?


A
Yes. On Day 11, "Subqueries and Data Correlation," you will learn about creating subqueries that take advantage of nested SELECT statements.

Workshop

This Workshop consists of a short quiz to test your understanding of the chapter and some exercises for you to put into practice what you have learned.

Quiz

1. What does this query return?
SELECT * FROM authors
WHERE au_lname LIKE `M%'
2. What does this query return?
SELECT emp_id AS EmployeeID,
    lname AS LastName,
    fname AS FirstName
    FROM employee
3. What does this query return?
SELECT ROUND ($7725.53, 1)
4. What does this query return?
SELECT lname + `, ` + SUBSTRING(fname,1,1) + `.' AS Name,
emp_id AS EmployeeID
FROM employee

Exercises

1. You want to retrieve title_id, title, and price for all books that have a publisher ID of 0877 or computer in the title, and for which the price is not null. What T-SQL would you use? (Hint: Use the titles table.)

2. Write a query to find all books in the titles table that have price values and are not null.

3. Write a query to list all book titles and prices in the titles table in descending order based on price.

Solutions to Exercises

1. SELECT title_id, title, price
	FROM titles
	WHERE pub_id = `0877'
	OR title LIKE `%computer%'

(returns 14 rows)

2. SELECT * FROM titles
	WHERE price IS NOT NULL

(returns 16 rows)

3. SELECT title, price
	FROM titles
	ORDER BY price DESC

(returns 18 rows)


Previous chapterNext chapterContents


Macmillan Computer Publishing USA

© Copyright, Macmillan Computer Publishing. All rights reserved.