/*
Binary type forces the comparison to be done byte by byte 
rather than character by character. Also it causes the comparison 
to be case sensitive even if the column is defined VARCHAR rather 
than as BINARY or BLOB. BINARY also causes trailing spaces to be 
significant. 
 
In the two queries below, CustomerID is converted to BINARY 
before the comparison is conducted. CustomerID is a varchar 
type of column but after the conversion, it becomes binary type.
*/
 
-- Query 1:
-- Zero rows are returned from the query because ALFAA is not 
-- equal to ALFAa in binary format.
SELECT CustomerID
FROM orders
WHERE CONVERT(CustomerID, BINARY)='ALFAa';
 
-- Query 2:
-- Zero rows are returned from the query because ALFAA is not 
-- equal to 'ALFAA ' in binary format because 'ALFAA ' has a 
-- trailing space.
SELECT CustomerID
FROM orders
WHERE CONVERT(CustomerID, BINARY)='ALFAA ';

/*
In this query, we only need info for the year, month, and date part 
of the OrderDate column. CONVERT is used to convert OrderDate to 
a string type of data and limit it to no more than 10 characters.
*/
SELECT CONCAT(CustomerID, ' ordered on ', CONVERT(OrderDate, CHAR(10)))
FROM orders;

/*
This query converts a string to a DATE data type.
The result is always in YYYY-MM-DD format.
*/
SELECT CONVERT('1998-03-16 00:00:00', DATE) AS date1,
       CONVERT('98-03-16 00:00:00', DATE) AS date2,
       CONVERT('19980316', DATE) AS date3,
       CONVERT('980316', DATE) AS date4;

/*
This query converts a string to a DATETIME data type.
The result is always in YYYY-MM-DD HH:MM:SS format.
*/
SELECT CONVERT('1998-03-16', DATETIME) AS datetime1,
       CONVERT('98-03-16', DATETIME) AS datetime2,
       CONVERT('19980316', DATETIME) AS datetime3,
       CONVERT('980316', DATETIME) AS datetime4;

/*
This query converts UnitPrice column from 
double data type to decimal data type.
*/
SELECT ProductName, CONVERT(UnitPrice, DECIMAL)
FROM products;

/*
This query converts a string to a TIME data type.
The result is always in HH:MM:SS format.
*/
SELECT CONVERT('1998-03-16 21:03:16', TIME) AS time1,
       CONVERT('210316', TIME) AS time2;

/*
If you are using numeric operators (such as + or -) and one 
of the operands is an unsigned integer, the result is unsigned.
 
This query converts an unsigned integer (the result of 0-5) 
to unsigned integer. Because the unsigned 64-bit big int 
ranges from 0 to 18446744073709551615, the result is 
converted to unsigned value 18446744073709551611, which is
18446744073709551616 - 5 = 18446744073709551611
 
18446744073709551616 is used because the range starts with 0.
 
In the second part of the SELECT statement, the result is
converted to signed integer so the correct result is displayed.
*/
SELECT CONVERT(0-5, UNSIGNED), 
       CONVERT(CONVERT(0-5, UNSIGNED), SIGNED);

/*
This query converts the string 'abc' in the default character 
set to the corresponding string in the utf8 character set.
*/
SELECT CONVERT('abc' USING utf8);

/*
This query reveals what the internal numeric value 
is for an enum column. It displays that y is internally
represented by 1 and n is internally represented by 2.
 
It also demonstrates that ORDER BY enum column is actual
order the column by internal numeric value.
*/
SELECT ProductName, 
       Discontinued AS enum,
       CAST(Discontinued AS SIGNED) AS enum_as_int
FROM products
ORDER BY Discontinued;

/*
This query shows that when converting an enum 
column to a string in ORDER BY clause, the
result is ordered by lexical sort rather than enum
column's internal numeric representation.
*/
SELECT ProductName, 
       CAST(Discontinued AS CHAR) AS enum_as_char
FROM products
ORDER BY CAST(Discontinued AS CHAR) DESC;

/*
Binary type forces the comparison to be done byte by byte 
rather than character by character. Also it causes the comparison 
to be case sensitive even if the column is defined VARCHAR rather 
than as BINARY or BLOB. BINARY also causes trailing spaces to be 
significant. 
 
In the two queries below, CustomerID is converted to BINARY 
before the comparison is conducted. CustomerID is a varchar 
type of column but after the conversion, it becomes binary type.
*/
 
-- Query 1:
-- Zero rows are returned from the query because ALFAA is not 
-- equal to ALFAa in binary format.
SELECT CustomerID
FROM orders
WHERE BINARY CustomerID='ALFAa';
 
-- Query 2:
-- Zero rows are returned from the query because ALFAA is not 
-- equal to 'ALFAA ' in binary format because 'ALFAA ' has a 
-- trailing space.
SELECT CustomerID
FROM orders
WHERE BINARY CustomerID='ALFAA ';