Imagine a teaching scenario: you want to find the students who haven't submitted their homework yet, so you ask AI to write some SQL for you. The program runs without a hitch, and a tidy list appears on screen.
No errors, and the column names line up. You're about to send out reminders when you notice that a student with no submission record at all is missing from the results.
The problem may be hiding in the join. The query kept only the records that matched across the two tables, so "the person with no record yet" was excluded from the very start.
This kind of error is quiet. The database did exactly what you wrote. What really needs checking is whether those operations express what you actually meant.
Hong Kong Polytechnic University's DSAI2202 Database Principles covers data modeling, SQL, transaction management, database security and more. Enter the course carrying a question like "why is one person missing from the result?" and abstract concepts gain a trail you can follow. PolyU official course description
Missing from the list—no record, or no person?
Let's shrink the opening example first.
The student table has three people: Ah Ming, Siu Yan and Ah Kit. The submission table holds records for only Ah Ming and Siu Yan; Ah Kit hasn't submitted yet.
If you first join the two tables with an inner join and then look for students who "haven't submitted," Ah Kit is already gone from the intermediate result. No matter how careful the later filtering is, it cannot bring back someone who was excluded earlier.
One approach is to start from the complete student roster, keep every student, and then check whether a corresponding submission record exists. A left join can help express this relationship, but you still need to check the assignment scope, the matching fields and the later filter conditions. Swapping in a different join keyword does not automatically guarantee a correct answer. PostgreSQL official join tutorial
In EvoClass's Lab 7: Reasoning Across Relations, learners compare a student registration roster with a learning platform roster and observe how different join methods keep or exclude records.
What is worth practicing here is saying clearly, before writing the query: who does one row in the result represent? Which people must be kept? What does it mean when no matching record is found?
With these few sentences, SQL has a target to measure itself against.
Before querying, decide how to record the world
Let's stay with this roster. A student may enroll in several courses, a course may have several assignments, and each assignment may allow multiple submissions.
If you cram all of this into one big table, names, course titles and instructor information may repeat over and over. Change one spot, and stale values remain elsewhere; when counting students, you may count the same person more than once.
At this point, the problem has already occurred before the query: what exactly is each row in the table recording?
Lab 2: From the Real World to PostgreSQL Tables starts from the redundancy and update problems of flat tables and moves into relational database design, while Lab 9: Normalizing the Registration Form goes further into normalization.
Try a small pen-and-paper exercise first: write "student," "course" and "enrollment relationship" on three separate sheets of paper, then explain how they connect. If a student switches majors, what needs to change? If a new course has no enrollees yet, can it still be recorded?
These questions put the data structure to the test of everyday change. Whether tables are split appropriately depends on whether they can express relationships clearly and stay consistent when changes are made.
Returning to the opening query, you'll also find it easier to judge: are those extra rows several different students, or several submissions by the same student?
One statement correct—the whole operation may still be only half done
Once you've retrieved results, the system often needs to modify data as well.
For example, a student applies to switch classes and must be removed from the original class and added to the new one. If the first step has already been saved but the second fails for some reason, the student may temporarily belong to no class at all. Looked at separately, both statements may be written correctly; looked at together, the system is left in an incomplete state.
Transactions provide a basic tool for this problem: organize the steps that must be completed together so they commit as a whole, or roll back on failure. How to draw the boundary of that "whole" still requires understanding the specific task. PostgreSQL official transactions tutorial
Lab 10's transactions and concurrency experiment takes the topic further into transaction boundaries and concurrency control, using seat handling at a course registration desk as the setting to observe what happens when two sessions operate at the same time.
When practicing, you can deliberately cause a failure between the two steps and then see what the database actually left behind. Next, let two sessions operate on the same data simultaneously and observe whether the previously smooth steps still hold.
An experiment thus takes on an extra layer of meaning: besides seeing how things succeed, you also need to know how they fail.
After asking AI to write SQL, what do you verify yourself?
With the earlier experience in hand, you'll look at a query from AI differently.
The roster that dropped someone at the start can be checked with three fictitious student records; the double-counting problem can be checked by adding a student with two submissions; the class-switch operation can be checked by examining the data state when it fails midway.
These small examples provide the basis for judgment. A fluent explanation is no substitute for checking the results.
Lab 12: Verifying AI-Generated SQL builds review into the exercise: Does it run? Does it answer the question? What might it break? Who has permission to run it?
Learners can work through a concrete task step by step: which records will this query keep? How many rows will the modification affect? If you expect to change only one person, how can you catch a mis-scoped statement before executing it? And does a tool having modification privileges mean the current task permits it to use them?
For those just getting to know relational databases, or who have already started using AI to write queries, EvoClass's DSAI2202 course entry offers a path from basic operations to deeper ground. You can prepare a small set of data you can verify by hand, then bring your expected results into the practice.
The next time a tidy list appears on screen, look first for the person most easily left out.
If you can explain why he appears—or why he's absent—only then do you begin to know whether the answer is worth trusting.
Course positioning is based on the PolyU official course description; lab descriptions are based on current EvoClass course materials. Lab links in this article point to Simplified Chinese learning materials. The roster, homework submission and class-switch scenarios are teaching examples constructed to aid understanding of concepts; they are not student cases or records of usage outcomes.