SQL - Case

SQL - CASE (select)

For this concept, visualize the following table.

employee_name	admin
Ted	0
Terry	0
Trish	1

As you can see, we have a list of a few employees and then an admin column with boolean (true/false) values for their administration rights (admin). A zero means they are not an admin a 1 mean they are.

Using case logic, we can present this information in a better form.

SQL Code:

SELECT employee_name
   CASE admin
     WHEN 1 THEN 'yes'
     ELSE 'no'
  END 'admin'
FROM employees;

employee_name	admin
Ted	no
Terry	no
Trish	yes

In short all we really did is replace 1's and 0's with the words 'yes' and 'no'.

SQL - Case (update)

Case functions additionally allow for the updating of records within your table. For example, we could update the prices of items in our online store, but more importantly we could update very specific records because of the conditional logic allowed by case.

Let's use the following table for this next example.

item	quantity	price
goldfish	12	1.00
guppy	24	0.50
blow fish	1	5.00

Let's say we wanted to have a sale to clean out some of our overstock. We'll go ahead and take 25% off of all our items that we currently have 20 or more of (>20). Then we'll take 10% off the items that we have 10-20 of (between 10 and 20) and everything else we will discount only 5%.

SQL Code:

UPDATE inventory SET price = price *
   CASE
     WHEN quantity > 20 THEN 0.75
     WHEN quantity BETWEEN 10 AND 20 THEN 0.90
  ELSE 0.95
END;

item	quantity	price
goldfish	12	.9
guppy	24	0.375
blow fish	1	4.75

Each price has automatically been reduced by the appropriate percentages.