sql

SQL Case Statement

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.

Fig. 1. Postgres on Docker

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.

Fig. 2: Film table data

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.

Fig. 3: Query output 1

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.

Fig. 4: Query output 2

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.

Download
You can download the full source code of this example here: SQL CASE statement

Yatin

An experience full-stack engineer well versed with Core Java, Spring/Springboot, MVC, Security, AOP, Frontend (Angular & React), and cloud technologies (such as AWS, GCP, Jenkins, Docker, K8).
Subscribe
Notify of
guest

This site uses Akismet to reduce spam. Learn how your comment data is processed.

0 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Back to top button