Most SQL tutorials stop at the "Big Six": SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY. And those six clauses will take you pretty far. 

But when I first started learning SQL after years in Excel, something surprised me: SQL has hundreds of built-in functions, and most courses barely mention them.

Date functions, numeric functions, string functions. These are the tools that let you reshape raw data before you ever get to aggregation. And once you know they exist, you stop hitting walls on questions that should be straightforward.

You do not have to memorize all of them. I certainly don't. What matters is knowing what's possible, so you can reach for the right tool when you need it. This post walks through three types of functions using a Pixar films dataset, step by step.

A Quick “Big Six” Recap

Before going further, here's the order SQL clauses must appear in your query, along with the mnemonic to remember it:

"SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY — Start Fridays With Grandma's Homemade Oatmeal."

The first thing I always do when I write a new query is start with SELECT * FROM [table] and just look at everything. Then I build from there, one clause at a time. That habit alone will save you a lot of debugging.

Date Functions: Extracting a Decade from a Date Column

Say you want to find the number of Pixar movies released per decade. You have a release_date column, but no decade column. Here's how to build one, step by step.

Step 1: Extract the year using the YEAR() function.

YEAR(release_date)

Step 2: Divide by 10. You'll get something like 199.9 or 200.3.

YEAR(release_date) / 10

Step 3: Use FLOOR() to round down to the nearest integer.

FLOOR(YEAR(release_date) / 10)

Step 4: Multiply by 10 to get back to decade format (1990, 2000, 2010...).

FLOOR(YEAR(release_date) / 10) * 10 AS decade

Now GROUP BY decade and count your rows. A date function and a numeric function, chained together to answer a question the raw data couldn't answer directly.

Numeric Functions: FLOOR() and Why Rounding Direction Matters

FLOOR() rounds a number down to the nearest integer. Its counterpart, CEILING(), rounds up. ROUND() goes to the nearest, with a second argument for decimal places.

For bucketing data into decades, age groups, salary bands, or any discrete range, FLOOR() is the right tool. The pattern (divide, floor, multiply) works for any bucket size, not just decades. It's one of those techniques that sounds specific but shows up constantly.

String Functions: Using Regular Expressions to Group a Text Field

Here's the scenario: you want to find how many Pixar movies belong to each franchise (Toy Story, Cars, Incredibles, etc.). The problem is your film column has values like "Toy Story", "Toy Story 2", "Toy Story 3", and "Toy Story 4". All slightly different strings that need to be treated as one group.

The solution is a regular expression that strips the trailing space-and-number from any title. In MySQL:

REGEXP_REPLACE(film, ' [0-9]+$', '') AS series

This says: find any pattern that's a space followed by one or more digits at the end of the string, and replace it with nothing. "Toy Story 2" becomes "Toy Story". "Toy Story 4" also becomes "Toy Story". Now GROUP BY series and you can count across the whole franchise. Four Toy Story movies, as it turns out – and counting.

I'll be honest: I did not write that regex from scratch. I asked ChatGPT to generate it. One caveat worth knowing: regex operations are computationally expensive, so they're best for exploration or small datasets. For production queries, clean the data at the source. Which brings me to the most important point in this post.

You Don't Have to Memorize the Syntax

The main thing to know is just the capabilities of SQL. Then you can have ChatGPT help you with the syntax.

This is the thing I wish someone had told me earlier. Knowing SQL doesn't mean memorizing every function name and argument. It means knowing that SQL can extract parts of a date, round numbers into buckets, and find patterns in text strings. Once you know those capabilities exist, you can describe what you want and look up the exact syntax in the docs or with an AI tool.

The judgment call, knowing which type of function solves the problem in front of you, is the actual skill. The syntax is just a lookup.

Final Thoughts

The real value of SQL functions comes from chaining them. You don't just use YEAR(). You use YEAR() inside FLOOR() inside a GROUP BY. You don't just use REGEXP_REPLACE(). You use it inside a SELECT to create a derived column, then GROUP BY that column.

Work step by step. Add one transformation at a time and run the query after each change. Starting with SELECT * and adding one piece at a time is more valuable than knowing any individual function. It's also how you catch mistakes before they compound.

If you want to build a solid foundation first, John Pauler's MySQL Data Analysis course at Maven is the right starting point. When you're ready to go deeper on functions, subqueries, window functions, and more, my Advanced SQL Querying course picks up exactly where the basics leave off.