Data Definition Language, Part 2 - Answers to Exercises, 31-60 (
Page 6 of 8 )
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:
-
a. The value inserted is NULL. Because NULL values are permitted for column
d, they'll be accepted.
-
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:
-
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.
|
 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.
|
