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 Wikipedia: 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.
   1. Run the query SELECT * FROM EMPLOYEES; to see all data in the EMPLOYEES table.
   2. Run the query SELECT * FROM EMPLOYEES WHERE DEPARTMENT_ID = 20; to see all employees in department 30.
   3. Run the query SELECT * FROM EMPLOYEES WHERE JOB_ID = 'SA_REP'; to see all employees with a job id of SA_REP.
   4. 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.
   5. Run the query SELECT * FROM EMPLOYEES WHERE HIRE_DATE >= '1 JAN 2000'; to see all employees hired on or after January 1, 2000.
   6. Run the query SELECT * FROM EMPLOYEES WHERE HIRE_DATE BETWEEN '1 JAN 1999' AND '31 DEC 1999'; to see all employees hired in 1999.
   7. Run the query SELECT * FROM EMPLOYEES WHERE MANAGER_ID IN (100, 145, 205); to see all employees who have manager 100, 145, or 205.
   8. Run the query SELECT * FROM EMPLOYEES WHERE PHONE_NUMBER LIKE '0%'; to see all employees with a phone number beginning with 0.
   9. Run the query SELECT * FROM EMPLOYEES WHERE FIRST_NAME LIKE 'J___'; to see all employees with a four-character first name beginning with J.
   10. Run the query SELECT * FROM EMPLOYEES WHERE DEPARTMENT_ID IS NULL; to see all employees not assigned to a department.
2. Select HR data using logical operators.
   1. 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.
   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.
   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.
3. Select OE data using the WHERE clause.
   1. Select all data in the CUSTOMERS table.
   2. Select all customers with a credit limit greater than 100000.
   3. Select all customers with Platinum status.
   4. Select all customers with Gold status.
   5. Select all customers with a credit limit between 50000 and 100000.
   6. Select all customers in Germany (DE), Italy (IT), and Switzerland (CH).
   7. Select all customers with phone numbers beginning with '+1 '.
   8. Select all customers with a six-character postal code ending with 0.
   9. Select all customers not assigned to an account manager.
4. Select OE data using logical operators.
   1. Select all US customers with Silver status.
   2. Select all customers from India (IN) or China (CN).
   3. 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

References

← Selecting Data

Oracle SQL Fundamentals

Sorting Data →