Computer Science - ICT Grade 8 SQL SELECT WHERE JOIN Queries

Study Guide: SQL Queries (SELECT, WHERE, JOIN) – Grade 8 Computer Science/ICT

1. The Driving Question

"If a school’s database has thousands of student records, club sign-ups, and attendance logs, how do you pull out just the info you need—like ‘Which 8th graders are in Robotics Club and have perfect attendance?’—without scrolling through every single file? And how does the computer ‘know’ which pieces of data belong together?"

2. The Core Idea – Built, Not Listed

Imagine your school’s database is like a giant Lego city where every brick is a piece of data. The students table is one Lego set (with bricks labeled student_id, name, grade), the clubs table is another (with club_id, club_name, meeting_day), and the attendance table is a third (student_id, date, status). You don’t need to rebuild the whole city to answer a question—you just need to snap the right bricks together using SQL.

• SELECT is like your shopping list: "I only want the name and grade bricks from the students set."
• WHERE is your filter: "Only grab the bricks where grade = 8."
• JOIN is the glue: "Now connect the student_id brick from the students set to the student_id brick in the attendance set so I can see who was present."

Key Vocabulary:
- Query: A question you ask the database (e.g., "Show me all 8th graders in Robotics Club").
- Example: Instead of asking your teacher for a list of everyone in the school play, you’d write a query to pull just the cast members from the drama club table.
- Table: A structured set of data (like a spreadsheet with rows and columns).
- Example: Your school’s lunch menu table has columns for date, main_dish, and allergens.
- Grade 9–12 note: In college, tables can have complex relationships (e.g., "many-to-many" between students and clubs), and SQL joins become more nuanced (e.g., LEFT JOIN vs. INNER JOIN).
- Primary Key: A unique identifier for each row in a table (like a student ID number).
- Example: In a library books table, the ISBN is the primary key—no two books share the same one.
- Foreign Key: A column in one table that links to the primary key of another table.
- Example: The student_id in the attendance table is a foreign key that points to the student_id in the students table.

3. Assessment Translation

How This Appears on State/ICT Assessments (Grade 8):
- Multiple Choice: Questions test syntax (e.g., "Which query returns all students in grade 8?") with distractors like: - Missing WHERE clause (returns all students).
- Wrong column name (e.g., SELECT grade instead of SELECT name).
- Incorrect join logic (e.g., joining student_id to club_id).
- Short Answer: "Write a query to list the names of students in the Chess Club who have missed fewer than 3 days of school." - Proficient response:
sql
SELECT s.name
FROM students s
JOIN clubs c ON s.student_id = c.student_id
JOIN attendance a ON s.student_id = a.student_id
WHERE c.club_name = 'Chess Club'
GROUP BY s.name
HAVING COUNT(CASE WHEN a.status = 'Absent' THEN 1 END) < 3; - Developing response: Omits the JOIN or miscounts absences (e.g., WHERE a.status = 'Absent' without grouping).
- Performance Task: Given a database schema (e.g., pets, owners, vet_visits), write queries to answer questions like "Which owners have cats that visited the vet in 2023?"

Model Proficient Response (Short Answer):
Prompt: "Write a query to find all 7th graders who are in the Science Olympiad club."

SELECT s.name
FROM students s
JOIN clubs c ON s.student_id = c.student_id
WHERE s.grade = 7 AND c.club_name = 'Science Olympiad';

Why it’s proficient: - Correctly uses JOIN to link tables.
- Filters with WHERE for both grade and club.
- Returns only the name column (not all data).

4. Mistake Taxonomy

Mistake 1: Forgetting the JOIN Condition
- Prompt: "List all students and their club names." - Common Wrong Response: sql SELECT s.name, c.club_name FROM students s, clubs c; - Why It Loses Credit: This creates a Cartesian product (every student paired with every club), not a meaningful connection. The query runs but returns nonsense (e.g., "Ava" paired with "Robotics" and "Chess").
- Correct Approach: sql SELECT s.name, c.club_name FROM students s JOIN clubs c ON s.student_id = c.student_id; Key: Always specify how tables relate (ON s.student_id = c.student_id).

Mistake 2: Misusing WHERE for Aggregations
- Prompt: "Find students who have missed more than 2 days." - Common Wrong Response: sql SELECT s.name FROM students s JOIN attendance a ON s.student_id = a.student_id WHERE a.status = 'Absent' > 2; - Why It Loses Credit: WHERE filters rows before grouping, so this checks if a single absence record is "greater than 2" (which makes no sense). It doesn’t count absences.
- Correct Approach: sql SELECT s.name FROM students s JOIN attendance a ON s.student_id = a.student_id WHERE a.status = 'Absent' GROUP BY s.name HAVING COUNT(a.status) > 2; Key: Use HAVING for conditions after grouping.

Mistake 3: Joining on the Wrong Columns
- Prompt: "List all students and their homeroom teachers." - Common Wrong Response: sql SELECT s.name, t.teacher_name FROM students s JOIN teachers t ON s.grade = t.grade; - Why It Loses Credit: This joins students and teachers by grade, which pairs all 8th graders with all 8th-grade teachers (e.g., "Ava" with "Mr. Smith" and "Ms. Lee"). It should join on homeroom_id or similar.
- Correct Approach: sql SELECT s.name, t.teacher_name FROM students s JOIN teachers t ON s.homeroom_id = t.homeroom_id; Key: Join on the specific column that links the tables (e.g., homeroom_id, not grade).

5. Connection Layer

1. Within Computer Science → Algorithms: SQL queries are like recipes—the SELECT is the ingredient list, WHERE is the filter (e.g., "no nuts"), and JOIN is combining steps from different recipes. Understanding queries helps you design efficient algorithms for sorting and searching data.
2. Across Subjects → Statistics: A GROUP BY query is like calculating the mean or mode in math. For example, SELECT club_name, AVG(grade) FROM students GROUP BY club_name finds the average grade per club—just like finding the average test score per class.
3. Outside School → Online Shopping: When you filter products on Amazon ("under $20," "4+ stars"), the website runs a SELECT with WHERE clauses. A JOIN happens when it shows you products and reviews together—just like linking students to their club sign-ups.

6. The Stretch Question

"If you run SELECT * FROM students JOIN clubs ON students.student_id = clubs.student_id, but some students aren’t in any clubs, their names disappear from the results. Why? And how would you modify the query to show all students, even those without clubs?"

Pointer Toward the Answer: This happens because JOIN (or INNER JOIN) only returns rows where there’s a match in both tables. To include students without clubs, you’d use a LEFT JOIN:

SELECT s.name, c.club_name
FROM students s
LEFT JOIN clubs c ON s.student_id = c.student_id;

Now, students without clubs appear with NULL for club_name. This is a key difference between INNER JOIN and LEFT JOIN—one is exclusive, the other inclusive. (Bonus: What would RIGHT JOIN do here?)