MySQL Interview Questions and Answers
Ques 106. How To Use CASE Expression?
There are 2 ways to use the CASE expression. The first way is to return one of the predefined values based on the comparison of a given value to a list of target values. The second way is to return one of the predefined values based on a list of conditions. Here is the syntax of both types of CASE expressions:
CASE value WHEN target_value THEN result
WHEN target_value THEN result
WHEN target_value THEN result
...
ELSE result
END
CASE WHEN condition THEN result
WHEN condition THEN result
WHEN condition THEN result
...
ELSE result
END
The tutorial exercise below gives two good examples:
SELECT CASE 'Sun' WHEN 'Mon' THEN 'Open'
WHEN "Fri" THEN "Open" ELSE 'Closed' END FROM DUAL;
Closed
SELECT CASE WHEN HOUR(CURRENT_TIME())<9 THEN
'Closed'
WHEN HOUR(CURRENT_TIME())>17 THEN 'Closed'
ELSE 'Open' END FROM DUAL;
Closed
Ques 107. What Are Date and Time Data Types in MySQL?
MySQL supports the following date and time data types:
► DATE - A date in the range of '1000-01-01' and '9999-12-31'. Default DATE format is "YYYY-MM-DD".
► DATETIME - A date with the time of day in the range of '1000-01-01 00:00:00' and '9999-12-31 23:59:59'. Default DATETIME format is "YYYY-MM-DD HH:MM:SS".
► TIMESTAMP - A timestamp. The range is '1970-01-01 00:00:00' to partway through the year 2037. Default DATETIME format is "YYYY-MM-DD HH:MM:SS".
► TIME - A time. The range is '-838:59:59' to '838:59:59'. Default TIME format is "HH:MM:SS".
► TIME - A time. The range is '-838:59:59' to '838:59:59'. Default TIME format is "HH:MM:SS".
► YEAR - A year in 4 digits in the range of 1901 and 2155. Default YEAR format is "YYYY".
Ques 108. How To Write Date and Time Literals?
MySQL offers a number of formats for you to use to enter date and time literals:
► ANSI standard format: "YYYY-MM-DD HH:MM:SS".
► Non-standard limiters. Like: "YYYY/MM/DD HH^MM^SS" or "YYYY.MM.DD HH-MM-SS".
► No limiters. Like: "YYYYMMDD" for a date or "HHMMSS" for a time.
► Decimal numbers. Like: 8 digits dddddddd for a date or 6 digits dddddd for a time.
The tutorial exercise below gives you some good examples:
SELECT DATE('1997-01-31') FROM DUAL;
1997-01-31
SELECT DATE('1997-01-31 09:26:50') FROM DUAL;
1997-01-31
SELECT TIME('1997-01-31 09:26:50') FROM DUAL;
09:26:50
SELECT DATE('1997/01/31 09^26^50') FROM DUAL;
1997-01-31
SELECT TIME('1997/01/31 09^26^50') FROM DUAL;
09:26:50
SELECT DATE('19970131') FROM DUAL;
1997-01-31
SELECT TIME('092650') FROM DUAL;
09:26:50
SELECT DATE(19970131) FROM DUAL; -- Crazy format
1997-01-31
SELECT TIME(092650) FROM DUAL; -- Crazy format
09:26:50
Most helpful rated by users: