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

4.12 Exercises - 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 1:

What statement do you use to drop the test database? How can you undo, or cancel, this statement?

Question 2:

Which statements can you use to drop the index idx_id on table tbl? How can you recover the index?

Question 3:

Name the four kinds of indexes that MySQL supports.

Question 4:

List the differences between a UNIQUE index and a PRIMARY KEY.

Question 5:

What must be true of the columns named in a UNIQUE index for the index to be functionally equivalent to a PRIMARY KEY on the same columns?

Question 6:

Which type of index cannot be created with CREATE INDEX or dropped with DROP INDEX?

Question 7:

If you want a table to include a column that will automatically record the current date and time when rows are inserted or modified, what column datatype should you use?

Question 8:

If you want to store monetary values (for example, values representing U.S. dollar-and-cent amounts such as $48.99), which column datatype should you use to avoid rounding errors?

Question 9:

Which column datatype is more space-efficient: CHAR(100) or VARCHAR(100)?

Question 10:

How do you make a CHAR or VARCHAR column case sensitive?

Question 11:

Which column datatype is case sensitive: TEXT or BLOB? Why?

Question 12:

What's the difference between a string value that consists of characters and a string value that consists of bytes?

Question 13:

When you use DROP TABLE to remove a table, how do you tell MySQL not to report an error if the table doesn't exist?

Question 14:

Is the following statement true or false?

A database must contain at least one table.

Question 15:

Is the following statement true or false?

A table must contain at least one column.

Question 16:

Is the following statement true or false?

A table must contain at least one row.

Question 17:

Is the following statement true or false?

To create a table, you must first issue a statement to choose a default database in which to store the table.

Question 18:

Is the following statement true or false?

MySQL itself imposes no limit on the number of databases you can create on the server.

Question 19:

Is the following statement true or false?

InnoDB imposes no limit on the number of tables that can be held in the InnoDB tablespace.

Question 20:

Is the following statement true or false?

In a MySQL database, every table has an .frm file in the appropriate database directory, regardless of the table type used.

Question 21:

Name four ways to work around a table size limitation that's imposed by the file size limitation of the operating system.

Question 22:

Which of the following statements are true for HEAP tables?

  1. Table structure, data, and indexes are held in memory only.

  2. They are read-only.

  3. They support row-level locking.

  4. They have extremely high performance.

Question 23:

Which clause can you add to a CREATE DATABASE or CREATE TABLE statement to ensure that no error occurs if the database or table already exists?

Question 24:

Which clause can you add to a DROP DATABASE or DROP TABLE statement to ensure that no error occurs if the database or table doesn't exist?

Question 25:

The test database is your default database. You want to create a table named cats in the friends database without changing the default database. What statement do you issue? (Leave out the column specifications for your answer.)

Question 26:

You want to create a table, but you want to decide later the database to which it should belong. How do you do accomplish this?

Question 27:

Why does the following SQL statement fail?

CREATE TABLE cats (
id  INT    UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY
name CHAR(10)
);

Question 28:

Provide the SQL statement that will create a table with the following structure:

mysql>
DESCRIBE mytbl;
+-------+------------------+------+-----+---------+ | Field | Type | Null | Key | Default | +-------+------------------+------+-----+---------+ | col1 | int(10) unsigned | | PRI | 0 | | col2 | char(50) | | UNI | | | col3 | char(50) | | MUL | | +-------+------------------+------+-----+---------+

Question 29:

Is the following statement true or false?

You can add a column to a table with a single ALTER TABLE statement.

Question 30:

Is the following statement true or false?

You can add multiple columns to a table with a single ALTER TABLE statement.

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: