Sql

Twenty-four weeks ago, I started this series with a confession: SQL seemed scary to me when I first learned it. The syntax didn’t look like any language I had seen before. The concepts felt unfamiliar. I was trying to tell SQL what steps to perform, and it kept not working. The problem wasn’t the SQL. It was how I was thinking. If you’ve followed along to this point, you’ve made that same shift. You think in sets now. You describe the result you want instead of writing loops to build it row by row. That mental model is the real skill, and it’s yours to keep. ...

You’ve spent 23 weeks building a SQL foundation. You can query, join, aggregate, test, and build pipelines. That covers most of what you’ll do day to day. But SQL has more to offer. This post is a sampler plate: six topics you’ll encounter as your SQL work gets more complex. Some of these you’ll use daily. Others you’ll reach for once a quarter. All of them are worth knowing exist so you recognize them when the moment comes. We won’t revisit COALESCE and NULLIF here since we covered those in Post 15, but the six topics below are all new. ...

You test your Python code. You probably don’t test your SQL. Here’s why you should, and how to start. I once had a query that ran fine for months. Then someone added a column to the source table and a SELECT * downstream started returning unexpected data. The query didn’t error. It just silently gave wrong results. A test would have caught it immediately. Schema changes break queries silently. Refactoring a CTE can shift results in ways you don’t notice. New data patterns expose assumptions you didn’t know you made. SQL deserves the same testing discipline as the rest of your code, and Python makes it straightforward. ...

At the beginning of this series, I promised that even if you know how to use an Object Relational Mapper (ORM) to interact with a database, knowing SQL would make you a better developer. Now that we have covered everything from SELECT to parameterized queries, it is time to answer the question that every Python developer eventually asks: when should I use an ORM, and when should I just write SQL? ...

As a Python developer, you are aware of software vulnerabilities. You have probably heard of SQL injection attacks, and you may have even done some work to protect against them. Today, we are going to discuss what they are and how easy it is to prevent them. SQL Injection So let’s say your application has a customer search screen. That screen allows users to search for customers by city. The user enters the city they are interested in, and you take the city they entered and make some SQL to search for customers in that city. ...

You’ve spent 19 posts learning SQL concepts: selecting, filtering, joining, grouping, window functions, and more. Today, you put them all together and build something real: a complete data pipeline that fetches data from an API, loads it into DuckDB, transforms it with SQL, and exports the results. What is ETL (and ELT)? This is a real-world example of a common data engineering pattern. You may have heard of ETL (Extract, Transform, Load), where data is transformed before it reaches its destination. What we are actually building today is the more modern variant, ELT: Extract, Load, Transform. ...

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. ...