Create a case statement in SQL


The following example uses ipython-sql. To install:
pip install ipython-sql

%sql is a Jupyter magic command and can be ignored when not using Jupyter notebooks.

Configure SQL for Jupyter notebook

#Load sql
%load_ext sql
#connect to sqlite
%sql sqlite://

Create a table

%%sql

CREATE TABLE players2 (first_name, last_name, age);
INSERT INTO players2 (first_name, last_name, age) VALUES
('Lebron', 'James', 33),
('Steph', 'Curry', 30),
('Mike', 'Jordan', 55),
('Mike', 'Bibby', 40);

Perform a case statement based on multiple conditions in SQL

%%sql
--Here, we will replace the first name with Goat for all Mikes over 45 years of age
--This is an example of modifying a field in a SQL table based on multiple conditions
SELECT
CASE WHEN first_name = 'Mike'
AND age >45 THEN 'Goat'
ELSE first_name
END AS first_name,
last_name,
age
FROM players2
first_name last_name age
Lebron James 33
Steph Curry 30
Goat Jordan 55
Mike Bibby 40


Ace your next data science interview

Get better at data science interviews by solving a few questions per week



Find a bug? Submit a suggested change on Github, or message me on Twitter.