Sorting Module.pdf

Document Sample
Sorting Module.pdf Powered By Docstoc
					                                   Digitally signed by Rudyanto Arief

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

                                        After completing this lesson, you
                                        should be able to do the following :
                                          Limit the rows that are retrieved by a
                                          Sort the rows that are retrieved by a
Restricting and Sorting Data
4th Chapter
# rudyanto arief #

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

 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...

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%’;

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%’;

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’
                    = ‘AD_PRES’
                                          1       FROM employees
                                                  WHERE (job_id       = ‘SA_REP’                2
                                                  OR     job_id       = ‘AD_PRES’)
AND    salary       > 15000;
                                                  AND    salary       > 15000;

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
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
                                                               Apply the logical AND, OR, and NOT operators
                                                            Use the ORDER BY clause to sort rows of

End of the Chapter
Thank you…☺


Shared By:
Description: document for education