Teaching Kai SQL: Part 2 — Diving Deeper into Real-World Databases, Keys, Indexes, Inserts, and Joins

In Part 1, we covered the basics of creating databases and tables and introduced primary and foreign keys. Now, as Kai continues his journey beyond Microsoft Access, we’re diving deeper into how real-world databases really work.

While Access is a handy tool for beginners, Kai quickly ran into its limitations — especially when trying to insert multiple rows or fully grasp complex database concepts like keys, indexes, and joins. Access can be a bit “finicky,” making it tricky to experiment freely.

To give Kai a more robust and practical foundation, I introduced him to Microsoft SQL Server (MSSQL). This powerful platform offers the flexibility and control needed to understand what’s happening under the hood of SQL, how data integrity is enforced, and how performance can be optimized.

In this post, we explore:

• How to insert single and multiple records into tables
• The impact of these operations on indexes and database performance
• Different types of SQL joins, including inner, outer, and Cartesian joins
• How to interpret NULL values in join results and what they mean

Let’s jump in!

Inserting Data: Single and Multiple Rows

Once we have tables ready, the next step is adding data using INSERT INTO. Here's how Kai learned to add both single and multiple records:

-- Insert a single student
INSERT INTO Students (StudentName) VALUES ('Kai');

-- Insert multiple students in one go
INSERT INTO Students (StudentName) VALUES
('Alice'),
('Bob'),
('Charlie');

We discussed how inserting many records in one statement is more efficient than multiple single inserts, especially for large datasets.

Indexes and Their Impact

As we insert data, indexes play a crucial role in how fast we can retrieve it later. But they also need maintenance because frequent inserts and updates can fragment indexes, leading to slower queries.

Thanks to modern SSDs, some of these issues are less severe, but SQL Server still relies on statistics and index health to optimize performance. I showed Kai how index fragmentation can affect query speed and why database admins schedule index rebuilds or reorganizations.

Retrieving Data with SELECT and Joins

Kai found it exciting to pull data back using SELECT statements and to combine data from multiple tables using joins. Here’s a refresher on the different types of joins we explored:

INNER JOIN

Returns only rows that have matching values in both tables.

SELECT s.StudentName, c.CourseName
FROM Students s
INNER JOIN Enrollment e ON s.StudentID = e.StudentID
INNER JOIN Courses c ON e.CourseID = c.CourseID;

LEFT OUTER JOIN

Returns all students, even if they have no enrollments. Non-matching enrollments show NULL.

SELECT s.StudentName, c.CourseName
FROM Students s
LEFT JOIN Enrollment e ON s.StudentID = e.StudentID
LEFT JOIN Courses c ON e.CourseID = c.CourseID;

RIGHT OUTER JOIN

Returns all courses, including those without enrolled students.

SELECT s.StudentName, c.CourseName
FROM Students s
RIGHT JOIN Enrollment e ON s.StudentID = e.StudentID
RIGHT JOIN Courses c ON e.CourseID = c.CourseID;

FULL OUTER JOIN

Shows all students and all courses, with NULL where there is no matching enrollment.

SELECT s.StudentName, c.CourseName
FROM Students s
FULL OUTER JOIN Enrollment e ON s.StudentID = e.StudentID
FULL OUTER JOIN Courses c ON e.CourseID = c.CourseID;

CROSS JOIN (Cartesian Product)

Returns every possible combination of students and courses — usually very large! Kai was surprised at how big these result sets can get.

SELECT s.StudentName, c.CourseName
FROM Students s
CROSS JOIN Courses c;

Interpreting NULLs in Joins

We talked about why NULL appears in some results — usually meaning "no matching data found." For example, students without any enrolled courses will have NULL in the CourseName column in a LEFT JOIN result.

Kai started to understand how these NULL values are clues for missing relationships in data, which is important for data cleaning and reporting.

Wrapping Up Part 2

This deeper dive helped Kai see the bigger picture of databases — beyond just tables and data entry. He’s starting to appreciate how keys keep data consistent, how indexes speed up data retrieval, and how joins let you combine information meaningfully.

Next up, we’ll explore data modification with UPDATE and DELETE statements, plus more advanced performance tuning.

If you’re learning SQL or teaching someone like Kai, keep experimenting, ask lots of questions, and enjoy the process!