Search⌘ K
Home/
Courses/
SQL Interview Preparation—Intermediate Level

Answer: Using CASE

Find a detailed explanation of CASE for handling logical operations in SQL.

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'; if it is equal to 'red', it returns 'Stop'. If @color matches none of these conditions, it returns 'Unknown'. The statement concludes with END ...