Tricky CASE Statement Interview Questions 

Part 6

(Click below for you tube video answer)

A case expression can only manipulate the value of an expression, not remove rows from the result. If you want to omit the nulls from the result, you'll have to add a where clause:

We use below table for demonstration:

DROP TABLE IF EXISTS [dbo].[tblEmployee]
CREATE TABLE [dbo].[tblEmployee](
[EmpName] [varchar](50) NOT NULL,
Salary INT NOT NULL,
[Dept] [int] NULL
)
GO
INSERT INTO [dbo].[tblEmployee]
           ([EmpName],Salary,[Dept])
           VALUES
           ('smith',10000, 1) ,('Peter',11500, 2) ,('Mark',12500, 4) ,('John',13500,3)
GO

--Q1 - What is the Output of the below query

SELECT * FROM [dbo].[tblEmployee]

SELECT EmpName, Salary, CASE WHEN Dept = 3 THEN 6 END Dept FROM [dbo].[tblEmployee]

-- Solution:

(USE ELSE Clause to avoid evaluating other rows to NULL when they not match WHEN Condition)

SELECT EmpName, Salary, CASE WHEN Dept = 3 THEN 6 ELSE Dept END Derived_Dept FROM [dbo].[tblEmployee]



--Q2 - What is the Output of the below query

SELECT * FROM [dbo].[tblEmployee]
UPDATE [dbo].[tblEmployee] SET Dept = CASE WHEN Dept = 3 THEN 6 END
SELECT * FROM [dbo].[tblEmployee]

-- Solution 1:
SELECT * FROM [dbo].[tblEmployee]
UPDATE [dbo].[tblEmployee] SET Dept = CASE WHEN Dept = 3 THEN 6 ELSE Dept END
SELECT * FROM [dbo].[tblEmployee]


-- Solution 2:
SELECT * FROM [dbo].[tblEmployee]
UPDATE [dbo].[tblEmployee] SET Dept = CASE WHEN Dept = 3 THEN 6 END WHERE Dept = 3
SELECT * FROM [dbo].[tblEmployee]

Note: If there is only one department to be updated we can use update statement without CASE Statement, but mostly will be used when they want to use multiple conditions.

UPDATE [dbo].[tblEmployee] SET
Dept = CASE WHEN Dept = 3 THEN 6
                                WHEN Dept = 4 THEN 2
                    ELSE Dept END



-- Q3 What is the output of the below query

SELECT * FROM [dbo].[tblEmployee]
SELECT
CASE WHEN Salary > 10000 THEN 'greater than 10000'
     WHEN Salary > 11000 THEN 'greater than 11000'
         WHEN Salary > 12000 THEN 'greater than 12000'
WHEN Salary > 13000 THEN 'greater than 13000'
ELSE 'Less than or equal to 10K' END Salary_Derived
FROM [dbo].[tblEmployee]

In the above statement the order of WHEN (Prioritization was given wrong) and it has to be given accordingly. Top Priority CASE should come Top.

-- Solution: 

SELECT * FROM [dbo].[tblEmployee]

SELECT

CASE WHEN Salary > 13000 THEN 'greater than 13000'

     WHEN Salary > 12000 THEN 'greater than 12000'

WHEN Salary > 11000 THEN 'greater than 11000'

     WHEN Salary > 10000 THEN 'greater than 10000'

ELSE 'Less than or equal to 10K' END Salary_Derived

FROM [dbo].[tblEmployee]

DECLARE @Input INT = 1;

SELECT CASE WHEN @Input = 1 THEN 1 ELSE MAX(1/0) END;

Solution: For the above query the result we get is below error:

When there are Functions in WHEN OR ELSE Clauses CASE Statement tries to evaluate functions first and then the actual CASE Statement

--What happens When Function is outside CASE Statement?

SELECT MAX(CASE WHEN @Input = 1 THEN 1 ELSE 1/0 END)

SELECT MAX(1)

It works fine and gives result 1.