Data Types

Question

What is the notation for writing literal BIT values?

Answer 1

Both binary and non-binary

Answer 2

Both case sensitive and case insensitive

Answer 3

String datatype

Answer 4

The YEAR datatype

Answer 5

1. The range of the values the data type represents
2. The amount of storage space required
3. The display width for presentation
4. The column precision for fixed-point and floating point values

Answer 6

Precision is the number of significant digits (integer part) and scale is the number of digits to the right of the decimal (fractional part)

Answer 7

Answer 8

The display width of the column

Answer 9

To pad the output with spaces to the left (for presentation)

Answer 10

The number of characters needed to display the full range of values for the data type (including the minus sign)

Answer 11

FLOAT (single precision, 4 bytes) and DOUBLE (double precision, 8 bytes)

Answer 12

The maximum of the hardware of the host

Answer 13

The mantissa/exponent representation

Answer 14

Due to the mantissa/exponent representation for storage

Answer 15

precision of 10 and a scale of 0

Answer 16

4 bytes per nine digits on each side of the decimal

Answer 17

In standard SQL, the precision for NUMERIC must be exactly the number of digits given in the column definition. The precision for DECIMAL must be at least that many digits but is allowed to be more.

Answer 18

The amount of bits

Answer 19

Answer 20

1. Is the string binary or non-binary?
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?

Answer 21

No, the amount of valid characters in the set defines the needed storage

Answer 22

May be fixed or may be variable

Answer 23

The sorting order

Answer 24

Case sensitivity and accent sensitivity

Answer 25

Byte based

Answer 26

Multiple individual bytes

Answer 27

0 to 65,635

Answer 28

1 byte for a value under 256, 2 bytes for a value above 256

Answer 29

10 * the amount of bytes for the widest character (3), thus 10 * 3

Answer 30

The character set and collation of the table

Answer 31

BINARY Yes
VARBINARY No

Answer 32

TINYBLOB
BLOB
MEDIUMBLOB
LONGBLOB

Answer 33

L + 1 byte and 255 bytes

Answer 34

As integers

Answer 35

1 or 2 bytes
1 if <= 255
2 if >= 255

Answer 36

It will be ignored

Answer 37

As bitmap pattern

Answer 38

Integer is the numeric index and string is the string value

Answer 39

DATE 3 bytes
TIME 3 bytes
DATETIME 8 bytes
TIMESTAMP 4 bytes
YEAR 1 byte

Answer 40

1901 to 2155

Answer 41

-838:59:59 to 838:59:59

Answer 42

1000-01-01 00:00:00 to 9999-12-31 23:59:59

Answer 43

1970-01-01 00:00:00 to mid-year 2037

Answer 44

0000-00-00 00:00:00

Answer 45

ANSI date representation also known as ISO 8601

Answer 46

1. Leading zero's for months or days may be ommitted
2. The delimiter does not have to be a '-' character
3. Two digit years are converted to 4 digit years

Answer 47

DATE_FORMAT() and TIME_FORMAT()

Answer 48

When tracking creation and modification times

Answer 49

DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP properties

Answer 50

CURRENT_TIMESTAMP

Answer 51

1. signed hour/minute offset
2. named time zone
3. SYSTEM time zone

Answer 52

The global timezone

Answer 53

The SYSTEM timezone

Answer 54

Read: SELECT @@session.time_zone;
Write: SET time_zone = '+00:00'

Answer 55

CONVERT_TZ(date, from, to);

Answer 56

1. UNSIGNED (No negative values)
2. ZEROFILL (left pad with zero's)
3. AUTO_INCREMENT (assign auto increment value)

Answer 57

1. CHARACTER SET (sets the charset)
2. COLLATE (sets the collation)
3. BINARY (sets a binary collation)

Answer 58

1. NULL and NOT NULL (allow/disallow null values)
2. DEFAULT (set the default value)

Answer 59

Empty string

Answer 60

Zero value of the datatype

Answer 61

TEXT and BLOB (and numeric auto_increment)

Answer 62

LAST_INSERT_ID()

Answer 63

Same as NULL, the next value except when NO_AUTO_VALUE_ON_ZERO is enabled then it's the value 0

Answer 64

It converts them to the nearest legal value

Answer 65

The implicit default value is inserted and a warning is issued

Answer 66

Error occurs (partial update may exist)

Answer 67

1. Out-of-range to in-range values
2. String truncation
3. Conversion to datatype default
4. Enumeration and set value conversion
5. Handling of NULL to NOT NULL columns

Answer 68

The minimum value of the datatype

Answer 69

It truncates the string

Answer 70

An empty string

Answer 71

The implicit default value for the column's datatype

Answer 72

NO_ZERO_DATE and NO_ZERO_IN_DATE

Answer 73

INSERT IGNORE and UPDATE IGNORE

Answer 74

ALLOW_INVALID_DATES

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?

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?

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?

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?

What are the 5 factors to consider when choosing a string data type?

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?

What are the four different sizes for a BLOB field? (From small to large)

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?

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)

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?

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?

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?

What function supports TIMEZONE conversion?

What are the 3 column attributes for numeric types? and what do they do?

What are the 3 column attributes for string types? and what do they do?

What are the 2 general column attributes? What do they do?

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?

a PDF, study it super fast