# Sorting Module.pdf

Document Sample

```					                                   Digitally signed by Rudyanto Arief

Rudyanto Arief
DN: cn=Rudyanto Arief, o=STMIK AMIKOM
YOGYAKARTA, ou=Penjamin Mutu,
email=arief_rudyanto@yahoo.com, c=ID
Date: 2011.04.05 22:24:36 +07'00'

Objetives
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
Restricting and Sorting Data
4th Chapter
# rudyanto arief #

Limiting the Rows That Are
Limiting Rows Using a Selection      Selected
EMPLOYEES
Restrict the rows that are
returned by using the WHERE
clause.
The WHERE clause follows the
FROM clause.
“retrieve all employees in
department 90”                       SELECT * | { [DISTINCT] column|expression [alias],...}
FROM table
[WHERE condition(s)];

1
Using the WHERE Clause                               Character Strings and Dates
SELECT id_nasabah, nama_nasabah, alamat_nasabah        Character strings and date values are
FROM nasabah                                           enclosed by single quotation marks.
WHERE id_nasabah = 3;
Character values are case-sensitive,
and date values are format-sensitive.

SELECT id_nasabah, nama_nasabah, alamat_nasabah
FROM nasabah
WHERE nama_nasabah = ‘Neneng Djaila’;

Comparison Conditions                                Using Comparison Conditions
Perbandingan                     Operator

Sama dengan                      =

Tidak sama dengan                <>
SELECT last_name, salary
Lebih besar dari                 >                     FROM employees
Lebih kecil dari                 <
WHERE salary <= 3000 ;
Lebih besar atau sama dengan     >=

Lebih kecil atau sama dengan     <=

Tidak kurang dari                !<

Tidak lebih dari                 !>

Tidak sama dengan                !=

Menyamakan pola karakter         LIKE

Nilai Null                       IS NULL

Match any of a list values       IN (set)

Between two values (inclusive)   BETWEEN...AND...

2
Using the BETWEEN Condition                     Using the IN Condition
Use the BETWEEN condition to display            Use the IN membership condition to
rows based on a range of values :               test for values in a list :
SELECT employee_id, last_name, salary, manager_id
SELECT last_name, salary
FROM employees
FROM employees
WHERE manager_id IN (100, 101, 201);
WHERE salary BETWEEN 2500 AND 3500;

Lower limit   Upper limit

Using the LIKE Condition                        Using the LIKE Condition
Use the LIKE condition to perform wildcard      You can combine pattern-matching
searches of valid search string values.         characters.
Search conditions can contain either literal    You can use the ESCAPE identifier to search
characters or numbers:                          for the actual % and _ symbols.
% denotes zero or many characters.
SELECT    last_name
_ denotes one character.
FROM employees
WHERElast_name LIKE ‘_o%’;
SELECT first_name
FROM   employees
WHERE first_name LIKE ‘S%’;

3
Using the NULL Conditions                              Logical Conditions
Test for nulls with the IS NULL
operator.                                              Operator     Meaning
NOT          Returns TRUE if both component
SELECT   last_name, manager_id                                      conditions are true
FROM employees                                         AND          Returns TRUE if either component
WHERE    manager_id IS NULL;                                        condition is true
OR           Returns TRUE if the following condition
is false

Using the AND Operator                                 Using the OR Operator
AND requires both conditions to be                     OR requires either condition to be
true:                                                  true:
SELECT       employee_id, last_name, job_id, salary    SELECT       employee_id, last_name, job_id, salary
FROM employees                                         FROM employees
WHERE salary >=10000                                   WHERE salary >= 10000
AND    job_id LIKE ‘%MAN%’;                            OR     job_id LIKE ‘%MAN%’;

4
Using the NOT Operator                            Rules of Precedence
Operator      Meaning
SELECT    last_name, job_id
FROM      employees                              1             Arithmetic operators
WHEREjob_id                                      2             Concatenation operator
NOT IN (‘IT_PROG’, ‘ST_CLERK’, ‘SA_REP’);
3             Comparison conditions
4             IS [NOT] NULL, LIKE, [NOT] IN
5             [NOT] BETWEEN
6             Not equal to
7             NOT logical condition
8             AND logical condition
9             OR logical condition

Rules of Precedence…                              Rules of Precedence…
SELECT        last_name, job_id, salary
SELECT        last_name, job_id, salary
FROM employees
WHERE job_id
OR     job_id
= ‘SA_REP’
1       FROM employees
WHERE (job_id       = ‘SA_REP’                2
AND    salary       > 15000;
AND    salary       > 15000;

5
Using the ORDER BY Clause                                  Sorting
Sort retrieved rows with the ORDER BY clause:              Sorting in descending order:
ASC: ascending order, default
DESC: descending order                                 SELECT     last_name, job_id, department_id, hire_date
FROM       employees
The ORDER BY clause comes last in the SELECT              ORDER BY   hire_date DESC ;                   1
statement:
SELECT       last_name, job_id, department_id, hire_date
FROM         employees                                         Sorting by column alias:
ORDER BY     hire_date;
SELECT      employee_id, last_name, salary, salary*12 annsal
FROM        employees
ORDER BY    annsal ;                             2

Sorting …                                                  Summary
Sorting by multiple columns:                               In this lesson, you should have learned
how to:
SELECT       last_name, department_id, salary               Use the WHERE clause to restrict rows of
FROM         employees                                      output:
ORDER BY     department_id, salary DESC;    3
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.

6
End of the Chapter
Thank you…☺

7

```
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
 views: 10 posted: 2/26/2012 language: pages: 7
Description: document for education