# Oracle SQL Fundamentals/Restricting Data

This lesson introduces restricting data using the WHERE clause.

## Objectives and Skills

Objectives and skills for the restricting data portion of Oracle SQL Fundamentals I certification include:
* Restricting and Sorting Data
** Limit the rows that are retrieved by a query

## Readings

* Read Wikipedia: Where (SQL).

## Multimedia

* Oracle SQL Tutorial - Querying data - Part 6 - WHERE clause
* Oracle SQL Tutorial - Querying data - Part 7 - WHERE clause continued
* Oracle SQL Tutorial - Querying data - Part 8 - WHERE clause continued

## Activities

* Select HR data using the WHERE clause.
** Run the query SELECT * FROM EMPLOYEES; to see all data in the EMPLOYEES table.
** Run the query SELECT * FROM EMPLOYEES WHERE DEPARTMENT_ID = 20; to see all employees in department 30.
** Run the query SELECT * FROM EMPLOYEES WHERE JOB_ID = 'SA_REP'; to see all employees with a job id of SA_REP.
** Run the query SELECT * FROM EMPLOYEES WHERE JOB_ID = 'sa_rep'; to see all employees with a job id of sa_rep. Note that Oracle WHERE comparisons are case-sensitive.
** Run the query SELECT * FROM EMPLOYEES WHERE HIRE_DATE >= '1 JAN 2000'; to see all employees hired on or after January 1, 2000.
** Run the query SELECT * FROM EMPLOYEES WHERE HIRE_DATE BETWEEN '1 JAN 1999' AND '31 DEC 1999'; to see all employees hired in 1999.
** Run the query SELECT * FROM EMPLOYEES WHERE MANAGER_ID IN (100, 145, 205); to see all employees who have manager 100, 145, or 205.
** Run the query SELECT * FROM EMPLOYEES WHERE PHONE_NUMBER LIKE '0%'; to see all employees with a phone number beginning with 0.
** Run the query SELECT * FROM EMPLOYEES WHERE FIRST_NAME LIKE 'J___'; to see all employees with a four-character first name beginning with J.
** Run the query SELECT * FROM EMPLOYEES WHERE DEPARTMENT_ID IS NULL; to see all employees not assigned to a department.
* Select HR data using logical operators.
** Run the query SELECT * FROM EMPLOYEES WHERE DEPARTMENT_ID = 80 AND COMMISSION_PCT < 0.2; to see all employees in department 80 with a commission percentage less than 0.2.
** Run the query SELECT * FROM EMPLOYEES WHERE SALARY > 20000 OR COMMISSION_PCT > 0.3; to see all employees with a salary greater than 20,000 or with a commission percentage greater than 0.3.
** Run the query SELECT * FROM EMPLOYEES WHERE PHONE_NUMBER NOT LIKE '515%'; to see all employees with phone numbers that do not begin with 515.
* Select OE data using the WHERE clause.
** Select all data in the CUSTOMERS table.
** Select all customers with a credit limit greater than 100000.
** Select all customers with Platinum status.
** Select all customers with Gold status.
** Select all customers with a credit limit between 50000 and 100000.
** Select all customers in Germany (DE), Italy (IT), and Switzerland (CH).
** Select all customers with phone numbers beginning with '+1 '.
** Select all customers with a six-character postal code ending with 0.
** Select all customers not assigned to an account manager.
* Select OE data using logical operators.
** Select all US customers with Silver status.
** Select all customers from India (IN) or China (CN).
** Select all customers who do not have account manager 145.

## Lesson Summary

* A WHERE clause in SQL specifies that a SQL Data Manipulation Language (DML) statement should only affect rows that meet specified criteria.
* The basic WHERE clause syntax is <SQL-DML-Statement> FROM <table> WHERE <predicate>.
* Simple predicates use one of the operators =, <>, >, >=, <, <=, IN, BETWEEN, LIKE, IS NULL or IS NOT NULL.
* IN will find any values existing in a set of candidates.
* BETWEEN will find any values within a range.
* LIKE will find a string fitting a certain description.
* The underscore character (_) is used as a wildcard character to match any single character for LIKE comparisons.
* The percent character (%) is used as a wildcard character for to match any character string for LIKE comparisons.
* The keywords AND and OR may be used to combine two predicates into a new one.
* The keyword NOT may be used to negate a condition.
* Predicates may be enclosed in parentheses if desired.
* Operator precedence is evaluated in the order, */, +-, ||, = <> <= >=, IN LIKE IS NULL, BETWEEN, !=, NOT, AND, OR.

## Key Terms
### operator precedence

    A collection of rules that define which procedures to perform first in order to evaluate a given mathematical expression.

### wildcard

    a single character used to represent a number of characters or an empty string.

## Assessments

* Flashcards: Quizlet: Oracle 1Z0-051 Exam - Select Statement

## See Also

* Oracle: Database 2 Day Developers Guide - Selecting Table Data