Restricting and Sorting Data in SQL
After completing this lesson, you should be able to
do the following:
• Limit the rows retrieved by a query
• Sort the rows retrieved by a query
Limiting Rows Using a Selection
In the example on the slide, assume that you want to display all the employees in department 90. The
rows with a value of 90 in the DEPARTMENT_ID column are the only ones returned. This method of
restriction is the basis of the WHERE clause in SQL.
Limiting the Rows Selected
• Restrict the rows returned by using the WHERE
clause.
• The WHERE clause follows the FROM clause.
SELECT *|{[DISTINCT] column|expression [alias],...}
FROM table
[WHERE condition(s)];
Limiting the Rows Selected
You can restrict the rows returned from the query by using the WHERE clause. A WHERE clause
contains a condition that must be met, and it directly follows the FROM clause. If the condition is true,
the row meeting the condition is returned.
In the syntax:
WHERE restricts the query to rows that meet a condition
condition is composed of column names, expressions,
constants, and a comparison operator
The WHERE clause can compare values in columns, literal values, arithmetic expressions, or functions.
It consists of three elements:
• Column name
• Comparison condition
• Column name, constant, or list of values
Using the WHERE Clause
SELECT employee_id, last_name, job_id, department_id
FROM employees
WHERE department_id = 90 ;
Using the WHERE Clause
In the example, the SELECT statement retrieves the name, job ID, and department number of all
employees whose job ID is SA_REP.
Note that the job title SA_REP has been specified in uppercase to ensure that it matches the job ID
column in the EMPLOYEES table. Character strings are case sensitive.
Character Strings and Dates
• Character strings and date values are enclosed in
single quotation marks.
• Character values are case sensitive, and date
values are format sensitive.
• The default date format is DD-MON-RR.
SELECT last_name, job_id, department_id
FROM employees
WHERE last_name = ’Whalen’;
Character Strings and Dates
Character strings and dates in the WHERE clause must be enclosed in single quotation marks (’’).
Number constants, however, should not be enclosed in single quotation marks.
All character searches are case sensitive. In the following example, no rows are returned because the
EMPLOYEES table stores all the last names in mixed case:
SELECT last_name, job_id, department_id
FROM employees
WHERE last_name = ’WHALEN’;
Oracle databases store dates in an internal numeric format, representing the century, year, month, day,
hours, minutes, and seconds. The default date display is DD-MON-RR.
Comparison Conditions
Operator Meaning
= Equal to
> Greater than
>= Greater than or equal to
< Less than
<= Less than or equal to
<> Not equal to
Comparison Conditions
Comparison conditions are used in conditions that compare one expression to another value or
expression. They are used in the WHERE clause in the following format:
Syntax
... WHERE expr operator value
For Example
... WHERE hire_date=’01-JAN-95’
... WHERE salary>=6000
... WHERE last_name=’Smith’
An alias cannot be used in the WHERE clause.
Note: The symbol != and ^= can also represent the not equal to condition.
Using Comparison Conditions
SELECT last_name, salary
FROM employees
WHERE salary <= 3000;
Using the Comparison Conditions
In the example, the SELECT statement retrieves the last name and salary from the EMPLOYEES table,
where the employee salary is less than or equal to 3000. Note that there is an explicit value supplied to
the WHERE clause. The explicit value of 3000 is compared to the salary value in the SALARY column
of the EMPLOYEES table.
Using the BETWEEN Condition
Use the BETWEEN condition to display rows based on
a range of values.
SELECT last_name, salary
FROM employees
WHERE salary BETWEEN 2500 AND 3500;
The BETWEEN Condition
You can display rows based on a range of values using the BETWEEN range condition. The range that
you specify contains a lower limit and an upper limit.
The SELECT statement on the slide returns rows from the EMPLOYEES table for any employee
whose salary is between $2,500 and $3,500.
Values specified with the BETWEEN condition are inclusive. You must specify the lower limit first.
Instructor Note
Emphasize that the values specified with the BETWEEN operator in the example are inclusive. Explain
that BETWEEN … AND … is actually translated by Oracle server to a pair of AND conditions: (a >=
lower limit) AND (a <= higher limit). So using BETWEEN … AND … has no
performance benefits, and it is used for logical simplicity.
Demo: 2_betw.sql
Purpose: To illustrate using the BETWEEN operator.
Using the IN Condition
Use the IN membership condition to test for values in
a list.
SELECT employee_id, last_name, salary, manager_id
FROM employees
WHERE manager_id IN (100, 101, 201);
The IN Condition
To test for values in a specified set of values, use the IN condition. The IN condition is also known as
the membership condition.
The slide example displays employee numbers, last names, salaries, and manager’s employee numbers
for all the employees whose manager’s employee number is 100, 101, or 201.
The IN condition can be used with any data type. The following example returns a row from the
EMPLOYEES table for any employee whose last name is included in the list of names in the WHERE
clause:
SELECT employee_id, manager_id, department_id
FROM employees
WHERE last_name IN (’Hartstein’, ’Vargas’);
If characters or dates are used in the list, they must be enclosed in single quotation marks (’’).
Using the LIKE Condition
• Use the LIKE condition to perform wildcard
searches of valid search string values.
• Search conditions can contain either literal
characters or numbers:
– % denotes zero or many characters.
– _ denotes one character.
SELECT first_name
FROM employees
WHERE first_name LIKE ’S%’;
FROM employees
WHERE salary <= 3000;
Using the Comparison Conditions
In the example, the SELECT statement retrieves the last name and salary from the EMPLOYEES table,
where the employee salary is less than or equal to 3000. Note that there is an explicit value supplied to
the WHERE clause. The explicit value of 3000 is compared to the salary value in the SALARY column
of the EMPLOYEES table.
Using the BETWEEN Condition
Use the BETWEEN condition to display rows based on
a range of values.
SELECT last_name, salary
FROM employees
WHERE salary BETWEEN 2500 AND 3500;
The BETWEEN Condition
You can display rows based on a range of values using the BETWEEN range condition. The range that
you specify contains a lower limit and an upper limit.
The SELECT statement on the slide returns rows from the EMPLOYEES table for any employee
whose salary is between $2,500 and $3,500.
Values specified with the BETWEEN condition are inclusive. You must specify the lower limit first.
Instructor Note
Emphasize that the values specified with the BETWEEN operator in the example are inclusive. Explain
that BETWEEN … AND … is actually translated by Oracle server to a pair of AND conditions: (a >=
lower limit) AND (a <= higher limit). So using BETWEEN … AND … has no
performance benefits, and it is used for logical simplicity.
Demo: 2_betw.sql
Purpose: To illustrate using the BETWEEN operator.
Using the IN Condition
Use the IN membership condition to test for values in
a list.
SELECT employee_id, last_name, salary, manager_id
FROM employees
WHERE manager_id IN (100, 101, 201);
The IN Condition
To test for values in a specified set of values, use the IN condition. The IN condition is also known as
the membership condition.
The slide example displays employee numbers, last names, salaries, and manager’s employee numbers
for all the employees whose manager’s employee number is 100, 101, or 201.
The IN condition can be used with any data type. The following example returns a row from the
EMPLOYEES table for any employee whose last name is included in the list of names in the WHERE
clause:
SELECT employee_id, manager_id, department_id
FROM employees
WHERE last_name IN (’Hartstein’, ’Vargas’);
If characters or dates are used in the list, they must be enclosed in single quotation marks (’’).
Using the LIKE Condition
• Use the LIKE condition to perform wildcard
searches of valid search string values.
• Search conditions can contain either literal
characters or numbers:
– % denotes zero or many characters.
– _ denotes one character.
SELECT first_name
FROM employees
WHERE first_name LIKE ’S%’;
The LIKE Condition
You may not always know the exact value to search for. You can select rows that match a character
pattern by using the LIKE condition. The character pattern-matching operation is referred to as a
wildcard search. Two symbols can be used to construct the search string.
The SELECT statement on the slide returns the employee first name from the EMPLOYEES table for
any employee whose first name begins with an S. Note the uppercase S. Names beginning with an s
are not returned.
The LIKE condition can be used as a shortcut for some BETWEEN comparisons. The following
example displays the last names and hire dates of all employees who joined between January 1995 and
December 1995:
SELECT last_name, hire_date
FROM employees
WHERE hire_date LIKE ’%95’;
Using the LIKE Condition
• You can combine pattern-matching characters.
SELECT last_name
FROM employees
WHERE last_name LIKE ’_o%’;
• You can use the ESCAPE identifier to search for the
actual % and _ symbols.
Combining Wildcard Characters
The % and _ symbols can be used in any combination with literal characters. The example on the slide
displays the names of all employees whose last names have an o as the second character.
The ESCAPE Option
When you need to have an exact match for the actual % and _ characters, use the ESCAPE option.
This option specifies what the escape character is. If you want to search for strings that contain ‘SA_’,
you can use the following SQL statement:
SELECT employee_id, last_name, job_id
FROM employees
WHERE job_id LIKE ’%SA\_%’ ESCAPE ’\’;
The ESCAPE option identifies the backslash (\) as the escape character. In the pattern, the escape
character precedes the underscore (_). This causes the Oracle Server to interpret the underscore
literally.
Using the NULL Conditions
Test for nulls with the IS NULL operator.
SELECT last_name, manager_id
FROM employees
WHERE manager_id IS NULL;
The NULL Conditions
The NULL conditions include the IS NULL condition and the IS NOT NULL condition.
The IS NULL condition tests for nulls. A null value means the value is unavailable, unassigned,
unknown, or inapplicable. Therefore, you cannot test with = because a null cannot be equal or unequal
to any value. The slide example retrieves the last names and managers of all employees who do not
have a manager.
For another example, to display last name, job ID, and commission for all employees who are NOT
entitled to get a commission, use the following SQL statement:
SELECT last_name, job_id, commission_pct
FROM employees
WHERE commission_pct IS NULL;
Logical Conditions
A logical condition combines the result of two component conditions to produce a single result based
on them or inverts the result of a single condition. A row is returned only if the overall result of the
condition is true. Three logical operators are available in SQL:
• AND
• OR
• NOT
All the examples so far have specified only one condition in the WHERE clause. You can use several
conditions in one WHERE clause using the AND and OR operators.
Using the AND Operator
AND requires both conditions to be true.
SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary >=10000
AND job_id LIKE ’%MAN%’;
The AND Operator
In the example, both conditions must be true for any record to be selected. Therefore, only employees
who have a job title that contains the string MAN and earn $10,000 or more are selected.
All character searches are case sensitive. No rows are returned if MAN is not in uppercase. Character
strings must be enclosed in quotation marks.
Using the OR Operator
OR requires either condition to be true.
SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary >= 10000
OR job_id LIKE ’%MAN%’;
The OR Operator
In the example, either condition can be true for any record to be selected. Therefore, any employee
who has a job ID containing MAN or earns $10,000 or more is selected.
Using the NOT Operator
SELECT last_name, job_id
FROM employees
WHERE job_id
NOT IN (’IT_PROG’, ’ST_CLERK’, ’SA_REP’);
The NOT Operator
The slide example displays the last name and job ID of all employees whose job ID is not IT_PROG,
ST_CLERK, or SA_REP.
ORDER BY Clause
• Sort rows with the ORDER BY clause
– ASC: ascending order, default
– DESC: descending order
• The ORDER BY clause comes last in the SELECT
statement.
SELECT last_name, job_id, department_id, hire_date
FROM employees
ORDER BY hire_date ;
The ORDER BY Clause
The order of rows returned in a query result is undefined. The ORDER BY clause can be used to sort
the rows. If you use the ORDER BY clause, it must be the last clause of the SQL statement. You can
specify an expression, or an alias, or column position as the sort condition.
Syntax
SELECT expr
FROM table
[WHERE condition(s)]
[ORDER BY {column, expr} [ASC|DESC]];
In the syntax:
ORDER BY specifies the order in which the retrieved rows are displayed
ASC orders the rows in ascending order (this is the default order)
DESC orders the rows in descending order
If the ORDER BY clause is not used, the sort order is undefined, and the Oracle server may not fetch
rows in the same order for the same query twice. Use the ORDER BY clause to display the rows in a
specific order.
Sorting in Descending Order
SELECT last_name, job_id, department_id, hire_date
FROM employees
ORDER BY hire_date DESC ;
Default Ordering of Data
The default sort order is ascending:
• Numeric values are displayed with the lowest values first—for example, 1–999.
• Date values are displayed with the earliest value first—for example, 01-JAN-92 before
01-JAN-95.
• Character values are displayed in alphabetical order—for example, A first and Z last.
• Null values are displayed last for ascending sequences and first for descending sequences.
Reversing the Default Order
To reverse the order in which rows are displayed, specify the DESC keyword after the column name in
the ORDER BY clause. The slide example sorts the result by the most recently hired employee.
Sorting by Column Alias
SELECT employee_id, last_name, salary*12 annsal
FROM employees
ORDER BY annsal;
Sorting by Multiple Columns
SELECT last_name, department_id, salary
FROM employees
ORDER BY department_id, salary DESC;
Sorting by Multiple Columns
You can sort query results by more than one column. The sort limit is the number of columns in the
given table.
In the ORDER BY clause, specify the columns, and separate the column names using commas. If you
want to reverse the order of a column, specify DESC after its name. You can also order by columns
that are not included in the SELECT clause.
Example
Display the last names and salaries of all employees. Order the result by department number, and then
in descending order by salary.
SELECT last_name, salary
FROM employees
ORDER BY department_id, salary DESC;
Summary
SELECT *|{[DISTINCT] column|expression [alias],...}
FROM table
[WHERE condition(s)]
[ORDER BY {column, expr, alias} [ASC|DESC]];
In this lesson, you should have learned how to:
• Use the WHERE clause to restrict rows of output
– Use the comparison conditions
– Use the BETWEEN, IN, LIKE, and NULL conditions
– Apply the logical AND, OR, and NOT operators
• Use the ORDER BY clause to sort rows of output
No comments:
Post a Comment