CASE Statement In SQL

SQL CASE: Theater of Conditions 🎭

SQL CASE could be likened to being a director of a play 🎭. You get to decide each character's performance. Characters in this case are rows. They perform based on conditions.

It is a conditional expression that allows you to execute diverse actions based on conditions. We will explore SQL CASE using a theater-themed explanation. 🎭🎬

Picture yourself directing a play. You have a talented cast representing your data rows. Each actor (row) has a role to perform based on their unique skills and attributes.

Basic Syntax - Casting the Roles 🎥

The SQL CASE statement can be compared to assigning roles to actors based on their abilities.

SELECT actor_name, 
       CASE 
           WHEN skill_level >= 90 THEN 'Lead Role'
           WHEN skill_level >= 70 THEN 'Supporting Role'
           ELSE 'Ensemble'
       END AS role
FROM actors;
    

Roles are assigned to actors based on their skill levels. High skill levels get the 'Lead Role', medium skill levels get the 'Supporting Role', and others join the 'Ensemble'.

Using CASE with Aggregation - Awarding Prizes 🏆

CASE can be used with aggregation functions to assign rewards (results) based on conditions.

SELECT play_title, 
       COUNT(*) AS total_cast,
       CASE 
           WHEN COUNT(*) > 10 THEN 'Gold Prize'
           WHEN COUNT(*) > 5 THEN 'Silver Prize'
           ELSE 'Bronze Prize'
       END AS prize
FROM casts
GROUP BY play_title;
    

Plays are rewarded (Gold, Silver, Bronze) based on the total count of the cast members.

Nesting CASE Statements - Drama within Drama 🎭

You can nest CASE statements to add layers of drama and complexity to your play.

SELECT actor_name, 
       CASE 
           WHEN skill_level >= 90 THEN 'Lead Role'
           WHEN skill_level >= 70 THEN 
               CASE 
                   WHEN age < 30 THEN 'Young Supporting Role'
                   ELSE 'Supporting Role'
               END
           ELSE 'Ensemble'
       END AS role
FROM actors;
    

The role is decided by skill level and age, creating a multi-dimensional drama within the SQL query.

SQL CASE is akin to sitting in the director's chair 🎭. You script the performance of data rows in the grand theater of SQL. Each CASE statement sets the stage for an exciting performance! 🌟🎭