Comments on the the "YSQL Tutorial: Fundamentals"

[bllewell]

This concerns the YSQL Tutorial: Fundamentals.

1 Make "Northwind sample database" a live link to the install instructions

You start by saying

[using] data from the Northwind sample database

Please don't cause the user the effort of searching. Instead, embed the link.

2. Use "The query will return..." instead of "should return"

Every example is followed by "The query should return 91 rows" or similar. Saying "should" makes it sound like SQL results are probabilistic! Rather, state up front that you assume that no data changes have been made between installing Northwind and starting the tutorial, and that (when you get as far as the first change-making example) the steps are executed in order. Else, your results might, of course, be different.

Then I stepped through the first few of the examples. Please consider making the following changes.

3. Pointless empty string

select first_name || '' || last_name as full_name,title from employees;

Why concatenate the empty string between first and last name? I think that you meant to concatenate a space: ' '. Or maybe this:

last_name || ', ' first_name

4 . Use a more realistic example for aliases. Instead of

SELECT product_id, discount FROM order_details AS details;

this would be more common, and therefore more useful:

SELECT a.product_id, a.discount FROM order_details AS a;

Better still, show column aliases too:

SELECT
  a.product_id AS "Product ID",
  a.discount   AS "Discount"
FROM order_details AS a;

Then you don't need the previous, separate example.

5. Don't use DISTINCT on a column with a unique constraint Look:

SELECT DISTINCT order_id
FROM orders;

The order_id column is the PK (see the \d output), and therefore unique. So DISTINCT has no purpose. Use a non-unique column, like customer_id, that has dups.

6. Don't use SELECT with LIMIT without ORDER BY

SELECT product_id,
       product_name,
       unit_price
FROM products
LIMIT 12;

When the query has no order by, the order os unpredictable/ So, with LIMIT, the result set is a sample with an unpredictable population. Never mind that you always seem to see the same result in successive ad hoc queries. Just believe the theory. Use this:

SELECT product_id,
       product_name,
       unit_price
FROM products
order by product_id
LIMIT 12;

I could go on. But I hope that you get the point. It's not enough that the examples show the syntax and run without error. Each example must also be a paradigm of proper practice and must serve a useful goal.

[rkarthik007]

@bllewell - this is great feedback, agree with all your points. Additionally, there are various places where there is simply not enough explanation for what is going on (esp the stuff around various types of joins).

Would love to have you be a course master for this effort. There are some infrastructure changes underway - once those are done, this should be very easy to edit and change.

[bllewell]

@rkarthik007 By all means. Talk to me about it.