Tt
Click this widget to change the font size.
CC
Click this widget to change contrast.

Home Page CH 1 | CH 2 | CH 3 | CH 4 | CH 5 | CH 6 | CH 7 | CH 8 | CH 9 | CH 10 | CH 11 | CH 12 | CH 13 | Links | Search | Bio |

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
ALLCompares all the values in one set to another set
ANDReturns values if all the contitinals are TRUE
ANYCompares one value tomore than one value
BETWEENReturns values within a range
EXISTSDetermines if a record is found matching the criteria
INDetermines if the value is in a list of values
LIKECompare values using wildcards
NOTUsed to negate any conditional operator
ORReturns values if at least one criteria is TRUE
IS NULLReturns a value if the field contains a NULL value
SOMEReturns values from a subquery

Figure 3-1: SQL Operators

Using Wildcards in WHERE Conditional

WildcardDescription
%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

  1. Open SQL Developer
  2. Connect to your database instance
  3. Open a new SQL editor window
  4. 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)
  5.  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
  6. Display the departments table date (use SELECT).
  7. Save your SQL query as "create_departments.sql".
  8. Open a new SQL editor window
  9. 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)
  10.  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
  11. Display the jobs table date (use SELECT).
  12. 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.

Help contribute to my OER Resources. Donate with PayPal button via my PayPal account.
Creative Commons License This work is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International License. Copyright © 2016-2024 Jim Gerland