Scribd
Upload
45 views20 pages

Restricting and Sorting Data

This document discusses how to restrict and sort data retrieved by SQL queries. It covers limiting rows using a WHERE clause with comparison operators like =, <, and BETWEEN. It also covers sorting results using an ORDER BY clause to sequence data in ascending or descending order based on one or multiple columns. Logical operators like AND, OR, and NOT can be combined with comparison conditions to filter rows. Parentheses can override the default order of operations.

Uploaded by

athar89
Copyright
© Attribution Non-Commercial (BY-NC)
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PPT, PDF, TXT or read online on Scribd
45 views20 pages

Restricting and Sorting Data

This document discusses how to restrict and sort data retrieved by SQL queries. It covers limiting rows using a WHERE clause with comparison operators like =, <, and BETWEEN. It also covers sorting results using an ORDER BY clause to sequence data in ascending or descending order based on one or multiple columns. Logical operators like AND, OR, and NOT can be combined with comparison conditions to filter rows. Parentheses can override the default order of operations.

Uploaded by

athar89
Copyright
© Attribution Non-Commercial (BY-NC)
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PPT, PDF, TXT or read online on Scribd
You are on page 1/ 20

Restricting and Sorting Data

Objectives

After completing this lesson, you should be able to do the following: Limit the rows that are retrieved by a query Sort the rows that are retrieved by a query

Limiting the Rows That Are Selected

Restrict the rows that are returned by using the WHERE clause:

SELECT *|{[DISTINCT] column|expression [alias],...} FROM table [WHERE condition(s)];

The WHERE clause follows the FROM clause.

Using the WHERE Clause

SELECT employee_id, last_name, job_id, department_id FROM employees WHERE department_id = 90 ;

Character Strings and Dates


Character strings and date values are enclosed by 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' ;

Comparison Conditions

Operator = >

Meaning Equal to Greater than

>=
< <= <>

Greater than or equal to


Less than Less than or equal to Not equal to

BETWEEN ...AND...
IN(set) LIKE

Between two values (inclusive)


Match any of a list of values Match a character pattern

IS NULL

Is a null value

Using Comparison Conditions

SELECT last_name, salary FROM employees WHERE salary <= 3000 ;

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 ;
Lower limit Upper limit

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) ;

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 FROM WHERE

first_name employees first_name LIKE 'S%' ;

Using the LIKE Condition

You can combine pattern-matching characters:

SELECT last_name FROM employees WHERE last_name LIKE '_o%' ;

Using the NULL Conditions

Test for nulls with the IS NULL operator.


SELECT last_name, manager_id FROM employees WHERE manager_id IS NULL ;

Logical Conditions

Operator AND OR

Meaning Returns TRUE if both component conditions are true Returns TRUE if either component condition is true

NOT

Returns TRUE if the following condition is false

Using the AND Operator

AND requires both conditions to be true:


SELECT FROM WHERE AND employee_id, last_name, job_id, salary employees salary >=10000 job_id LIKE '%MAN%' ;

Using the OR Operator

OR requires either condition to be true:


SELECT FROM WHERE OR employee_id, last_name, job_id, salary employees salary >= 10000 job_id LIKE '%MAN%' ;

Using the NOT Operator

SELECT last_name, job_id FROM employees WHERE job_id NOT IN ('IT_PROG', 'ST_CLERK', 'SA_REP') ;

Rules of Precedence

Operator 1

Meaning Arithmetic operators

2
3 4

Concatenation operator
Comparison conditions IS [NOT] NULL, LIKE, [NOT] IN

5
6 7 8

[NOT] BETWEEN
Not equal to NOT logical condition AND logical condition

OR logical condition

You can use parentheses to override rules of precedence.

Rules of Precedence
SELECT FROM WHERE OR AND last_name, job_id, salary employees job_id = 'SA_REP' job_id = 'AD_PRES' salary > 15000;

SELECT FROM WHERE OR AND

last_name, job_id, salary employees (job_id = 'SA_REP' job_id = 'AD_PRES') salary > 15000;

Using the ORDER BY Clause

Sort retrieved 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 ;

Sorting

Sorting in descending order:

SELECT last_name, job_id, department_id, hire_date FROM employees 1 ORDER BY hire_date DESC ;

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;

You might also like