Be able to use a single criterion, or multiple criteria to select subsets of data using a query

Topic 18 – Databases

Objective

Be able to use a single criterion, or multiple criteria to select subsets of data using a query.

1. What is a Query?

A query is a request for information from a database. In SQL (Structured Query Language) the most common form of query is the SELECT statement, which retrieves rows that meet specified conditions.

2. Basic Structure of a SELECT Query

SELECT column1, column2, …
FROM table_name
WHERE condition;

The WHERE clause is where you define the criteria that rows must satisfy to be included in the result set.

3. Types of Criteria

• Single‑column criteria – test one field only.
• Multiple‑column criteria – combine two or more tests using logical operators.

4. Single‑Column Criteria

Common comparison operators:

• = – equal to
• <> or != – not equal to
• > – greater than
• < – less than
• >= – greater than or equal to
• <= – less than or equal to

5. Multiple‑Column Criteria

Combine conditions with logical operators:

• AND – both conditions must be true.
• OR – either condition may be true.
• NOT – reverses the truth value of a condition.

6. Special Operators for Text and Ranges

• LIKE – pattern matching with % (any number of characters) and _ (single character).
• BETWEEN … AND … – selects values within a range (inclusive).
• IN (value1, value2, …) – matches any value in a list.

7. Example Database – Students Table

8. Sample Queries

1. Single criterion – students who scored above 80:

   SELECT Name, Score
   FROM Students
   WHERE Score > 80;
   

2. Multiple criteria – female students aged 17 or 18 who study Mathematics:

   SELECT Name, Age, Course
   FROM Students
   WHERE Gender = 'F' 
   AND Age IN (17, 18) 
   AND Course = 'Mathematics';
   

3. Range criterion – scores between 70 and 85 inclusive:

   SELECT Name, Score
   FROM Students
   WHERE Score BETWEEN 70 AND 85;
   

4. Pattern matching – names that start with “A”:

   SELECT Name, Age
   FROM Students
   WHERE Name LIKE 'A%';
   

5. Combined AND/OR – students who either scored above 90 or are in the History course:

   SELECT Name, Score, Course
   FROM Students
   WHERE Score > 90 OR Course = 'History';
   

9. Step‑by‑Step Approach to Building a Query

1. Identify the information required (which columns?).
2. Choose the table that contains those columns.
3. Determine the condition(s) that define the subset.
4. Write the SELECT clause with the required columns.
5. Add the FROM clause with the table name.
6. Construct the WHERE clause using appropriate operators.
7. Test the query and adjust criteria if needed.

10. Practice Questions

1. Write a query to list the StudentID and Name of all male students who scored less than 70.
2. Using the same table, retrieve the names of students whose course is either “Science” or “History”.
3. Find all students whose names contain the letter “e” (any position) and whose age is greater than 17.
4. Show the Name and Score for students whose scores are not between 75 and 85.
5. List the distinct courses offered in the Students table.

11. Common Mistakes to Avoid

• Forgetting to enclose text values in single quotes (e.g., WHERE Gender = 'F').
• Using AND when OR is required, which can return an empty result set.
• Misplacing parentheses in complex conditions, leading to incorrect logical grouping.
• Using LIKE without the wildcard symbols, which behaves like an equality test.

12. Summary

Selecting subsets of data involves:

• Choosing the correct columns with SELECT.
• Specifying the source table with FROM.
• Applying one or more criteria in the WHERE clause using comparison, logical, and special operators.

Mastering these skills enables you to retrieve exactly the information you need from any database.