In this chapter, we took another week to go deeper into some specific SQL topics:
- sub-queries
- temporary tables
- stored routines
- window functions
For the sub-queries and temporary tables, we were again working on the Magist database from earlier chapters and were given a bit more challenging tasks:
- Select all the products from the health_beauty or perfumery categories that have been paid by credit card with a payment amount of more than 1000$, from orders that were purchased during 2018 and have a ‘delivered’ status.
- For the products that you selected, get the following information:
- The average weight of those products
- The cities where there are sellers that sell those products
- The cities where there are customers who bought products
Because I know common table expressions (CTE) from earlier projects and quite like them because they allow to give reasonable names to intermediate results and to step-by-step build on top of each other, I came up with this solution. It also uses the UNION keyword to collect the results for the different tasks into a single result table.
Then, I learned about the fantastic SQL extension for IPython that allows you to use Jupyter notebooks as an interactive SQL shell and used that for more challenges on sub-queries, temporary tables (or CTEs) and window functions using Internet Movie Database data: See this notebook.
Of course, this also allows to use other Python functionality like Seaborn plots and makes Jupyter notebooks an awesome tool to explore SQL databases and prototype SQL code!
For our exercises on stored SQL functions and procedures, we were given the Northwind sales database which includes products, orders and restocking levels. As we were free to find our tasks ourselves, I decided to develop logic to generate automatic restocking orders:
The idea is to evaluate the current stock, including already placed but not yet shipped orders and to decide for which suppliers orders should be issued.
- orders are only placed for suppliers for which at least one product is running out-of-stock
- for suppliers for which at least one product needs to be restocked, all products that are going below reorder-level are stocked up to the reorder-level
- products that are marked as discontinued must be skipped
For the architecture, I decided to produce the result of the restocking logic as JSON output on the database-side which would then be passed on to a document generator which in this example was written in Python and would generate Markdown/HTML documents which could then be emailed/faxed/mailed to the respective suppliers.
Take a look at this notebook!
Finally, we focused a little more on window functions using the publications database from our SQL primer course. Take a look if you are interested!
I have been working with many SQL databases in the past, including MySQL, MariaDB, SQLite and PostgreSQL but as always, there is still more to learn and I really enjoyed learning about Jupyter notebooks with the SQL extension. That is definitely technology, I will use in the future!