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?

  • Creating databases
  • Transactions
  • 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:

  • SELECT queries: PostgreSQL, SQL Server 
  • INSERT a row, then immediately SELECT that row in the same submission: PostgreSQL, SQL Server (keep in mind changes get rolled back after each submission, so a learner can submit the same INSERT query twice in a row and it will produce the same result both times)
  • Same as above (INSERT followed by SELECT) but in multiple steps: PostgreSQL, SQL Server
  • CREATE TABLE: PostgreSQL, SQL Server
  • CREATE VIEW: PostgreSQL, SQL Server
Did this answer your question?