Progressive SQL Activity — Leveling Up

This activity was made by Robinson Andres Cortes

In this activity, you’ll explore SQL queries through a set of research-based exercises. Try to complete the activity using only W3Schools, w3resource or SQLhabit as a reference.

Copy and paste the provided code into your preferred SQL DBMS. While the exercises work with any SQL engine, the example solution uses MySQL.

We don’t repeat queries that do exactly the same thing.
Each step adds a new layer of reasoning.

When someone finishes Level 5 entirely on their own, they are no longer just learning SQL
they are learning to think in data.

Level 1 — Fundamentals (Basic Exploration)

List all users.
Check if I’m right
In this exercise, we need to select all the data from the table in the database we created. In this case, the table is called users.
In real-world SQL, this solution does not differ much from what you’ll see in practice. The key concept here is that the * operator means all columns.
With that in mind, here is the solution:
SELECT * FROM users
Show only first_name, last_name, and email.
Reveal the truth
In this exercise, we continue with the same logic as the previous one. The only change is the selected columns.
Instead of selecting all columns (*), we now only need to retrieve first_name, last_name, and email.
With that in mind, here is the solution:
SELECT first_name, last_name, email FROM users
Filter users whose role is 'admin'.
Show me the magic
In this exercise, we introduce another SQL concept called WHERE.
This is a conditional statement that filters data: only rows that match the given condition will be shown:
SELECT * FROM users WHERE role = 'admin'
Filter users with document_type = 'CC'.
Moment of truth
This one follows the last exercise, now we are not asking for the role.
Instead we are searching for the document_type:
SELECT * FROM users WHERE document_type = 'CC'
Show users older than 18 years (calculate age from birth_date).
Did I nail it?
To solve this challenge, we need to introduce the TIMESTAMPDIFF() function.
In MySQL, TIMESTAMPDIFF() is a powerful function used to calculate the difference between two DATE or DATETIME values.
In this case we convert the birth_date into years by comparing it with current date, and the check whether the user is older than 18 years.
The exercise might look like this:
SELECT * FROM users WHERE TIMESTAMPDIFF(YEAR, birth_date , CURDATE()) > 18
More information about TIMESTAMPDIFF() in here.
Show users whose income is greater than 5,000,000.
Click for spoilers
Following the same login in the last exercise, we can use > greater than symbol.
We have to make sure that monthly_income is grater than 5000000.
With that in mind, here is the solution:
SELECT * FROM users WHERE monthly_income > 5000000
Show users whose name starts with "A".
Let’s see…
In order to solve this exercise, I have to introduce you to a new concept call LIKE operator.
The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.
To return records that starts with a specific letter or phrase, add the % at the end of the letter or phrase.
The exercise might look like this:
SELECT * FROM users WHERE first_name like 'a%'
More information about LIKE operator in here.
Show users who do not have a company.
Risky click
To solve this challenge, we first need to understand that a user without a company has company = ǸULL.
For this, we introduce a new SQL concept: the IS operator.
The IS operator is used to compare a value with NULL or with boolean values (TRUE, FALSE). It is especially important because NULL cannot be compared using the = operator..
With that in mind, here is the solution:
SELECT * FROM users WHERE company IS NULL
More information about IS operator in here.

Here you already learned SELECT, WHERE, logical operators, and NULL.

Level 2 — Combining Conditions

Users older than 25 years who are 'employee'.
Answer, please
In this exercise, we introduce some new SQL operators. The first one is AND.
The AND operator is a logical operator that returns TRUE only if both conditions are TRUE.
In this case, the condition will return TRUE if the user is older than 25 and is an employee.
Another important concept is the NOT operator.
The NOT operator is also a logical operator that returns TRUE when a condition is not met.
In this case, if company is not NULL, the condition will return TRUE.
With that in mind, here is the solution:
SELECT * FROM users WHERE TIMESTAMPDIFF(YEAR, birth_date , CURDATE()) > 25 AND company IS NOT NULL
More information about the AND operator in here.
More information about the NOT operator in here.
Users with 'CC' who are active.
Am I a genius?
Following the last exercise we only need to check if the document_type = 'CC' and is_active = 1:
SELECT * FROM users WHERE document_type = 'CC' AND is_active = 1
Users of legal age without employment.
Or… not?
This exercise is the same as Exercise 9, but it filters users who are over 18 years old, which is the legal age in my country.
SELECT * FROM users WHERE TIMESTAMPDIFF(YEAR, birth_date , CURDATE()) > 17 and company IS NULL
Users with a job and income greater than 3,000,000.
Time to confess
First, we check that the user has a job.
This means the company column must not be NULL.
Then, we filter users whose income is greater than 3,000,000 using the > comparison operator.
Both conditions must be true for a user to appear in the result.
Taking that into count, here is the solution:
SELECT * FROM users WHERE company IS NOT NULL AND monthly_income > 3000000
Married users with at least 1 child.
Show the answer
First, we check the marital status by comparing the marital_status column with the value 'Casado'.
Then, we make sure the user has at least one child by checking that children_count is greater than or equal to 1.
Both conditions must be true for the user to be included in the result.
SELECT * FROM users WHERE marital_status = 'Casado' AND children_count >= 1
Users between 30 and 40 years old.
Proof or pain
In this one we first check that the age of the user is equal or more than 30 and equal or less than 40.
This makes the result to be between 30 and 40.
SELECT * FROM users WHERE TIMESTAMPDIFF(YEAR, birth_date , CURDATE()) >= 30 AND TIMESTAMPDIFF(YEAR, birth_date , CURDATE()) <= 40
Verified 'admin' users older than 25 years.
Judge me
We need first make sure that the role of the user is admin and he is older that 25.
SELECT * FROM users WHERE role = 'admin' AND TIMESTAMPDIFF(YEAR, birth_date , CURDATE()) > 25

Here we combine multiple conditions and boolean logic.

Level 3 — Introduction to Analysis (Aggregations)

Count users by role.
Reality check
To start this section, we need to introduce some new concepts that will help us solve this challenge.
The first concept is the COUNT() function.
The COUNT() function is used to count the number of rows that match a specified condition.
The second concept is the GROUP BY statement.
It groups rows that have the same values into summary rows.
The last concept is the AS keyword, which is used to rename a column or table using an alias.
Enough theory — let’s put our hands on the keyboard.
First, we need to SELECT the data we want to analyze. In this case, we select the role column and the number of times each role appears using COUNT(role).
Instead of showing the default column name COUNT(role), we use the AS keyword to give it a more readable alias.
Next, we specify the table where the data is stored, and finally we use GROUP BY to group the results by role.
SELECT role, COUNT(role) AS Counting FROM users GROUP BY role
More information about the COUNT() function in here.
More information about the GROUP BY statement in here.
More information about the AS keyword in here.
Count users by document_type.
Don’t laugh
This exercise is really similar to the last one. Instead we should use the document_type as the search field.
SELECT document_type, COUNT(document_type) AS Counting FROM users GROUP BY document_type
Count how many users are unemployed.
Be gentle
In this case we take the 'Unemployed' as a fixed value and COUNT(*) for all the users in the users table.
Then we only take the users WHERE its company IS NULL, that means they are unemployed.
SELECT 'Unemployed', COUNT(*) AS Counting FROM users WHERE company is NULL
Calculate the overall average income.
Click wisely
For this exercise, we need to introduce a couple of new SQL concepts.
The first one is the AVG() function.
The AVG() function returns the average value of a numeric column.
The second and final concept in this section is the ROUND() function.
The ROUND() function rounds a number to a specified number of decimal places.
In this case, we want to calculate the average monthly income for all users.
We use the AVG() function to get the average value and the ROUND() function to round the result to 0 decimal places.
This is the final result:
SELECT 'Average Income', ROUND(AVG(monthly_income ), 0) FROM users
More information about the AVG() function in here.
More information about the ROUND() function in here.
Calculate the average income by role.
The big reveal
This exercise is really similar to the last one, but in this case we GROUP BY role the results.
SELECT role, ROUND(AVG(monthly_income ), 0) as average_income FROM users GROUP BY role

Now you’re no longer querying individuals—you’re reading patterns.

Level 4 — Analytical Thinking

Show professions with more than 10 people.
Show the city with the most users.
Compare the number of minors vs adults.
Average income by city, ordered from highest to lowest.
Show the top 5 people with the highest income.

Here you’re already using GROUP BY, ORDER BY, LIMIT, and HAVING.

Level 5 — Engineer Level

Classify users as:
- "Minor"
- "Adult"
- "Senior"
Show how many users fall into each of the classifications above.
Income ranking by city.
Profession with the highest average income.
Show users whose income is above the overall average.

Level 1 — Fundamentals (Basic Exploration)​

Level 2 — Combining Conditions​

Level 3 — Introduction to Analysis (Aggregations)​

Level 4 — Analytical Thinking​

Level 5 — Engineer Level​

Level 1 — Fundamentals (Basic Exploration)

Level 2 — Combining Conditions

Level 3 — Introduction to Analysis (Aggregations)

Level 4 — Analytical Thinking

Level 5 — Engineer Level