Guide to Oracle SQL (Buffalo State University Version)

Restricting and Sorting Data

In Chapter 2 I introduced the SELECT statement and provided examples for retreiving all the fields or just certain fields in a record. SQL also allows you to restrict your query to return on certail records in a table using condtitionals.

Using a `WHERE` Conditional on a `SELECT` Statement

The WHERE keyword can ne used to provide a condition (restriction) so the records retreived (result set) contains only the records you are interested in. The WHERE can use operators and wildcards.

Conparison Operator	Description
=	Equal To
>	Greater Than
<	Less Than
!>	Not Greater Than
!<	Not Less Than
>=	Greater Than or Equal To
<=	Less Than or Equal To
<>	Not Equal To
!=	Not Equal To

Arithmatic Operator	Description
+	Addition
-	Subtraction
*	Multiplication
/	Division
%	Modulus

Logical Operator	Description
ALL	Compares all the values in one set to another set
AND	Returns values if all the contitinals are TRUE
ANY	Compares one value tomore than one value
BETWEEN	Returns values within a range
EXISTS	Determines if a record is found matching the criteria
IN	Determines if the value is in a list of values
LIKE	Compare values using wildcards
NOT	Used to negate any conditional operator
OR	Returns values if at least one criteria is TRUE
IS NULL	Returns a value if the field contains a NULL value
SOME	Returns values from a subquery

Figure 3-1: SQL Operators

Using Wildcards in `WHERE` Conditional

Wildcard	Description
%	Will match any sequence of charaters and numbers
_	Will match any single character in a certain position

Figure 3-2: SQL Wildcards

SELECT *
FROM jimTable
WHERE jimID = 1;

     JIMID FIRSTNAME                 LASTNAME
---------- ------------------------- -------------------------
         1 Jim                       Gerland

Figure 3-3: Using the WHERE Conditional With a equal to (=) Operator

Note, when using the LIKE operator the string needs to be surroiunded with single quotes (''). The LIKE operator is case-sensitive.

SELECT *
FROM jimTable
WHERE firstName LIKE 'J%';

     JIMID FIRSTNAME                 LASTNAME
---------- ------------------------- -------------------------
         1 Jim                       Gerland

Figure 3-4: Using the WHERE Conditional the LIKE and the % Wildcard

SELECT *
FROM jimTable
WHERE firstName LIKE '_ally';

     JIMID FIRSTNAME                 LASTNAME
---------- ------------------------- -------------------------
         1 Sally                     Smart

Figure 3-5: Using the WHERE Conditional the LIKE and the _ Wildcard

Sorting Your Result Set

You can use the ORDER BY clause to sort the result set from your SELECT statement. By default, the result set is sorted ascending based on the field (or fields) you supply on the ORDER BY clause. You can add the DESC to order your result set in descending order.


  
  SELECT *
FROM jimTable
ORDER BY lastName DESC;


     JIMID FIRSTNAME                 LASTNAME
---------- ------------------------- -------------------------
         3 Sally                     Smart
         2 Phil                      Phailure
         1 Jim                       Gerland

  Figure 3-5: Using the ORDER BY DESC Clause

Guide to Oracle SQL (Buffalo State University Version)

Restricting and Sorting Data

Using a WHERE Conditional on a SELECT Statement

Using Wildcards in WHERE Conditional

Sorting Your Result Set

Using a `WHERE` Conditional on a `SELECT` Statement

Using Wildcards in `WHERE` Conditional