zichongkao / selectstarsql

An interactive SQL book

Select Star SQL

This is the repository for selectstarsql.com. It is an interactive book that teaches SQL by conveying a mental model for writing queries.

Development

The structure of the code is pretty standard for a Jekyll-built site. See Jekyll Directory Structure.

All the pages are stored as markdown(.md) files in the top-level directory. Jekyll takes these markdown files and converts them into html files in /_site. During the conversion, it does all sorts of cool energy-saving things like embedding them in templates with standardized header and footer elements. These templates are stored in /_layouts.

You can serve a local version by running jekyll serve.

The main technical complexity lies in the interactive sql exercises. These are implemented as a custom html tag in /scripts/main.js. Firefox doesn't support custom html elements by default, so we pulled in the custom-elements.min.js library from unpkg. (See _layouts/default.html.)

Contributing

To contribute, either email me directly at [email protected], or submit a pull request by following these steps:

• Install Jekyll and other project dependencies (bundle install)
• Fork this repo (button on the top right)
• Clone your forked repo git clone https://github.com/MYUSERNAME/selectstarsql/
• Make your changes
• Run jekyll serve and check your changes on your browser at your localhost address. This will probably be http://127.0.0.1.
• Commit your changes, and push it to your forked repo
• Click "Create Pull-request" when looking at your forked repo on Github.

Licensing

The prose of the book is licensed by Zi Chong Kao under a Creative Commons BY-SA 4.0 License which allows sharing and adapting under the same license and with attribution. The code and datasets are released into the public domain under the Creative Commons CC0 License.

Todo

• Complete hiatus tutorial
• Clarify double quotes for SQLite
• Improve CASE WHEN explanation

selectstarsql's Issues

Question for innocence page

Hello,

I'm a random guy on the internet who stumbled upon selectstarsql and I think it's rad, but I have one question.

Would

select count(1) from executions

be a "better" overall solution to show how to count all the rows of a table since that way you don't ever have to figure out which column would have a complete set of non-null records or select count(*) from executions? Or am I just over thinking this beginner section and being nit picky?

The interactive SQL editor is not working

Licensing and build documentation

Is there any chance of republishing this with CC0/MIT licensing? I'd love to trial using this to run SQL workshops in my company to teach people basics of SQL modifying some of the data/code appropriate to my context. Alternatively, are you planning for a licensing model for use in a commercial setting?

Incorrect solution in GROUP BY section

Where

The problem exists at the page: https://selectstarsql.com/longtail.html.

What

Current solution's code for reference:

SELECT
  county,
  last_statement IS NOT NULL AS has_last_statement,
  COUNT(*)
FROM executions
GROUP BY county, has_last_statement

The given solution's code of the second task in the section The GROUP BY Block does not, as the task states:

Modify this query to find the number of executions from each county with and without a last statement.

but instead it fits the following description:

• Column 1 is the county
• Column 2 is 1 or 0 interpreted as boolean, whether there are any last_statement is not NULL for the correspoding county
• Column 3 is the total amount of executions for the corresponding county

The post-run indicator Correct / Incorrect is also wrong analog to the problem above.

Solution

Change the task description to fit the solution

or

change the solution's code to fit the task and change the expected dataset accordingly.
An example for the solution's code using case-when could be:

SELECT
  county,
  COUNT(CASE WHEN last_statement IS NOT NULL THEN 1 ELSE NULL END) AS with_statement,
  COUNT(CASE WHEN last_statement IS NULL THEN 1 ELSE NULL END) AS no_statement
FROM executions
GROUP BY county

Cheers and thank you a ton for a great tutorial!

Window functions

Hello,
the most recent sql.js version (3.28.0), supports window functions.

"Proportion of last statements with claims of innocence" is ambiguous?

At the end of Chapter 2, the reader's asked to calculate the proportion of last statements with a claim of innocence. The right answer is given as:

SELECT
1.0 * COUNT(CASE WHEN last_statement LIKE '%innocent%'
    THEN 1 ELSE NULL END) / COUNT(*)
FROM executions

However, I think a more natural answer (given how the question is phrased) would be:

SELECT
1.0 * COUNT(CASE WHEN last_statement LIKE '%innocent%'
    THEN 1 ELSE NULL END) / COUNT(statements)
FROM executions

Since 443 last statements were given, and 31 claim innocence, which ≈ 7%. But I think it would be fine to stick with the former answer, but then maybe the question could be clarified by rephrasing it to something like:

"Find the proportion of inmates who claim innocence in their last statement."

Hebrew version translation

(working on it on https://github.com/noamoss/selectstarsql)

Code sometimes work and sometimes does not

In the last section and the last question.

When i enter this code:

SELECT DISTINCT c1.cosponsor_name FROM cosponsors c1 LEFT JOIN cosponsors CS2 ON c1.sponsor_name = CS2.cosponsor_name WHERE CS2.cosponsor_name IS NULL

Sometimes it shows:

No data returned

Other times it seems to work correctly (showing "Correct" a few seconds after showing the resulting table).

It seems to me that the code should work always, or at least give a deterministic result. You might have to try 10-20 times to see it is giving out different results.