Exercises, Questions 91-114 - MySQL

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> I
NSERT 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.

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:

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

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

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

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

Answer 22:

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

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

3. False. HEAP tables support table locking only.

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

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.

>>> More MySQL Articles          >>> More By Sams Publishing

blog comments powered by Disqus