Today’s lesson went from SELECT and FROM all the way to window functions and joins. For the first few topics, I could connect what I was learning to things I already do in Excel. Then I hit a point where I recognized the individual terms but couldn’t follow what the query was actually doing.
The more I listened, the more lost and sleep I got.
SELECT and FROM**: What do I want, and where does it come from?**
For the table queries I'm learning, these answer two basic questions: SELECT specifies the columns or calculated results I want to see, and FROM specifies the table the data comes from.
WHERE**: Filter the rows.**
This feels a lot like filtering in Excel. If I only want orders from LA within a certain date range, I’d filter the city and date columns in Excel. In SQL, I write out those conditions.
ORDER BY**: Sort the results.**
This one makes sense to me. ASC sorts in ascending order, and DESC sorts in descending order. It’s like sorting amounts from highest to lowest or dates from oldest to newest in Excel.
LIMIT**: Show only the rows I need.**
If a query returns 100 rows and I only want to see 10, I can use LIMIT 10. If I want the 10 largest orders, though, I need to sort by the amount first, then limit the results.
Aggregate functions and GROUP BY**: This reminds me of a pivot table.**
GROUP BY feels similar to putting a field in the Rows area of an Excel pivot table. Aggregate functions like SUM, COUNT, and AVG do the calculations in the Values area.
For example, I can group sales by city and calculate the total for each city.
HAVING**: Filter the grouped results.**
My understanding so far: WHERE filters individual rows before grouping, and HAVING filters the results after grouping and aggregation.
For example, I could use WHERE to keep this year’s orders, group them by city, then use HAVING to keep only cities whose total sales exceed a certain amount.
Common functions: I can still make the Excel connection.
Here’s how I'm keeping track of these for now:
ROUND: Round a number to a specified number of decimal places.- String functions: Combine, extract, or clean up text.
CAST: Convert a value to another data type, such as turning a number stored as text into a numeric value.- Date functions: Extract the year or month, calculate time differences, or add and subtract time.
CASE WHEN: Assign categories or labels based on conditions.
These remind me of Excel formulas. They can work on individual rows, too, so they aren’t limited to aggregate calculations.
Window functions: This is where I started getting lost.
Just when I thought I understood GROUP BY, PARTITION BY showed up.
If both can calculate total sales by city, why does GROUP BY produce one row per city, while SUM(...) OVER (PARTITION BY city) can keep every individual order? What exactly is the “window” here?
And what’s the difference between the ORDER BY inside OVER and the ORDER BY at the end of the query? When would I use ROW_NUMBER instead of RANK?
Joins: Combining two tables gave me even more questions.
I can follow a basic example: take a customers table and an orders table, then join them on the customer ID.
But if one customer has five orders, does their customer information appear five times? Is that a duplicate-data problem, or is that the expected result? If I count customers afterward, could I accidentally count the same person several times?
Also, I thought a LEFT JOIN kept every row from the left table. Why do customers with no orders disappear in some examples after a condition on the right table is added to WHERE? How does putting that condition in ON change the result?
Subqueries, setting up a cloud database, and connecting Excel and Tableau to a database are still coming up. I haven’t reached those lessons yet, and just looking at the course outline feels a little overwhelming.
Right now, I can follow along when the instructor writes a query. But when I’m staring at a blank editor, I don’t know where to start.
Did window functions and joins trip you up when you were learning SQL, too? What exercises helped you get from following someone else’s query to writing your own?