Guide to Oracle SQL (University at Buffalo 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


    Create, Populate, and Display a departments and a jobs Table
    Requirements

Open SQL Developer
Connect to your database instance
Open a new SQL editor window
Enter code to CREATE a "departments" table with these fields:
departent_id NUMBER(4) NOT NULL PRIMARY KEY, department_name VARCHAR2(30) NOT NULL, manager_id NUMBER(6), location_id NUMBER(4)
 INSERT these records into the "departments" table:

20, Marketing, NULL, NULL
80, Purchasing, NULL, NULL
90, Shipping, NULL, NULL
60, Accounting, NULL, NULL
40, Manufacturing, NULL, NULL
50, Contracting, NULL, NULL
30, Recruiting, NULL, NULL


Display the departments table date (use SELECT).
Save your SQL query as "create_departments.sql".
Open a new SQL editor window
Enter code to CREATE a "jobs" table with these fields:
job_id VARCHAR2(10) PRIMARY KEY, job_title VARCHAR2(20), min_salary NUMBER(10,2), max_salary NUMBER(10,2)
 INSERT these records into the "jobs" table:

SA_MAN, Srvc Acct Manager, 35000, 49999
IT_PROG, IT Programmer, 55000, 79999
MK_REP, Marketing Rep, 22000, 34999
ST_CLERK, Srvc Tech Clerk, 19000, 21999
AD_ASST, Admin Assistant, 17000, 18999


Display the jobs table date (use SELECT).
Save your SQL query as "create_jobs.sql".

When you have this Assignment ready for me to view and grade, you should click on this assignment and then choose "Add a File" and upload your create_departments.sql and create_jobs.sql files, so I can download them and grade your work.

Guide to Oracle SQL (University at Buffalo Version)

Restricting and Sorting Data

Using a WHERE Conditional on a SELECT Statement

Using Wildcards in WHERE Conditional

Sorting Your Result Set

Create, Populate, and Display a departments and a jobs Table

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

Using Wildcards in `WHERE` Conditional