Data Types
89 important questions on Data Types
What is the notation for writing literal BIT values?
Can strings be non-binary and/or binary?
Can strings be case sensitive and/or case insensitive?
- Higher grades + faster learning
- Never study anything twice
- 100% sure, 100% understanding
What general type of data type is a BLOB field?
Name a special temporal data type in MySQL?
Does MySQL have support for spatial data types?
What 4 factors must be considered when choosing a numeric datatype?
2. The amount of storage space required
3. The display width for presentation
4. The column precision for fixed-point and floating point values
What is precision and scale for floating-point and fixed-point values?
What are the 5 integer data types and their storage, signed, and unsigned range?
2. SMALLINT | 2 bytes (256^2) | -32,768 to 32,767 | 0 to 65,535
3. MEDIUMINT | 3 bytes (256^3)| -8,388,608 to 8,388,608 | 0 to 16,777,215
4. INT | 4 bytes (256^4)| -(0.5*256^4) to 0.5*256^4-1 | 0 to 256^4 -1
5. BIGINT | 8 bytes
In the query century INT(4), what does the 4 stand for?
What is the purpose of the display width?
What is the default display width based on?
What are the 2 floating point data types?
What is the default precision and scale for the float and double datatype?
In what format are floating-point values stored?
Why are floating point numbers approximates in MySQL?
Which data type is more accurate, FLOAT and DOUBLE or DECIMAL?
What are the default precision and scale of a DECIMAL?
What is the storage requirement formula for a DECIMAL?
What data type is the synonym of a DECIMAL in MySQL?
What is the difference between a MySQL NUMERIC and a Standard SQL NUMERIC?
Given bit_col1 BIT(4) bit_col2 BIT(20), what does 4 and 20 stand for?
What are the 8 string data types in MySQL, if they are variable or fixed length, and if they are binary or not?
2. VARCHAR | variable | non-binary
3. TEXT | variable | non-binary
4. BINARY | fixed | binary
5. VARBINARY | variable | binary
6. BLOB | variable | binary
7. ENUM | Enumeration of fixed set of legal values
8. SET | Set consisting of fixed set of legal values
What are the 5 factors to consider when choosing a string data type?
2. Maximum length of values?
3. Fixed or variable amount of storage?
4. How are trailing spaces handled for comparison, storage and retrieval?
5. Number of distinct values required?
Do all character sets have the same storage requirements?
Does a multi-byte character set require a fixed or variable number of bytes per character?
What is the collation of a character set?
What are 2 factors the collation changes in character comparison?
Is a binary collocation case sensitive? and accent sensitive?
Are comparisons for binary strings byte or character based?
Can you write a byte value as quoted string in MySQL?
Is a multi-byte character if stored in a binary string treated as multiple individual bytes or a collection of bytes?
What is the range of acceptable values for the length of a VARCHAR?
How many bytes are used to store the length of a VARCHAR value?
What is the storage requirement of a CHAR(10) with a utf8 character set?
What are the default character set and collation for columns?
What is the range of the length of a BINARY field?
Does MySQL remove trailing spaces for a BINARY field, and VARBINARY?
VARBINARY No
What are the four different sizes for a BLOB field? (From small to large)
BLOB
MEDIUMBLOB
LONGBLOB
What is the storage requirement formula for a TINYBLOB? And what is the maximum size of a TINYBLOB?
As what datatype does MySQL internally store values of ENUM fields?
What is the maximum allowed amount of members for an ENUM field?
What is the storage formula for members of an ENUM field?
1 if <= 255
2 if >= 255
What happens with an invalid SET member on insertion?
How does MySQL internally store values of a SET field?
What is the maximum amount of SET members?
What is the difference between an integer 1 and string '1' in terms of SET or ENUM field values?
What 5 temporal datatypes are there in MySQL? (with storage requirement)
TIME 3 bytes
DATETIME 8 bytes
TIMESTAMP 4 bytes
YEAR 1 byte
What is the range of YEAR(4)?
What is the range of a TIME field?
What is the range of a DATETIME field?
What is the range of a TIMESTAMP field?
What is the ZERO value for a DATETIME field?
What is the default date representation of MySQL?
What is the deviation between MySQL date input and ANSI SQL?
2. The delimiter does not have to be a '-' character
3. Two digit years are converted to 4 digit years
What function formats dates? and times?
In what circumstances is a TIMESTAMP field appropriate?
In what timezone are TIMESTAMP fields internally stored?
What does MySQL automatically assign on the first timestamp field?
Can one TIMESTAMP field have DEFAULT CURRENT_TIMESTAMP an another TIMESTAMP field ON UPDATE CURRENT_TIMESTAMP in the same table?
What is the meaning of a NULL insertion for TIMESTAMP fields?
Is it possible to set the timezone for a MySQL connection?
What are the 3 time zone formats supported by MySQL?
2. named time zone
3. SYSTEM time zone
What is the default time zone for a connection?
What is the default of the global timezone in MySQL?
How do you read and set the timezone for a connection?
Write: SET time_zone = '+00:00'
What function supports TIMEZONE conversion?
What are the 3 column attributes for numeric types? and what do they do?
2. ZEROFILL (left pad with zero's)
3. AUTO_INCREMENT (assign auto increment value)
What are the 3 column attributes for string types? and what do they do?
2. COLLATE (sets the collation)
3. BINARY (sets a binary collation)
What are the 2 general column attributes? What do they do?
2. DEFAULT (set the default value)
What is the default default value for string fields?
What is the default default value for date fields?
What datatypes do not support default values?
What function returns the last inserted auto increment id?
Does MySQL reassign the ids of deleted highest auto increment keys in a composite key?
Does MySQL allow an auto_increment field with a Non-unique index?
What happens when you INSERT a 0 in an auto_increment field?
What does MySQL by default do with erroneous input values?
What happens on INSERT for a field with no default value and strict mode turned off?
What happens on INSERT for a field with no default value and strict mode is turned on in a non-transactional table?
What are the 5 conversions MySQL handles for invalid values?
2. String truncation
3. Conversion to datatype default
4. Enumeration and set value conversion
5. Handling of NULL to NOT NULL columns
What does MySQL insert when storing a value smaller than the minimum value of the datatype?
What does MySQL do when a to lengthy string is inserted in a char field?
What does MySQL insert for an invalid ENUM value?
What does MySQL insert when inserting a NULL into a NOT NULL column in a multiple command statement?
Can partial updates occur when STRICT_ALL_TABLES turned on and invalid input data provided?
What 2 date restrictions are enabled in TRADITIONAL sql mode?
How do you disable strict mode for a single insert/update statement?
What sql mode enables support for invalid dates?
The question on the page originate from the summary of the following study material:
- A unique study and practice tool
- Never study anything twice again
- Get the grades you hope for
- 100% sure, 100% understanding
![Trustpilot Logo](/images/design_2020/lp/clp-trustpilot-en.png)