Login

Data Definition Language, Part 2

Studying for the MySQL Certification exam? This article, the second of two parts, covers the remainder of roughly 20 percent of the material that will appear on the exam, and includes several sections with questions and answers covering both articles. It is excerpted from chapter four of the book MySQL Certification Guide written by Paul Dubois et. al. (Sams, 2004, ISBN: 0672326329).

4.11 Using SHOW and DESCRIBE to Review Table Structures

The SELECT statement retrieves the information containedin your
databases. You can also ask MySQL to show you information aboutyour
databases, such as database and table names or information about the columns or
indexes in a table. This section discusses the SHOW and DESCRIBE statements,
which provide the following types of information:

The mysql and test databases are created during MySQL installation, so you’re
likely to see both of them in the output from the SHOW DATABASES statement. The
mysql database contains the grant tables and should always be present because
the grant tables contain user account information that the server uses to
control access to the databases. The test database will be present unless
someone has removed it.

The output of the SHOW DATABASES statement depends on whether you have the
SHOW DATABASES privilege. If you have the privilege, the statement shows the
names of all existing databases. Otherwise, it shows only those databases to
which you have access.

To determine the tables a particular database contains, use SHOW TABLES:

The FROM clause names the database whose table names you want to determine.
With no FROM clause, SHOW TABLES displays the names of the tables in the default
database. If there is no default database, an error occurs:

mysql>
SHOW TABLES;ERROR 1046: No Database Selected

SHOW DATABASES and SHOW TABLES can each take a LIKE ‘pattern
‘clause (note the quotes). With LIKE, the statement performs a
pattern-matching operation and displays information only about databases or
tables with names that match the pattern. Pattern matching is discussed in
section 6.2, “Using LIKE for Pattern Matching.”

This chapter excerpt is from MySQL
Certification Guide by Paul Dubois et al. (Sams, 2004,
ISBN: 0672326329 ). Check it out at your favorite bookstore today. Buy
this book now.

{mospagebreak title=4.12 Exercises}

Question 1:

What statement do you use to drop the test database? How can you undo, or
cancel, this statement?

Question 2:

Which statements can you use to drop the index idx_id on table tbl? How can
you recover the index?

Question 3:

Name the four kinds of indexes that MySQL supports.

Question 4:

List the differences between a UNIQUE index and a PRIMARY KEY.

Question 5:

What must be true of the columns named in a UNIQUE index for the index to be
functionally equivalent to a PRIMARY KEY on the same columns?

Question 6:

Which type of index cannot be created with CREATE INDEX or dropped with DROP
INDEX?

Question 7:

If you want a table to include a column that will automatically record the
current date and time when rows are inserted or modified, what column datatype
should you use?

Question 8:

If you want to store monetary values (for example, values representing U.S.
dollar-and-cent amounts such as $48.99), which column datatype should you use to
avoid rounding errors?

Question 9:

Which column datatype is more space-efficient: CHAR(100) or VARCHAR(100)?

Question 10:

How do you make a CHAR or VARCHAR column case sensitive?

Question 11:

Which column datatype is case sensitive: TEXT or BLOB? Why?

Question 12:

What’s the difference between a string value that consists of characters and
a string value that consists of bytes?

Question 13:

When you use DROP TABLE to remove a table, how do you tell MySQL not to
report an error if the table doesn’t exist?

Question 14:

Is the following statement true or false?

A database must contain at least one table.

Question 15:

Is the following statement true or false?

A table must contain at least one column.

Question 16:

Is the following statement true or false?

A table must contain at least one row.

Question 17:

Is the following statement true or false?

To create a table, you must first issue a statement to choose a default
database in which to store the table.

Question 18:

Is the following statement true or false?

MySQL itself imposes no limit on the number of databases you can create on
the server.

Question 19:

Is the following statement true or false?

InnoDB imposes no limit on the number of tables that can be held in the
InnoDB tablespace.

Question 20:

Is the following statement true or false?

In a MySQL database, every table has an .frm file in the appropriate database
directory, regardless of the table type used.

Question 21:

Name four ways to work around a table size limitation that’s imposed by the
file size limitation of the operating system.

Question 22:

Which of the following statements are true for HEAP tables?

Table structure, data, and indexes are held in memory only.

They are read-only.

They support row-level locking.

They have extremely high performance.

Question 23:

Which clause can you add to a CREATE DATABASE or CREATE TABLE statement to
ensure that no error occurs if the database or table already exists?

Question 24:

Which clause can you add to a DROP DATABASE or DROP TABLE statement to ensure
that no error occurs if the database or table doesn’t exist?

Question 25:

The test database is your default database. You want to create a table named
cats in the friends database without changing the default database. What
statement do you issue? (Leave out the column specifications for your
answer.)

Question 26:

You want to create a table, but you want to decide later the database to
which it should belong. How do you do accomplish this?

Table mytable contains the data shown in the following listing. The data
should remain unchanged. Is it possible to add a PRIMARY KEY to table mytable?
If it’s possible, what SQL statement would you use to create a composite PRIMARY
KEY for col1 and col2 on the table?

You want to add three more columns: col0 as the first column in the table,
col2 between col1 and col3, and col4 as the last column. All new columns should
be of type INT. What SQL statement do you issue?

Question 46:

You want to see what indexes you have in table tbl, but DESCRIBE tbl does not
show sufficient information. What other statement can you issue to obtain
additional information about the table structure?

Question 47:

What happens if you don’t provide an index name when creating an index with
ALTER TABLE or with CREATE INDEX?

Question 48:

Can you drop multiple indexes with a single DROP INDEX statement?

Question 49:

To declare a primary key on only one column (col1, with datatype INT) of
table tbl at creation time, you can use the following syntax:

mysql>
CREATE TABLE tbl (col1 INT NOT NULL PRIMARY KEY);

What’s the correct syntax if you want to declare a composite primary key for
this table on two INT columns col1 and col2?

Question 50:

In a table population, you want to store the number of inhabitants of cities.
Storage is at a premium. You expect the maximum population to be 15,000,000 for
a city. Which column datatype (and desired column options) would you use? What’s
the storage requirement for this column datatype for each row in the table?

Question 51:

In a table user, you have a comment column to store remarks. For each remark,
you want to be able to store up to 2,000 characters. What column datatype would
you use, and what’s the storage requirement for each row if the average remark
is 300 characters long?

Question 52:

You have a table in which you want to store birthdays of historical persons,
and you decide to use the DATE datatype to store the information. What’s the
earliest birthday you can store?

Question 53:

Here’s the structure of a table datetest with a single column d of datatype
DATE. This table will be used for the next seven questions.

What data values will actually be stored in the table? Provide a short
explanation.

This chapter excerpt is from MySQL
Certification Guide by Paul Dubois et al. (Sams, 2004,
ISBN: 0672326329 ). Check it out at your favorite bookstore today. Buy
this book now.

{mospagebreak title=Exercises, Questions 61-90}

Question 61:

You perform the following INSERT operation on table typetest:

INSERT INTO typetest VALUES (1000,’yoodoo’,’999-12-31′);

What data values will actually be stored in the table? Provide a short
explanation. (Reminder: The table has three columns. number is a TINYINT column,
string is a CHAR(5) column, and dates is a DATE column. All three allow NULL
values.)

Question 62:

You perform the following INSERT operation on table typetest:

INSERT INTO typetest VALUES (NULL,NULL,NULL);

What data values will actually be stored in the table? Provide a short
explanation. (Reminder: The table has three columns. number is a TINYINT column,
string is a CHAR(5) column, and dates is a DATE column. All three allow NULL
values.)

Question 63:

You perform the following INSERT operation on table typetest:

INSERT INTO typetest VALUES (‘string’,5+5,’string’);

What data values will actually be stored in the table? Provide a short
explanation. (Reminder: The table has three columns. number is a TINYINT column,
string is a CHAR(5) column, and dates is a DATE column. All three allow NULL
values.)

Question 64:

You perform the following INSERT operation on table typetest:

INSERT INTO typetest VALUES (-1,-1,’2000-02-32′);

What data values will actually be stored in the table? Provide a short
explanation. (Reminder: The table has three columns. number is a TINYINT column,
string is a CHAR(5) column, and dates is a DATE column. All three allow NULL
values.)

Question 65:

Here’s the structure of a table timetest with three columns (alteration,
creation, and entry), which will be used for the next six questions.

What data values will actually be stored in the two TIMESTAMP columns?
Provide a short explanation. Note:Because the values are dependent on the
system date and time, you cannot know exactly which values will result. For this
and the next five questions, what you can say is whether TIMESTAMP values will
be entered or changed, and whether NULL values will be entered.

Question 66:

You now perform the following UPDATE operation on table timetest:

UPDATE timetest SET entry=1 WHERE entry=1;

What data values will actually be stored in the two TIMESTAMP columns?
Provide a short explanation. (Reminder: The table has three columns. alteration
is a TIMESTAMP column, creation is a TIMESTAMP column, and entry is an INT
column. All three allow NULL values. Assume a system date of February 13, 2003
and system time of 22:23:37.)

Question 67:

You now perform the following UPDATE operation on table timetest:

UPDATE timetest SET entry=2 WHERE entry=1;

What data values will actually be stored in the two TIMESTAMP columns?
Provide a short explanation. (Reminder: The table has three columns. alteration
is a TIMESTAMP column, creation is a TIMESTAMP column, and entry is an INT
column. All three allow NULL values. Assume a system date of February 13, 2003
and system time of 22:32:09.)

What data values will actually be stored in the two TIMESTAMP columns?
Provide a short explanation. (Reminder: The table has three columns. alteration
is a TIMESTAMP column, creation is a TIMESTAMP column, and entry is an INT
column. All three allow NULL values. Assume a system date of February 13, 2003
and system time of 22:53:17.)

Question 69:

You now perform the following INSERT operation on table timetest:

INSERT INTO timetest (entry) VALUES (4);

What data values will actually be stored in the two TIMESTAMP columns?
Provide a short explanation. (Reminder: The table has three columns. alteration
is a TIMESTAMP column, creation is a TIMESTAMP column, and entry is an INT
column. All three allow NULL values. Assume a system date of February 13, 2003
and system time of 22:55:44.)

Question 70:

You now perform the following INSERT operation on table timetest:

INSERT INTO timetest VALUES(‘2002-02-08′,200202082139,5);

What data values will actually be stored in the two TIMESTAMP columns?
Provide a short explanation. (Reminder: The table has three columns. alteration
is a TIMESTAMP column, creation is a TIMESTAMP column, and entry is an INT
column. All three allow NULL values. Assume a system date of February 13, 2003
and system time of 22:55:55.)

Question 71:

Here’s the structure of the table datetimetest with one column (dt), which
will be used for the next six questions.

What data value will actually be stored in the DATETIME column? Provide a
short explanation.

Question 72:

You perform the following INSERT operation on table datetimetest:

INSERT INTO datetimetest VALUES (‘string’);

What data value will actually be stored in the DATETIME column? Provide a
short explanation. (Reminder: The table has one column. dt is a DATETIME column
that allows NULL values.)

Question 73:

You perform the following INSERT operation on table datetimetest:

INSERT INTO datetimetest VALUES (200202082139);

What data value will actually be stored in the DATETIME columns? Provide a
short explanation. (Reminder: The table has one column. dt is a DATETIME column
that allows NULL values.)

Question 74:

You perform the following INSERT operation on table datetimetest:

INSERT INTO datetimetest VALUES (20020208213900);

What data value will actually be stored in the DATETIME columns? Provide a
short explanation. (Reminder: The table has one column. dt is a DATETIME column
that allows NULL values.)

Question 75:

You perform the following INSERT operation on table datetimetest:

INSERT INTO datetimetest VALUES (‘2002-02-31 23:59:59′);

What data value will actually be stored in the DATETIME columns? Provide a
short explanation. (Reminder: The table has one column. dt is a DATETIME column
that allows NULL values.)

Question 76:

You perform the following INSERT operation on table datetimetest:

INSERT INTO datetimetest VALUES (‘2002-02-31 23:59:60′);

What data value will actually be stored in the DATETIME columns? Provide a
short explanation. (Reminder: The table has one column. dt is a DATETIME column
that allows NULL values.)

Question 77:

MySQL will make context-specific datatype conversions not only when working
with column values, but also when working with functions and operators that
expect specific datatypes. For example, the CONCAT() function expects data of a
string type, whereas the + operator expects data of a numeric type. What value
will result from the following operation? Give a short explanation.

SELECT CONCAT(1,1,1);

Question 78:

Based on MySQL’s capability to make context-specific datatype conversions
when working with functions and operators, what value will result from the
following operation? Give a short explanation.

SELECT CONCAT(NULL,’Lennart’);

Question 79:

Based on MySQL’s capability to make context-specific datatype conversions
when working with functions and operators, what value will result from the
following operation? Give a short explanation.

SELECT CONCAT(1,’ plus ‘,1,’ equals ‘,2);

Question 80:

Based on MySQL’s capability to make context-specific datatype conversions
when working with functions and operators, what value will result from the
following operation? Give a short explanation.

SELECT 1 + 1 + ‘ equals 2′;

Question 81:

Based on MySQL’s capability to make context-specific datatype conversions
when working with functions and operators, what value will result from the
following operation? Give a short explanation.

SELECT 1 + 1 + ‘1.1 equals GUESS!';

Question 82:

Based on MySQL’s capability to make context-specific datatype conversions
when working with functions and operators, what value will result from the
following operation? Give a short explanation.

SELECT 1 + NULL;

Question 83:

What’s the largest value you can store in a TINYINT(2) column?

Question 84:

Which numeric datatype is slowest in regard to processing time?

Question 85:

Which numeric datatype is a common choice for financial applications?
Why?

Question 86:

What’s the explanation for the following datatype conversion in a MyISAM
table?

How do you define columns with the CHAR, VARCHAR, TEXT, or BLOB datatypes to
ensure that their values will be compared in a case-sensitive manner?

Question 89:

What column types would you choose for a table that contains pictures with a
maximum data length of 10 megabytes, and remarks with a maximum length of 250
characters?

Question 90:

You want to store user IDs and passwords in a table. You know you’ll need to
store up to 1,000 users. User IDs need be nothing more than serial numbers, but
MySQL should ensure that no number is ever stored more than once for the table.
Each password will be exactly eight characters long, and passwords that differ
in lettercase (such as secret and SECRET) are considered different passwords.
What would your table structure look like?

This chapter excerpt is from MySQL
Certification Guide by Paul Dubois et al. (Sams, 2004,
ISBN: 0672326329 ). Check it out at your favorite bookstore today. Buy
this book now.

{mospagebreak title=Exercises, Questions 91-114}

Question 91:

Is it possible to declare a column as CHAR(0)? What would be the use of such
a column?

Question 92:

How much space is required to store a value that is 2,000 bytes long in a
BLOB column?

Question 93:

Here’s the structure of a table continent that has only one column (name,
which stores names of continents). This table will be used for the next seven
questions.

What string value will be stored by the following INSERT operation? What
integer value will be stored internally?

INSERT INTO continent VALUES (‘Africa’);

Question 94:

Recall that table continent has only one column (name, with a datatype of
ENUM) that stores names of continents, with NULL values allowed. A DESCRIBE of
the table shows the following (partial data only):

What string value will be stored by the following INSERT operation? What
integer value will be stored internally?

INSERT INTO continent VALUES (‘Europa’);

Question 95:

Recall that table continent has only one column (name, with a datatype of
ENUM) that stores names of continents, with NULL values allowed. A DESCRIBE of
the table shows the following (partial data only):

What string value will be stored by the following INSERT operation? What
integer value will be stored internally?

INSERT INTO continent VALUES (”);

Question 96:

Recall that table continent has only one column (name, with a datatype of
ENUM) that stores names of continents, with NULL values allowed. A DESCRIBE of
the table shows the following (partial data only):

What string value will be stored by the following INSERT operation? What
integer value will be stored internally?

INSERT INTO continent VALUES (0);

Question 97:

Recall that table continent has only one column (name, with a datatype of
ENUM) that stores names of continents, with NULL values allowed. A DESCRIBE of
the table shows the following (partial data only):

What string value will be stored by the following INSERT operation? What
integer value will be stored internally?

INSERT INTO continent VALUES (1);

Question 98:

Recall that table continent has only one column (name, with a datatype of
ENUM) that stores names of continents, with NULL values allowed. A DESCRIBE of
the table shows the following (partial data only):

What string value will be stored by the following INSERT operation? What
integer value will be stored internally?

INSERT INTO continent VALUES (‘1′);

Question 99:

Recall that table continent has only one column (name, with a datatype of
ENUM) that stores names of continents, with NULL values allowed. A DESCRIBE of
the table shows the following (partial data only):

No records have been inserted into the table so far. Now, a value is inserted
like this:

mysql>
INSERT INTO myauto (id) VALUES (NULL);

Which SQL function would you use to retrieve the last inserted value for id
and what would be that value? When you invoke this function over and over again
without inserting new values, and some other user on another connection inserts
new rows into the table, what would your function call return?

An application attempting to insert data with a program loop issues the
following statement during every iteration of the loop:

INSERT INTO mytiny (id) VALUES (NULL);

How many times will this loop run without error? When an error occurs, what
will be the reason?

Question 110:

Which SHOW statement will retrieve a list of all tables in the current
database with a table name that contains the string ‘test
‘?

Question 111:

Which SHOW statement will retrieve a list of tables in the database test,
even if test isn’t the current database?

Question 112:

Which SHOW statement will retrieve a list of the columns in the table mytest,
found in the test database?

Question 113:

Which SHOW statement will retrieve a list of columns in the table
test.mytest, where the column names begin with id?

Question 114:

Which SHOW statement will retrieve a statement that could be used to
re-create the table test.mytest in an arbitrary database? Assume that test is
not the default database.

This chapter excerpt is from MySQL
Certification Guide by Paul Dubois et al. (Sams, 2004,
ISBN: 0672326329 ). Check it out at your favorite bookstore today. Buy
this book now.

{mospagebreak title=Answers
to Exercises, 1-30}

Answer 1:

DROP DATABASE test. This statement cannot be undone, so be careful with
it.

Answer 2:

DROP INDEX idx_id ON tbl or ALTER TABLE tbl DROP INDEX idx_id. You can
recover the index by rebuilding it with a CREATE INDEX or ALTER TABLE … ADD
INDEX statement.

Answer 3:

PRIMARY KEY, UNIQUE, INDEX (nonunique), and FULLTEXT.

Answer 4:

A UNIQUE index can contain NULL values; a PRIMARY KEY cannot. It’s possible
to have multiple UNIQUE indexes for a table, but there can be only one index
defined as a PRIMARY KEY for each table.

Answer 5:

The columns must be declared NOT NULL.

Answer 6:

PRIMARY KEY

Answer 7:

TIMESTAMP

Answer 8:

DECIMAL

Answer 9:

CHAR(100) stores 100 bytes for every record, whereas VARCHAR(100) only stores
the number of bytes actually inserted, plus one byte to store the length of the
entry. This means that VARCHAR(100) is normally more space-efficient. However,
in the special case that you consistently insert 100-byte values into the
column, CHAR(100) is more space-efficient because the byte used by VARCHAR to
store the length of the entry is unneeded.

Answer 10:

By using the keyword BINARY when specifying the column, for example, codeName
CHAR(10) BINARY.

Answer 11:

BLOB is case sensitive because it stores binary strings.

Answer 12:

Binary values are sequences of arbitrary bytes. Nonbinary values are
sequences of characters. Characters might require one or more bytes each to
store, whereas byte values require only a single byte each.

Answer 13:

By using the IF EXISTS clause; for example, DROP TABLE IF EXISTS tbl.

Answer 14:

False. A database can be empty.

Answer 15:

True. There cannot be a table with zero columns.

Answer 16:

False. Tables do not have to contain data, they may be empty.

Answer 17:

False. You can specify the database in which to create the table by using a
fully qualified table name—that is, database_name.table_name (for example,
mydb.mytable).

Answer 18:

True. Such a limit could, however, be imposed by the operating system.

Answer 19:

False. InnoDB allows for a maximum of two billion tables in its
tablespace.

Answer 20:

True. However, depending on the table type, other files may also be present
in the database directory.

Answer 21:

Use features of the table storage manager, such as MERGE tables for the
MyISAM storage manager.

Use the RAID feature for MyISAM tables to partition the datafile (the .MYD
file).

Convert the table for use with a storage engine that allows larger tables.
For example, convert MyISAM tables to InnoDB tables. The InnoDB tablespace can
consist of several files and InnoDB can spread a table’s contents over more than
one of these files. This allows the table to be larger than any single file.

Use another filesystem or a newer version of the operating system that allows
for larger files.

Answer 22:

False. Although the data and index information is stored in memory, the
format (.frm) file is stored on disk.

False. You can insert, update, and delete data, just as you can with other
tables.

False. HEAP tables support table locking only.

True. The table’s contents are always stored in memory and never need to be
read from, or written to, disk.

Answer 23:

IF NOT EXISTS

Answer 24:

IF EXISTS

Answer 25:

CREATE TABLE friends.cats (…);

Answer 26:

It’s not possible. All tables must belong to a database, and therefore must
be created within a database.

Answer 27:

Column specifications must be separated by commas. In this case, there must
be a comma between the words KEY and name.

Other variations are possible. For example, the indexes created by the
PRIMARY KEY and UNIQUE clauses could be specified by adding PRIMARY KEY to the
end of the col1 definition and UNIQUE to the end of the col2 definition.

This chapter excerpt is from MySQL
Certification Guide by Paul Dubois et al. (Sams, 2004,
ISBN: 0672326329 ). Check it out at your favorite bookstore today. Buy
this book now.

{mospagebreak title=Answers to Exercises, 31-60}

Answer 31:

False. Rows cannot be added with an SQL statement that changes the table
structure.

Answer 32:

True. MySQL will convert existing data if necessary.

Answer 33:

True.

Answer 34:

True.

Answer 35:

True.

Answer 36:

False. MySQL will tell you to use the DROP TABLE command for this action.

Answer 37:

True. To do so, specify the keyword FIRST at the end of the ADD clause that
provides the column definition.

Answer 38:

True, although this will happen only as a side effect. You could truncate
existing data by shortening the length of a CHAR or VARCHAR column, or you could
convert data by changing the datatype.

Answer 39:

You cannot do this. Column names in a table must be unique no matter what the
lettercase is.

Answer 40:

Either of the following statements renames the table:

ALTER TABLE tbl RENAME TO tbl_new;
RENAME TABLE tbl TO tbl_new;

Answer 41:

Indexes can speed up table scans, especially for large tables, and they can
be used to place restrictions on columns to ensure that a column or a set of
columns may contain only unique-valued entries.

Answer 42:

The Key value in DESCRIBE output for a UNIQUE index will be UNI or PRI if the
index does not allow NULL values. The Key value is MUL if the index does allow
NULL values because NULL in a UNIQUE index is a special case: Multiple NULL
values are allowed, unlike any other value. For mytable, the Key value is MUL,
which indicates that the UNIQUE index on col allows multiple NULL values.
Consequently, the INSERT statement will not fail, even though it inserts several
NULL values.

Answer 43:

No, it isn’t possible. A PRIMARY KEY can only contain columns that are
specified as NOT NULL.

Answer 44:

Yes. Because the combination of col1 and col2 has unique and non-NULL values
only, it’s possible to create a PRIMARY KEY with this SQL statement:

SHOW CREATE TABLE tbl will display all index information for the table,
including composite indexes. SHOW INDEX FROM tbl also shows index information,
although the output might not be as easy to interpret.

Answer 47:

With ALTER TABLE … ADD INDEX, if you don’t explicitly provide a name for
the index, MySQL creates an index name based on the name of the first indexed
column. With CREATE INDEX, an error occurs if you don’t provide a name for the
index.

Answer 48:

No. If you want to drop more than one index at the same time, you must use
ALTER TABLE … DROP INDEX.

MEDIUMINT UNSIGNED can hold numbers up to 16,777,215. The UNSIGNED option
ensures that you don’t store negative numbers by accident. Without UNSIGNED, the
maximum positive number would only be 8,388,607. The storage requirement is 3
bytes per row.

Answer 51:

TEXT is the column datatype best suited to this situation. It can hold up to
65,535 characters. For 300 characters, the storage requirement is 302 bytes (300
bytes for a remark plus 2 bytes to store the actual length of the entry).

Answer 52:

‘1000-01-01 ‘(January 1, 1000) is the earliest date that
can be stored in a DATE column. You might be able to store earlier dates, but
doing so isn’t recommended because unexpected results from date operations might
result.

Answer 53:

The value inserted is ‘2002-02-31 ‘. MySQL performs only
elementary checking on the validity of a date.

Answer 54:

a. The value inserted is NULL. Because NULL values are permitted for column
d, they’ll be accepted.

b. The value inserted is ‘0000-00-00 ‘because
‘NULL ‘is a string value which is an invalid date. MySQL
converts this to the default date value ‘0000-00-00
‘.

Answer 55:

The value inserted is ‘2010-00-00 ‘(this could differ in
your MySQL version). The valid DATE range is from ‘1000-01-01
‘to ‘9999-12-31 ‘. If you insert values outside of
this range, you’ll get unpredictable results.

Answer 56:

If a date is entered with a two-digit year value, MySQL converts it to a date
between ‘1970-01-01 ‘and ‘2069-12-31 ‘. For
each of the three examples, then, the results are as follows:

The value inserted is ‘2010-02-08 ‘.

The value inserted is ‘2069-12-31 ‘.

The value inserted is ‘1970-01-01 ‘.

Answer 57:

The value inserted is ‘2012-00-00 ‘. MySQL interprets the
inserted value as a date, ‘12-00-00 ‘, which is interpreted as
the year ‘2012 ‘.

Answer 58:

The value inserted is ‘0000-00-00 ‘. ‘12:00
‘is considered to be an invalid date and so gets converted to the
“zero” date value ‘0000-00-00 ‘.

Answer 59:

The value inserted is ‘2002-02-08 ‘. ‘2002-02-08
21:39 ‘is a DATETIME value. When inserted into a DATE column, the time
portion of a DATETIME value is truncated.

The inserted number 1000 is too big to fit in the TINYINT UNSIGNED column, so
the highest possible value (255) is inserted. ‘yoodoo ‘is too
long for a CHAR(5) column and is thus truncated to five characters.
‘999-12-31 ‘is a date which is earlier than the earliest
possible DATE value ( ‘1000-01-01 ‘). This is interpreted as
an invalid date, so the “zero” date is stored.

‘string ‘is converted to a number for the number column;
because there are no digit characters at the beginning of the string, the result
is 0. 5+5 is evaluated to 10, which is converted to the string ‘10
‘before it is stored in the string column. ‘string
‘is converted to a date before it is stored in the dates column;
because it is invalid as a date, the “zero” date is stored.

-1 is lower than the lowest possible value for any unsigned integer column,
so it’s converted to 0 before it’s stored. -1 is converted to the corresponding
string value ( ‘-1 ‘) before it’s stored. The inserted date
has an invalid day portion (32); because this is interpreted as an invalid date,
the “zero” date is stored.

Answer 65:

Note:The values displayed for the alteration and creation columns are
examples only, given for a system date of February 13, 2003 (20030213) and a
system time sometime after 22 hours (10 p.m.) (22).

When an attempt is made to insert a NULL value into a TIMESTAMP column, a
timestamp consisting of the system date and time is inserted instead, regardless
of whether the column definition includes NULL or NOT NULL. (In fact, it isn’t
really possible to define a TIMESTAMP column as NULL; the declaration will be
ignored.)

This UPDATE statement actually changes data in the row, so the
firstTIMESTAMP column is set to the system date and time, even though
column alteration isn’t explicitly mentioned in the list of columns to update.
The other TIMESTAMP column remains untouched.

None of the TIMESTAMP columns is mentioned in the INSERT statement, so
creation is set to the standard default value. (Although creation is declared as
NOT NULL, MySQL will never assign a NULL value to a TIMESTAMP column, but use
the “zero” value instead.) alteration is set to the system date and time.

For alteration, the DATE value given is converted appropriately (with the
time portion set to 0). For creation, the conversion of the 12-digit number
200202082139 produces a puzzling result. The value is interpreted as a DATETIME,
with the leftmost two digits treated as the year portion of the DATETIME. 20
thus becomes the year value 2020, and the rest of the digits (0202082139) are
interpreted as month (02), day (02), hour (08), minute (21), and second (39).
The rules for interpretation of “short” TIMESTAMP values are detailed in the
MySQL Reference Manual.

Answer 71:

+——+
| dt |
+——+
| NULL |
+——+

Because dt isn’t explicitly declared NOT NULL, it can hold NULL values.

Answer 72:

+———————+
| dt |
+———————+
| 0000-00-00 00:00:00 |
+———————+

‘string ‘is converted into a DATETIME value before it’s
inserted. Because the result of the conversion is an invalid DATETIME of 0, the
“zero” value is inserted instead.

Answer 73:

+———————+
| dt |
+———————+
| 2020-02-02 08:21:39 |
+———————+

The conversion of the 12-digit number 200202082139 produces a puzzling
result. The value is interpreted as a DATETIME, with the leftmost two digits
treated as the year portion of the DATETIME. 20 becomes the year value 2020, and
the rest of the digits (0202082139) are interpreted as month (02), day (02),
hour (08), minute (21), and second (39).

Answer 74:

+———————+
| dt |
+———————+
| 2002-02-08 21:39:00 |
+———————+

20020208213900 looks like a 14-digit TIMESTAMP value and is interpreted as a
DATETIME on insertion, where the rightmost 00 is the seconds portion of the
value.

Answer 75:

+———————+
| dt |
+———————+
| 2002-02-31 23:59:59 |
+———————+

2002-02-31 23:59:59 is a valid DATETIME value although it isn’t a valid
calendar date. MySQL regards the value as valid because all parts of it (year,
month, day, hour, minute, second) are within a valid range.

2. The string ‘equals 2 ‘is interpreted as a number. It
evaluates to 0 because it has no leftmost numeric part. Thus, the operation
performed is 1 + 1 + 0.

Answer 81:

3.1. The leftmost part of the string ‘1.1 equals GUESS
‘contains the floating point number 1.1. Thus, all numbers are
converted to floats, so the operation performed is 1.0 + 1.0 + 1.1.

Answer 82:

NULL. The result of an arithmetic operation is indeterminate with a NULL
operand, so the result is NULL.

Answer 83:

127. The display width of (2) in the column type indicates only that values
should be displayed in a two-digit format when they have only one digit. It
doesn’t restrict the range of values that can be stored in a TINYINT column.

Answer 84:

DECIMAL columns cannot be processed as fast as FLOAT and DOUBLE columns
because values are stored as strings, and strings cannot be processed as quickly
as numbers represented in binary.

Answer 85:

DECIMAL values are not subject to rounding errors when they are stored, so,
despite their speed disadvantages, they’re a common choice in financial
applications where accuracy is of prime importance.

Answer 86:

MySQL converts myotherchar to VARCHAR because this datatype is more often
space efficient than CHAR. Because VARCHAR columns are usually slower in table
scans, MySQL will do a datatype change like this only if the table will become
variable length, anyway. In the example, myvarchar is the column that will cause
the table to become variable length. Thus, MySQL can save disk space if the
datatype of myotherchar is changed to VARCHAR. For mychar, however, this does
not hold true because this column is shorter than 3 bytes. If the mychar column
was converted to VARCHAR, MySQL would waste disk space, not save it; therefore,
the column definition remains unchanged.

Answer 87:

MySQL changes the datatype of myvarchar to CHAR because a VARCHAR that is 3
bytes long is less space efficient than a CHAR(3). As a side effect, the
datatype change also keeps the table fixed length, which provides a speed
advantage over variable-length MyISAM tables during a table scan.

Answer 88:

You can only do this for CHAR and VARCHAR columns by adding the keyword
BINARY when defining the columns in a CREATE TABLE or ALTER TABLE statement.
BLOB column values are always case sensitive, so no special declaration is
necessary. TEXT column values are always case insensitive; you cannot declare a
TEXT column to be case sensitive.

Answer 89:

For the pictures, you would choose MEDIUMBLOB, which can store almost 16
megabytes. For the remarks, you would choose VARCHAR(250), which can store up to
250 characters and is more space-efficient than CHAR(250). (If you happened to
choose CHAR(250), MySQL would convert it to VARCHAR(250) anyway. The MEDIUMBLOB
column is a variable-length type, so MySQL automatically converts other
fixed-length types in the table to their corresponding variable-length types to
save space.)

For up to 1,000 users, SMALLINT provides a sufficiently large range. This
column should be declared UNSIGNED to ensure that you don’t accidentally enter
negative values, which would lead to confusion. The UNSIGNED option is also a
prerequisite for the AUTO_INCREMENT option to work as expected; if you store
negative values in an AUTO_INCREMENT column this might produce unexpected
results.

AUTO_INCREMENT will take care of the numbering of new users when they’re
inserted into the table. To be able to use this option, the column must be
declared as a primary key (that is, as a NOT NULL column that is indexed with
either PRIMARY KEY or as a UNIQUE index). The primary key index ensures that
every entry is unique-valued, and NOT NULL ensures that you have no NULL users
in the table.

When all passwords are exactly eight characters long, a CHAR column is better
suited than a VARCHAR because a VARCHAR would require one additional byte per
row to store the actual length of the entry. As a side effect, passwords stored
as CHAR are faster to retrieve (although one would not recognize the difference
in speed with such a small table). To make comparisons of password column values
case sensitive, the column should be specified with the BINARY option.

This chapter excerpt is from MySQL
Certification Guide by Paul Dubois et al. (Sams, 2004,
ISBN: 0672326329 ). Check it out at your favorite bookstore today. Buy
this book now.

{mospagebreak title=Answers to Exercises, 91-114}

Answer 91:

CHAR(0) is not defined in ANSI SQL, but in MySQL it’s possible to define a
column with this datatype specification. This is mainly useful when you have to
be compliant with older applications that depend on the existence of a column
but that do not actually use the value. It is also useful when you need a column
that can accept only two values. A CHAR(0) that isn’t defined as NOT NULL will
occupy only one bit and can take only two values: NULL or ”(the empty
string).

Answer 92:

2,002 bytes; 2,000 bytes for the value and 2 bytes to store the length of the
value.

Answer 93:

String value: ‘Africa ‘. Internal number: 1 because Africa
is the first member in the ENUM list.

Answer 94:

String value: ”(the empty string). Internal number: 0. Because
‘Europa ‘isn’t a member of the ENUM list, the special error
value of ”(or 0 as the internal representation) is stored.

Answer 95:

String value: ”(the empty string). Internal number: 0. The empty
string (internally, 0) is the special error value that is stored if the inserted
value isn’t a member of the ENUM list, or if the empty string (or 0) is
explicitly stored, as in this case.

Answer 96:

String value: ”(the empty string). Internal number: 0. The empty
string (internally, 0) is the special error value that is stored if the inserted
value isn’t a member of the ENUM list, or if the empty string (or 0) is
explicitly stored, as in this case.

Answer 97:

String value: ‘Africa ‘. Internal number: 1.
‘Africa ‘is the first member in the ENUM list. The value can
be inserted by giving the element number instead of the string value.

Answer 98:

String value: ‘Africa ‘. Internal number: 1.
‘Africa ‘is the first member in the ENUM list. MySQL first
converts the string ‘1 ‘to a number before it’s inserted.

Answer 99:

NULL can be inserted into the name column because the column definition
allows NULL values.

Answer 100:

These values will be inserted into the table’s other INT columns (partial
listing only):

col4: Because this column has no defined DEFAULT value and can accept NULL
values, the value inserted is NULL.

col5: This column is declared NOT NULL and has no defined DEFAULT value.
Because the INSERT provides no explicit value for this column, MySQL assigns the
standard default value, in this case ”(the empty string).

col6: This column was explicitly declared with a DEFAULT value (
‘yoo ‘), so this value is inserted.

Answer 102:

These values will be inserted into the table’s TEXT columns (partial listing
only):

col7: Because this column can accept NULL values, the value inserted is
NULL.

col8: This column is declared NOT NULL. Because the INSERT provides no
explicit value for this column, MySQL assigns the standard default value, in
this case ”(the empty string). (You cannot declare a DEFAULT value for
a TEXT column.)

Answer 103:

This value will be inserted into the table’s TIME column (partial listing
only):

col9: This column is declared NOT NULL and has no defined DEFAULT value.
Because the INSERT provides no explicit value for this column, MySQL assigns the
standard default value, in this case ‘00:00:00 ‘.

Answer 104:

These values will be inserted into the table’s DATE columns (partial listing
only):

col10: Because this column has no defined DEFAULT value and can accept NULL
values, the value inserted is NULL.

col11: This column is declared NOT NULL and has no defined DEFAULT value.
Because the INSERT provides no explicit value for this column, MySQL assigns the
standard default value, in this case ‘0000-00-00 ‘.

col12: This column was explicitly defined with a DEFAULT value (
‘2002-02-08 ‘), so this value is inserted.

Answer 105:

These values will be inserted into the table’s ENUM columns (partial listing
only):

col13: Because this column has no defined DEFAULT value and can accept NULL
values, the value inserted is NULL.

col15: The ENUM column is declared NOT NULL and has no defined DEFAULT value.
Because the INSERT provides no explicit value for this column, MySQL uses the
first list member as the standard default value.

Answer 106:

These values will be inserted into the table’s SET columns (partial listing
only):

col14: Because this column has no defined DEFAULT value and can accept NULL
values, the value inserted is NULL.

col16: The SET column is declared NOT NULL and has no defined DEFAULT value.
Because the INSERT provides no explicit value for this column, MySQL uses the
empty string as the standard default value.

Answer 107:

You could use the SQL function LAST_INSERT_ID(). The inserted value is 1. If
you call LAST_INSERT_ID() repeatedly within the same connection, it will
continue to return the same value (1), even if other connections insert new rows
into the table.

1000000 is clipped to the maximum value of TINYINT UNSIGNED. The string is
converted to an integer number, and because it doesn’t begin with an integer
part, the result of the conversion is the number 0. The NULL entries are
converted to values that match the new option NOT NULL, and because there are no
DEFAULT values specified in the ALTER TABLE statement, MySQL uses the standard
default values for integers, which is 0.

Answer 109:

The loop will run 127 times without error. With the first loop run, 1 is
inserted, with the second run, 2 is inserted, and so on. 127 is the maximum
value for a TINYINT column. An error will occur when the application tries to
insert id number 128. This number will be clipped to 127, and MySQL will try to
insert this value once again. Because of the PRIMARY KEY restriction that allows
for only unique values in the id column, this will result in a duplicate-key
error. With the mysql client, the error would be displayed as follows:

ERROR 1062: Duplicate entry ‘127’ for key 1

Answer 110:

Either of the following statements provides the desired information:

SHOW TABLES LIKE ‘%test';
SHOW TABLE STATUS LIKE ‘%test%';

SHOW TABLES lists just the table names. SHOW TABLE STATUS displays the names
and additional table information.

Answer 111:

Either of the following statements provides the desired information:

SHOW TABLES FROM test;
SHOW TABLE STATUS FROM test;

Answer 112:

SHOW COLUMNS FROM mytest FROM test;

Answer 113:

SHOW COLUMNS FROM mytest FROM test LIKE ‘id%';

Answer 114:

SHOW CREATE TABLE test.mytest;

This chapter excerpt is from MySQL Certification Guide by Paul Dubois et al. (Sams, 2004, ISBN: 0672326329 ). Check it out at your favorite bookstore today. Buy this book now.