The CASE Statement
Adding a CASE Statement to a Query
In a SQL Server query we use a CASE statement to test different conditions and return different answers based on whether those conditions have been met. The example below uses the CASE statement to categorize the data in the persons table by age.
SELECT firstName, residence, age, CASE WHEN age <= 17 THEN 'Minor' WHEN age BETWEEN 18 AND 59 THEN 'Adult' WHEN age BETWEEN 60 AND 90 THEN 'Senior' ELSE 'Wise' END AS AgeCategory FROM person
Start by adding the keyword CASE followed by the keyword END. Between these two keywords you start testing for conditions; each test begins with the keyword WHEN followed by a logical test, which is similar to what you would add in a WHERE clause. "age <= 17" is the first logical test in the example above. After the logical condition you then specify what you want to have happen if that condition is met. This is done with the THEN keyword. The category "Minor" is what is specified for our example above. This same sequence can be repeated for as many conditions you want to test. The ELSE keyword is always the last condition that tests for any condition that does not meet any of the previous ones specified. Just like other calculated columns we have seen before a case statement can have an alias, in our example above the CASE statement column has an alias called "AgeCategory".
1.) Create a query that tests to see if anyone lives in the city of New York if they do then indicate it "NY", and another condition to tests to see if anyone lives in London and indicate it as "Lon". Lastly categorize everyone else who do not meet these two conditions as "Other"
CASE statements can be used in a WHERE clause, however simply referencing it's alius does not work in SQL Server. We would need to repeat the entire CASE statement in the WHERE clause and specify what value of the condition we want to see. Our example below uses the CASE statement in the WHERE clause to find all persons categorized as a "Minor".
SELECT firstName, residence, age, CASE WHEN age <= 17 THEN 'Minor' WHEN age BETWEEN 18 AND 59 THEN 'Adult' WHEN age BETWEEN 60 AND 90 THEN 'Senior' ELSE 'Wise' END AS AgeCategory FROM person WHERE CASE WHEN age <= 17 THEN 'Minor' WHEN age BETWEEN 18 AND 59 THEN 'Adult' WHEN age BETWEEN 60 AND 90 THEN 'Senior' ELSE 'Wise' END = 'Minor'
2.) Using the query you created in question 1, filter your results and return only the people who live in "NY".
3.) Create another condition in your query for the CASE statement to test. Have this condition test for all people whose name starts with "S" and assign the value "S Name" to it.