Homework Assignment: Understanding MySQL Outer Joins

Objective:

To gain practical experience with LEFT OUTER JOIN and RIGHT OUTER JOIN in SQL$,$ understanding how they differ from INNER JOIN and each other.

Background:

Consider a small school database with two tables: Students and Courses.

Students Table:

student$\_$id $($Primary Key$)$: A unique identifier for each student.

student$\_$fname: The first name of the student.

student$\_$lname: The last name of student

Courses Table:

course$\_$id $($Primary Key$)$: A unique identifier for each course.

course$\_$name: The name of the course.

student$\_$id: The identifier of a student enrolled in the course $($Foreign Key, a student can enroll in multiple courses$).$

Tasks:

Create Tables: Write SQL queries to create the Students and Courses tables as per the structure provided.

Insert Data: Populate the tables with at least $5$ records in each. Ensure that not all students are enrolled in courses and not all courses have students.

LEFT OUTER JOIN Query: Write a query using LEFT OUTER JOIN to list all students along with the courses they are enrolled in$,$ including students who are not enrolled in any course.

RIGHT OUTER JOIN Query: Write a query using RIGHT OUTER JOIN to list all courses along with the students enrolled in them, including courses that have no students enrolled.

Comparison: Write a short reflection comparing the results of the LEFT OUTER JOIN and RIGHT OUTER JOIN queries, and how they differ from an INNER JOIN.

Deliverables:

SQL code for table creation and data insertion.

SQL queries for the LEFT OUTER JOIN and RIGHT OUTER JOIN.

Reflection on the behavior of OUTER JOINS compared to INNER JOIN.

Screenshot of the results on Workbench

Tips:

Notice how the LEFT OUTER JOIN includes all records from the left table $($Students$),$ and the matched records from the right table $($Courses$),$ plus NULL in case of no match.

Observe the behavior of RIGHT OUTER JOIN and how it includes all records from the right table $($Courses$),$ regardless of whether there is a match in the left table $($Students$).$

Reflect on the use cases of different types of joins depending on the data requirements.