Search⌘ K
AI Features
Home
Courses
SQL Interview Preparation—Intermediate Level

Answer: Using CASE

Explore how to implement conditional logic in SQL using CASE statements to assign values based on criteria. Understand session variables, column aliases, and alternative methods like IF statements and temporary tables for dynamic query results. This lesson equips you to write more flexible queries for complex data handling scenarios.

We'll cover the following...

Solution

The solution is given below:

MySQL
/* The query to use CASE statement to handle logical operations */
SET @color = 'green';  -- Set this variable to any color you want to test
SELECT @color AS Color,
       CASE 
           WHEN @color = 'green' THEN 'Go'
           WHEN @color = 'yellow' THEN 'Caution'
           WHEN @color = 'red' THEN 'Stop'
           ELSE 'Unknown'
       END AS TrafficAction;

Code explanation

The explanation of the solution code is given below:

  • Line 2: The SET command assigns the string value 'green' to the variable @color. This variable can be used later in the query to reference the color.

  • Line 4: The SELECT query selects the value of the variable @color. We use AS to set the aliases for the columns.

  • Lines 5–10: The CASE statement evaluates the value of @color to determine the traffic action. It checks if @color is equal to 'green', it returns 'Go'; if it is equal to 'yellow', it returns 'Caution' ...