Data Definition Language, Part 2 - Exercises, Questions 31-60

(Page 3 of 8 )

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.

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.

Next: Exercises, Questions 61-90 >>
 

More MySQL Articles
More By Sams Publishing

Comments On: Data Definition Language, Part 2

>>> Be the FIRST to comment on this article!