Queries Between 2 Tables

What Are SQL Queries?

SQL queries are used to retrieve and manipulate data stored in relational databases.

To efficiently retrieve data from multiple tables, SQL supports joins, filtering, ordering, and pattern matching.

Essential SQL Commands

SQL Command	Purpose
`SELECT`	Retrieves data from tables
`DISTINCT`	Removes duplicate values from results
`FROM`	Specifies the table(s) to retrieve data from
`WHERE`	Filters results based on conditions
`BETWEEN`	Checks if a value falls within a range
`ORDER BY`	Sorts results in ascending or descending order
`ASC` / `DESC`	Defines sort direction in `ORDER BY`
`GROUP BY`	Groups data by one or more columns
`HAVING`	Applies conditions to grouped data
`JOIN`	Combines rows from multiple tables
`LIKE`	Performs pattern matching using wildcards (e.g. `%`)
`AND / OR / NOT`	Combines or negates logical conditions

Example: Student & Course Database

In these examples, we will use a 2-table scenario: Students and Enrolments

Sample Tables

        Students(StudentID, Name, Age)

        Enrolments(EnrolmentID, StudentID, CourseName, Grade)

The sample data has deliberately included a student (StudentID 6) who has not enrolled and an enrolment (EnrolmentID 108) for a student not in the Students table. This is to highlight how different JOINs match and retrieve data.

Sample Data: Students

StudentID	Name	Age
1	Alice	20
2	Bob	22
3	Amira	19
4	Leo	17
5	Ava	21
6	Marc	21

Sample Data: Enrolments

EnrolmentID	StudentID	CourseName	Grade
101	1	Maths	A
102	2	Physics	B
103	3	Chemistry	A
104	1	Physics	B
105	4	Biology	C
106	5	Maths	A
107	3	Maths	B
108	7	Biology	B

Example: Student & Course Database

In these examples, we will use a 2-table scenario: Students and Enrolments

Sample Tables

        Students(StudentID, Name, Age)

        Enrolments(EnrolmentID, StudentID, CourseName, Grade)

The sample data deliberately includes a student (StudentID 6) with no enrolments and an enrolment (EnrolmentID 108) whose StudentID is not in the Students table. This highlights how different JOINs match and retrieve data.

Sample Data: Students

StudentID	Name	Age
1	Alice	20
2	Bob	22
3	Amira	19
4	Leo	17
5	Ava	21
6	Marc	21

Sample Data: Enrolments

EnrolmentID	StudentID	CourseName	Grade
101	1	Maths	A
102	2	Physics	B
103	3	Chemistry	A
104	1	Physics	B
105	4	Biology	C
106	5	Maths	A
107	3	Maths	B
108	7	Biology	B

Constructing SQL Queries (Grouped)

Explore each group, then switch sub-tabs to compare variations.

INNER JOIN (only matches in both tables)

                                SELECT s.Name, e.CourseName

                                FROM Students s

                                INNER JOIN Enrolments e ON s.StudentID = e.StudentID;

Result

Why these rows?
Only student–enrolment pairs where the StudentID exists in both tables. Marc (ID: 6) is excluded (no enrolments); Enrolment 108 is excluded (no matching student).

Name	CourseName
Alice	Maths
Alice	Physics
Bob	Physics
Amira	Chemistry
Amira	Maths
Leo	Biology
Ava	Maths

LEFT JOIN (keep all Students; show NULL if no match)

                                SELECT s.Name, e.CourseName

                                FROM Students s

                                LEFT JOIN Enrolments e ON s.StudentID = e.StudentID;

Here, Marc has no enrolments, so LEFT JOIN includes him with NULL for CourseName.

Result

Why these rows?
Keeps all Students. IDs 1–5 show their courses; Marc (ID: 6) has no matching enrolment, so CourseName = NULL.

Name	CourseName
Alice	Maths
Alice	Physics
Bob	Physics
Amira	Chemistry
Amira	Maths
Leo	Biology
Ava	Maths
Marc	NULL

RIGHT JOIN (keep all Enrolments; show NULL if no student match)

                                SELECT s.Name, e.CourseName

                                FROM Students s

                                RIGHT JOIN Enrolments e ON s.StudentID = e.StudentID;

Enrolment 108 has a StudentID not in Students, so it appears with NULL for Name.

Result

Why these rows?
Keeps all Enrolments. Matches for IDs 1–5 appear as in INNER; Enrolment 108 (StudentID 7) has no matching student, so Name = NULL.

Name	CourseName
Alice	Maths
Alice	Physics
Bob	Physics
Amira	Chemistry
Amira	Maths
Leo	Biology
Ava	Maths
NULL	Biology

ALL (no filter)

                                SELECT Name, Age

                                FROM Students;

Result

Why these rows?
No filter - returns every row from Students (6 rows).

Name	Age
Alice	20
Bob	22
Amira	19
Leo	17
Ava	21
Marc	21

Age ≥ 20 AND Age ≤ 21

                                SELECT Name, Age

                                FROM Students

                                WHERE Age >= 20 AND Age <= 21;

Result

Why these rows?
Age between 20 and 21 inclusive (Age ≥ 20 AND Age ≤ 21). Matches Alice (20), Ava (21), Marc (21); excludes 17, 19, 22.

Name	Age
Alice	20
Ava	21
Marc	21

Maths OR Physics

                                SELECT s.Name, e.CourseName

                                FROM Students s

                                JOIN Enrolments e ON s.StudentID = e.StudentID

                                WHERE e.CourseName = 'Maths' OR e.CourseName = 'Physics';

Result

Why these rows?
After joining, keeps only rows where CourseName is ‘Maths’ OR ‘Physics’. Chemistry and Biology rows are filtered out.

Name	CourseName
Alice	Maths
Alice	Physics
Bob	Physics
Ava	Maths
Amira	Maths

NOT BETWEEN 18 AND 22

                                SELECT Name, Age

                                FROM Students

                                WHERE NOT Age BETWEEN 18 AND 22;

Result

Why these rows?
Ages outside 18–22 (inclusive). Only Leo (17) qualifies; Bob (22) and others within 18–22 are excluded because BETWEEN is inclusive.

Name	Age
Leo	17

Age BETWEEN 18 AND 22

                                SELECT Name, Age

                                FROM Students

                                WHERE Age BETWEEN 18 AND 22;

Result

Why these rows?
Inclusive bounds: returns ages 18, 19, 20, 21, 22 - everyone except Leo (17).

Name	Age
Alice	20
Bob	22
Amira	19
Ava	21
Marc	21

Name LIKE "A%"

                                SELECT Name

                                FROM Students

                                WHERE Name LIKE 'A%';

Result

Why these rows?
Prefix match: Name LIKE 'A%' → Alice, Amira, Ava.

Name
Alice
Amira
Ava

ORDER BY Name ASC

                                SELECT Name, Age

                                FROM Students

                                ORDER BY Name ASC;

Result

Why these rows?
Same rows as ALL, ordered alphabetically by Name (A→Z).

Name	Age
Alice	20
Amira	19
Ava	21
Bob	22
Leo	17
Marc	21

ORDER BY Age DESC

                                SELECT Name, Age

                                FROM Students

                                ORDER BY Age DESC;

Result

Why these rows?
Same rows as ALL, ordered by Age high→low. Ties at the same age (e.g. Ava/Marc) keep database-default order unless a secondary sort is added.

Name	Age
Bob	22
Ava	21
Marc	21
Alice	20
Amira	19
Leo	17

DISTINCT Course Names

                                SELECT DISTINCT CourseName

                                FROM Enrolments;

Result

Why these rows?
Lists unique CourseName values from Enrolments; duplicate names removed.

CourseName
Maths
Physics
Chemistry
Biology

GROUP → HAVING → ORDER

                                SELECT CourseName, COUNT(StudentID) AS Total

                                FROM Enrolments

                                GROUP BY CourseName

                                HAVING COUNT(StudentID) > 1

                                ORDER BY Total DESC;

Result

Why these rows?
After grouping, keeps only courses with COUNT(StudentID) > 1, then orders by Total desc → Maths (3), Physics (2), Biology (2).

CourseName	Total
Maths	3
Physics	2
Biology	2

Why Are SQL Queries Important?

Joins: Retrieve related data from multiple tables.
Filtering: Extract specific data based on conditions.
Sorting & Ordering: organise query results efficiently.
Pattern Matching: Search for data using wildcards.
Grouping Data: Summarize data for reports and analytics.

Key Takeaways

SQL allows querying multiple tables using joins and relational operators.
Common SQL commands include SELECT, DISTINCT, WHERE, JOIN, ORDER BY, GROUP BY, HAVING, BETWEEN, LIKE, AND, OR, NOT.
Pattern matching with LIKE and wildcards % allows flexible searches.
Grouping and aggregating data using GROUP BY and HAVING is useful for summarizing information.