zstumgoren / gentle-intro-to-sql Goto Github PK

Possibly as an extension of Part II's subqueries section, we should mention the ability to use subqueries in select clauses. This can be a really powerful feature when combined with calculations #6.

For example, when working with the contributors.db in Part II, we can show how you can find
the percent of all contributions that went to each state using a select-clause subquery.

-- Show the percentage of all contributions spent in each state
SELECT 
    state, 
    sum(amount) as state_total,
    ( (sum(amount) * 1.0 ) / (select sum(amount) from contributors) ) * 100 as pct_of_all_contribs
FROM contributors
GROUP BY state
ORDER BY pct_of_all_contribs DESC
;

A nice-to-have would be exercises for at least the more difficult sections such as subqueries, group by, having (and eventually) table joins.

DB Browser's CSV import feature will auto-generate a table on import if none by the specified name already exists. It's a handy feature that's worth documenting in the Importing Data section (in the Appendix).

Excluding the self joins section for now due to a few issues:

• The last section seems to abruptly cut off (seems like some narrative may not have been committed).
• The image reference for the join is incorrect (appears to be image from previous section).
• It's a bit confusing: This particular data set doesn't seem like a good fit to explain this advanced concept. The canonical example for self joins is a table with a foreign key reference to itself, such as an employee's table with a manager foreign key that references other employees in the same table. Here's an example.

Add a Part 4 that introduces indexes and optimization.

DB Browser doesn't document the save/open buttons or the ability to create new SQL tabs, and students are accidentally exporting the entire db as a SQL script instead of saving tabs as sql scripts.

Neither DB Browser's GUI import nor raw SQLite (on the command line) will import blank fields into a TEXT or VARCHAR column as NULLs. Instead, it replaces those values with an empty string. This causes the queries in the "IS NULL" section of the tutorial (Part 2) to fail to return any rows when trying to match last_name field on IS NULL.

In order to demonstrate the use of IS NULL without a major rewrite of this section, we'll need to manually insert null values in the database. Will need to review this section and decide if all currently blank fields should be made null, or perhaps just the one or two relevant fields.

Further, with this change, the "Importing data" section at end of Part 1 becomes unusable for setting the stage for Part 2. May need to cut this out and just make it a separate section devoted to data importing. And then create a downloadable database file for Part 2 that contains the null values and other bits we need.

The Choosing to Denormalize page mentions that it can be expensive to do a lot of joins, and therefore sometimes it's worth saving the output of a query in a new table. It goes on to note the downside that this table can become out-of-date if source tables are updated.

Probably worth mentioning "views" on this page as a possible additional strategy to spare you the work of having to update a table. You take the hit in terms of efficiency, it can be useful for scenarios where the query executes in a reasonable amount of time.

An important bit of the basics that we don't seem to have covered is how to perform calculations in the select clause. This could be something that we fold in after Part II's Aggregates section, for example by showing how we can do basic math to calculate averages, etc.

Parts I and II have been updated. Need to update Part 3 for DB Browser for SQLite.

In Beyond functions: Custom calculations the image to the query SELECT sum(amount) / 103 FROM contributors; incorrectly shows result with decimal values instead of without as expected by the integer conversion.

It seems the author updated the query but forgot to execute it before taking another screenshot.

Add an include for CC license to bottom of all pages