/*
This query returns the first non-NULL value in
the comma separated list. It returns NULL if all values
are NULL in the list. 
*/
SELECT COALESCE(NULL, 2, 4, NULL, 5) AS col1, 
       COALESCE(NULL, NULL, NULL) AS col2;

/*
This query finds the greatest value in this list based
on what the context the list is in.
*/
SELECT GREATEST(2, 5, 0) AS integer_context,
       GREATEST(34.0, 3.0, 5.0, 767.0) AS real_context,
       GREATEST('B', 'a', 'c', 'd') AS case_insensitive_context;

/*
This query finds the first occurrence of the index position 
number for the argument that is greater than the first argument.
 
In the first column, 23 is less than 30 which has a
index position number of 3 so it returns 3.
 
In the second column, 10 is less than 100 which has a 
index position number of 2 so it returns 2.
 
In the third column, 22 is less than 23 which has a
index position number of 0 so it returns 0.
 
In the fourth column, -1 is returned because the first
argument is NULL.
 
In the fifth column, 7 is returned because 501 is not 
less than any number in the list. So it returns is the 
last index number in the list plus one which is 6+1.
*/
SELECT INTERVAL(23, 1, 15, 17, 30, 44, 200) AS col1,
       INTERVAL(8, 1, 8, 100, 1000) AS col2,
       INTERVAL(22, 23, 30, 44, 200) AS col3,
       INTERVAL(NULL, 23, 30, 44, 200) AS col4,
       INTERVAL(501, 1, 15, 17, 30, 44, 200, 449) AS col5;

/*
This query retrieves the employee who does not 
report to anyone.
*/
SELECT EmployeeID, FirstName, LastName
FROM employees 
WHERE ISNULL(ReportsTo)=1;
 
-- This query returns the same result.
SELECT EmployeeID, FirstName, LastName
FROM employees
WHERE ISNULL(ReportsTo)=TRUE;

/*
This query finds the returns the smallest (minimum-valued) 
value in this list based on what the context the list is in.
*/
SELECT LEAST(2, 5, 0) AS integer_context,
       LEAST(34.0, 3.0, 5.0, 767.0) AS real_context,
       LEAST('B', 'a', 'c', 'd') AS case_insensitive_context;