MySQL Database Multiple Choice Questions on “Choosing Data Types”.
1. The AUTO_INCREMENT column attribute is best used with which type?
a) FLOAT
b) INT
c) CHARACTER
d) DOUBLE
Answer: b
Clarification: The AUTO_INCREMENT is a column attribute and it is best used with the integer datatypes. It automatically assigns monotonically increasing values to the AUTO_INCREMENT columns.
2. Which datatype is best suited to store currency values?
a) INT
b) FLOAT
c) DOUBLE
d) DECIMAL
Answer: d
Clarification: Currency is a numeric information. For monetary calculations, FLOAT and DOUBLE are subject to rounding error and may not be suitable. A DECIMAL(M, 2) type is best suited for it.
3. MySQL provides a date type that has an optional time part.
a) True
b) False
Answer: b
Clarification: In MySQL, there is no data type provided which has an optional time part. The ‘DATE’ type values never have a time part. The ‘DATETIME’ type values must have a time part in them.
4. If a column is expected to store values up to 2 million, the best datatype for it is _____________
a) SMALLINT
b) TINYINT
c) MEDIUMINT
d) BIGINT
Answer: d
Clarification: The different numeric types used in MySQL are used to store different range of values. To store values of the order of a million, the MEDIUMINT or BIGINT datatype is sufficient.
5. Which of these types store the longest length of strings?
a) CHAR
b) VARCHAR
c) TINYTEXT
d) TEXT
Answer: d
Clarification: in MySQL, the different string datatypes are used to store different lenghts of the string. Here, the length would refer to the number of characters in the string. TEXT stores longer strings.
6. AUTO_INCREMENT columns must be NOT NULL.
a) True
b) False
Answer: a
Clarification: The ‘AUTO_INCREMENT’ columns must be NOT NULL. If the NOT NULL is omitted, MySQL adds it automatically. AUTO_INCREMENT column values begin with 1 and increase monotonically after it.
7. Which of these values is not valid as an AUTO_INCREMENT value?
a) 0
b) 1
c) 2
d) 3
Answer: a
Clarification: In MySQL, the ‘AUTO_INCREMENT’ values are a sequence of unique values assigned to the columns such that they increase monotonically. They begin with the value 1 and increment by a unit.
8. If an integer column is used for the values in range 1 to 99999, the best suitable datatype is ____________
a) MEDIUMINT SIGNED
b) MEDIUMINT UNSIGNED
c) SMALLINT SIGNED
d) SMALLINT UNSIGNED
Answer: b
Clarification: Since the range starts from the positive value 1, it is best to keep the datatype as UNSIGNED. The maximum value is of the order of 10^5, so a MEDIUMINT datatype is well suited.
9. INT2 maps to MySQL type _____________
a) TINYINT
b) SMALLINT
c) MEDIUMINT
d) BIGINT
Answer: b
Clarification: In order to facilitate the use of code written for SQL implementations from other vendors, MySQL maps the data types from the other vendor types to the appropriate MySQL type.
10. INT3 maps to MySQL type _____________
a) TINYINT
b) SMALLINT
c) MEDIUMINT
d) BIGINT
Answer: c
Clarification: In order to facilitate the use of the code written for SQL implementations (reusability of code) from other vendors, MySQL maps the data types from the other vendor types to the appropriate MySQL type.