Overview
This lecture covers how to use the SQL WHERE statement to search for specific records or records meeting certain criteria, including how to combine conditions and the importance of parentheses.
Using the WHERE Statement
- The WHERE statement filters records in a SQL query based on specified criteria.
- To search for a specific record, use WHERE with the column and value (e.g.,
passenger.name = "Thomas Kiley").
- The WHERE clause can be combined with logical operators like AND and OR for multiple conditions.
Combining Multiple Criteria
- Use AND to require that multiple conditions are true for a record (e.g., name is "Thomas Kiley" AND ticket price > 1000).
- Use OR to require that at least one of several conditions is true (e.g., ticket price > 1000 OR miles > 5000).
Importance of Parentheses in WHERE Clauses
- Parentheses group conditions and control the order in which SQL evaluates them.
- Without parentheses, SQL may misinterpret your intended logic, leading to incorrect results.
- Example: To find records where the name is "Thomas Kiley" AND (ticket price > 1000 OR miles > 5000), use parentheses to group the OR conditions.
Comparison Operators in WHERE Clauses
> means greater than.< means less than.>= means greater than or equal to.<= means less than or equal to.<> means not equal to.- These operators can be used for both numeric and string conditions in WHERE clauses.
Key Terms & Definitions
- WHERE statement — SQL clause used to filter records based on specified conditions.
- AND operator — logical operator that requires all combined conditions to be true.
- OR operator — logical operator that requires at least one condition to be true.
- Parentheses — used in SQL to group conditions and control the order of evaluation.
- Comparison operators — symbols like >, <, >=, <=, <> used to compare values in conditions.
Action Items / Next Steps
- Practice writing SQL queries with WHERE clauses using AND, OR, and parentheses.
- Experiment with different comparison operators in your queries.
- Review and save your queries to ensure correct results.