4.12 Exercises - MySQL

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?

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

2. They are read-only.

3. They support row-level locking.

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