Learn to Code SQL Example – SQL | Case Statement

(SQL Example for Citizen Data Scientist & Business Analyst)


SQL | Case Statement

Control statements form the heart of most languages since they control the execution of other sets of statements. These are found in SQL too, and should be exploited for uses such as query filtering and query optimization through careful selection of tuples that match our requirement. In this post, we explore the Case-Switch statement in SQL.
The CASE statement is SQL’s way of handling if/then logic.
There can be two valid ways of going about the case-switch statements.

    1. The first takes a variable called case_value and matches it with some statement_list.
      CASE case_value
          WHEN when_value THEN statement_list
          [WHEN when_value THEN statement_list] ...
          [ELSE statement_list]
      END CASE
    2. The second considers a search_condition instead of variable equality and executes the statement_list accordingly.
          WHEN search_condition THEN statement_list
          [WHEN search_condition THEN statement_list] ...
          [ELSE statement_list]
      END CASE


Say we have a relation, Faculty.

Faculty Table:

001 Aakash CS M
002 Sahil EC M
003 John HSS M
004 Shelley CS F
005 Anannya CS F
006 Sia HSS F

Let’s say we would like to modify this table such that if the department name is ‘CS’, it gets modified to ‘Computer Science’, if it is ‘EC’ it gets modified to ‘Electronics and Communication’, and if it is ‘HSS’ it gets modified to ‘Humanities and Social Sciences’. This can be achieved using case statement.

Sample Query:
Consider a variable, department_name which is entered in the SQL code.

CASE department_name
  department='Computer Science';
  department='Electronics and Communication';
 department='Humanities and Social Sciences';


The department name corresponding to the given input gets renamed.

Consider another query which selects all the fields corresponding to the Faculty table. Since the values written in the Gender field are single character values (M/F), we would like to present them in a more readable format.

SELECT FacultyID, Name, Department,
CASE Gender
 WHEN'M' THEN 'Male'
 WHEN'F' THEN 'Female'
FROM Faculty


001 Aakash CS Male
002 Sahil EC Male
003 John HSS Male
004 Shelley CS Female
005 Anannya CS Female
006 Sia HSS Female

Consider yet another application of case-switch in SQL- custom sorting.

CREATE PROCEDURE GetFaculty(@ColToSort varchar(150)) AS
SELECT FacultyID, Name, Gender, Department
FROM Customers
  CASE WHEN @ColToSort='Department' THEN Department
       WHEN @ColToSort='Name' THEN Name
       WHEN @ColToSort='Gender' THEN Gender
       ElSE FacultyID


The output gets sorted according to the provided field.

The above procedure (function) takes a variable of varchar data type as its argument, and on the basis of that, sorts the tuples in the Faculty table.


Beginners tutorial with R – If Statement


Learn to Code SQL Example – SQL | Case Statement

Personal Career & Learning Guide for Data Analyst, Data Engineer and Data Scientist

Applied Machine Learning & Data Science Projects and Coding Recipes for Beginners

A list of FREE programming examples together with eTutorials & eBooks @ SETScholars

95% Discount on “Projects & Recipes, tutorials, ebooks”

Projects and Coding Recipes, eTutorials and eBooks: The best All-in-One resources for Data Analyst, Data Scientist, Machine Learning Engineer and Software Developer

Topics included: Classification, Clustering, Regression, Forecasting, Algorithms, Data Structures, Data Analytics & Data Science, Deep Learning, Machine Learning, Programming Languages and Software Tools & Packages.
(Discount is valid for limited time only)

Disclaimer: The information and code presented within this recipe/tutorial is only for educational and coaching purposes for beginners and developers. Anyone can practice and apply the recipe/tutorial presented here, but the reader is taking full responsibility for his/her actions. The author (content curator) of this recipe (code / program) has made every effort to ensure the accuracy of the information was correct at time of publication. The author (content curator) does not assume and hereby disclaims any liability to any party for any loss, damage, or disruption caused by errors or omissions, whether such errors or omissions result from accident, negligence, or any other cause. The information presented here could also be found in public knowledge domains.

Learn by Coding: v-Tutorials on Applied Machine Learning and Data Science for Beginners

Please do not waste your valuable time by watching videos, rather use end-to-end (Python and R) recipes from Professional Data Scientists to practice coding, and land the most demandable jobs in the fields of Predictive analytics & AI (Machine Learning and Data Science).

The objective is to guide the developers & analysts to “Learn how to Code” for Applied AI using end-to-end coding solutions, and unlock the world of opportunities!