Page 30 - SQL

SELECT * FROM DEPT
ORDER BY
CASE DEPARTMENT
WHEN 'MARKETING' THEN 1
WHEN 'SALES' THEN 2
WHEN 'RESEARCH' THEN 3
WHEN 'INNOVATION' THEN 4
ELSE 5
END,
CITY

ID REGION CITY DEPARTMENT EMPLOYEES_NUMBER

12 New England Boston MARKETING 9

15 West San Francisco MARKETING 12

9 Midwest Chicago SALES 8

14 Mid-Atlantic New York SALES 12

5 West Los Angeles RESEARCH 11

10 Mid-Atlantic Philadelphia RESEARCH 13

4 Midwest Chicago INNOVATION 11

2 Midwest Detroit HUMAN RESOURCES 9

Using CASE in UPDATE

sample on price increases:

UPDATE ItemPrice
SET Price = Price *
CASE ItemId
WHEN 1 THEN 1.05
WHEN 2 THEN 1.10
WHEN 3 THEN 1.15
ELSE 1.00
END

CASE use for NULL values ordered last

in this way '0' representing the known values are ranked first, '1' representing the NULL values are
sorted by the last:

SELECT ID
,REGION
,CITY
,DEPARTMENT
,EMPLOYEES_NUMBER

https://riptutorial.com/ 12