5 Common SQL Problems For You To Crush

Know these problems in detail and understand the approaches to solve them easily

Photo by Geometric Photography on Unsplash

👋 Welcome

A warm welcome to the 3 new subscribers joining us this week.

Let me know what you think of this post in a comment, if it’s no trouble. If you prefer Twitter, drop me a DM.

Now, let’s get started with this issue.

5 Common SQL Problems For You To Crush

Having great skill in SQL is a an asset for any software engineer. Both learning new concepts and revising them thoroughly can be accomplished by picking and solving some common problems asked in the domain.

In this article, I’m going to relay 5 such problems, ones that I’ve handpicked from LeetCode and describe what they want and lay down the approaches to solving them.

With these problems, I also hope to cover some of the basic concepts that you should know (and revise) along with some advanced queries making approaches that can be helpful to remember when preparing for an interview, or simply adding to your SQL knowledge.

Without any further delay, let’s begin!

1. Combine Two Tables

Q: You have been given two tables — Person and Address, and are being asked to write the SQL query to report the first name, last name, city, and state of each person in the Person table.

(If the address of a personId is not present in the Address table, report null instead.)

Link to the problem.

Your Output: 
+-----------+----------+---------------+----------+
| firstName | lastName | city          | state    |
+-----------+----------+---------------+----------+
| Allen     | Wang     | Null          | Null     |
| Bob       | Alice    | New York City | New York |
+-----------+----------+---------------+----------+

Approaches:

The easiest approach to solving is using the concept of JOINS. However, you may need to decide which join to you should use here.

Remember — since we need to report the city and state column values as null for a row that doesn’t have it, we should get the intuition that a left join is what’s required here.

So, performing a simple left join, we can draw up the following query in MySQL:

select a.firstName, a.lastName, b.city, b.state 
from Person a
left join Address b 
on a.personId = b.personId;

Note how we need to match the foreign key from the Address table to the Person table in order to get each Person’s city and state columns.

---

I like learning constantly and sharing things with my audience directly. It’s literally free to subscribe to Codecast, so why not do it 👇

---

2. Customers Who Never Order

Q: There are two tables in question — Customers and Orders. You are being tasked to write an SQL query to report all customers who never order anything.

Link to the problem.

Your output:
+-----------+
| Customers |
+-----------+
| Henry     |
| Max       |
+-----------+

Approaches:

No 1: Using the WHERE NOT EXISTS clause:

First, as a sub-query, we want to select all of the rows from the Orders table where customer ids from Customers table match with the customer ids from the Orders table. This means that — we are selecting all the customers who have placed an order at least once.

Now, in the main query, we can simply not select those customers (rows) to produce the output. This is done with the help of NOT EXISTS clause.

select c.name as 'Customers' 
from Customers c
where not exists
(
    select null 
    from Orders o
    where c.id = o.customerId
);

No 2: Using the NOT IN keywords:

There is an albeit, slower way to do the same thing, in which we can easily select all customer ids from Order table, and select all the customer from Customers table which aren’t present in the result of the sub-query.

select c.name as 'Customers'
from Customers c
where c.id not in
(
    select customerId from Orders
);

3. Second Highest Salary

Q: We are being tasked to find the second highest salary given an Employee table. If there is no second highest salary, the query should output null.

Link to the problem.

Your output:
+----+--------+
| Id | Salary |
+----+--------+
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |
+----+--------+

Approaches:

No 1: The simplest way to solve this problem is with the help of limit and offset keywords.

We will select all distinct salaries from the table, order them in descending order and limit the output by 1 so we get only 1 salary and finally, we offset the descending order by 1 meaning we select not the highest but the second highest salary. Something like this:

select distinct e.salary as SecondHighestSalary 
from Employee e
order by e.salary desc
limit 1 offset 1

No 2: Another way to solve it is by using the Max() function from SQL.

Within a sub-query, we select the maximum salary from the Employee table. Finally, we select the maximum salary from the output of this sub-query and present it as the final output — which is now the second highest salary.

select max(e.salary) as SecondHighestSalary
from Employee e
where e.salary < (select max(salary) from Employee );

4. Duplicate Emails

Q: We are being asked to find the all the duplicate emails given the Person table.

Link to the problem.

Your output:
+---------+
| Email   |
+---------+
| a@b.com |
+---------+

Approaches:

We can approach this problem by making sure that the first thing we understand is that for selecting duplicates, our count of emails of any one kind should be more than 1. Having said that, we want to only present the emails as the output, and the best way to do that is with “Group by” and “Having” keywords.

select p.email
from Person p
group by email
having count(p.email)  > 1;

5. Rising Temperature

Q: We want to write an SQL query to find all dates with higher temperature compared to its previous dates, given the Weather table.

Link to the problem.

Your output:
+----+
| id |
+----+
| 2  |
| 4  |
+----+

Approaches:

The core of the problem focuses on making sure that the neighbouring dates aren’t the ones with the increasing temperature. So, in order to solve this, we can use the Datediff(date1, date2) function.

Notice that we only need to show the higher dateId in our output table? So in our query, we will make sure to only select the higher temperature date, for instance, the w2, as we do it here:

select w2.id 
from Weather w1, Weather w2
where w2.temperature > w1.temperature
and datediff(w2.recordDate, w1.recordDate) = 1;

Next, we want to compare w2 temperature to the instance that is just before it, so we take another variable w1 for that. Then we apply two Where conditions: First, we make sure w2’s temperature is higher than w1’s. And second, we make sure that the date difference is only 1 day. Thus, we have successfully built our query!

A few parting words…

So that was it — a list of 5 problems very much worth knowing and I hope it was helpful in giving you some understanding or a revision of some of the core concepts of writing better queries to solve problems.

Thank you for reading! Find me on Twitter. :)

If you found value in this issue, share it with your friends too maybe? 😃