How to use an SQL case statement with an example.

Writing SQL queries with multiple conditions will be a difficult and tiring task.

For example, an if() else if() else {} check case expression handles all SQL conditionals. If the first condition is satisfied, the query stops executing with a return value. The value specified within the else is returned if no condition is satisfied.

So here we will cover:

1. What is an SQL case statement and how it works.

2. An example with the use of SQL Case statement.

3. Some important terms like order by, left join, and alias.

Explanation of SQL Case statement:

In programming, when your requirement consists of a given set of conditions, you end up using conditionals (switch or if-else) to know which block of code to execute when a condition is met.

With SQL, you can do this using the CASE statement. You use the CASE keyword together with the WHEN clause to execute a block of conditional statement code. You use a THEN statement to return the result of the expression. If none of the conditions are met, then you use a final ELSE clause to return a fallback result.

The syntax for SQL Case statement:

CASE

WHEN conditional_statement1 THEN result1

.

.

.

WHEN condition_statementN THEN resultN

ELSE result

END;

When you use the CASE statement, it has to be followed by a WHEN and THEN the result if the first condition is met. If the first condition is not met it keeps on checking the other conditions until the nth (or final) condition. If that is still not met then the ELSE condition gets executed.

Also ELSE part is optional when using the CASE statement. If you are not using the ELSE part, the query returns NULL.

SQL Challenge:

A SQL query to return the employee with the third highest salary from a table. Also, have to replace the position of the DivisionID column with the corresponding DivisionName from the table company_divisions. Then you'll need to replace the ManagerID column with the ManagerName if the ID exists in the table and is not NULL.

What problem does the SQL CASE statement solve in this challenge?

In this challenge, we need the CASE statement to help achieve the followings:

1. Ensure that the MangerID is not NULL.

2. Match the company ManagerID to the company ID and return the Name as the ManagerName.

3. Ensure that if no Name is returned, then the name Numpy Ninja is used as the default ManagerName.

Data you need to solve this challenge: Table 1: company

Id	name	Division_id	Manager_id	salary
356	Valli	100	133	40000
122	Nagesh	101	null	60000
467	Aadya	100	null	80000
112	Avyay	105	467	65000
775	Geeta	103	null	90000
111	Srimati	104	35534	75000
222	JayaShri	102	133	86000
577	Srija	105	12343	76000
133	Poornima	105	577	110000

Table 2: company_divisions

Division_id	Division_name
100	Accounting
101	IT
102	Sales
103	Marketing
104	Engineering
105	Customer Support

How to solve the SQL statement challenge:

Step 1: Get the third-highest salary

First, you'll need to structure a query to return the third-highest salary. You'll do this by selecting from the company table and ordering by salary

You can do that like this:

SELECT * from company ORDER BY salary DESC limit 1 offset 2;

So what’s going on with the above query?

SELECT :

You use the SELECT command with the asterisk (*), to retrieve all columns from the company table.

ORDER BY:

The ORDER BY command orders column(s) in ascending or descending order. SQL orders by ascending (ASC) by default, but we will order the salary column by descending (DESC). This is because we need the desc salary from the highest to the lowest, that is 110,000 - 40,000.

limit:

The limit command limits the number of records returned based on the limit's set value. Since we are only interested in just one row, we will set the limit in the query to 1. This ensures that we will get a return value of a single record every time this query gets executed.

offset:

Offset lets us skip the two highest-paid rows (Poornima and Geeta) and return the third highest-paid (Jayashri).

The query returns the employee's row with the third highest salary, as expected.

​Id	name	Division_id	Manager_id	salary
222	JayaShri	102	133	86000

Step 2: Replace DivisionId with DivisionName:

Now, you need to modify the query by selecting only the columns you need – ID, name, Manager_id, Division_name, and Salary. Then you need to replace the Division_id column with the corresponding Division_name from the table company_divisions.

You can do that like this:

SELECT c."Id" , c."name" , c."Manager_id" , c.salary , cd.division_name

FROM company as c

LEFT JOIN company_divisions as cd on c."Division_id" = cd.division_id

ORDER BY salary DESC limit 1 offset 2;

Here’s the output:

​Id	name	Manager_id	salary	Division_name
222	JayaShri	133	86000	Sales

Let's discuss what's going on in the above query:

LEFT JOIN:

Since records are returned from the left side (company), we will match them using the LEFT JOIN on the right side (company_divisions) using the condition company.Division_id = company_divivsions.id

If a matching record is found, that is the company's id is also present in company_division, then the Division_name column is populated with the actual value from the left join, in our case (Sales). If there is no record, nothing is returned.

as (alias):

The alias used is a temporary name for the table. Using aliases helps improve readability.

Step 3: Replace Manager_id with ManagerName:

We'll use the CASE statement we learned to add conditionals for when the Manager_id is not null and to check if the Manager_id also exists.

The first thing we need to do is check if the company.Manager_iD is not null and make sure that the Id exists in the table. We will apply the CASE statement here.

CASE

WHEN c."Manager_id" IS NOT NULL

The second part of the CASE statement is to replace the Manager_id column with the ManagerName. Then we'll need to use the THEN block we learned earlier like this:

CASE

WHEN c."Manager_id" IS NOT NULL THEN (SELECT c2."name" FROM

company c2 WHERE c2."Id" = c."Manager_id")

ELSE 'Numpy Ninja' END as Manager_name

Finally, we can now include the CASE block into the already existing code we had from STEP 2. This will look somewhat like this now:

SELECT c."Id" , c."name" , c."Manager_id" , c.salary , cd.division_name,

CASE

WHEN c."Manager_id" IS NOT NULL THEN (SELECT c2."name" FROM

company c2 WHERE c2."Id" = c."Manager_id")

ELSE 'Numpy Ninja' END as Manager_name

FROM company as c

LEFT JOIN company_divisions as cd on c."Division_id" = cd.division_id

ORDER BY salary DESC limit 1 offset 2;

The result of Step 3 is the expected output – the employee with the third-highest salary.

Id	name	Division_name	salary	Manager_name
222	JayaShri	Sales	86000	Poornima

We learned about the CASE statement in SQL and how to approach a real-world problem using CASE. We also learned other SQL commands such as SELECT, ORDER BY, LIMIT, OFFSET, LEFT JOIN, and ALIAS.