Restricting and Sorting Data

1
Restricting and Sorting Data

• Kroenke, Chapter Two

2
Topics

• Limiting rows selected (Where)
• Sorting selected rows (Order By)

3
Limiting Rows Selected
SELECT DISTINCT column alias,
FROM table WHERE condition (s)
4
Where Clause Can Compare

• Values in columns
• Literal values
• Arithmetic expressions, or
• Functions

5
Character Strings Dates

• Enclosed in single quotation marks
• Character values?case sensitive
• Date values?
• format sensitive
• Default DD-MON-YY

6
Comparison Operators

• gt
• gt
• lt
• lt
• ltgt

Syntax -- WHERE expr operator value
7
Examples
8
Other Comparison Operators

• BETWEEN AND ? Between two values
  (inclusive)
• IN(list) ? Matches any value in list of
  values
• LIKE ? Match a character pattern
• IS NULL ? Is a null value

9
LIKE Wildcards

• ? any sequence of zero or more characters
• _ (underscore) ? any single character
• ESCAPE Option to match wildcard characters

10
Logical Operators

• AND ?True if both conditions True
• OR ? True if either condition True
• NOT ?
• True if condition False
• False if condition True
• Null if condition Null

11
Operator Precedence Rules

• All comparison operators
• NOT
• AND
• OR
• Override rules with parenthesis.

12
ORDER BY Clause

• Sorts rows
• ASC ? ascending order (default)
• DESC ? descending order
• Must be last clause in Select statement

13
Order By Syntax
SELECT expression FROM table WHERE
condition(s) ORDER BY column, exprASCDESC