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 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? | | Question 27: | Why does the following SQL statement fail?
CREATE TABLE cats (
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY
name CHAR(10)
);
| | Question 28: | Provide the SQL statement that will create a table with the following structure:
mysql> DESCRIBE mytbl;
+-------+------------------+------+-----+---------+
| Field | Type | Null | Key | Default |
+-------+------------------+------+-----+---------+
| col1 | int(10) unsigned | | PRI | 0 |
| col2 | char(50) | | UNI | |
| col3 | char(50) | | MUL | |
+-------+------------------+------+-----+---------+
| | Question 29: | Is the following statement true or false?
You can add a column to a table with a single ALTER TABLE statement. | | Question 30: | Is the following statement true or false?
You can add multiple columns to a table with a single ALTER TABLE statement. | | Question 31: | Is the following statement true or false?
You can add one or more rows to a table with a single ALTER TABLE statement. | | Question 32: | Is the following statement true or false?
You can change the datatype of a column with a single ALTER TABLE statement. | | Question 33: | Is the following statement true or false?
You can change the name of a column with a single ALTER TABLE statement. | | Question 34: | Is the following statement true or false?
You can drop indexes with a single ALTER TABLE statement. | | Question 35: | Is the following statement true or false?
You can create indexes with a single ALTER TABLE statement. | | Question 36: | Is the following statement true or false?
You can drop all columns of a table (thus dropping the table itself) with a single ALTER TABLE statement. | | Question 37: | Is the following statement true or false?
Using a single ALTER TABLE statement, you can add a new column as the first column in a table. | | Question 38: | Is the following statement true or false?
You can change existing data in the table with a single ALTER TABLE statement. | | Question 39: | Suppose that you have the following table structure:
+-------+---------+
| Field | Type |
+-------+---------+
| col | int(11) |
+-------+---------+
You want to add another column with the name COL (all uppercase letters). How can you do this? | | Question 40: | There are two ways to rename table tbl to tbl_new with SQL statements. What statements can you use? | | Question 41: | Name the two most common reasons to create an index on a table. | | Question 42: | The table mytable has the following structure, with a UNIQUE index on its only column, col:
mysql> DESCRIBE mytable;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| col | char(10) | YES | MUL | NULL | |
+-------+----------+------+-----+---------+-------+
The table is empty. Will the following INSERT statement fail?
mysql> INSERT INTO mytable VALUES (NULL),(NULL),('data'),('test'),(NULL);
| | Question 43: | Table mytable has a composite PRIMARY KEY consisting of both col1 and col2. Is it possible to declare one of the two columns as NULL, like this?
mysql> CREATE TABLE mytable (
-> col1 CHAR(5) NOT NULL,
-> col2 CHAR(5) NULL,
-> PRIMARY KEY (col1,col2)
-> );
| | Question 44: | 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?
mysql> SELECT * FROM mytable;
+------+------+
| col1 | col2 |
+------+------+
| yoo | doo |
| doo | yoo |
| doo | doo |
| yoo | yoo |
+------+------+
| | Question 45: | You have a table mytable that looks like this:
mysql> DESCRIBE mytable;
+-------+---------+
| Field | Type |
+-------+---------+
| col1 | int(11) |
| col3 | int(11) |
+-------+---------+
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.
mysql> DESCRIBE datetest;
+-------+------+------+-----+---------+
| Field | Type | Null | Key | Default |
+-------+------+------+-----+---------+
| d | date | YES | | NULL |
+-------+------+------+-----+---------+
You perform the following INSERT operation on table datetest:
INSERT INTO datetest VALUES ('2002-02-31');
What data value will actually be stored in the table? Provide a short explanation. | | Question 54: | You perform the following INSERT operations on table datetest, which has a single DATE column called d with a default value of NULL:
INSERT INTO datetest VALUES (NULL);
INSERT INTO datetest VALUES ('NULL');
What data value will actually be stored in the table for each statement? Provide a short explanation. | | Question 55: | You perform the following INSERT operation on table datetest, which has a single DATE column called d with a default value of NULL:
INSERT INTO datetest VALUES ('10000-01-01');
What data value will actually be stored in the table? Provide a short explanation. | | Question 56: | You perform the following INSERT operations on table datetest, which has a single DATE column called d with a default value of NULL:
INSERT INTO datetest VALUES ('10-02-08');
INSERT INTO datetest VALUES ('69-12-31');
INSERT INTO datetest VALUES ('70-01-01');
What data value will actually be stored in the table for each statement? Provide a short explanation. | | Question 57: | You perform the following INSERT operation on table datetest, which has a single DATE column called d with a default value of NULL:
INSERT INTO datetest VALUES ('12:00:00');
What data value will actually be stored in the table? Provide a short explanation. | | Question 58: | You perform the following INSERT operation on table datetest, which has a single DATE column called d with a default value of NULL:
INSERT INTO datetest VALUES ('12:00');
What data value will actually be stored in the table? Provide a short explanation. | | Question 59: | You perform the following INSERT operation on table datetest, which has a single DATE column called d with a default value of NULL:
INSERT INTO datetest VALUES ('2002-02-08 21:39');
What data value will actually be stored in the table? Provide a short explanation. | | Question 60: | Here's the structure of a table typetest with three columns (number, string, and dates), which will be used for the next five questions.
mysql> DESCRIBE typetest;
+--------+---------------------+------+
| Field | Type | Null |
+--------+---------------------+------+
| number | tinyint(3) unsigned | YES |
| string | char(5) | YES |
| dates | date | YES |
+--------+---------------------+------+
You perform the following INSERT operation on table typetest:
INSERT INTO typetest VALUES (1,22,333);
What data values will actually be stored in the table? Provide a short explanation. | | 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? | | 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.
mysql> DESCRIBE continent\G
*************************** 1. row ***************************
Field: name
Type: enum('Africa','America','Antarctica','Asia','Australia','Europe')
Null: YES
Key:
Default: NULL
Extra:
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):
Field: name
Type: enum('Africa','America','Antarctica','Asia','Australia','Europe')
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):
Field: name
Type: enum('Africa','America','Antarctica','Asia','Australia','Europe')
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):
Field: name
Type: enum('Africa','America','Antarctica','Asia','Australia','Europe')
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):
Field: name
Type: enum('Africa','America','Antarctica','Asia','Australia','Europe')
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):
Field: name
Type: enum('Africa','America','Antarctica','Asia','Australia','Europe')
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):
Field: name
Type: enum('Africa','America','Antarctica','Asia','Australia','Europe')
What string value will be stored by the following INSERT operation? What integer value will be stored internally?
INSERT INTO continent VALUES (NULL);
| | Question 100: | The following CREATE TABLE statement shows the definition for table defaults, which will be used for the next seven questions.
mysql> CREATE TABLE defaults (
-> id INT UNSIGNED NOT NULL UNIQUE,
-> col1 INT NULL,
-> col2 INT NOT NULL,
-> col3 INT DEFAULT 42,
-> col4 CHAR(5) NULL,
-> col5 CHAR(5) NOT NULL,
-> col6 CHAR(5) DEFAULT 'yoo',
-> col7 TEXT NULL,
-> col8 TEXT NOT NULL,
-> col9 TIME NOT NULL,
-> col10 DATE NULL,
-> col11 DATE NOT NULL,
-> col12 DATE DEFAULT '2002-02-08',
-> col13 ENUM('doo','yoo'),
-> col14 SET('blabla','yooyoo'),
-> col15 ENUM('doo','yoo') NOT NULL,
-> col16 SET('blabla','yooyoo') NOT NULL
-> );
What's the effect on the other columns with an INT datatype if you issue the following INSERT statement? Why?
mysql> INSERT INTO defaults (id) VALUES (1);
| | Question 101: | Refer to the definition of the defaults table, shown in the previous question. What's the effect on the columns with a CHAR datatype if you issue this INSERT statement? Why?
mysql> INSERT INTO defaults (id) VALUES (1);
| | Question 102: | Refer to the definition of the defaults table, shown two questions earlier. What's the effect on the columns with a TEXT datatype if you issue this INSERT statement? Why?
mysql> INSERT INTO defaults (id) VALUES (1);
Reminder:
Table defaults has two TEXT columns, shown in this partial table definition:
mysql> CREATE TABLE defaults (
-> id INT UNSIGNED NOT NULL UNIQUE,
-> . . .
-> col7 TEXT NULL,
-> col8 TEXT NOT NULL,
-> . . .
-> );
| | Question 103: | Refer to the definition of the defaults table, shown three questions earlier. What's the effect on the columns with a TIME datatype if you issue this INSERT statement? Why?
mysql> INSERT INTO defaults (id) VALUES (1);
Reminder:
Table defaults has one TIME column, shown in this partial table definition:
mysql> CREATE TABLE defaults (
-> id INT UNSIGNED NOT NULL UNIQUE,
-> . . .
-> col9 TIME NOT NULL,
-> . . .
-> );
| | Question 104: | Refer to the definition of the defaults table, shown four questions earlier. What's the effect on the columns with a DATE datatype if you issue this INSERT statement? Why?
mysql> INSERT INTO defaults (id) VALUES (1);
Reminder:
Table defaults has three DATE columns, shown in this partial table definition:
mysql> CREATE TABLE defaults (
-> id INT UNSIGNED NOT NULL UNIQUE,
-> . . .
-> col10 DATE NULL,
-> col11 DATE NOT NULL,
-> col12 DATE DEFAULT '2002-02-08',
-> . . .
-> );
| | Question 105: | Refer to the definition of the defaults table, shown five questions earlier. What's the effect on the columns with an ENUM datatype if you issue this INSERT statement? Why?
mysql> INSERT INTO defaults (id) VALUES (1);
Reminder:
Table defaults has two ENUM columns, shown in this partial table definition:
mysql> CREATE TABLE defaults (
-> id INT UNSIGNED NOT NULL UNIQUE,
-> . . .
-> col13 ENUM('doo','yoo'),
-> col15 ENUM('doo','yoo') NOT NULL,
-> . . .
-> );
| | Question 106: | Refer to the definition of the defaults table, shown six questions earlier. What's the effect on the columns with a SET datatype if you issue this INSERT statement? Why?
mysql> INSERT INTO defaults (id) VALUES (1);
Reminder:
Table defaults has two SET columns, shown in this partial table definition:
mysql> CREATE TABLE defaults (
-> id INT UNSIGNED NOT NULL UNIQUE,
-> . . .
-> col14 SET('blabla','yooyoo'),
-> col16 SET('blabla','yooyoo') NOT NULL
-> . . .
-> );
| | Question 107: | The table myauto looks like this:
mysql> DESCRIBE myauto;
+-------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+----------------+
| id | int(11) | | PRI | NULL | auto_increment |
+-------+---------+------+-----+---------+----------------+
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? | | Question 108: | The table cliptest has the following columns and rows:
mysql> DESCRIBE cliptest;
+--------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------+
| number | int(11) | YES | | NULL | |
| string | varchar(255) | YES | | NULL | |
+--------+--------------+------+-----+---------+-------+
mysql> SELECT * FROM cliptest;
+---------+--------------------------------------+
| number | string |
+---------+--------------------------------------+
| 1000000 | The Hitchhiker's Guide to the Galaxy |
| NULL | NULL |
+---------+--------------------------------------+
The table structure is modified with this statement:
mysql> ALTER TABLE cliptest
-> MODIFY number TINYINT UNSIGNED NOT NULL,
-> MODIFY string TINYINT UNSIGNED NOT NULL
-> ;
What will the table data look like afterward? | | Question 109: | The table mytiny has the following structure:
mysql> DESCRIBE mytiny;
+-------+------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+----------------+
| id | tinyint(4) | | PRI | NULL | auto_increment |
+-------+------------+------+-----+---------+----------------+
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. |
Answers to Exercises | | | 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. | | | | Answer 28: |
mysql> CREATE TABLE mytbl (
-> col1 INT UNSIGNED NOT NULL,
-> col2 CHAR(50) NOT NULL,
-> col3 CHAR(50) NOT NULL,
-> PRIMARY KEY(col1),
-> UNIQUE(col2),
-> INDEX(col3)
-> );
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. | | | | Answer 29: | True. | | | | Answer 30: | True. MySQL supports multiple actions for a single ALTER TABLE statement. | | | | 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:
ALTER TABLE mytable ADD PRIMARY KEY (col1, col2);
| | | | Answer 45: |
mysql> ALTER TABLE mytable
-> ADD col0 INT FIRST,
-> ADD col2 INT AFTER col1,
-> ADD col4 INT
-> ;
| | | | Answer 46: | 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. | | | | Answer 49: |
mysql> CREATE TABLE tbl (
-> col1 INT NOT NULL,
-> col2 INT NOT NULL,
-> PRIMARY KEY (col1, col2)
-> );
| | | | Answer 50: | 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: |
The value inserted is NULL. Because NULL values are permitted for column d, they'll be accepted. 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. | | | | Answer 60: |
+--------+--------+------------+
| number | string | dates |
+--------+--------+------------+
| 1 | 22 | 0000-00-00 |
+--------+--------+------------+
22 is converted to the string value '22'. The number 333 is interpreted as an invalid date, so the "zero" date is stored. | | | | Answer 61: |
+--------+--------+------------+
| number | string | dates |
+--------+--------+------------+
| 255 | yoodo | 0000-00-00 |
+--------+--------+------------+
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. | | | | Answer 62: |
+--------+--------+------------+
| number | string | dates |
+--------+--------+------------+
| NULL | NULL | NULL |
+--------+--------+------------+
All columns are declared NULL (by not specifying them as NOT NULL), so they accept NULL values. | | | | Answer 63: |
+--------+--------+------------+
| number | string | dates |
+--------+--------+------------+
| 0 | 10 | 0000-00-00 |
+--------+--------+------------+
'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. | | | | Answer 64: |
+--------+--------+------------+
| number | string | dates |
+--------+--------+------------+
| 0 | -1 | 0000-00-00 |
+--------+--------+------------+
-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).
+----------------+----------------+-------+
| alteration | creation | entry |
+----------------+----------------+-------+
| 20030213222337 | 20030213222337 | 1 |
+----------------+----------------+-------+
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.) | | | | Answer 66: |
+----------------+----------------+-------+
| alteration | creation | entry |
+----------------+----------------+-------+
| 20030213222337 | 20030213222337 | 1 |
+----------------+----------------+-------+
Because the UPDATE statement doesn't actually change any data, the table row remains unchanged. | | | | Answer 67: |
+----------------+----------------+-------+
| alteration | creation | entry |
+----------------+----------------+-------+
| 20030213223209 | 20030213222337 | 2 |
+----------------+----------------+-------+
This UPDATE statement actually changes data in the row, so the first TIMESTAMP 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. | | | | Answer 68: |
+----------------+----------------+-------+
| alteration | creation | entry |
+----------------+----------------+-------+
| 20030213225317 | 20030213225317 | 3 |
+----------------+----------------+-------+
When any TIMESTAMP column is explicitly set to a value, it gets updated, so column alteration is updated as well. If the value is NULL, the column is set to the current date and time. | | | | Answer 69: | The newly inserted row looks like this:
+----------------+----------------+-------+
| alteration | creation | entry |
+----------------+----------------+-------+
| 20030213225544 | 00000000000000 | 4 |
+----------------+----------------+-------+
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. | | | | Answer 70: | The newly inserted row looks like this:
+----------------+----------------+-------+
| alteration | creation | entry |
+----------------+----------------+-------+
| 20020208000000 | 20200202082139 | 5 |
+----------------+----------------+-------+
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. | | | | Answer 76: |
+---------------------+
| dt |
+---------------------+
| 0000-00-00 00:00:00 |
+---------------------+
2002-02-31 23:59:60 isn't a valid DATETIME value because the seconds portion (60) isn't within the valid range. MySQL thus converts the value to the "zero" DATETIME value. | | | | Answer 77: | '111'. The three arguments to CONCAT() are converted into strings before they're concatenated. | | | | Answer 78: | NULL. If any argument to CONCAT() is NULL, the result is NULL as well. | | | | Answer 79: | '1 plus 1 equals 2'. All arguments to CONCAT() are converted to strings before they're concatenated. | | | | Answer 80: | 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.) | | | | Answer 90: |
mysql> DESCRIBE passwords;
+---------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+----------------------+------+-----+---------+----------------+
| user_id | smallint(5) unsigned | | PRI | NULL | auto_increment |
| user_pw | char(8) binary | | | | |
+---------+----------------------+------+-----+---------+----------------+
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. | | | | 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):
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;
|
|
