Introduction.
Starting your journey with SQL can feel like staring at a massive wall of syntax. But here’s a secret: you don’t need to know everything to be effective. Most real-world data analysis relies on a core set of functions and operations.
Based on the roadmap below, let’s dive into the six pillars of SQL that will take you from "running basic selects" to "building meaningful insights."
1. String Functions: Cleaning the Noise
Data is messy. You'll often find names in all caps, extra spaces, or data tucked inside long strings. String functions are your primary tools for data cleaning.
UPPER() / LOWER().
Standardizes casing for easier comparisons.
TRIM().
Removes annoying leading or trailing spaces.
CONCAT().
Merges columns together (like joining first_name and last_name).
SUBSTRING().
Extracts a specific portion of a text string.
2. Number Functions: Doing the Math.
SQL isn't just for retrieving data; it’s for calculating it. Beyond standard arithmetic, number functions help you handle precision and statistics.
ROUND().
Cleans up those long decimals to a readable format.
ABS()
Returns the absolute value of a number.
CEIL() / FLOOR().
Forces numbers up or down to the nearest integer.
3. DateTime Functions: Mastering History
Almost every record in a database has a timestamp. Knowing how to manipulate dates is crucial for "Year-over-Year" or "Month-to-Date" reporting.
EXTRACT() / DATE_PART().
Pulls the year, month, or day out of a timestamp.
DATEDIFF().
Calculates the time elapsed between two events.
CURRENT_DATE.
Grabs today’s date for dynamic reporting.
4. Joins: Connecting the Dots
Data is rarely stored in one giant table. It’s spread across many, and Joins are the "glue" that brings them together.
INNER JOIN.
The most common join; returns records with matching values in both tables.
LEFT JOIN.
Keeps everything from the "left" table, even if there’s no match in the "right."
CROSS JOIN.
Creates a Cartesian product (every row from table A paired with every row from table B).
5. Window Functions: The "Pro" Level
Window functions allow you to perform calculations across a set of rows that are related to the current row. This is the "magic" step for advanced analysis.
ROW_NUMBER():
Assigns a unique ID to rows in a specific order.
RANK():
Handles ties in your data (great for leaderboards).
LAG() / LEAD():
Lets you look at the previous or next row’s value without a complex join—perfect for calculating growth rates.
6. Set Operators:
Combining Results
While Joins combine tables horizontally (adding columns), Set Operators combine them vertically (adding rows).
UNION / UNION ALL.
Stacks the results of two queries on top of each other.
INTERSECT.
Returns only the rows that appear in both query results.
EXCEPT (or MINUS).
Returns rows from the first query that are not present in the second.
Top comments (0)