Back to articles list Articles Cookbook
7 minutes read

SQL Course of the Month – Standard SQL Functions

Are you dreaming of becoming a data analyst? Or maybe you just want to work effectively with your company’s databases? You can – with SQL functions. We’ve selected Standard SQL Functions to be our January 2021 SQL Course of the Month. What will you find inside? I asked Agnieszka Kozubek-Krycuń, Vertabelo's Chief Content Officer, about it. Here's what she said.

Standard SQL Functions

What are SQL functions? The term reminds me of SELECT or JOIN. Am I close?

If you mean phrases such as SELECT, FROM, JOIN, WHERE, GROUP BY, or HAVING, these are SQL keywords, not SQL functions. Confusing them with functions is a common mistake!

Keywords form the structure of the SQL language. They are the “grammar” of the language. With the help of keywords, you define the structure of an SQL query or statement. Generally, you can’t easily add new keywords to the language.

SQL functions are named pieces of code that transform inputs into output. You can use functions in your SQL queries and SQL statements to perform computations on data. And it’s quite possible to add new functions to a database.

If keywords are SQL’s grammar, then functions are part of its vocabulary.

Standard SQL Functions

Each database comes with a predefined, built-in set of functions. The SQL standard specifies a number of functions that should be available in a database. This interactive course teaches common functions available in each standard-compliant database (for example, PostgreSQL or MS SQL Server).

Knowledge of SQL functions is essential to anyone who wants to use SQL in real work. I'm sure knowing SQL functions will help you with both SQL report creation and SQL programming. This course provides an intermediate step between knowing the basics of SQL syntax and actual working proficiency in SQL.

Can you give me some examples of SQL functions?

A popular text operator is concatenation – it lets you combine two or more texts into a single text. If you had people’s first and last names in different columns and you wanted to display them on a report as one column, you’d use the concatenation operator.

If you wanted to put the last name in ALL CAPS, as some international corporations do, you could use the UPPER() function to put that text into uppercase. SQL also has functions that convert text to lowercase, compute the length of a string, trim unnecessary spaces, or find a substring in a string . All of these can be extremely useful when you want to clean up messy input, find some information in the text, or transform text in any way.

The most popular numeric functions are arithmetic operators: addition, subtraction, multiplication, and division. In this course, we discuss them in detail and show common mistakes that can happen when working with numeric data. Then we discuss rounding functions, which are essential to creating reports in SQL. We also show other useful numeric functions, like ABS() and SQRT().

We also discuss date and time functions in the course. Working with date and time data is essential for most businesses. So, we talk about date and time data types. We also show you how to apply functions that deal with dates and times: they can compute the difference between two dates, add or subtract an interval to/from a date, convert a time between time zones, or format dates and times in a specific way.

Finally, the course covers aggregate functions. These are functions such as SUM(), COUNT(), or AVG() that can compute statistics for a group of rows. You learn a little about aggregate functions when you learn GROUP BY, but some of the rather important details can be overwhelming for beginners. In this course, we revisit those details. We also show how aggregate functions can be leveraged together with the CASE expression to build complex reports in SQL. Knowing the aggregate function + CASE + GROUP BY combo is crucial for any data analyst working with SQL.

How long will it take me to do this SQL course? How many exercises are there?

The course consists of 211 exercises. It’s our longest course, so we divided it into 8 parts. It starts with an introductory quiz, then there are 6 parts related to different types of functions:

  • text functions
  • numeric functions
  • date and time functions
  • functions dealing with NULL values
  • aggregate functions.

Finally, there is a quiz to check your knowledge.

We estimate that the whole course takes about 18 hours to complete.

I know that SQL courses at LearnSQL.com combine theory with a lot of practice. Is that also the case here?

Yes! As you know, learning by doing is our motto. The course is fully interactive; each exercise has a bit of reading and a coding exercise to practice what you’ve just learned. This way, each piece of information is reinforced by doing a practical exercise. You can immediately see if you’ve understood the concept.

LearnSQL.com

Is this free course part of a larger track?

It’s part of our SQL from A to Z track. This is our complete SQL learning path that goes from the basics through to intermediate topics and advanced, complex SQL features. You can read about the SQL from A to Z track here. It was our previous Track of the Season.

SQL from A to Z

Standard SQL Functions course is also part of our SQL Fundamentals track. This is for beginners who want to learn the foundations of SQL quickly. It only teaches the topics that are essential to start working with SQL and databases.

Who should take this course?

Everyone! Once you master the basics of SELECT, mastering SQL functions is the next logical step. This knowledge will definitely come in handy for SQL developers, programmers, software engineers, data analysts, business analysts, and even managers who need to know and use SQL functions. The best way to learn SQL is through practice, which is why we recommend our interactive Standard SQL Functions course.

Standard SQL Functions

Will I receive a certificate to post on my LinkedIn profile?

Yes. After completing the exercises in this course, you’ll receive a PDF certificate that you can publish on your LinkedIn profile. See how easy it is by reading our short guide.

Do you remember working on this course?

I do. This was the fourth course we created, after SQL Basics, How to INSERT, UPDATE, or DELETE Data and the now-retired “Creating Tables in SQL”. We thought that with the three previous courses we’d covered “all” of SQL, or at least “all” of the basics of SQL. We were wondering: What should be our next step? What should we teach now?

Around the same time, we were creating custom SQL courses for Uber. Preparing the courses for Uber made us realize that knowing common SQL functions is essential for data analysts. Uber showed us which patterns and constructions in SQL are needed for everyday work with data. We based the Standard SQL Functions course on our experience with Uber. We designed it to be an intermediate step between SQL basics and SQL proficiency.

To be honest, I didn’t really believe that this course would be successful. I thought it was an important and necessary course, but I also thought that SQL functions might be too technical to be popular. I couldn’t have been more wrong. This course is extremely popular and our users appreciate the practical experience it gives them. I urge you to take it as well. You will definitely need it!

Free SQL Course for January 2020: Standard SQL Functions

As with our other courses from the SQL Course of the Month series, Standard SQL Functions is FREE for a month! We're giving everyone full access to this great SQL tutorial until the end of January. Excited? We are! Just log into your LearnSQL account to start learning SQL Functions.

Take the next step towards being a SQL expert. Sign up and start learning today!