Shared Flashcard Set

Details

MySQL 5.0 Certification, Chapter 5
Data Types
148
Computer Science
Professional
06/01/2010
Click here to study/print these flashcards.

Create your own flash cards! Sign up here.

Additional Computer Science Flashcards

 

 

Cards

Term
Numeric values
 Definition
May or may not have a fractional part and may have a leading sign.
Term
String values
 Definition
May be non-binary or binary to store characters or raw bytes. Written within quotes.
Term
Temporal values
 Definition
Include dates, times, and values with both a date and time part.
Term
TINYINT
 Definition
1 byte integer storage
Term
TINYINT signed range
 Definition
-128 to 127
Term
TINYINT unsigned range
 Definition
0 to 255
Term
SMALLINT
 Definition
2 byte integer storage
Term
SMALLINT signed range
 Definition
-32,768 to 32,767
Term
SMALLINT unsigned range
 Definition
0 to 65,535
Term
MEDIUMINT
 Definition
3 byte integer storage
Term
MEDIUMINT signed range
 Definition
-8,388,608 to 8,388,607
Term
MEDIUMINT unsigned range
 Definition
0 to 16,777,215
Term
INT
 Definition
4 byte integer storage
Term
INT signed range
 Definition
-2,147,483,648 to 2,147,483,647
Term
INT unsigned range
 Definition
0 to 4,294,967,295
Term
BIGINT
 Definition
8 byte integer storage
Term
BIGINT signed range
 Definition
-9,223,372,036,854,775,808 to 9,223,372,036,854,775,807
Term
BIGINT unsigned range
 Definition
0 to 18,446,744,073,709,551,615
Term
Does INT(4) indicate the number of bytes or the display width?
 Definition
Display width.
Term
Floating-point data types
 Definition
Approximate value numbers with an integer part, a fractional part, or both.
Term
How are floating point values stored?
 Definition
The native binary floating-point format used by the server host's CPU.
Term
Are floating point values subject to rounding errors?
 Definition
Yes.
Term
FLOAT
 Definition
Single precision floating type that requires 4 bytes for storage.
Term
DOUBLE
 Definition
Double-precision floating type that requires 8 bytes for storage.
Term
What does FLOAT(10,3) specify?
 Definition
A single-precision column with a precision of 10 digits and a scale of 3 digits.
Term
Fixed-point data types
 Definition
Represent exact-value numbers that have an integer part, a fractional part, or both.
Term
DECIMAL
 Definition
Fixed-decimal storage format.
Term
What are the default precision and scale for the DECIMAL data type?
 Definition
10 and 0
Term
What does DECIMAL(10,2) represent?
 Definition
A fixed-point data type with precision of 10 and scale of 2.
Term
How many bytes are required for DECIMAL storage?
 Definition
Approximately 4 bytes per nine digits on each side of the decimal point.
Term
NUMERIC is a synonym for what other data type?
 Definition
DECIMAL
Term
Does BIT(4) specify the the number of bits or the display width?
 Definition
Number of bits.
Term
How many bits can a BIT field hold?
 Definition
1 to 64 bits.
Term
How can literal bit values be written in a BIT field?
 Definition
b'val' ... for instance, b'111' equals 15
Term
CHAR
 Definition
Fixed-length non-binary string
Term
VARCHAR
 Definition
Variable-length non-binary string
Term
TEXT
 Definition
Variable-length non-binary string
Term
BINARY
 Definition
Fixed-length binary string
Term
VARBINARY
 Definition
Variable-length binary string
Term
BLOB
 Definition
Variable-length binary string
Term
ENUM
 Definition
Fixed set of legal values, pick one
Term
SET
 Definition
Fixed set of legal values, pick one or more
Term
If character set and collation are important, use ______ strings.
 Definition
non-binary
Term
Does CHAR(4) contain 4 bytes of data?
 Definition
Only if you are using a one-byte-per-character character set.
Term
What does a _cs suffix on a collation indicate?
 Definition
case sensitive
Term
What does a _ci suffix on a collation indicate?
 Definition
case insensitve
Term
What does a _bin suffix on a collation indicate?
 Definition
binary
Term
What result do you get from the following statement:
mysql> SELECT UPPER(BINARY 'AaBb');
 Definition
AaBb
Term
How is a CHAR(30) column with less than 30 characters stored?
 Definition
It is padded with spaces to a length of 30 characters.
Term
What range of lengths can a CHAR column have?
 Definition
0 to 255
Term
VARCHAR maximum length
 Definition
Up to 65,535
Term
How much space does it take to store a VARCHAR?
 Definition
The number of actual characters, plus 1 or 2 bytes to record the length.
Term
What is the storage required for TINYTEXT?
 Definition
L characters + 1 byte
Term
TINYTEXT maximum length
 Definition
255 characters
Term
TEXT maximum length
 Definition
65,535 characters
Term
TEXT storage required
 Definition
L characters + 2 bytes
Term
MEDIUMTEXT maximum length
 Definition
16,777,215 characters
Term
MEDIUMTEXT storage required
 Definition
L characters + 3 bytes
Term
LONGTEXT maximum length
 Definition
4,292,967,295 characters
Term
LONGTEXT storage required
 Definition
L characters + 4 bytes
Term
BINARY maximum length
 Definition
255 bytes
Term
BINARY storage required
 Definition
M bytes
Term
VARBINARY maximum length
 Definition
65,535 bytes
Term
VARBINARY storage required
 Definition
L bytes plus 1 or 2 bytes
Term
TINYBLOB maximum length
 Definition
255 bytes
Term
TINYBLOB storage required
 Definition
L + 1 bytes
Term
BLOB storage required
 Definition
L + 2 bytes
Term
BLOB maximum length
 Definition
65,535 bytes
Term
MEDIUMBLOB storage required
 Definition
L + 3 bytes
Term
MEDIUMBLOB maximum length
 Definition
16,777,215 bytes
Term
LONGBLOB storage required
 Definition
L + 4 bytes
Term
LONGBLOB maximum length
 Definition
4,294,967,295 bytes
Term
How are ENUM values stored?
 Definition
As an integer.
Term
What is the value 0 used for in ENUM types?
 Definition
Illegal values.
Term
How does SET differ from ENUM?
 Definition
You can pick multiple values from a SET.
Term
How are SET values stored?
 Definition
As a bitmap using one bit per member.
Term
What happens if you try to store an invalid member in a SET column?
 Definition
It is ignored. (Error in strict mode.)
Term
Here is an example table:
CREATE TABLE t (siblings ENUM('0', '1', '2', '3', '>3');
INSERT INTO t (siblings) VALUES ('3');
INSERT INTO t (siblings) VALUES (3);

What result does this query give:
SELECT * FROM t WHERE siblings = 3;
 Definition
2
Term
DATE storage required
 Definition
3 bytes
Term
DATE range
 Definition
'1000-01-01' to '9999-12-31'
Term
TIME storage reqired
 Definition
3 bytes
Term
TIME range
 Definition
'-838:59:59' to '838:59:59'
Term
DATETIME storage required
 Definition
8 bytes
Term
DATETIME range
 Definition
'1000-01-01 00:00:00' to '9999-12-31 23:59:59'
Term
TIMESTAMP storage required
 Definition
4 bytes
Term
TIMESTAMP range
 Definition
'1970-01-01 00:00:00' to mid-year 2037
Term
YEAR storage required
 Definition
1 byte
Term
YEAR range
 Definition
1901 to 2155 (for YEAR(4)),
1970 to 2069 (for YEAR(2))
Term
How are 2-digit years stored?
 Definition
Converted to 4-digit years between 1970 and 2069.
Term
What is the range for the TIME portion of the DATETIME datatype?
 Definition
'00:00:00' to '23:59:59'
Term
What is the default initialization and update behavior for TIMESTAMP?
 Definition
The first TIMESTAMP will use current date for both creation and updating.
Term
How can you use DEFAULT CURRENT_TIMESTAMP on one column and UPDATE CURRENT_TIMESTAMP on another?
 Definition
Explicitly set the first column to null on creation (which defaults to CURRENT_TIMESTAMP) and specify ON UPDATE CURRENT_TIMESTAMP on the other column.
Term
How is signed hour/minute offset of a time zone expressed?
 Definition
'+hh:mm' or '-hh:mm'
Term
How is a named time zone defined?
 Definition
By a string such as 'US/Eastern', but depends on having a set of time zone tables in the mysql database.
Term
What is the SYSTEM time zone?
 Definition
The time zone value that the MySQL server retrieves from the server host.
Term
Which time zone format is the default?
 Definition
SYSTEM
Term
What is the output from the following series of commands:
> SET time_zone = '+00:00';
> CREATE TABLE ts_test (ts TIMESTAMP);
> INSERT INTO ts_test (ts) VALUES (NULL);
> SET time_zone '+02:00';
> SELECT * FROM ts_test;
 Definition
The current time plus 2 hours.
Term
What is the output of the following query:
> SELECT CONVERT_TZ('2010-06-01 13:30:00', '+01:00', '+03:00');
 Definition
2010-06-01 15:30:00
Term
UNSIGNED attribute
 Definition
Causes negative values to be disallowed.
Term
ZEROFILL attribute
 Definition
Causes retrieved values to be left-padded with zeros up to the column's display width.
Term
AUTO_INCREMENT attribute
 Definition
Generates sequence of successive unique integer values.
Term
Can there be more than one AUTO_INCREMENT column per table?
 Definition
No
Term
What other attributes must an AUTO_INCREMENT column have?
 Definition
Indexed, NOT NULL
Term
CHARACTER SET attribute
 Definition
Specifies the character set to use for the column.
Term
What is a synonym for the CHARACTER SET attribute?
 Definition
CHARSET
Term
COLLATE attribute
 Definition
specifies the character set collation
Term
BINARY attribute
 Definition
specifies a binary collation of the column's character set
Term
How is the binary character set special?
 Definition
It modifies the column's datatype; CHAR, VARCHAR, and TEXT become BINARY, VARBINARY, and BLOB, respectively
Term
NULL and NOT NULL attributes
 Definition
indicate whether a column can contain null values (default is NULL)
Term
DEFAULT value attribute
 Definition
Provides a default value for creating new columns.
Term
Which columns cannot have a DEFAULT attribute?
 Definition
TEXT and BLOB columns, or columns with the AUTO_INCREMENT attribute
Term
Can default values be an expression?
 Definition
No, with the exception of CURRENT_TIMESTAMP for a single TIMESTAMP column.
Term
What happens if a default value of NULL is specified for a NOT NULL column?
 Definition
error
Term
What happens if an out of range default value is specified when creating a table?
 Definition
error
Term
What happens if no default value is specified when creating a table?
 Definition
For columns that allow NULL, MySQL adds DEFAULT NULL. For columns that do not allow NULL, no default clause is added and implicit default values are used.
Term
What is the implicit default value for numeric columns?
 Definition
0
Term
What is the implicit default value for string columns?
 Definition
empty string, or first value for ENUM types
Term
What is the implicit default value for temporal columns?
 Definition
The "zero" value for the data type.
Term
Which data types cannot be specified as PRIMARY KEY or UNIQUE?
 Definition
BLOB and TEXT
Term
What function returns the most recently generated AUTO_INCREMENT value?
 Definition
LAST_INSERT_ID()
Term
Is LAST_INSERT_ID() client-specific?
 Definition
yes
Term
What data types can have an AUTO_INCREMENT attribute?
 Definition
integer
Term
Can AUTO_INCREMENT sequences contain negative values?
 Definition
no
Term
What happens if an AUTO_INCREMENT column value of null or 0 is inserted explicitly?
 Definition
The next value in the sequence is generated.
Term
What happens if an integer value is inserted explicitly into an AUTO_INCREMENT column?
 Definition
No error if the value isn't already present in the column. If the value is larger than the current sequence counter, subsequent values begin with the value plus one.
Term
What happens when you REPLACE an AUTO_INCREMENT column?
 Definition
The existing record is deleted and then the new record is inserted using the next sequence value.
Term
What happens when you reach the upper limit of an AUTO_INCREMENT column?
 Definition
Duplicate key error.
Term
Are deleted rows at the high end of a sequence reused for new AUTO_INCREMENT values?
 Definition
no (for MyISAM or InnoDB tables)
Term
How do MyISAM composite indexes use an AUTO_INCREMENT column?
 Definition
They create independent sequences within a single table and reuse values deleted from the high end of any sequence.
Term
What is the default MySQL mode for handling missing or invalid data values (forgiving or strict)?
 Definition
forgiving
Term
How do you explicitly set sql_mode to forgiving?
 Definition
SET sql_mode = '';
Term
How do you set sql_mode to strict?
 Definition
SET sql_mode = 'STRICT_TRANS_TABLES';
or
SET sql_mode = 'STRICT_ALL_TABLES';
Term
How do you set sql_mode to be strict plus other restrictions on date checking and division by zero?
 Definition
SET sql_mode = 'TRADITIONAL';
Term
What happens with the following statement in forgiving mode if column i is an integer column:
INSERT INTO t (i) VALUES('43x');
 Definition
The value is converted to 43 and inserted, and it generates a warning.
Term
What happens in strict mode when data is missing and no default value is specified?
 Definition
An error occurs for transactional tables and the data is rolled back. An error also occurs for non-transactional tables but a partial update might result.
Term
How can warnings generated in non-strict mode be displayed?
 Definition
SHOW WARNINGS;
Term
How are out-of-range numbers converted in non-strict mode?
 Definition
Numbers below the minimum are stored as the minimum number in the range, numbers above the maximum are stored as the maximum number in the range.
Term
How are strings too long to fit the column stored in non-strict mode?
 Definition
Truncated to fit the column.
Term
Will a string where trailing spaces must be truncated to fit the column generate a warning?
 Definition
no
Term
What happens in non-strict mode when attempting to store a value that cannot be converted to the column data type?
 Definition
MySQL uses the implicit default value for that type.
Term
What happens in non-strict mode when a NULL value is inserted into a NOT NULL column?
 Definition
An error occurs in single-row inserts, but for multiple-row inserts MySQL assigns the column to the implicit default value for its data type.
Term
What happens when ALTER TABLE is used to change a column's data type?
 Definition
Existing values are mapped to new values according to the constraints imposed by the new data type.
Term
What is the difference between STRICT_TRANS_TABLES and STRICT_ALL_TABLES?
 Definition
STRICT_TRANS_TABLES only applies to errors that can be rolled back or canceled without changing the table into which data is being entered while STRICT_ALL_TABLES causes statements to abort for non-transactional tables even for errors in the second or later row in a multiple-row insert.
Term
What option enables treating division by zero as an error?
 Definition
ERROR_FOR_DIVISION_BY_ZERO mode value and strict mode.
Term
What option prohibits dates with zero values?
 Definition
Strict mode with NO_ZERO_DATE and NO_ZERO_IN_DATE
Term
How can input data restrictions be overridden for a single statement?
 Definition
INSERT IGNORE or UPDATE IGNORE
Term
Is '2009-02-31' a valid date in MySQL 5?
 Definition
no
Term
What sql_mode relaxes date checking to only check that months are in range from 1-12 and days are in range 1-31?
 Definition
ALLOW_INVALID_DATES
Supporting users have an ad free experience!