Answers to Exercises, 91-114 - MySQL

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).

TABLE OF CONTENTS:

By: Sams Publishing

Poor Best

Rating:

/ 15

January 26, 2005

print this article

SEARCH DEV SHED

TOOLS YOU CAN USE

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:

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):

mysql>
SELECT * FROM defaults\G*********** 1. row
id: 1
col1: NULL
col2: 0
col3: 42
...

col1: Because this column has no defined DEFAULT value and can accept NULL values, the value inserted is NULL.
col2: 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 0.
col3: This column was explicitly defined with a DEFAULT value (42), so this value is inserted.

Answer 101:

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

mysql>
SELECT * FROM defaults\G*********** 1. row
id: 1
...
col4: NULL
col5:
col6: yoo
...

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):

mysql>
SELECT * FROM defaults\G*********** 1. row
id: 1
...
col7: NULL
col8:
...

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):

mysql>
SELECT * FROM defaults\G*********** 1. row
id: 1
...
col9: 00:00:00
...

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):

mysql>
SELECT * FROM defaults\G*********** 1. row
id: 1
...
col10: NULL
col11: 0000-00-00
col12: 2002-02-08
...

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):

mysql>
SELECT * FROM defaults\G*********** 1. row
id: 1
...
col13: NULL
col15: doo
...

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):

mysql>
SELECT * FROM defaults\G*********** 1. row
id: 1
...
col14: NULL
col16:
...

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.

Answer 108:

mysql>
SELECT * FROM cliptest;
+--------+--------+
| number | string |
+--------+--------+
|  255   |   0    |
|   0    |   0    |
+--------+--------+

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.