#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.
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.
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.
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.
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.
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
Related videos
- #122 SQL Joins: Inner, Left and Anti, With Real Output SQL and analytics
- #157 Model Registry by Hand: Versions, Promote, Roll Back MLOps and deployment
- #208 Right to Erasure Across a Pipeline: Delete, Scan, Restore Data governance
- #238 Event-Driven AI Agents: Queues, Crashes and Duplicates Enterprise architecture and AI agents
