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:

• search a database using the operators AND, OR, NOT, LIKE, >, <, =, >=, <=;
• understand how searching fits into the wider database workflow (design, manipulation, presentation);
• recognise common pitfalls such as operator precedence, data‑type mismatches and syntax errors;
• apply basic e‑safety and data‑protection principles when querying personal data.

1. Why Searches Matter – The Wider Database Context

• Database design first: searches are performed on tables that have been created with appropriate field types (text, number, date, Boolean) and keys (primary/foreign). A well‑designed structure ensures that the correct operators can be used.
• Typical workflow (Section 18.2): select table → choose fields → set criteria (operators) → sort / filter → view results.
• Presenting the results (Section 18.3): after a search you may generate a report, add headings/footers, or export the data. Only the fields required for the task should be displayed.
• e‑Safety (Section 8.2/8.3): when personal or sensitive data are involved, retrieve only what is needed, keep the query secure and store results safely.

2. Operator Overview

3. Operator Precedence

When several operators appear in the same WHERE clause, SQL evaluates them in this order (the default precedence), unless parentheses are used to override it:

1️⃣ NOT  2️⃣ AND  3️⃣ OR

Always use parentheses to make the intended logic explicit and to avoid accidental results.

Example – Correct use of parentheses

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

Without the parentheses the query would be interpreted as:

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

which returns a different set of records.

4. Using the Operators in Practice

4.1 Combining AND, OR and NOT

• AND + OR: group the AND part(s) with parentheses.
  

  WHERE (status = 'Active' AND region = 'East') OR region = 'West'

• NOT with other operators: enclose the whole condition you wish to exclude.
  

  WHERE NOT (category = 'Books' OR category = 'Magazines')

4.2 Pattern Matching with LIKE (text fields only)

Wild‑cards:

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

Examples:

• Names that start with “J”: WHERE name LIKE 'J%'
• E‑mail addresses that end in “.edu”: WHERE email LIKE '%.edu'
• Postcodes with a letter followed by two digits: WHERE postcode LIKE '_##'

Note: Only % and _ are required for the IGCSE exam; other pattern syntax (e.g., []) is not needed.

4.3 Numerical, Date and Boolean Comparisons

• Numeric range: WHERE score >= 70 AND score <= 90
• Date range (ISO format yyyy‑mm‑dd): WHERE orderdate >= '2023-01-01' AND orderdate <= '2023-12-31'
• Boolean field: WHERE is_active = TRUE (no quotes required for TRUE/FALSE in most exam‑level DBMS)

4.4 Calculated Fields (Section 18.2 – Manipulate Data)

You can combine a search with a calculation in the SELECT list, for example:

SELECT product_name, price * quantity AS total
FROM Orders
WHERE price >= 20 AND price <= 50;

This demonstrates that searching, sorting and calculations are often used together.

4.5 Sorting Results (Section 18.2)

After setting the criteria you may order the output:

WHERE category = 'Books'
ORDER BY price DESC, title ASC;

5. Query‑Building Workflow (Checklist)

1. Select the table you need to search.
2. Identify the fields you want to display (SELECT clause).
3. Set the criteria using the appropriate operators (WHERE clause).
4. Control evaluation order with parentheses.
5. Apply sorting (ORDER BY) or limit the result set (LIMIT/TOP) if required.
6. Check syntax: quotes around text, ISO dates, balanced parentheses.
7. Run the query and verify the output against the expected result.
8. Present the data: add headings/footers, export to a report, or print as required (Section 18.3).

6. Common Errors & How to Avoid Them

• Missing quotes around text literals – e.g. WHERE country = UK causes an error; use 'UK'.
• Case‑sensitivity – Some DBMS treat text comparisons as case‑sensitive. Use UPPER() or LOWER() to standardise.
• Incorrect wildcard use – % matches any length; _ matches exactly one character.
• Operator precedence mistakes – Always test complex criteria with parentheses.
• Data‑type mismatches – Do not compare a date field with a number, or a text field with > unless lexical ordering is intended.
• Boolean syntax – Use TRUE/FALSE (no quotes) unless your DBMS requires otherwise.

7. Performance Tips (Optional – for efficient queries)

• Search on indexed fields (primary keys or frequently‑used columns) to speed up retrieval.
• Avoid leading wild‑cards in LIKE (e.g. LIKE '%smith') because they prevent index use.
• Limit the result set with LIMIT or TOP when only a subset is required.
• Combine conditions that can use the same index, e.g. WHERE price >= 20 AND price <= 50.

8. e‑Safety & Data‑Protection Reminder

When constructing queries that involve personal data (names, addresses, dates of birth, etc.), remember to:

• retrieve only the fields that are strictly necessary for the task;
• store query results securely and delete them when no longer needed;
• follow your school’s data‑protection policy and any relevant legislation (e.g., GDPR) – treat the data as “personal or sensitive”.

9. Search Scenarios (Apply What You’ve Learned)

1. Find all orders placed in 2023 that have a total amount greater than $500.
   

   WHERE orderdate BETWEEN '2023-01-01' AND '2023-12-31' AND totalamount > 500

2. List employees whose last name begins with “S” and who are not in the “Sales” department.
   

   WHERE last_name LIKE 'S%' AND NOT department = 'Sales'

3. Show products that are either out of stock (quantity = 0) or discontinued (status = 'Discontinued').
   

   WHERE quantity = 0 OR status = 'Discontinued'

4. Retrieve customers from any city that starts with “New” (e.g., New York, Newcastle).
   

   WHERE city LIKE 'New%'

5. Generate a report of students who scored between 70 and 90 inclusive, sorted by score descending.
   

   WHERE score >= 70 AND score <= 90 ORDER BY score DESC

10. Practice Exercises – Write the WHERE Clause

1. Students who have a grade of “A” and have completed more than 30 credits.
   

   WHERE grade = 'A' AND credits > 30

2. Books whose title contains the word “History” but not the word “World”.
   

   WHERE title LIKE '%History%' AND NOT title LIKE '%World%'

3. Products priced between \$20 and \$50 inclusive, and that are in stock (quantity > 0).
   

   WHERE price >= 20 AND price <= 50 AND quantity > 0

4. Orders placed before 1 January 2022 or after 31 December 2022.
   

   WHERE orderdate < '2022-01-01' OR orderdate > '2022-12-31'

5. Employees whose department is either “HR” or “Finance”, but who are not managers.
   

   WHERE (department = 'HR' OR department = 'Finance') AND NOT role = 'Manager'

11. Key Points to Remember

• AND – all conditions must be true.
• OR – any condition can be true.
• NOT – reverses a condition; usually combined with parentheses.
• Operator precedence (default): NOT → AND → OR. Use parentheses to override.
• LIKE uses % (any length) and _ (single character) wild‑cards; only these are required for the exam.
• Numeric and date comparisons use >, <, =, >=, <= and can be combined with AND for ranges.
• Text values need single quotes; dates should follow the ISO format yyyy‑mm‑dd; Boolean values are TRUE/FALSE (no quotes).
• Check syntax carefully – balanced parentheses, correct wild‑cards, matching data types.
• e‑safety: query only the data you need and keep results secure.
• After a search you may sort (ORDER BY), calculate fields, and present the data in a report.