Be able to perform searches using a variety of operators including AND, OR, NOT, LIKE, >, <, =, >=, <=

18 Databases – Performing Searches with Operators

Learning Objective

By the end of this lesson you will be able to perform searches in a database using a variety of operators, including AND, OR, NOT, LIKE, >, <, =, >= and <=.

Why Operators Matter

Operators let you define the criteria that records must meet to be retrieved. Using the right combination of operators makes your queries precise and efficient.

Common Search Operators

Using Operators in Practice

1. Combining AND and OR

When mixing AND and OR, use parentheses to control the order of evaluation.

Example: Find products that are either in the “Electronics” category and cost less than $100, or are in the “Books” category.

WHERE (category = 'Electronics' AND price < 100) OR category = 'Books'

2. Excluding with NOT

Example: Retrieve all customers who are not from Canada.

WHERE NOT country = 'Canada'

3. Pattern Matching with LIKE

Wildcards:

• % – any sequence of characters (including none)
• _ – exactly one character

Examples:

• Names that start with “J”: WHERE name LIKE 'J%'
• Phone numbers ending in “1234”: WHERE phone LIKE '%1234'
• Postcodes that have a letter followed by two digits: WHERE postcode LIKE '_##'

4. Numerical Comparisons

Use >, <, =, >=, <= to compare numeric fields such as price, quantity, scores, dates, etc.

Example: Students who scored between 70 and 90 (inclusive).

WHERE score >= 70 AND score <= 90

Search Scenarios

1. Find all orders placed in 2023 that have a total amount greater than $500.
2. List employees whose last name begins with “S” and who are not in the “Sales” department.
3. Show products that are either out of stock (quantity = 0) or discontinued (status = 'Discontinued').
4. Retrieve customers from any city that starts with “New” (e.g., New York, Newcastle).

Practice Exercises

Write the SQL WHERE clause for each of the following requirements.

1. Students who have a grade of “A” and have completed more than 30 credits.
2. Books whose title contains the word “History” but not the word “World”.
3. Products priced between \$20 and \$50 inclusive, and that are in stock (quantity > 0).
4. Orders placed before 1 January 2022 or after 31 December 2022.
5. Employees whose department is either “HR” or “Finance”, but who are not managers (role != 'Manager').

Key Points to Remember

• Use AND when all conditions must be true; use OR when any condition can be true.
• Enclose combined conditions in parentheses to avoid logical errors.
• NOT reverses a condition; it can be combined with other operators.
• LIKE works with wildcards % (any length) and _ (single character).
• Numeric comparisons use >, <, =, >=, <= and can be chained with AND for ranges.