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.

mysql>
DESCRIBE timetest;
+------------+------------------+------+-----+---------+
| Field      | Type             | Null | Key | Default |
+------------+------------------+------+-----+---------+
| alteration | timestamp(14)    | YES  |     | NULL    |
| creation   | timestamp(14)    | YES  |     | NULL    |
| entry      | int(10) unsigned | YES  |     | NULL    |
+------------+------------------+------+-----+---------+

You perform the following INSERT operation on table timetest:

INSERT INTO timetest VALUES (NULL,NULL,1);

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

Question 68:

You now perform the following UPDATE operation on table timetest:

UPDATE timetest SET alteration=NULL, creation=NULL, entry=3
WHERE entry=2;

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.

mysql>
DESCRIBE datetimetest;
+-------+----------+------+
| Field | Type     | Null |
+-------+----------+------+
| dt    | datetime | YES  |
+-------+----------+------+

You perform the following INSERT operation on table datetimetest:

INSERT INTO datetimetest VALUES (NULL);

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?

mysql>
CREATE TABLE convtest (
  -> mychar   CHAR(3),
  -> myotherchar CHAR(4),
  -> myvarchar  VARCHAR(10)
  -> ) TYPE=MyISAM;mysql>
DESCRIBE convtest;
+-------------+-------------+------+
| Field       | Type        | Null |
+-------------+-------------+------+
| mychar      | char(3)     |  YES |
| myotherchar | varchar(4)  |  YES |
| myvarchar   | varchar(10) |  YES |
+-------------+-------------+------+

Question 87:

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

mysql>
CREATE TABLE convtest2 (
  -> mychar   CHAR(3),
  -> myotherchar CHAR(2),
  -> myvarchar  VARCHAR(3)
  -> ) TYPE=MyISAM;mysql>
DESCRIBE convtest2;
+-------------+---------+------+-----+---------+-------+
| Field       | Type    | Null | Key | Default | Extra |
+-------------+---------+------+-----+---------+-------+
| mychar      | char(3) | YES  |     | NULL    |       |
| myotherchar | char(2) | YES  |     | NULL    |       |
| myvarchar   | char(3) | YES  |     | NULL    |       |
+-------------+---------+------+-----+---------+-------+

Question 88:

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.