Answers to Exercises, 31-60 - MySQL

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:

1. a. The value inserted is NULL. Because NULL values are permitted for column d, they'll be accepted.

2. b. 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:

1. The value inserted is '2010-02-08 '.

2. The value inserted is '2069-12-31 '.

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