Oracle

Chapter 7 - DQL (Data Query Language)

WHERE Clause

The WHERE clause in Oracle's Data Query Language (DQL) is used to filter rows from a result set based on specified conditions. It is one of the most commonly used clauses in SQL queries. By using the WHERE clause, you can narrow down the records that meet specific criteria, making it easier to retrieve relevant data from large datasets. For example, when working with tables such as authors, books, libraries, memberships, and rentals, the WHERE clause can help you retrieve only the records you are interested in.

Key Features of the WHERE Clause:

  1. Basic Syntax
    The WHERE clause follows the basic syntax structure in a query:

    SELECT column1, column2, ...
FROM table_name
WHERE condition;
    • It filters rows that satisfy the condition(s) in the WHERE clause.

  2. Example Query
    Let's retrieve all the books published after the year 2015:

    SELECT title, author_id, published_year
FROM books
WHERE published_year > 2015;
    • This query returns the book title, author ID, and year of publication for books published after 2015.

  3. Multiple Conditions
    The WHERE clause supports multiple conditions using AND, OR, and NOT operators.
    Example: Find books published by a specific author after 2010:

    SELECT title, author_id, published_year
FROM books
WHERE author_id = 102 AND published_year > 2010;
    • Use AND to combine conditions that all must be true.
    • Use OR when any condition can be true.
    • Use NOT to negate a condition.

  4. Operators in the WHERE Clause

    • Comparison Operators: =, >, <, >=, <=, <> (not equal to).
    • Logical Operators: AND, OR, NOT.
    • Other Operators:
      • IN: Matches any of a list of values.
      • BETWEEN: Finds values within a range.
      • LIKE: Matches a pattern using wildcard characters.

    Example:

    SELECT title
FROM books
WHERE published_year BETWEEN 2010 AND 2020;

  5. Using LIKE in WHERE Clause
    The LIKE operator is useful for pattern matching.
    Example: Find all books with titles that start with "The":

    SELECT title
FROM books
WHERE title LIKE 'The%';
    • % matches zero or more characters, so this query will return any title starting with "The".

  6. Best Practices

    • Always use proper conditions to avoid returning unwanted data.
    • Combine multiple conditions wisely using AND and OR to ensure accurate results.
    • Use indexed columns in the WHERE clause for better performance.
    • Avoid using SELECT * unless you need all columns; it can negatively impact performance.
    • Ensure correct data types are used in comparisons to prevent unexpected results.

Following these practices ensures your queries are both efficient and easy to maintain.

Tansy SQL Course | WHERE Clause | Chapter 7 | Lesson 2 - Video Thumbnail

TEST CODE

To retrieve data in Oracle using the equality operator (=) on a numeric column to identify clients who are married, assuming `married_flag` is a numeric column where 0 represents married clients:

SELECT *
FROM org_clients
WHERE married_flag = 0;

To retrieve data in Oracle using the equality operator (=) on a character column to identify female clients, assuming `gender` is a character column where 'F' represents female clients:

SELECT *
FROM org_clients
WHERE gender = 'F';

To retrieve data in Oracle using the greater than (`>`) comparison operator on a numeric column to identify clients with a credit limit greater than $1,000, assuming `credit_limit` is a numeric column:

SELECT *
FROM org_clients
WHERE credit_limit > 1000;

To retrieve data in Oracle using the less than (`<`) comparison operator on a numeric column to identify clients with a credit limit less than $1,000, assuming `credit_limit` is a numeric column:

SELECT *
FROM org_clients
WHERE credit_limit < 1000;

To retrieve data in Oracle using the greater than or equal to (`>=`) comparison operator on a numeric column to identify clients with a credit limit greater than or equal to $1,000, assuming `credit_limit` is a numeric column:

SELECT *
FROM org_clients
WHERE credit_limit >= 1000;

To retrieve data in Oracle using the greater than or equal to (`>=`) comparison operator on a numeric column to identify clients with a credit limit less than or equal to $1,000, assuming `credit_limit` is a numeric column:

SELECT *
FROM org_clients
WHERE credit_limit <= 1000;

To retrieve data in Oracle using the not equal to (`<>`) comparison operator on a numeric column to identify clients who are married, assuming `married_flag` is a numeric column where 0 represents clients who are not married:

SELECT *
FROM org_clients
WHERE married_flag <> 0;

To retrieve data in Oracle using the not equal to (`<>`) comparison operator on a character column to identify clients who are not males, assuming `gender` is a character column where 'M' represents male clients:

SELECT *
FROM org_clients
WHERE gender <> 'M';

To retrieve data in Oracle using the logical operator `AND` to identify unmarried male clients, assuming `married_flag` is a numeric column where 0 represents unmarried clients and `gender` is a character column where 'M' represents male clients:

SELECT *
FROM org_clients
WHERE married_flag = 0
AND gender = 'M';

To retrieve data in Oracle using the logical operator `OR` to find orders that are in OPEN status or orders that have not been shipped:

SELECT *
FROM act_order
WHERE order_status_id = 1 -- Open
OR shipped_date IS NULL;

To retrieve data in Oracle using the logical operator `LIKE` to find clients whose last name starts with 'MA':

SELECT *
FROM org_client
WHERE last_name LIKE 'MA%';

To retrieve data in Oracle using the logical operator `LIKE` to find clients whose last names contain the string 'MA' in any position:

SELECT *
FROM org_client
WHERE last_name LIKE '%ma%';

To retrieve data in Oracle using the logical operator `NOT LIKE` to find clients whose last names do not start with 'MA':

SELECT *
FROM org_client
WHERE last_name NOT LIKE 'MA%';

To retrieve data in Oracle using the logical operator `BETWEEN` to find clients whose credit limit is between 2000 and 5000 (inclusive):

SELECT *
FROM org_client
WHERE credit_limit BETWEEN 2000 AND 5000;

To retrieve orders ordered between January 1st and December 31st in Oracle using the logical operator `BETWEEN`:

SELECT *
FROM orders
WHERE order_date BETWEEN TO_DATE('2022-01-01', 'YYYY-MM-DD') AND TO_DATE('2022-12-31', 'YYYY-MM-DD');

To retrieve data in Oracle using the `IN` operator to find clients whose city is either 'Albany', 'Buffalo', or 'Niagara Falls':

SELECT *
FROM org_client
WHERE city IN ('Albany', 'Buffalo', 'Niagara Falls');

To retrieve data in Oracle using the logical operator NOT IN to find clients who do not live in 'Albany' or 'Rochester':

SELECT *
FROM org_client
WHERE city NOT IN ('Rochester', 'Albany');

To retrieve data in Oracle using the logical operator IS NULL to list orders that are not yet shipped: plaintext

SELECT *
FROM act_order
WHERE shipped_date IS NULL;

To retrieve data in Oracle using the logical operator IS NOT NULL to list orders that have been shipped:

SELECT *
FROM act_order
WHERE shipped_date IS NOT NULL;

To retrieve data in Oracle for orders where the shipment status is not NULL (indicating they have been shipped) and the order date is within the last 30 days:

SELECT *
FROM act_order
WHERE shipped_date IS NOT NULL
AND order_date > SYSDATE - 30;

To retrieve data in Oracle using the logical operator EXISTS to fetch clients who have placed orders, excluding clients who do not have any orders:

SELECT *
FROM org_client
WHERE EXISTS (SELECT * FROM act_order WHERE org_client.client_id = act_order.client_id);

To retrieve data in Oracle using the logical operator NOT EXISTS to fetch clients who do not have any associated orders:

SELECT *
FROM org_client
WHERE NOT EXISTS (SELECT * FROM act_order WHERE org_client.client_id = act_order.client_id);

To retrieve data in Oracle to find products that were sold with a quantity greater than one using the ANY operator:

SELECT *
FROM prd_product
WHERE product_id = ANY (SELECT product_id FROM act_order_detail WHERE quantity > 1);

Example 1:

Let's explore the procedure of retrieving information from a designated table using the SQL WHERE cluase with string data. Fetch records for clients identified as females.

Example 1 - Raw data from client table

Example 1 Raw data from client table

Example 1 - Query

SELECT *
FROM org_client
WHERE gender = 'F';

Example 1 - Query data mapping

Example 1 Query data mapping

In the provided image, the green color signifies the data that has been selected or satisfies the conditions specified in our query. Data points in red indicate information that does not meet the criteria set by the query.

Example 1 - Query Output

Example 1 Query Output

Example 2:

Let's explore the procedure of extracting information from a designated table using WHERE clause with numeric data. Fetch orders with statuses different from 5.

Example 2 - Raw data from orders table

Example 2 Raw data from orders table

Example 2 - Query

SELECT *
FROM act_order
WHERE order_status_id != 5;

Example 2 - Query data mapping

Example 2 Query data mapping

In the provided image, the green color signifies the data that has been selected or satisfies the conditions specified in our query. Data points in red indicate information that does not meet the criteria set by the query.

Example 2 - Query Output

Example 2 Query Output
Previous Next
Comments(0 comments)

Comments Not Found

Related Topics

Topics not Found

Most visited
Oracle SELECT * 48 views
Oracle WHERE Clause 37 views
Oracle FETCH OFFSET 29 views
Oracle Comparison Operators 25 views
Oracle Data Types 24 views
Oracle COUNT, SUM, AVG, MIN, MAX Functions 22 views