Designing Queries with Multiple Criteria
Master the art of asking precise questions to extract exactly what you need from your database!
What is a Query? (The Question)
The Concept: A query is a specific "question" you ask the database. It's like having a conversation with your data!
The "Subset" Idea: Queries don't create new data; they simply display a specific view of existing data. Think of it as putting filters on a camera lens.
Type "Blue Shoes" to see how a query scans the database and returns only matching results!
The Logic of "AND" vs. "OR" (Objective 12)
The "AND" Criteria (The Specialist)
Both conditions must be true. Example: Color = "Red" AND Size = "Large"
Result: Fewer, more specific records. Think of it as narrowing down your search.
The "OR" Criteria (The Generalist)
Either condition can be true. Example: City = "Kingston" OR City = "Montego Bay"
Result: More records, broader search. Think of it as expanding your options.
Drag criteria into the circles and toggle between AND/OR to see how results change!
AND: Both conditions must be true (overlap area)
OR: Either condition can be true (entire circles)
Using Relational Operators
The Symbols
- > (Greater than)
- < (Less than)
- >= (Greater than or equal to)
- <= (Less than or equal to)
- <> (Not equal to)
Complex Combinations
Example: Price > 50 AND Price < 100 finds items in a specific price range.
Drag the handles to set a price range. Watch the query criteria update in real-time!
Querying Text: Wildcards and Exact Matches
Exact Match
Searching for exact text: "Grade 11" will only find records with exactly "Grade 11".
The Wildcard (* or %)
- Sm* finds Smith, Small, and Smart
- *son finds Johnson, Thompson, and Mason
- A*e finds Anne, Alice, and Adele
- *car* finds racecar, cardboard, and car
Type a search pattern with asterisks (*) to see which student names match your wildcard!
Matches found: 0 students
The Design Grid (Query View)
Anatomy of the Grid
- Field: Which column are we looking at?
- Table: Which table does it come from?
- Sort: Ascending (A-Z) or Descending (Z-A)?
- Show: Do we want this column to appear in the final answer?
- Criteria: The actual rules we are setting
Build a query to solve: "Find all students who scored over 80 in Math"
Available Fields
Query Design Grid
| Field | Table | Sort | Show | Criteria |
|---|
CSEC SBA Focus: Multi-Table Queries
The Joins
Queries can pull data from two related tables. Example: Student Name from Students table and Grade from Grades table.
Common Error
Forgetting to link tables results in a Cartesian Product - every student matched with every grade (incorrect!).
Watch how data from two tables combines correctly when properly linked!
Students
StudentID: 1001
Name: Maria
Grade: 11
Grades
GradeID: 501
StudentID: 1001
Subject: Math
Score: 92
Query Result (Correct Join)
Maria (StudentID: 1001) scored 92 in Math
The tables are linked by StudentID, so each student is correctly matched with their grades.
Cartesian Product (Wrong - No Link)
If tables aren't linked, you'd get:
• Maria matched with ALL grades
• David matched with ALL grades
• Every student × every grade = Incorrect results!
Knowledge Check: The Query Master
Logic Challenge
"You need to find all employees who work in the 'Sales' department AND earn more than $3000."
Which criteria line is correct?
The "Fix the Formula" Quiz
A query is returning 0 results. Look at the criteria below and explain why it's logically impossible: