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

Exercises, Questions 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

Question 61:

You perform the following INSERT operation on table typetest:

INSERT INTO typetest VALUES (1000,'yoodoo','999-12-31');

What data values will actually be stored in the table? Provide a short explanation. (Reminder: The table has three columns. number is a TINYINT column, string is a CHAR(5) column, and dates is a DATE column. All three allow NULL values.)

Question 62:

You perform the following INSERT operation on table typetest:

INSERT INTO typetest VALUES (NULL,NULL,NULL);

What data values will actually be stored in the table? Provide a short explanation. (Reminder: The table has three columns. number is a TINYINT column, string is a CHAR(5) column, and dates is a DATE column. All three allow NULL values.)

Question 63:

You perform the following INSERT operation on table typetest:

INSERT INTO typetest VALUES ('string',5+5,'string');

What data values will actually be stored in the table? Provide a short explanation. (Reminder: The table has three columns. number is a TINYINT column, string is a CHAR(5) column, and dates is a DATE column. All three allow NULL values.)

Question 64:

You perform the following INSERT operation on table typetest:

INSERT INTO typetest VALUES (-1,-1,'2000-02-32');

What data values will actually be stored in the table? Provide a short explanation. (Reminder: The table has three columns. number is a TINYINT column, string is a CHAR(5) column, and dates is a DATE column. All three allow NULL values.)

Question 65:

Here's the structure of a table timetest with three columns (alteration, creation, and entry), which will be used for the next six questions.

mysql>
DESCRIBE timetest;
+------------+------------------+------+-----+---------+ | Field | Type | Null | Key | Default | +------------+------------------+------+-----+---------+ | alteration | timestamp(14) | YES | | NULL | | creation | timestamp(14) | YES | | NULL | | entry | int(10) unsigned | YES | | NULL | +------------+------------------+------+-----+---------+

You perform the following INSERT operation on table timetest:

INSERT INTO timetest VALUES (NULL,NULL,1);

What data values will actually be stored in the two TIMESTAMP columns? Provide a short explanation. Note:Because the values are dependent on the system date and time, you cannot know exactly which values will result. For this and the next five questions, what you can say is whether TIMESTAMP values will be entered or changed, and whether NULL values will be entered.

Question 66:

You now perform the following UPDATE operation on table timetest:

UPDATE timetest SET entry=1 WHERE entry=1;

What data values will actually be stored in the two TIMESTAMP columns? Provide a short explanation. (Reminder: The table has three columns. alteration is a TIMESTAMP column, creation is a TIMESTAMP column, and entry is an INT column. All three allow NULL values. Assume a system date of February 13, 2003 and system time of 22:23:37.)

Question 67:

You now perform the following UPDATE operation on table timetest:

UPDATE timetest SET entry=2 WHERE entry=1;

What data values will actually be stored in the two TIMESTAMP columns? Provide a short explanation. (Reminder: The table has three columns. alteration is a TIMESTAMP column, creation is a TIMESTAMP column, and entry is an INT column. All three allow NULL values. Assume a system date of February 13, 2003 and system time of 22:32:09.)

Question 68:

You now perform the following UPDATE operation on table timetest:

UPDATE timetest SET alteration=NULL, creation=NULL, entry=3
WHERE entry=2;

What data values will actually be stored in the two TIMESTAMP columns? Provide a short explanation. (Reminder: The table has three columns. alteration is a TIMESTAMP column, creation is a TIMESTAMP column, and entry is an INT column. All three allow NULL values. Assume a system date of February 13, 2003 and system time of 22:53:17.)

Question 69:

You now perform the following INSERT operation on table timetest:

INSERT INTO timetest (entry) VALUES (4);

What data values will actually be stored in the two TIMESTAMP columns? Provide a short explanation. (Reminder: The table has three columns. alteration is a TIMESTAMP column, creation is a TIMESTAMP column, and entry is an INT column. All three allow NULL values. Assume a system date of February 13, 2003 and system time of 22:55:44.)

Question 70:

You now perform the following INSERT operation on table timetest:

INSERT INTO timetest VALUES('2002-02-08',200202082139,5);

What data values will actually be stored in the two TIMESTAMP columns? Provide a short explanation. (Reminder: The table has three columns. alteration is a TIMESTAMP column, creation is a TIMESTAMP column, and entry is an INT column. All three allow NULL values. Assume a system date of February 13, 2003 and system time of 22:55:55.)

Question 71:

Here's the structure of the table datetimetest with one column (dt), which will be used for the next six questions.

mysql>
DESCRIBE datetimetest;
+-------+----------+------+ | Field | Type | Null | +-------+----------+------+ | dt | datetime | YES | +-------+----------+------+

You perform the following INSERT operation on table datetimetest:

INSERT INTO datetimetest VALUES (NULL);

What data value will actually be stored in the DATETIME column? Provide a short explanation.

Question 72:

You perform the following INSERT operation on table datetimetest:

INSERT INTO datetimetest VALUES ('string');

What data value will actually be stored in the DATETIME column? Provide a short explanation. (Reminder: The table has one column. dt is a DATETIME column that allows NULL values.)

Question 73:

You perform the following INSERT operation on table datetimetest:

INSERT INTO datetimetest VALUES (200202082139);

What data value will actually be stored in the DATETIME columns? Provide a short explanation. (Reminder: The table has one column. dt is a DATETIME column that allows NULL values.)

Question 74:

You perform the following INSERT operation on table datetimetest:

INSERT INTO datetimetest VALUES (20020208213900);

What data value will actually be stored in the DATETIME columns? Provide a short explanation. (Reminder: The table has one column. dt is a DATETIME column that allows NULL values.)

Question 75:

You perform the following INSERT operation on table datetimetest:

INSERT INTO datetimetest VALUES ('2002-02-31 23:59:59');

What data value will actually be stored in the DATETIME columns? Provide a short explanation. (Reminder: The table has one column. dt is a DATETIME column that allows NULL values.)

Question 76:

You perform the following INSERT operation on table datetimetest:

INSERT INTO datetimetest VALUES ('2002-02-31 23:59:60');

What data value will actually be stored in the DATETIME columns? Provide a short explanation. (Reminder: The table has one column. dt is a DATETIME column that allows NULL values.)

Question 77:

MySQL will make context-specific datatype conversions not only when working with column values, but also when working with functions and operators that expect specific datatypes. For example, the CONCAT() function expects data of a string type, whereas the + operator expects data of a numeric type. What value will result from the following operation? Give a short explanation.

SELECT CONCAT(1,1,1);

Question 78:

Based on MySQL's capability to make context-specific datatype conversions when working with functions and operators, what value will result from the following operation? Give a short explanation.

SELECT CONCAT(NULL,'Lennart');

Question 79:

Based on MySQL's capability to make context-specific datatype conversions when working with functions and operators, what value will result from the following operation? Give a short explanation.

SELECT CONCAT(1,' plus ',1,' equals ',2);

Question 80:

Based on MySQL's capability to make context-specific datatype conversions when working with functions and operators, what value will result from the following operation? Give a short explanation.

SELECT 1 + 1 + ' equals 2';

Question 81:

Based on MySQL's capability to make context-specific datatype conversions when working with functions and operators, what value will result from the following operation? Give a short explanation.

SELECT 1 + 1 + '1.1 equals GUESS!';

Question 82:

Based on MySQL's capability to make context-specific datatype conversions when working with functions and operators, what value will result from the following operation? Give a short explanation.

SELECT 1 + NULL;

Question 83:

What's the largest value you can store in a TINYINT(2) column?

Question 84:

Which numeric datatype is slowest in regard to processing time?

Question 85:

Which numeric datatype is a common choice for financial applications? Why?

Question 86:

What's the explanation for the following datatype conversion in a MyISAM table?

mysql>
CREATE TABLE convtest (
-> mychar   CHAR(3),
-> myotherchar CHAR(4),
-> myvarchar  VARCHAR(10)
-> ) TYPE=MyISAM;mysql>
DESCRIBE convtest;
+-------------+-------------+------+ | Field | Type | Null | +-------------+-------------+------+ | mychar | char(3) | YES | | myotherchar | varchar(4) | YES | | myvarchar | varchar(10) | YES | +-------------+-------------+------+

Question 87:

What's the explanation for the following datatype conversion in a MyISAM table?

mysql>
CREATE TABLE convtest2 (
-> mychar   CHAR(3),
-> myotherchar CHAR(2),
-> myvarchar  VARCHAR(3)
-> ) TYPE=MyISAM;mysql>
DESCRIBE convtest2;
+-------------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+---------+------+-----+---------+-------+ | mychar | char(3) | YES | | NULL | | | myotherchar | char(2) | YES | | NULL | | | myvarchar | char(3) | YES | | NULL | | +-------------+---------+------+-----+---------+-------+

Question 88:

How do you define columns with the CHAR, VARCHAR, TEXT, or BLOB datatypes to ensure that their values will be compared in a case-sensitive manner?

Question 89:

What column types would you choose for a table that contains pictures with a maximum data length of 10 megabytes, and remarks with a maximum length of 250 characters?

Question 90:

You want to store user IDs and passwords in a table. You know you'll need to store up to 1,000 users. User IDs need be nothing more than serial numbers, but MySQL should ensure that no number is ever stored more than once for the table. Each password will be exactly eight characters long, and passwords that differ in lettercase (such as secret and SECRET) are considered different passwords. What would your table structure look like?

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: