[SQL] CASE, HAVING, EXISTS

CASE

Example 1

SELECT SW_ID, Fox, Badger,
CASE
	WHEN Badger < 100 then 'A little'
	WHEN Badger Between 100 and 140  then 'Average'
	ELSE 'A lot'
END AS BadgerPopulation
FROM SmallWild
ORDER BY Fox

Example 2

+ WHERE

---Showing records that comply the condition
SELECT Fox, Badger, WildRabbits FROM SmallWild
WHERE
CASE WHEN Fox > 700 then 'show'
ELSE 'hide'
END = 'show'

Example 3

---order by Jackal, but if Jackal population is 0, order by Fox:
SELECT Fox, Jackal
FROM SmallWild
ORDER BY 
(CASE
	WHEN Jackal is null then Fox
	ELSE Jackal
END);

HAVING

SELECT COUNT(ForestDistrict) as FD_Count, HuntingDistrict
FROM ForestDistricts
GROUP BY HuntingDistrict
HAVING COUNT(ForestID) > 18;

EXISTS


SELECT Fox, Badger
FROM SmallWild
WHERE EXISTS (SELECT ForestDistrict FROM ForestDistricts 
WHERE SmallWild.SW_ID = ForestDistricts.ForestID 
and HuntingDistrict > 31);