Study Notes on SQL Select and Where Clause

Select Statement Basics

  • The focus of this section is on the select clause of the select statement and an exploration of the where clause.

Overview of the WHERE Clause

  • Syntax: The syntax for the where clause is straightforward: it consists of the keyword WHERE followed by a condition.

  • Types of Conditions:

    • Comparing columns with each other.

    • Checking if columns hold a NULL value.

    • Using the BETWEEN operator to check if a value lies between two endpoints.

    • Performing pattern matching operations.

Functionality

  • When the conditions specified in the where clause are true for any given row, that row will be included in the results of the select statement.

Arithmetic Operations

  • Arithmetic operations usable directly within a select statement are also applicable in the where clause.

SQL Developer Example: Employee Information Retrieval

  • Scenario: Retrieve employee information for Douglas Grant from the employees table.

    • Steps to retrieve information:

    1. Open the HR DB connection in SQL Developer.

    2. Expand the tables tree to view tables available.

    3. Select the departments table to review columns.

    4. Check the employees table for available columns, particularly the last name column.

  • Execution of Query:

    • Move to the SQL worksheet to construct the select statement filtering on the last name.

    • Important Note: String constants should maintain case sensitivity when enclosed in single quotes.

    • Running this initial select statement results in two rows being returned for the last name, which leads to a need for refinement of the where clause.

Additional Capabilities in the WHERE Clause

  • Beyond basic conditional operations, the where clause allows for:

    • Relational Operators: Compare one column to another column or a numeric value, constant, or character string.

    • Example: To find employees earning more than $20,000, the statement filters to show only one employee, Stephen King.

    • Logical Operators: including AND, OR, and NOT to combine multiple conditions.

    • Pattern Matching Using LIKE:

    • The percent sign (%) matches zero or more characters.

    • The underscore (_) matches exactly one character.

    • BETWEEN Operator: Shorthand for determining if a column value is within a specified range (greater than or equal to the first value and less than or equal to the second value).

    • IN Operator: Checks if a value exists within a specified list, functionally equivalent to multiple equality comparisons joined by OR.

    • NULL Checks: You can explicitly check if a value in a column is NULL or NOT NULL. This is the sole method to perform such checks.

Refinement of the Previous Query

  • Following the observation that two rows returned in the first query, additional refinement is necessary.

    • Solution: Add an AND condition to also filter on the first name, specifically checking for Douglas.

    • After running this refined query, only one row is returned, showing the successful filtering technique.

Best Practices in Constructing the WHERE Clause

  • The importance of constructing an effective where clause is emphasized to avoid returning no rows.

  • Aim to configure the where clause to retrieve the necessary rows from the select statement effectively.

  • Failure to do so may yield no results, which contradicts the typical expectation of getting some output from a select operation.