SQL Case Expression
In this lesson, you will learn how to return a specific value based on a list of conditions using SQL.
The CASE Expression In SQL
The CASE expression can be used within a SELECT statement to return a specific value when the first condition is met.
Syntax
SELECT column_list
CASE
WHEN condition1 THEN value1
WHEN condition2 THEN value2
WHEN conditionN THEN valueN
ELSE default_value
END;
FROM table_name;
Once a condition is true, it will return its result and ignore all other conditions set after.
However, if no condition is true, it returns the default value in the ELSE clause.
If there is no ELSE part and no condition is true, it returns NULL as the default value.
Preparing A Database
Since you will learn how to return specific values based on a list of conditions, you must have a database table with some records to practice setting the conditions. Execute the following query to create the database.
Example
-- If there is already a database named "demo" delete it
DROP DATABASE IF EXISTS demo;
-- Create a new database called "demo"
CREATE DATABASE demo;
-- Select the "demo" database
USE demo;
-- Create a new table called "students" inside the selected database
CREATE TABLE students (
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
full_name VARCHAR(100),
math_score INT
);
-- Insert 10 records in the "students" table
-- We add null in the id column because its value will be generated automatically by the database server
INSERT INTO students VALUES (null, "Emily Johnson", 82);
INSERT INTO students VALUES (null, "Benjamin Anderson", 87);
INSERT INTO students VALUES (null, "Olivia Smith", 73);
INSERT INTO students VALUES (null, "William Davis", 58);
INSERT INTO students VALUES (null, "Sophia Brown", 78);
INSERT INTO students VALUES (null, "Daniel Martinez", 65);
INSERT INTO students VALUES (null, "Ava Taylor", 45);
INSERT INTO students VALUES (null, "James Wilson", 95);
INSERT INTO students VALUES (null, "Mia Garcia", 68);
INSERT INTO students VALUES (null, "Ethan Jones", 120);
This is the students table that we created in the demo database.
| id | full_name | math_score |
|---|---|---|
| 1 | Emily Johnson | 82 |
| 2 | Benjamin Anderson | 87 |
| 3 | Olivia Smith | 73 |
| 4 | William Davis | 58 |
| 5 | Sophia Brown | 78 |
| 6 | Daniel Martinez | 65 |
| 7 | Ava Taylor | 45 |
| 6 | James Wilson | 95 |
| 9 | Mia Garcia | 68 |
| 10 | Ethan Jones | 120 |
Select Case Examples
The following query show students' results in math as follows:
- 60-100 score, means Passed.
- 0-59 score, means Failed.
- Other score, Refer to Professor.
Example 1
SELECT
id AS 'Id',
full_name AS 'Name',
CASE
WHEN math_score BETWEEN 60 AND 100 THEN 'Passed'
WHEN math_score BETWEEN 0 AND 59 THEN 'Failed'
ELSE 'Refer to Professor'
END AS 'Math Result'
FROM
students;
Output
| ID | Name | Math Result |
|---|---|---|
| 1 | Emily Johnson | Passed |
| 2 | Benjamin Anderson | Passed |
| 3 | Olivia Smith | Passed |
| 4 | William Davis | Failed |
| 5 | Sophia Brown | Passed |
| 6 | Daniel Martinez | Passed |
| 7 | Ava Taylor | Failed |
| 6 | James Wilson | Passed |
| 9 | Mia Garcia | Passed |
| 10 | Ethan Jones | Refer to Professor |
The following query show students' score and grade in math as follows:
- 90-100 score, means A.
- 80-89 score, means B.
- 70-79 score, means C.
- 60-69 score, means D.
- 0-59 score, means F.
- Other score, Refer to Professor
Example 2
SELECT
id AS 'Id',
full_name AS 'Name',
math_score AS 'Math Score',
CASE
WHEN math_score BETWEEN 90 AND 100 THEN 'A'
WHEN math_score BETWEEN 80 AND 89 THEN 'B'
WHEN math_score BETWEEN 70 AND 79 THEN 'C'
WHEN math_score BETWEEN 60 AND 69 THEN 'D'
WHEN math_score BETWEEN 0 AND 59 THEN 'F'
ELSE 'Refer to Professor'
END AS 'Math Grade'
FROM
students;
Output
| ID | Name | Math Score | Math Grade |
|---|---|---|---|
| 1 | Emily Johnson | 82 | B |
| 2 | Benjamin Anderson | 87 | B |
| 3 | Olivia Smith | 73 | C |
| 4 | William Davis | 58 | F |
| 5 | Sophia Brown | 78 | C |
| 6 | Daniel Martinez | 65 | D |
| 7 | Ava Taylor | 45 | F |
| 6 | James Wilson | 95 | A |
| 9 | Mia Garcia | 68 | D |
| 10 | Ethan Jones | 120 | Refer to Professor |
Select Case Exercise
1- Write a query that creates a new database called "training".
Note: you should recreate it if it already exists.
2- Write a query that creates a table called "users" and check whether it exists or not. the table should contain the following fields:
id INT NOT NULL PRIMARY KEY AUTO_INCREMENTusername VARCHAR(50)revenue DECIMAL(7, 2)
3- Write a query that inserts 5 rows in the "users" table.
4- Write a query that returns all records from the "users" table with an extra field called "Trophy" and its value should be returned as follows:
- If revenue >= 500, trophy value should be "Gold".
- If revenue >= 300, trophy value should be "Silver".
- If revenue >= 100, trophy value should be "Bronze".
- Else, trophy value should be "No trophy".
Solution
DROP DATABASE IF EXISTS training;
CREATE DATABASE training;
USE training;
CREATE TABLE IF NOT EXISTS users (
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(50),
revenue DECIMAL(7, 2)
);
INSERT INTO users (username, revenue) VALUES
('Jack', 340),
('Emma', 213),
('Luke', 475),
('Jim', 730),
('Lily', 57);
SELECT
id as 'ID',
username as 'Username',
revenue as 'Revenue',
CASE
WHEN revenue >= 500 THEN 'Gold'
WHEN revenue >= 300 THEN 'Silver'
WHEN revenue >= 100 THEN 'Bronze'
ELSE 'No trophy'
END AS 'Trophy'
FROM
students;
