Home arrow MySQL arrow Page 7 - Data Definition Language, Part 2

Answers to Exercises, 61-90 - MySQL

Studying for the MySQL Certification exam? This article, the second of two parts, covers the remainder of roughly 20 percent of the material that will appear on the exam, and includes several sections with questions and answers covering both articles. It is excerpted from chapter four of the book MySQL Certification Guide written by Paul Dubois et. al. (Sams, 2004, ISBN: 0672326329).

TABLE OF CONTENTS:
  1. Data Definition Language, Part 2
  2. 4.12 Exercises
  3. Exercises, Questions 31-60
  4. Exercises, Questions 61-90
  5. Exercises, Questions 91-114
  6. Answers to Exercises, 31-60
  7. Answers to Exercises, 61-90
  8. Answers to Exercises, 91-114
By: Sams Publishing
Rating: starstarstarstarstar / 15
January 26, 2005

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

Answer 61:

+--------+--------+------------+
| number | string | dates      |
+--------+--------+------------+
|  255   | yoodo  | 0000-00-00 |
+--------+--------+------------+

The inserted number 1000 is too big to fit in the TINYINT UNSIGNED column, so the highest possible value (255) is inserted. 'yoodoo 'is too long for a CHAR(5) column and is thus truncated to five characters. '999-12-31 'is a date which is earlier than the earliest possible DATE value ( '1000-01-01 '). This is interpreted as an invalid date, so the "zero" date is stored.

Answer 62:

+--------+--------+------------+
| number | string | dates      |
+--------+--------+------------+
|  NULL  | NULL   | NULL       |
+--------+--------+------------+

All columns are declared NULL (by not specifying them as NOT NULL), so they accept NULL values.

Answer 63:

+--------+--------+------------+
| number | string | dates      |
+--------+--------+------------+
|   0    | 10     | 0000-00-00 |
+--------+--------+------------+

'string 'is converted to a number for the number column; because there are no digit characters at the beginning of the string, the result is 0. 5+5 is evaluated to 10, which is converted to the string '10 'before it is stored in the string column. 'string 'is converted to a date before it is stored in the dates column; because it is invalid as a date, the "zero" date is stored.

Answer 64:

+--------+--------+------------+
| number | string | dates      |
+--------+--------+------------+
|   0    | -1     | 0000-00-00 |
+--------+--------+------------+

-1 is lower than the lowest possible value for any unsigned integer column, so it's converted to 0 before it's stored. -1 is converted to the corresponding string value ( '-1 ') before it's stored. The inserted date has an invalid day portion (32); because this is interpreted as an invalid date, the "zero" date is stored.

Answer 65:

Note:The values displayed for the alteration and creation columns are examples only, given for a system date of February 13, 2003 (20030213) and a system time sometime after 22 hours (10 p.m.) (22).

+----------------+----------------+-------+
| alteration     | creation       | entry |
+----------------+----------------+-------+
| 20030213222337 | 20030213222337 |   1   |
+----------------+----------------+-------+

When an attempt is made to insert a NULL value into a TIMESTAMP column, a timestamp consisting of the system date and time is inserted instead, regardless of whether the column definition includes NULL or NOT NULL. (In fact, it isn't really possible to define a TIMESTAMP column as NULL; the declaration will be ignored.)

Answer 66:

+----------------+----------------+-------+
| alteration     | creation       | entry |
+----------------+----------------+-------+
| 20030213222337 | 20030213222337 |   1   |
+----------------+----------------+-------+

Because the UPDATE statement doesn't actually change any data, the table row remains unchanged.

Answer 67:

+----------------+----------------+-------+
| alteration     | creation       | entry |
+----------------+----------------+-------+
| 20030213223209 | 20030213222337 |   2   |
+----------------+----------------+-------+

This UPDATE statement actually changes data in the row, so the firstTIMESTAMP column is set to the system date and time, even though column alteration isn't explicitly mentioned in the list of columns to update. The other TIMESTAMP column remains untouched.

Answer 68:

+----------------+----------------+-------+
| alteration     | creation       | entry |
+----------------+----------------+-------+
| 20030213225317 | 20030213225317 |   3   |
+----------------+----------------+-------+

When any TIMESTAMP column is explicitly set to a value, it gets updated, so column alteration is updated as well. If the value is NULL, the column is set to the current date and time.

Answer 69:

The newly inserted row looks like this:

+----------------+----------------+-------+
| alteration     | creation       | entry |
+----------------+----------------+-------+
| 20030213225544 | 00000000000000 |   4   |
+----------------+----------------+-------+

None of the TIMESTAMP columns is mentioned in the INSERT statement, so creation is set to the standard default value. (Although creation is declared as NOT NULL, MySQL will never assign a NULL value to a TIMESTAMP column, but use the "zero" value instead.) alteration is set to the system date and time.

Answer 70:

The newly inserted row looks like this:

+----------------+----------------+-------+
| alteration     | creation       | entry |
+----------------+----------------+-------+
| 20020208000000 | 20200202082139 |   5   |
+----------------+----------------+-------+

For alteration, the DATE value given is converted appropriately (with the time portion set to 0). For creation, the conversion of the 12-digit number 200202082139 produces a puzzling result. The value is interpreted as a DATETIME, with the leftmost two digits treated as the year portion of the DATETIME. 20 thus becomes the year value 2020, and the rest of the digits (0202082139) are interpreted as month (02), day (02), hour (08), minute (21), and second (39). The rules for interpretation of "short" TIMESTAMP values are detailed in the MySQL Reference Manual.

Answer 71:

+------+
| dt   |
+------+
| NULL |
+------+

Because dt isn't explicitly declared NOT NULL, it can hold NULL values.

Answer 72:

+---------------------+
| dt                  |
+---------------------+
| 0000-00-00 00:00:00 |
+---------------------+

'string 'is converted into a DATETIME value before it's inserted. Because the result of the conversion is an invalid DATETIME of 0, the "zero" value is inserted instead.

Answer 73:

+---------------------+
| dt                  |
+---------------------+
| 2020-02-02 08:21:39 |
+---------------------+

The conversion of the 12-digit number 200202082139 produces a puzzling result. The value is interpreted as a DATETIME, with the leftmost two digits treated as the year portion of the DATETIME. 20 becomes the year value 2020, and the rest of the digits (0202082139) are interpreted as month (02), day (02), hour (08), minute (21), and second (39).

Answer 74:

+---------------------+
| dt                  |
+---------------------+
| 2002-02-08 21:39:00 |
+---------------------+

20020208213900 looks like a 14-digit TIMESTAMP value and is interpreted as a DATETIME on insertion, where the rightmost 00 is the seconds portion of the value.

Answer 75:

+---------------------+
| dt                  |
+---------------------+
| 2002-02-31 23:59:59 |
+---------------------+

2002-02-31 23:59:59 is a valid DATETIME value although it isn't a valid calendar date. MySQL regards the value as valid because all parts of it (year, month, day, hour, minute, second) are within a valid range.

Answer 76:

+---------------------+
| dt                  |
+---------------------+
| 0000-00-00 00:00:00 |
+---------------------+

2002-02-31 23:59:60 isn't a valid DATETIME value because the seconds portion (60) isn't within the valid range. MySQL thus converts the value to the "zero" DATETIME value.

Answer 77:

'111 '. The three arguments to CONCAT() are converted into strings before they're concatenated.

Answer 78:

NULL. If any argument to CONCAT() is NULL, the result is NULL as well.

Answer 79:

'1 plus 1 equals 2 '. All arguments to CONCAT() are converted to strings before they're concatenated.

Answer 80:

2. The string 'equals 2 'is interpreted as a number. It evaluates to 0 because it has no leftmost numeric part. Thus, the operation performed is 1 + 1 + 0.

Answer 81:

3.1. The leftmost part of the string '1.1 equals GUESS 'contains the floating point number 1.1. Thus, all numbers are converted to floats, so the operation performed is 1.0 + 1.0 + 1.1.

Answer 82:

NULL. The result of an arithmetic operation is indeterminate with a NULL operand, so the result is NULL.

Answer 83:

127. The display width of (2) in the column type indicates only that values should be displayed in a two-digit format when they have only one digit. It doesn't restrict the range of values that can be stored in a TINYINT column.

Answer 84:

DECIMAL columns cannot be processed as fast as FLOAT and DOUBLE columns because values are stored as strings, and strings cannot be processed as quickly as numbers represented in binary.

Answer 85:

DECIMAL values are not subject to rounding errors when they are stored, so, despite their speed disadvantages, they're a common choice in financial applications where accuracy is of prime importance.

Answer 86:

MySQL converts myotherchar to VARCHAR because this datatype is more often space efficient than CHAR. Because VARCHAR columns are usually slower in table scans, MySQL will do a datatype change like this only if the table will become variable length, anyway. In the example, myvarchar is the column that will cause the table to become variable length. Thus, MySQL can save disk space if the datatype of myotherchar is changed to VARCHAR. For mychar, however, this does not hold true because this column is shorter than 3 bytes. If the mychar column was converted to VARCHAR, MySQL would waste disk space, not save it; therefore, the column definition remains unchanged.

Answer 87:

MySQL changes the datatype of myvarchar to CHAR because a VARCHAR that is 3 bytes long is less space efficient than a CHAR(3). As a side effect, the datatype change also keeps the table fixed length, which provides a speed advantage over variable-length MyISAM tables during a table scan.

Answer 88:

You can only do this for CHAR and VARCHAR columns by adding the keyword BINARY when defining the columns in a CREATE TABLE or ALTER TABLE statement. BLOB column values are always case sensitive, so no special declaration is necessary. TEXT column values are always case insensitive; you cannot declare a TEXT column to be case sensitive.

Answer 89:

For the pictures, you would choose MEDIUMBLOB, which can store almost 16 megabytes. For the remarks, you would choose VARCHAR(250), which can store up to 250 characters and is more space-efficient than CHAR(250). (If you happened to choose CHAR(250), MySQL would convert it to VARCHAR(250) anyway. The MEDIUMBLOB column is a variable-length type, so MySQL automatically converts other fixed-length types in the table to their corresponding variable-length types to save space.)

Answer 90:

mysql>
DESCRIBE passwords;
+---------+----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------+----------------------+------+-----+---------+----------------+ | user_id | smallint(5) unsigned | | PRI | NULL | auto_increment | | user_pw | char(8) binary | | | | | +---------+----------------------+------+-----+---------+----------------+

For up to 1,000 users, SMALLINT provides a sufficiently large range. This column should be declared UNSIGNED to ensure that you don't accidentally enter negative values, which would lead to confusion. The UNSIGNED option is also a prerequisite for the AUTO_INCREMENT option to work as expected; if you store negative values in an AUTO_INCREMENT column this might produce unexpected results.

AUTO_INCREMENT will take care of the numbering of new users when they're inserted into the table. To be able to use this option, the column must be declared as a primary key (that is, as a NOT NULL column that is indexed with either PRIMARY KEY or as a UNIQUE index). The primary key index ensures that every entry is unique-valued, and NOT NULL ensures that you have no NULL users in the table.

When all passwords are exactly eight characters long, a CHAR column is better suited than a VARCHAR because a VARCHAR would require one additional byte per row to store the actual length of the entry. As a side effect, passwords stored as CHAR are faster to retrieve (although one would not recognize the difference in speed with such a small table). To make comparisons of password column values case sensitive, the column should be specified with the BINARY option.

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
escort Bursa Bursa escort Antalya eskort
   

MYSQL ARTICLES

- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: