A3.3.2 Construct queries between two tables in SQL.

📚 You can find additional information related to these learning standards in the course companion pages 195 to 204

The Big Idea

Relational databases are powerful because they let us query data across multiple related tables. This is done using joins, relational operators, filtering, and pattern matching in SQL. Complex queries can combine results, apply conditions, sort rows, and group data—all while preserving clarity and precision.

To do this, SQL provides a rich set of commands such as SELECT, JOIN, WHERE, ORDER BY, LIKE, and others. In this article, we’ll explore how to construct queries between two tables using the required SQL features.

---

Sample Schema

students Table

student_id	name	grade_level
1001	Alice Smith	11
1002	Bob Zhang	12

enrollments Table

enrollment_id	student_id	course_name	enrollment_date
2001	1001	Computer Science	2024-09-01
2002	1002	Math	2024-09-01

---

1. JOIN – Combining Two Tables

To query data from two tables, use a JOIN to match rows where a foreign key in one table corresponds to a primary key in another.

SELECT students.name, enrollments.course_name
FROM students
JOIN enrollments ON students.student_id = enrollments.student_id;

---

2. Relational Operators – Filtering Data by Value

Relational operators: =, >, <, >=, <=, <>

SELECT name, grade_level
FROM students
WHERE grade_level >= 11;

---

3. Filtering with AND, OR, NOT

SELECT name
FROM students
WHERE grade_level >= 11 AND name NOT LIKE 'A%';

• AND: both conditions must be true
• OR: at least one must be true
• NOT: negates a condition

---

4. Pattern Matching with LIKE and %

SELECT name
FROM students
WHERE name LIKE 'A%';

• % is a wildcard: 'A%' means “starts with A”
• '%.com' would match emails ending in “.com”

---

5. BETWEEN – Filtering by Ranges

SELECT *
FROM enrollments
WHERE enrollment_date BETWEEN '2024-09-01' AND '2024-12-31';

---

6. ORDER BY – Sorting Results

SELECT name, grade_level
FROM students
ORDER BY grade_level DESC;

• ASC (default): ascending order
• DESC: descending order

---

7. DISTINCT – Removing Duplicate Results

SELECT DISTINCT course_name
FROM enrollments;

---

8. GROUP BY and HAVING – Aggregating Data

SELECT course_name, COUNT(*) AS num_enrolled
FROM enrollments
GROUP BY course_name
HAVING COUNT(*) > 1;

• GROUP BY groups rows by a column
• HAVING filters grouped results (like WHERE but used after grouping)

---

Example Query – Putting It All Together

Question: Find the names of students in grade 11 or higher who are enrolled in any course that starts with “Comp”, and sort the results alphabetically.

SELECT DISTINCT students.name, enrollments.course_name
FROM students
JOIN enrollments ON students.student_id = enrollments.student_id
WHERE students.grade_level >= 11
  AND enrollments.course_name LIKE 'Comp%'
ORDER BY students.name ASC;

---

Summary

This topic trains you to construct multi-table queries using essential SQL features. You should be able to:

• Use JOIN to combine data from related tables
• Apply filters using WHERE, BETWEEN, LIKE, and logical operators
• Sort results using ORDER BY
• Use aggregation with GROUP BY and HAVING
• Eliminate duplicates with DISTINCT

These tools are core to relational data retrieval and mastering them enables you to extract meaningful insights from structured data.

 

Please remember: SQL queries allow us to retrieve information from a relational database by specifying what data we want and how different tables relate to each other. When queries involve two tables, we must explicitly describe the relationship between them using a JOIN. Once the tables are connected, we can filter rows, match patterns, group results, and order the output.

Below is a glossary-style table with definitions and examples appropriate for learning to construct multi-table SQL queries.

Term	Definition	Example
SELECT	Specifies which columns should appear in the output of the query. It is the starting point of almost every SQL query.	SELECT student_name, course_name FROM Students JOIN Enrollments ON Students.student_id = Enrollments.student_id;
DISTINCT	Removes duplicate rows from the result set so that each returned value appears only once.	SELECT DISTINCT course_id FROM Enrollments;
FROM	Identifies the table (or tables) where the data is stored. When using joins, multiple tables are referenced.	SELECT * FROM Students;
JOIN	Combines rows from two tables based on a related column (usually a primary key–foreign key relationship).	SELECT Students.student_name, Courses.course_name FROM Students JOIN Enrollments ON Students.student_id = Enrollments.student_id JOIN Courses ON Enrollments.course_id = Courses.course_id;
WHERE	Filters rows based on a condition. Only rows that meet the condition are returned.	SELECT student_name FROM Students WHERE grade_level = 11;
Relational Operators	Comparison operators used in conditions to compare values (e.g., =, >, <, >=, <=, <>).	SELECT * FROM Courses WHERE credits > 3;
BETWEEN	Filters results within a specified range (inclusive).	SELECT student_name FROM Students WHERE age BETWEEN 16 AND 18;
LIKE	Used for pattern matching in text fields. Often used with %, which represents any number of characters.	SELECT student_name FROM Students WHERE student_name LIKE 'A%';
% (Wildcard)	Represents zero or more characters in pattern matching with LIKE.	'A%' matches Alice, Andrew, Ava
AND	Combines multiple conditions; all conditions must be true for the row to be returned.	SELECT * FROM Students WHERE grade_level = 11 AND gpa > 3.5;
AS	Assigns a temporary alias to a column or table name within a query. This improves readability and simplifies referencing long names.	SELECT student_name AS name FROM Students;
OR	Combines conditions where at least one condition must be true.	SELECT * FROM Students WHERE grade_level = 11 OR grade_level = 12;
NOT	Negates a condition so that rows that do not match the condition are returned.	SELECT * FROM Courses WHERE NOT department = 'History';
ORDER BY	Sorts the result set by one or more columns.	SELECT student_name, gpa FROM Students ORDER BY gpa DESC;
ASC	Sorts results in ascending order (smallest to largest). This is the default sorting order.	ORDER BY gpa ASC
DESC	Sorts results in descending order (largest to smallest).	ORDER BY gpa DESC
GROUP BY	Groups rows that have the same values in specified columns, often used with aggregate functions like COUNT or AVG.	SELECT course_id, COUNT(student_id) FROM Enrollments GROUP BY course_id;
HAVING	Filters grouped results after GROUP BY has been applied.	SELECT course_id, COUNT(student_id) FROM Enrollments GROUP BY course_id HAVING COUNT(student_id) > 10;

A complete two-table query example might look like this:

SELECT Students.student_name, Courses.course_name
FROM Students
JOIN Enrollments ON Students.student_id = Enrollments.student_id
JOIN Courses ON Enrollments.course_id = Courses.course_id
WHERE Courses.department = 'Computer Science'
AND Students.gpa > 3.0
ORDER BY Students.student_name ASC;

This query demonstrates the core mechanics students must master for A3.3.2:

• JOIN connects the tables.
• WHERE filters the results.
• AND combines conditions.
• ORDER BY sorts the output.