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

 * 1) Read  Where (SQL).

Multimedia

 * 1) Oracle SQL Tutorial - Querying data - Part 6 - WHERE clause
 * 2) Oracle SQL Tutorial - Querying data - Part 7 - WHERE clause continued
 * 3) Oracle SQL Tutorial - Querying data - Part 8 - WHERE clause continued

Activities

 * 1) Select HR data using the WHERE clause.
 * 2) Run the query   to see all data in the EMPLOYEES table.
 * 3) Run the query   to see all employees in department 30.
 * 4) Run the query   to see all employees with a job id of SA_REP.
 * 5) Run the query   to see all employees with a job id of sa_rep. Note that Oracle WHERE comparisons are case-sensitive.
 * 6) Run the query   to see all employees hired on or after January 1, 2000.
 * 7) Run the query   to see all employees hired in 1999.
 * 8) Run the query   to see all employees who have manager 100, 145, or 205.
 * 9) Run the query   to see all employees with a phone number beginning with 0.
 * 10) Run the query   to see all employees with a four-character first name beginning with J.
 * 11) Run the query   to see all employees not assigned to a department.
 * 12) Select HR data using logical operators.
 * 13) Run the query   to see all employees in department 80 with a commission percentage less than 0.2.
 * 14) Run the query   to see all employees with a salary greater than 20,000 or with a commission percentage greater than 0.3.
 * 15) Run the query   to see all employees with phone numbers that do not begin with 515.
 * 16) Select OE data using the WHERE clause.
 * 17) Select all data in the CUSTOMERS table.
 * 18) Select all customers with a credit limit greater than 100000.
 * 19) Select all customers with Platinum status.
 * 20) Select all customers with Gold status.
 * 21) Select all customers with a credit limit between 50000 and 100000.
 * 22) Select all customers in Germany (DE), Italy (IT), and Switzerland (CH).
 * 23) Select all customers with phone numbers beginning with '+1 '.
 * 24) Select all customers with a six-character postal code ending with 0.
 * 25) Select all customers not assigned to an account manager.
 * 26) Select OE data using logical operators.
 * 27) Select all US customers with Silver status.
 * 28) Select all customers from India (IN) or China (CN).
 * 29) 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 &lt;SQL-DML-Statement&gt; FROM &lt;table&gt; WHERE &lt;predicate&gt;.
 * Simple predicates use one of the operators =, &lt;&gt;, &gt;, &gt;=, &lt;, &lt;=, 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, */, +-, ||, = &lt;&gt; &lt;= &gt;=, 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