/*
Get the supplier's continent based on its country. For 
example, if country is USA, then continent is North America. 
If the country is not USA, Canada, Brazil, Japan, Singapore, 
Australia, the continent is Europe which is covered in the 
ELSE part of the CASE.
*/
SELECT CompanyName, 
    CASE Country WHEN 'USA' THEN 'North America' 
                 WHEN 'Canada' THEN 'North America'
                 WHEN 'Brazil' THEN 'South America'
                 WHEN 'Japan' THEN 'Asia'
                 WHEN 'Singapore' THEN 'Asia'
                 WHEN 'Australia' THEN 'Australia'
                 ELSE 'Europe' END AS Continent
FROM Suppliers
ORDER BY CompanyName;

/*
Get the supplier's continent based on its country. 
The result is ordered by using column alias Continent.
*/
SELECT CompanyName, 
    CASE Country WHEN 'USA' THEN 'North America' 
                 WHEN 'Canada' THEN 'North America'
                 WHEN 'Brazil' THEN 'South America'
                 WHEN 'Japan' THEN 'Asia'
                 WHEN 'Singapore' THEN 'Asia'
                 WHEN 'Australia' THEN 'Australia'
                 ELSE 'Europe' END AS Continent
FROM Suppliers
ORDER BY Continent;

/*
This query returns the exact result as that returned
by Practice #1-1. But here the second version of CASE
statement is used.
*/
SELECT CompanyName, 
    CASE WHEN Country = 'USA' THEN 'North America' 
         WHEN Country = 'Canada' THEN 'North America'
         WHEN Country = 'Brazil' THEN 'South America'
         WHEN Country = 'Japan' THEN 'Asia'
         WHEN Country = 'Singapore' THEN 'Asia'
         WHEN Country = 'Australia' THEN 'Australia'
         ELSE 'Europe' END AS Continent
FROM Suppliers
ORDER BY CompanyName;

/*
This query returns the same result as that in 
Practice #1-3 but the query here is shorter. 
This is because IN comparison operator is used 
with CSV (Comma Separated Values) list.
*/
SELECT CompanyName, 
    CASE WHEN Country IN ('USA', 'Canada') THEN 'North America'
         WHEN Country = 'Brazil' THEN 'South America'
         WHEN Country IN ('Japan', 'Singapore') THEN 'Asia'
         WHEN Country = 'Australia' THEN 'Australia'
         ELSE 'Europe' END AS Continent
FROM Suppliers
ORDER BY CompanyName;

/*
In this query, we categorize products into High Price and
Low Price. If the unit price is equal to or greater than 20,
the product is in High Price group. Otherwise, Low Price group.
*/
SELECT ProductName, 
    UnitPrice,
    IF(UnitPrice>=20, 'High Price', 'Low Price') AS "Price Level"
FROM Products;

/*
In this query, the result is ordered by FirstName or 
LastName, depending on if modulo operation result is 
0 or not. If the current Unix timestamp modulo 2 is zero, 
the query result is ordered by FirstName. Otherwise, LastName.
 
So if you execute the query multiple times, you would normally
get results in different orders.
 
Info about UNIX_TIMESTAMP, refer to Date and Time Function.
 
Info about modulo operator, refer to Comparison Operators.
*/
SELECT FirstName, LastName
FROM Employees
ORDER BY IF(UNIX_TIMESTAMP()%2=0, FirstName, LastName);

/*
This query finds out who is the CEO of Northwind Trader.
If ReportsTo is NULL, we want to return 'Is CEO'. Otherwise,
return the ReportsTo value.
 
The ReportsTo is NULL for Andrew Fuller because he does
not report to anyone. 
*/
SELECT FirstName, LastName, IFNULL(ReportsTo, 'Is CEO')
FROM Employees;

/*
Query 1:
 
This query uses IF function as inner function and wrapped 
it with NULLIF as outer function.
 
For inner function IF, when Fax is not available 
(empty string), it returns No Fax. Otherwise, 
returns Got Fax. 
 
In outer function NULLIF, we compare the result of IF function
with the string No Fax. When IF function returns No Fax, 
NULLIF function returns NULL. Otherwise, it returns the 
result of IF function.
*/
SELECT CompanyName, 
       Fax, 
       NULLIF(IF(Fax='', 'No Fax', 'Got Fax'), 'No Fax') AS Note
FROM Customers;
 
/*
Query 2:
 
This query is an equivalent query as Query 1 and returns
the same result. But here CASE statement is used instead 
of NULLIF function.
*/
SELECT CompanyName, 
    Fax, 
    CASE WHEN IF(Fax='', 'No Fax', 'Got Fax')='No Fax' THEN NULL 
         ELSE IF(Fax='', 'No Fax', 'Got Fax') 
    END AS Note
FROM Customers;