Hello. In this tutorial, we will learn the SQL Case statements and will use the postgresql database running on Docker.
1. Introduction
SQL stands for Structured Query Language and is used to extract and organize data stored in relational databases like MySQL, PostgreSQL, Oracle, etc. Also, a relational database consists of rows and columns that allow fetching specific information from databases that can be used later for analysis. In real-time, SQL manages a large amount of data that is written and read simultaneously and any query that reaches the SQL server is processed into three parts –
- Parsing – Process to check the SQL query syntax
- Binding – Process to check the SQL query semantics
- Optimization – Process to generate the SQL query execution plan
1.1 Usage of SQL
Structured Query Language (popularly known as SQL) is commonly used by data analysts and data science professionals and is helpful too –
- Execute queries against the database
- Retrieve data from the database
- Insert new records into the database
- Update existing records into the database
- Created stored procedures, functions, and materialized views in the database
- Create users and grant permissions
- Set permissions on tables, stored procedures, functions, and materialized views
1.2 What is a CASE statement?
Postgresql CASE keyword in a sql statement is the same as the if/else statement in the programming language. It is represented by the below syntax in the sql query –
CASE syntax
CASE
WHEN condition_1 THEN result_1
WHEN condition_2 THEN result_2
...
ELSE "other_result"
END
1.2.1 Types of CASE statements
There are two types of CASE statements i.e.
- Simple statement – Here the search expression is compared to each expression from top to bottom. If found its corresponding result is executed and no further comparison is done. If no match is found then the optional ELSE statement is executed. If no else statement in the CASE statement a sql CASE-NOT-FOUND exception is thrown
- Searched statement – Here each Boolean expression is evaluated from top to bottom. If true, the corresponding result is executed and no further comparison is done. If no match is found then the optional ELSE statement is executed. If no else statement in the CASE statement a sql CASE-NOT-FOUND exception is thrown
2. SQL CASE statements
Let us dive into some practice implementation on the postgresql database.
2.1 Pre-requirement – Postgres Setup
Usually, setting up the database is a tedious step but with Docker, it is a simple process. You can watch the video available at this link to understand the Docker installation on Windows OS. Afterward, open the terminal and trigger the below command to set up and run postgresql.
Docker commands
-- command to run postgres on docker -- -- remember to change the password -- docker run -d -p 5433:5432 -e POSTGRES_PASSWORD= --name postgres postgres -- command to stop the Postgres docker container -- docker stop postgres -- command to remove the Postgres docker container -- docker rm postgres
Remember to enter the password of your choice. If everything goes well the postgresql database server would be up and running on a port number – 5433 and you can connect with the Dbeaver GUI tool for connecting to the server.
2.2. Creating a database
To practice this tutorial I will be using the dvdrental database. You can below command to create a database.
Create database sql query
-- create database create database dvdrental
2.3 Creating table and inserting data
For this tutorial, I am using the sql dump provided by postgresql and have restored it into the postgresql server running on my machine inside the dvdrental database. The sql dump can be downloaded from this link. Once the dump is downloaded it can be restored via the below command.
Restore db dump
-- restoring dump sql pg_restore command pg_restore -d dvdrental path_where_dump_is_downloaded\dvdrental.tar
Once the dump is restored in the dvdrental database use the SELECT command to verify. In this tutorial, I will be using the film table for CASE statements so let us view this table.
2.4 Playing with the CASE statement
Let us see some examples.
2.4.1 Simple case statement
The below sql query will fetch the records from the film table and will replace the rating column values based on the case statement.
Simple case statement query
-- query1: example of a simple case statement select film_id, title, description, length, release_year, case rating when 'PG' then 'parental guidance' when 'G' then 'general auidence' when 'NC-17' then 'inappropriate for ages 17 and under' when 'R' then 'adult' when 'PG-13' then 'parental strongly cautioned' else 'undefined' end rating from film order by film_id asc;
If everything goes well the below result will be shown.
2.4.2 Searched case statement
The below sql query will fetch the records from the film table and will create an additional column named – duration on runtime to map the value in the length with a string.
Simple case statement query
-- query2: example of a searched case statement select film_id, title, description, release_year, length, case when length >= 0 and length < 100 then 'short' when length >= 100 and length < 200 then 'medium' when length >= 200 and length < 300 then 'long' else 'very long' end as duration from film order by film_id asc;
If everything goes well the below result will be shown.
3. Summary
In this tutorial, we learned about SQL Case statements and their practical implementation via different examples. You can download the SQL scripts used in this tutorial from the Downloads section.
4. Download the SQL scripts
This was a tutorial to understand the SQL Case statements.
You can download the full source code of this example here: SQL CASE statement
