learning-it by example

#297 SQL and analytics 6.8 min

SQL in 10 Queries: From SELECT to LEFT JOIN

Ten SQL queries on one small shop: a left join finds the 1 customer who never ordered.

1. In SQL, what is the difference between WHERE and HAVING?
2. In a made-up shop database in SQLite with 5 customers, a LEFT JOIN from customers to orders, keeping rows where the order id is null, returned which customer?
3. The customers table has 5 rows and one customer has no city. What do COUNT(*) and COUNT(city) return?
4. In Python's sqlite3, why are values passed to a query through question mark placeholders instead of being pasted into the text of the query?
5. In a made-up shop in SQLite, a query adds up quantity times price for each customer. Which customer spent the most, and how much?

Practice only: nothing is saved and no score counts towards anything. Each answer comes with the reason.

  1. Change a filter

    Copy the code folder first. In queries.py change the price filter in query 2 from under 5 to under 4 (the text WHERE price followed by the sign and 4), then run python run.py 2. Predict which products appear before you look. You should see Black tea at 3.0 and Biscuits at 2.5, because Green tea now costs too much.

    A hint

    The prices are in the PRODUCTS list in shop.py.

  2. Sort the other way

    In query 3 change ORDER BY price DESC LIMIT 3 to ORDER BY price ASC LIMIT 2. Predict the two rows, then run python run.py 3. You should see Biscuits at 2.5 and then Black tea at 3.0.

    A hint

    ASC puts the smallest first. LIMIT cuts the list after the sort.

  3. Raise the HAVING bar

    In query 6 change the HAVING test from a count of 2 or more to a count of 3 or more, then run python run.py 6. Predict how many customers stay. You should see only customer 1 with 3 orders.

    A hint

    Look at the ORDERS list in shop.py and count the orders for each customer id.

  4. Break the left join

    In query 8 change LEFT JOIN orders to JOIN orders, then run python run.py 8. Predict the result before you run it. You should see no names at all, only the column heading, because a plain join drops customers with no order. Change it back afterwards.

    A hint

    A left join keeps every row from the left table and fills the missing side with nulls.

  5. Pass a different name

    In queries.py change the PARAMS entry for query 10 from Ben to Asha, then run python run.py 10. Predict her orders before you run it. You should see Green tea 2, Teapot 1 and Biscuits 2. The name goes in through the question mark placeholder, never pasted into the query text.

    A hint

    Look at the ORDERS list in shop.py for customer 1.

These tasks are for you to try on your own machine. Nothing is checked or saved.

What this does not show

The shop data is made up and tiny, so the video says nothing about speed or indexes; it does not cover insert beyond setup, update, delete, dates, subqueries or right and full joins.

Up next in SQL and analytics

More in SQL and analytics

All SQL and analytics videos

Related videos