As databases grow, queries take longer to run. It’s to be expected. If your queries are consistently slow, the query itself could be the problem. We talked early in this series about SQL being a declarative language. You tell the database what you want, and it figures out how to get it. But we’ve also seen that SQL gives you the freedom to do things in many ways, and some of those ways are more efficient than others. ...
This week, we are going to look at modifying data in a database, and I’ll be honest, it can be scary. There is no safety net, no undo button. There are some techniques you can use to minimize your risk. We will take a look at them today as we discover how to add, update, and delete data in your database. Python Comparison In Python, modifying a list is forgiving. You can append, reassign, and remove items, and if something goes wrong, your original data is usually still sitting in memory or on disk. ...
So far, we have spent our time learning how to query data. For most of us, this might be as much SQL as we will ever use. There is a whole other side to SQL used to design and define tables that are optimized to store and return data quickly. Even if you only plan to query someone else’s data, it is still helpful to get an understanding of SQL’s Data Definition Language (DDL) because it will help you to understand how the data is stored, and how to write better queries. ...
This week, we are going to focus on window functions, which are very powerful data manipulation tools. It’s something that you can do in Python, but in SQL, it is super simple and very powerful. We’ll continue using the same orders and customers tables from previous posts in our DuckDB sample database. Before I continue, I have a confession to make. While I have used window functions, I haven’t used them nearly as much as I should have. I only discovered them in the past few years. Prior to that, I used some other SQL tricks for these types of queries, but this method is much cleaner. ...
This post is about nothing, or rather, it’s about the unknown. By now, you’ve bumped into NULL several times. Let’s finally make sense of it. The SQL NULL is sort of like None in Python. After all, they both represent the lack of a value, right? value = None if value == None: # True (though `is None` is preferred) print("It's None") print(None == None) # True In Python, None is a thing you can compare. Let’s see how NULL compares. ...
We learned last week about subqueries, which are like helper functions for your SQL code. They can bring back temporary values used in larger calculations or find additional data points from an id. While subqueries are very powerful, they do add complexity to your code. This complexity adds to the cognitive load when trying to read and understand your code. I’ve been the person who has to dust off an old piece of SQL code that has been running in production for years and just recently started returning invalid rows or experiencing massive latency. ...
Last week, we talked about the superpower of relational databases, the ability to join tables to make data storage more efficient. In fact, we have covered much of the syntax that you would use on a daily basis already. But SQL’s simplicity hides surprising flexibility. You can model data in many ways, and you can often get the same results with different syntax. The art of SQL is optimizing your queries so that they run well. This comes with experience, so I encourage you to start playing around with the queries and data we are working with. We will see some of this flexibility with today’s topic: subqueries. ...
Why This Caught My Eye SQL is still heavily entrenched in corporate systems. No-SQL didn’t kill it, and so far, AI hasn’t killed it. I’m curious is SQL practitioners will become more or less in demand going forward. I’m guessing the role will evolve.
So far in this series we have covered all the core SQL clauses: SELECT, FROM, WHERE, GROUP BY, HAVING, and ORDER BY. We can do quite a bit with those tools, but we have been working with a single table. SQL is the language of relational databases, and it is time to talk about the relational part. JOINs connect related tables. It’s like looking up values in a Python dictionary or merging pandas DataFrames, except that the database handles the matching. Today we are going to see how this works, but first we need a little setup. ...
When I first encountered HAVING, I thought, “Why do we need this? It’s just like WHERE.” Then I tried filtering on COUNT() and hit a strange error. That’s when it clicked: HAVING filters after grouping, not before. It’s what you need when WHERE won’t work because the thing you want to filter on doesn’t exist until after GROUP BY runs. Let’s start with a simple query of customer count by city. But there are a lot of cities and we only care about those with more than ten customers. ...