SQL for Data Analytics · How the site works

How the site works

Lessons and practice

The course is split into units. A unit has a few lessons and ends with one practice.

Lessons are a video, usually with a query box under it so students can try things while they watch, and then a short section or two where they work with the idea: a question to answer, something to click through, a query to write. Nothing a student does in a lesson is recorded. Lessons are for learning, and students can get things wrong in them as many times as they like.

The practice is the one page in each unit that counts. It's the only page that records anything, and it's what the student turns in. A practice for a SQL unit has fifteen questions in two parts.

  • Part 1, writing queries. Ten queries, in the order the lessons taught them. Each one says exactly what it wants.
  • Part 2, using queries. Five questions closer to real analytics work: a business request to turn into a query, a question they answer by running whatever queries they need, one where the second query depends on what the first one found, a multiple choice where several queries all run and only one answers the request, and a scenario where they choose some of the columns themselves.
How answers are checked

Everything is checked in the student's browser, the moment they run it. There is no server.

The database is SQLite, compiled to run in the browser, with a layer on top that makes it behave like MariaDB for what students type: the same division, the same date functions, the same handling of quoted numbers, the same constraints. The videos are recorded against MariaDB, so this matters. A test suite in the repository checks a few hundred queries against known answers, and lists the handful of differences that remain. Students don't install anything.

When the site is built, each question's reference query is run against the database. The page gets the shape of the correct result and a fingerprint of its values. It never gets the query or the rows. When a student runs a query, their result is fingerprinted the same way and compared. So a student can't find the answers by looking at the page's source.

Columns can come in any order, numbers are compared at two decimal places, and rows that tie on the sort column can come in either order. Another test runs every checked question with the variations a student might type, like renamed columns, lowercase keywords, a rounded average, or a flipped sort, and checks both the verdict and the feedback.

The feedback is specific where it can be: the wrong number of columns, too many rows, the right rows in the wrong order. Typed answers and multiple choice work the same way, with a fingerprint of the answer.

Questions for creating and changing tables give each question its own small database, and check what the database looks like after the student's script has run: its tables, columns, keys, and rows.

What a student turns in

Every query and every answer a student tries on a practice page is kept in a history, with the time. A query that isn't the answer yet isn't marked wrong. It's a step, and students are told a messy history is normal.

When they're done, they type their name and download a submission file. It's a small text file with a summary at the top (which questions are done, how many attempts, when) and the full history underneath. They upload that file to the assignment in Canvas. They can submit as many times as they like.

I grade practice on completion. The file is there so that completion means something, and so there is something to look at in a conversation with a student.

The file ends with a checksum over everything above it. If someone edits the summary or the history by hand, the checksum no longer matches. It is worked out in the browser, so a determined student could forge one, and I treat it as a deterrent. The accountability in this course is the one-on-one check-in and the final exam.

Publishing units

A unit is only published when it's marked live in the site's list of units. Anything that isn't live shows up on the home page as "coming soon", and its pages aren't built at all, so students can't get to them early by guessing an address. I turn units on as the quarter goes.