The goal of this article is to clarify what you can teach in your SQL course, regardless of whether you've chosen to use PostgreSQL or SQL Server.
Because there are many possible SQL operations, it's easier to focus on what's NOT possible and explain why, rather than trying to enumerate every possible operation that is allowed. We can't promise the list is exhaustive, so if you have questions, just ask (or try!).
A look under the hood
Behind the scenes in SQL courses, we wrap every submission in a transaction, which gets rolled back immediately after we return the query result. (For the purposes of this article, we define a "submission" as a learner running the code in their editor via the Run Code or Submit Answer button.)
This means that the state of the database resets to it's initial state after each submission. As a result, if a learner submits an incorrect answer, there's no risk that they will accidentally change the state of the database in such a way that they can no longer produce the expected result. This is a feature, not a bug 😃
What's not supported?
Changing the state of a database, then querying the updated database in separate submissions (this must happen in the same submission - see below)
Examples of supported operations
Here's a non-exhaustive list of operations that are possible, with examples in PostgreSQL and SQL Server:
SELECTqueries: PostgreSQL, SQL ServerINSERTa row, then immediatelySELECTthat row in the same submission: PostgreSQL, SQL Server (keep in mind changes get rolled back after each submission, so a learner can submit the sameINSERTquery twice in a row and it will produce the same result both times)Same as above (
INSERTfollowed bySELECT) but in multiple steps: PostgreSQL, SQL ServerCREATE TABLE: PostgreSQL, SQL ServerCREATE VIEW: PostgreSQL, SQL Server