SQL Course of the Month – Creating Basic SQL Reports

In May, we chose Creating Basic SQL Reports as our course of the month. Why? Because the basis of every successful business is making the right decisions based on proven data and good SQL reports. I asked Agnieszka Kozubek-Krycuń, Chief Content Officer at Vertabelo, about this. She knows SQL better than just about anyone and is probably the best person to talk about SQL Reporting.

What does it mean to create SQL reports?

SQL is the language used to get data out of databases and it can be helpful in many different scenarios. For example, we can use SQL queries to generate reports that will later give us an advantage when making various business decisions.

At LearnSQL.com, we have several foundational SQL courses. After we’d prepared courses for modern SQL constructs, we wanted to do courses about using SQL in realistic business settings. In the real world, SQL queries used for reporting tend to be long, complex, and often quite messy. We wanted to give our users something that will show the complexity of using SQL for business. So we did the SQL Reporting track.

The SQL Reporting track includes our Course of the Month, Creating Basic SQL Reports, right?

Yes. Creating Basic SQL Reports is the first course in the SQL Reporting track. The other courses in the track are Revenue Trends Analysis in SQL and Analyzing Customer Behavior in SQL, which is still in development.

Creating Basic SQL Reports focuses on the GROUP BY clause. This clause is essential to compute various statistics in SQL: totals, averages, counts, and so on. It is the basis for all business reports prepared in SQL. This course gives users the opportunity to practice using GROUP BY. And it sets users up with the knowledge needed for our other SQL reporting courses.

We introduce the GROUP BY clause in the SQL Basics course. Why add another course just for GROUP BY?

There are a lot of nuances in using GROUP BY correctly. And people often don’t grasp them when first learning GROUP BY. There are quite a lot of common mistakes and pitfalls that beginners make. This course highlights these mistakes and shows how to avoid them.

The course also talks about the advanced use of GROUP BY. For example, the user learns about the CASE WHEN construction, which is a way to create custom classifications in SQL. For example, suppose you work with an online shop’s database that stores information about transactions. You decide that transactions with a value under $100 are “small”, between $100 and $1,000 are “medium”, and over $1,000 are “big”. With CASE WHEN, you can compute how many small, medium, and big transactions you have. If you want to change the ranges in this definition, you can easily do it with CASE WHEN.

The course introduces the CASE WHEN construction and shows how to use it with GROUP BY. This way, you can build complex reports based on your custom classifications. The course also shows how to use multiple GROUP BY clauses in one query. For example, say you want to find out the city-level average value of transactions. To do this, you first need to compute the value of each transaction by tallying the value of items in these transactions; then you compute the city-level average. In SQL, you need a nested GROUP BY to do this. During the course, we explain how that works.

Finally, the course shows how to compute multiple metrics or compare groups in a single SQL query.

Some may fear that they will not understand the examples or will not be able to use them in their work. What would you say?

We designed this course to be useful in business situations. For example, our ‘data’ comes from the Microsoft Northwind database. It is a database for a fictional store: it has tables for products, employees, customers, orders, and suppliers. We chose this data set because the store model is easily understood by everyone. Most businesses can relate to the problems a store has and to the reports a store needs. All businesses have customers, employees, and orders.

Who should take this course?

Creating Basic SQL Reports is for anyone who knows the basics of SQL and would like to practice using the GROUP BY clause. I especially recommend this course for data analysts and people who need to make data-based business decisions. This course will give you solid foundations for preparing reports in SQL.

Can users apply the knowledge gained from the course to all popular database engines?

This month’s course uses standard SQL. The queries we show will work in all popular database engines: SQL Server, PostgreSQL, Oracle, and MySQL. However, for those interested in a specific database, we have versions of this course available in SQL Server (SQL Reporting in MS SQL Server) and PostgreSQL (Creating Basic SQL Reports in PostgreSQL).

Do I have to install anything to start the course?

As with all courses at LearnSQL.com, all you need is a web browser and an internet connection. You write queries in our online console and our platform verifies the code and tells you if you’re correct. This greatly simplifies learning and solving the exercises.

How many exercises will I do during the course? How long will it take? Is this enough to master new skills?

There are 97 exercises in this course. We estimate that it takes about 10 hours to complete them.

This course builds on your GROUP BY knowledge. It gives you an opportunity to practice writing GROUP BY queries and shows how you can use GROUP BY in more complex scenarios with CASE WHEN and nested SQL queries. By the end of this course, you should be able to master these issues and easily apply them in later projects.

Will I get a certificate after completing this course? Will I be able to show it on LinkedIn?

Yes, you’ll receive a certificate after you complete all the course exercises. We encourage our users to attach certificates to their resumes and LinkedIn profiles. It’s also worth showing off your new skills in social media.

As the person who supervised the creation of LearnSQL’s courses, can you tell how the work on this course went?

Preparing the syllabus for the course was easy and fun. I’ve had the outline of it – the topics that I wanted to include in the course – in my head for quite some time. It was a natural continuation of the SQL Basics course and our SQL courses for Uber.

The most challenging part of preparing this course was finding a suitable data set. We wanted to find a data set that could be easily understood by most people, so we decided on “store” as the theme for the data set.

We also wanted our data set to be quite small. The most popular sample store database, AdventureWorks by Microsoft, is very large. It has a few dozen tables. We thought it would be overwhelming for the user to work with 50+ tables in a course. So we decided to use the older, much smaller database Northwind. We trimmed down the original Microsoft database by a few tables, moved the 1990’s timestamps to the present, and our data set was ready.

Do you already know why you should learn to create SQL reports – that the ability to use the information that your company collects can help you run your business? Great! Make better analyses (and decisions) with SQL. Let May become the month you learned Creating SQL Reports!