MySQL 5 Certification Study Guide / Edition 2

Multimedia Set (Print)
Buy Used
Buy Used from BN.com
$38.23
(Save 41%)
Item is in good condition but packaging may have signs of shelf wear/aging or torn packaging.
Condition: Used – Good details
Used and New from Other Sellers
Used and New from Other Sellers
from $17.05
Usually ships in 1-2 business days
(Save 73%)
Other sellers (Multimedia Set)
  • All (17) from $17.05   
  • New (13) from $22.96   
  • Used (4) from $17.05   

Overview

This is the official guide to passing the two MySQL certification tests for MySQL 5, the long-awaited major revision of MySQL. The number of MySQL certification exams taken has doubled in the last six months.

Certcities.com lists the MySQL certification as one of the top 10 certifications to grow in 2005. MySQL professionals need a way to distinguish themselves from the vast majority of database administrators and developers. With more than 4 million active installations, MySQL is the world's most popular open-source database. Known for its speed, reliability and case of use, MySQL has become a low-cost alternative to expensive database systems such as Oracle, IBM and Microsoft. MySQL AB has aggressively improved the feature set of MySQL with MySQL 5, making it more suitable for enterprise-level applications and uses. The MySQL certification tests, available at over 3,000 PearsonVUE testing centers, is a key component of this enterprise growth strategy, establishing a base level of skills for database users, administrators and programmers.

The MySQL Core Certification is aimed at the database user who wants proof of his or her abilities in such fundamental areas as SQL, data entry and maintenance, and data extraction. The MySQL Professional Certification test is designed for the advanced user who wants to prove his or her knowledge in such areas as database management, installation, security, disaster prevention and optimization. Both tests are thoroughly covered in the MySQL 5.0 Certification Study Guide . Written by Paul DuBois, the leading author of books on MySQL topics, and reviewed for technical accuracy by MySQL AB, this book is the fastest, most reliable way for MySQL users, developers, and administrators to prepare for either of the MySQL tests.

Read More Show Less

Product Details

  • ISBN-13: 9780672328121
  • Publisher: MySQL Press
  • Publication date: 8/24/2005
  • Series: MySQL Press Series
  • Edition description: Book and CD-ROM
  • Edition number: 2
  • Pages: 643
  • Product dimensions: 7.00 (w) x 9.00 (h) x 1.37 (d)

Meet the Author

Paul DuBois is the leading author of books on MySQL topics. His book, entitled MySQL, is widely considered the definitive work on MySQL. He is a developer for MySQL AB, where he edits and maintains the official English-language documentation for MySQL. Stefan Hinz is leads the MySQL documentation team, is a MySQL trainer and consultant, and the German translator of the MySQL Reference Manual. He is also the translator of Paul's MySQL Cookbook (O'Reilly and Associates) and translator and author of MySQL-related German books. Stefan passed the MySQL Certification exam before he joined MySQL AB.
Read More Show Less

Table of Contents

Introduction.

About This Book.

Sample Exercises

Other Required Reading

Manuals

Sample Data

Study Guide Errata

Certification Information at www.mysql.com

The MySQL Certification Candidate Guide

The Certification Mailing List

Conventions Used in This Book

Running MySQL on Microsoft Windows

About the Exams

Registering for an Exam

Going to the Exam

Taking the Exam

Reading Questions

Answering Questions

After the Exam

Retaking Exams

Warning

Interpreting DESCRIBE Output

Sample Tables

MYSQL DEVELOPER EXAMS.

MySQL Developer I Exam.

1. Client/Server Concepts.

General MySQL Architecture

Invoking Client Programs

General Command Option Syntax

Connection Parameter Options

Using Option Files

Selecting a Default Database

Establishing a Connection with a GUI Client

Server SQL Modes

2. The mysql Client Program.

Using mysql Interactively

Statement Terminators

The mysql Prompts

Using Editing Keys in mysql

Using Script Files with mysql

mysql Output Formats

Client Commands and SQL Statements

Using Server-Side Help

Using the --safe-updates Option

3. MySQL Query Browser.

MySQL Query Browser Capabilities

Using MySQL Query Browser

Using the Query Window

Entering Queries

The Result Area

The Script Editor

Stored Routine Management

The Object and Information Browsers

The MySQL Table Editor

Connection Management

Using the Connection Dialog

Editing Connection Profiles

The Options Dialog

4. MySQL Connectors.

MySQL Client Interfaces

MySQL Connector/ODBC

MySQL Connector/J

MySQL Connector/NET

5. Data Types.

Data Type Overview

Numeric Data Types

Integer Data Types

Floating-Point Data Types

Fixed-Point Data Types

The BIT Data Type

String Data Types

Character Set Support

Non-Binary String Data Types: _CHAR, VARCHAR, TEXT

Binary String Data Types: BINARY, VARBINARY, BLOB

The ENUM and SET Data Types

Temporal Data Types

The DATE, TIME, DATETIME, and YEAR Data Types

The TIMESTAMP Data Type

Per-Connection Time Zone Support

Column Attributes

Numeric Column Attributes

String Column Attributes

General Column Attributes

Using the AUTO_INCREMENT Column Attribute

Handling Missing or Invalid Data Values

Handling Missing Values

Handling Invalid Values in Non-Strict Mode

Handling Invalid Values in Strict Mode

Enabling Additional Input Data Restrictions

Overriding Input Data Restrictions

6. Identifiers.

Identifier Syntax

Case Sensitivity of Identifiers

Using Qualified Names

Using Reserved Words as Identifiers

7. Databases.

Database Properties

Creating Databases

Altering Databases

Dropping Databases

Obtaining Database Metadata

8. Tables and Indexes.

Table Properties

Creating Tables

Creating Tables Using an Explicit Definition

Specifying the Storage Engine for a Table

Creating Tables Based on Existing Tables

Using TEMPORARY Tables

Altering Tables

Adding and Dropping Columns

Modifying Existing Columns

Renaming a Table

Specifying Multiple Table Alterations

Dropping Tables

Emptying Tables

Indexes

Types of Indexes

Creating Indexes

Choosing an Indexing Algorithm

Dropping Indexes

Obtaining Table and Index Metadata

9. Querying for Data.

Using SELECT to Retrieve Data

Specifying Which Columns to Retrieve

Renaming Retrieved Columns

Identifying the Database Containing a Table

Specifying Which Rows to Retrieve

Using ORDER BY to Sort Query Results

The Natural Sort Order of Data Types

Limiting a Selection Using LIMIT

Using DISTINCT to Eliminate Duplicates

Aggregating Results

The MIN() and MAX() Aggregate Functions

The SUM() and AVG() Aggregate Functions

The COUNT() Aggregate Function

The GROUP_CONCAT() Function

Aggregation for NULL Values or Empty Sets

Grouping Results

GROUP BY and Sorting

Selecting Groups with HAVING

Using GROUP BY and WITH ROLLUP

Using UNION

10. SQL Expressions.

Components of SQL Expressions

Numeric Expressions

String Expressions

Case Sensitivity in String Comparisons

Using LIKE for Pattern Matching

Temporal Expressions

NULL Values

Functions in SQL Expressions

Comparison Functions

Control Flow Functions

Aggregate Functions

Mathematical Functions

String Functions

Temporal Functions

NULL-Related Functions

Comments in SQL Statements

11. Updating Data.

Update Operations

The INSERT Statement

Adding Multiple Records with a Single INSERT Statement

Handling Duplicate Key Values

Using INSERT ... ON DUPLICATE KEY UPDATE

The REPLACE Statement

The UPDATE Statement

Using UPDATE with ORDER BY and LIMIT

Preventing Dangerous UPDATE Statements

Multiple-Table UPDATE Statements

The DELETE and TRUNCATE TABLE Statements

Using DELETE with ORDER BY and LIMIT

Multiple-Table DELETE Statements

Privileges Required for Update Statements

MySQL Developer II Exam.

12. Joins.

Overview

Writing Inner Joins

Writing Inner Joins with the Comma Operator

Writing Inner Joins with INNER JOIN

Writing Outer Joins

Writing LEFT JOIN Queries

Writing RIGHT JOIN Queries

Resolving Name Clashes Using Qualifiers and Aliases

Qualifying Column Names

Qualifying and Aliasing Table Names

Multiple-Table UPDATE and DELETE Statements

13. Subqueries.

Types of Subqueries

Subqueries as Scalar Expressions

Correlated Subqueries

Comparing Subquery Results to Outer Query Columns

Using ALL, ANY, and SOME

Using IN

Using EXISTS

Comparison Using Row Subqueries

Using Subqueries in the FROM Clause

Converting Subqueries to Joins

Converting Subqueries to Inner Joins

Converting Subqueries to Outer Joins

Using Subqueries in Updates

14. Views.

Reasons to Use Views

Creating Views

Restrictions on Views

View Algorithms

Updatable Views

Altering Views

Dropping Views

Checking Views

Obtaining View Metadata

Privileges Required for Views

15. Importing and Exporting Data.

Import and Export Operations

Importing and Exporting Using SQL

Importing Data with LOAD DATA INFILE

Exporting Data with SELECT ... INTO OUTFILE

Data File Format Specifiers

Importing and Exporting NULL Values

Importing and Exporting Data from the Command Line

Importing Data with mysqlimport

Exporting Data with mysqldump

16. User Variables.

User Variable Syntax

User Variable Properties

17. Prepared Statements.

Benefits of Prepared Statements

Using Prepared Statements from the mysql Client

Preparing a Statement

Executing a Prepared Statement

Deallocating Prepared Statements

18. Stored Procedures and Functions.

Benefits of Stored Routines

Differences Between Stored Procedures and Functions

The Namespace for Stored Routines

Defining Stored Routines

Creating Stored Routines

Compound Statements

Declaring Parameters

The DECLARE Statement

Variables in Stored Routines

Conditions and Handlers

Cursors

Retrieving Multiple Result Sets

Flow Control

Altering Stored Routines

Dropping Stored Routines

Invoking Stored Routines

Obtaining Stored Routine Metadata

Stored Routine Privileges and Execution Security

19. Triggers.

Reasons to Use Triggers

Trigger Concepts

Creating a Trigger

Restrictions on Triggers

Referring to Old and New Column Values

Destroying a Trigger

Privileges Required for Triggers

20. Obtaining Database Metadata.

Overview of Metadata Access Methods

Using INFORMATION_SCHEMA to Obtain Metadata

Using SHOW and DESCRIBE to Obtain Metadata

Using mysqlshow to Obtain Metadata

21. Debugging MySQL Applications.

Interpreting Error Messages

The SHOW WARNINGS Statement

The SHOW ERRORS Statement

The perror Utility

22. Basic Optimizations.

Overview of Optimization Principles

Using Indexes for Optimization

Types of Indexes

Principles for Index Creation

Indexing Column Prefixes

Leftmost Index Prefixes

General Query Enhancement

Query Rewriting Techniques

Using EXPLAIN to Obtain Optimizer Information

Optimizing Queries by Limiting Output

Using Summary Tables

Optimizing Updates

Choosing Appropriate Storage Engines

Normalization

MYSQL DBA EXAMS.

MySQL DBA I Exam.

23. MySQL Architecture.

Client/Server Overview

Communication Protocols

The SQL Parser and Storage Engine Tiers

How MySQL Uses Disk Space

How MySQL Uses Memory

24. Starting, Stopping, and Configuring MySQL.

Types of MySQL Distributions

MySQL Binary Distributions

MySQL Source Distributions

Starting and Stopping MySQL Server on Windows

Server Startup Prerequisites on Windows

Running MySQL Server Manually on Windows

Running MySQL Server as a Windows Service

Starting and Stopping MySQL Server on Unix

Server Startup Prerequisites on Unix

Choosing a Server Startup Method on Unix

Runtime MySQL Configuration

Log and Status Files

The General Query Log

The Binary Log

The Slow Query Log

The Error Log

Status Files

Loading Time Zone Tables

Security-Related Configuration

Setting the Default SQL Mode

Upgrading MySQL

25. Client Programs for DBA Work.

Overview of Administrative Clients

MySQL Administrator

mysql

mysqladmin

mysqlimport

mysqldump

Client Program Limitations

26. MySQL Administrator.

MySQL Administrator Capabilities

Using MySQL Administrator

Starting MySQL Administrator

Selecting an Operational Mode

Server Monitoring Capabilities

Server Information

Server Connections

Health

Server Logs

Replication Status

Catalogs

Server Configuration

Service Control

Startup Variables

User Administration

Backup and Restore Capabilities

Making Backups

Restoring Backups

MySQL Administrator System Tray Monitor

27. Character Set Support.

Performance Issues

Choosing Data Types for Character Columns

28. Locking.

Locking Concepts

Explicit Table Locking

Advisory Locking

29. Storage Engines.

MySQL Storage Engines

The MyISAM Engine

MyISAM Locking Characteristics

MyISAM Row-Storage Formats

The MERGE Engine

MERGE Locking Characteristics

The InnoDB Engine

The InnoDB Tablespace and Logs

InnoDB and ACID Compliance

The InnoDB Transaction Model

InnoDB Locking Characteristics

InnoDB Isolation Levels, Multi-Versioning, and Concurrency

Using Foreign Keys

Configuring and Monitoring InnoDB

The MEMORY Engine

MEMORY Indexing Options

The FEDERATED Engine

The Cluster Storage Engine

Other Storage Engines

30. Table Maintenance.

Types of Table Maintenance Operations

SQL Statements for Table Maintenance

CHECK TABLE

REPAIR TABLE

ANALYZE TABLE

OPTIMIZE TABLE

Client and Utility Programs for Table Maintenance

The mysqlcheck Client Program

The myisamchk Utility

Options for mysqlcheck and myisamchk

Repairing InnoDB Tables

Enabling MyISAM Auto-Repair

31. The INFORMATION_SCHEMA Database.

INFORMATION_SCHEMA Access Syntax

INFORMATION_SCHEMA Versus SHOW

Limitations of INFORMATION_SCHEMA

32. Data Backup and Recovery Methods.

Introduction

Binary Versus Textual Backups

Making Binary Backups

Making Binary MyISAM Backups

Making Binary InnoDB Backups

Other Binary Backup Tools

Conditions for Binary Portability

Making Text Backups

Making Text Backups via SQL

Making Text Backups with mysqldump

Making Text Backups with MySQL Administrator

Backing Up Log and Status Files

Replication as an Aid to Backup

MySQL Cluster as Disaster Prevention

Data Recovery

Reloading mysqldump Output

Reloading Dumps with MySQL Administrator

Processing Binary Log Contents

33. Using Stored Routines and Triggers for Administration.

Using Stored Routines and Triggers for Security Purposes

Using Stored Routines to Enhance Performance

MySQL DBA II EXAM.

34. User Management.

User Account Management

Types of Privileges That MySQL Supports

The Grant Tables

Approaches to Account Management

Creating and Dropping User Accounts

Specifying Account Names

Granting Privileges

Revoking Privileges

Changing Account Passwords

When Privilege Changes Take Effect

Specifying Resource Limits

Privileges Needed for Account Management

Client Access Control

Connection Request Checking

Statement Privilege Checking

Resource Limit Checking

Disabling Client Access Control

35. Securing the MySQL Installation.

Security Issues

Operating System Security

Filesystem Security

Log Files and Security

Network Security

Securing the Initial MySQL Accounts

General Privilege Precautions

MySQL Cluster Network Security

FEDERATED Table Security

36. Upgrade-Related Security Issues.

Upgrading the Privilege Tables

Security-Related SQL Mode Values

37. Optimizing Queries.

Identifying Candidates for Query Analysis

Using EXPLAIN to Analyze Queries

How EXPLAIN Works

Analyzing a Query

EXPLAIN Output Columns

Using SHOW WARNINGS for Optimization

MyISAM Index Caching

38. Optimizing Databases.

General Table Optimizations

Normalization

MyISAM-Specific Optimizations

MyISAM Row-Storage Formats

Keep Optimizer Information Up to Date

FULLTEXT Indexes

Specifying MyISAM Maximum Row Count

InnoDB-Specific Optimizations

MERGE-Specific Optimizations

MEMORY-Specific Optimizations

39. Optimizing the Server.

Interpreting mysqld Server Information

Accessing Server System Variables

Accessing Server Status Variables

Measuring Server Load

Tuning Memory Parameters

Global (Server-Wide) Parameters

Per-Client Parameters

Using the Query Cache

Enabling the Query Cache

Measuring Query Cache Utilization

40. Interpreting Diagnostic Messages.

Sources of Diagnostic Information

Using the Error Log for Diagnostic Purposes

Using The Slow Query Log for Diagnostic Purposes

41. Optimizing the Environment.

Choosing Hardware for MySQL Use

Configuring Disks for MySQL Use

Moving Databases Using Symbolic Links

MyISAM Table Symlinking

Network Issues

Optimizing the Operating System for MySQL Use

42. Scaling MySQL.

Using Multiple Servers

Replication

Setting Up Replication

The Binary and Relay Logs

Replication-Related Threads

Replication Troubleshooting

Replication Compatibility and Upgrading

APPENDIXES.

A. References.

B. Other Offers.

Index.

Read More Show Less

Preface

IntroductionIntroduction About This Book

This is a study guide for the MySQL Developer Certification and the MySQL Database Administrator Certification. As such, it is a primer for the MySQL certification exams, but not a replacement for the MySQL Reference Manual or any other MySQL documentation. As part of your preparation for an exam, make sure that you are thoroughly familiar with the MySQL Reference Manual, the MySQL Query Browser Manual (for the Developer exams) and the MySQL Administrator Manual (for the Database Administrator exams). All of these manuals are available on-line from the MySQL Developer Zone Web site at http://dev.mysql.com.

This introduction provides some general hints on what to expect from the exam, what to do in order to take the exam, what happens on the day of the exam, and what happens after you have passed the exam.

The remainder of this study guide covers each section of the exams, as defined in the MySQL 5.0 Certification Candidate Guide. The book is divided into two main parts, each corresponding to one of the two certifications:

  • Chapter 1, "Client/Server Concepts," through Chapter 22, "Basic Optimizations," pertain to the Developer certification.

  • Chapter 23, "MySQL Architecture," through Chapter 42, "Scaling MySQL," pertain to the Database Administrator certification.

Each of the sections is further subdivided into Parts I and II, as follows:

  • Chapter 1, "Client/Server Concepts," through Chapter 11, "Updating Data," pertain to the Developer-Iexam.

  • Chapter 12, "Joins," through Chapter 22, "Basic Optimizations," pertain to the Developer-II exam.

  • Chapter 23, "MySQL Architecture," through Chapter 32, "Data Backup and Recovery Methods," pertain to the DBA-I exam.

  • Chapter 33, "Using Stored Routines and Triggers for Administration," through Chapter 42, "Scaling MySQL," pertain to the DBA-II exam.

However, the split between parts I and II within a certification title may not always be as clear-cut as is suggested by the chapter divisions. Therefore, you should be familiar with all of the material presented for a certification level before going to any particular exam.

There are many cross-references within this book that go across the "boundary" between the two certifications. For example, Chapter 22, "Basic Optimizations," which is in the Developer part of the book, contains a cross reference to Chapter 37, "Optimizing Queries," which is in the DBA part of the book. In cases like this, you are not expected to read the chapter outside the exam for which you're studying. However, doing so will obviously increase your understanding of the subject area.

You might find that the wording of a topic covered in this guide corresponds exactly to the wording of a question on an exam. However, that is the exception. Rote memorization of the material in this guide will not be very effective in helping you pass the exam. You need to understand the principles discussed so that you can apply them to the exam questions. Working through the exercises will be very beneficial in this respect. If you find that you are still having difficulties with some of the materials, you might want to consider the training classes offered by MySQL AB. These classes are presented in a format that facilitates greater understanding through interaction with the instructor.

Because the study guide is targeted to MySQL 5.0, it doesn't normally point out when features are unavailable in earlier versions (nor are you expected to know about this on the exams). This differs from what you might be used to in the MySQL Reference Manual.

Sample Exercises

The CD-ROM that accompanies this book has a number of sample exercises. It's essential that you work through the exercises to test your knowledge. Doing so will prepare you to take the exam far better than just reading the text. Another reason to read the exercises is that occasionally they augment a topic with more detail than is given in the body of the chapter.

Note that the exercises are not always in the same format as the exam questions. The exam questions are in a format that is suited for testing your knowledge. The exercises are designed to help you get a better understanding of the contents of this book, and to help you prove to yourself that you really grasp the topics covered.

Other Required Reading

This book will give you a good overall insight into everything you need to know for MySQL certification. It will not tell you every little detail about how things work in MySQL; nor does it tell you every detail you need to know about actually attending the exam. Other material that you can take advantage of is listed in the following sections.

Manuals
  • Before going to any of the exams, make sure you have familiarized yourself with the MySQL Reference Manual. Familiarizing yourself with the manual is not the same as knowing every word in it, but you should at least skim through it and look more closely at those parts that pertain to the particular exam which you are going to attend.

  • Before taking either of the Developer exams, you should read the MySQL Query Browser Manual.

  • Before taking either of the DBA exams, you should read the MySQL Administrator Manual.

Each of the manuals just listed is available on the MySQL developer Web site, http://dev.mysql.com. You will also find many good technical articles on that Web site. These articles do not make up part of the exam curriculum per se, but they explain many of the concepts presented in this book in a different way and may enable you to get a better perspective on some details.

Sample Data

Almost all examples and exercises in this study guide use the world database as the sample data set. The accompanying CD-ROM contains the data for this database and instructions that describe how to create and populate the database for use with your own MySQL installation.

Study Guide Errata

Although this book was thoroughly checked for correctness prior to publication, errors might remain. Any errors found after publication are noted at http://www.mysql.com/certification/studyguides.

Certification Information at http://www.mysql.com

The Certification pages at http://www.mysql.com/certification contain the overview of the current state of all things you need to know about the MySQL certification program. It is recommended that you read through this information as you start planning your certification, as well as when you plan to go to exams to ensure that you are aware of any last-minute updates.

The Certification area of the MySQL Web site provides comprehensive information on the certifications offered, upcoming certifications and betas, training offers, and so forth. After you've taken a certification exam, the Web site is also where you will be able to check the status of your certification.

The MySQL Certification Candidate Guide

Of particular interest on the MySQL certification Web pages is the MySQL Certification Candidate Guide. It contains the overall description of the MySQL Certification program, as well as all the practical information you will need in order to write an exam. The latest version of the Candidate Guide can be found at http://www.mysql.com/certification/candguide.

The Candidate Guide contains a list of items providing practical advice to you as the candidate, an overview of the entire certification program, prices, policies, practical details regarding going to the exam, and so forth.

The Candidate Guide includes the MySQL Certification Non-Disclosure and Logo Usage Agreement (NDA/LUA). You'll be asked to agree to the agreement when you go to take the exam. At that point, legal agreements will probably be the last thing on your mind, so reading the agreement before you go will save you some distraction and also some exam time.

The Certification Mailing List

Anyone considering pursuing MySQL certification should subscribe to the MySQL Certification mailing list. This is a low-volume list (messages go out once every two months or so), to which MySQL AB posts news related to the certification program. The subscription address for the mailing list is certification-subscribe@lists.mysql.com. To subscribe, send an empty message to that address.

Conventions Used in This Book

This section explains the conventions used in this study guide.

Text in this style is used for program and shell script names, SQL keywords, and command output.

Text in this style represents input that you would type while entering a command or statement.

Text in this style represents variable input for which you're expected to enter a value of your own choosing. Some examples show commands or statements that aren't meant to be entered exactly as shown. Thus, in an example such as the following, you would substitute the name of some particular table for table_name:

SELECT * FROM table_name;

In syntax descriptions, square brackets indicate optional information. For example, the following syntax for the DROP TABLE statement indicates that you can invoke the statement with or without an IF EXISTS clause:

DROP TABLE IF EXISTS table_name;

Lists of items are shown with items separated by vertical bars. If choosing an item is optional, the list is enclosed within square brackets. If choosing an item is mandatory, the list is enclosed within curly braces:

item1 item2 item3 { item1 item2 item3 }

In most cases, SQL statements are shown with a trailing semicolon character (';'). The semicolon indicates where the statement ends and is useful particularly in reading multiple-statement examples. However, the semicolon is not part of the statement itself.

If a statement is shown together with the output that it produces, it's shown preceded by a mysql> prompt. An example shown in this manner is meant to illustrate the output you would see were you to issue the statement using the mysql client program. For example, a section that discusses the use of the VERSION() function might contain an example like this:

mysql> SELECT VERSION();+————————-+ VERSION() +————————-+ 5.0.10-beta-log +————————-+

Some commands are intended to be invoked from the command line, such as from a Windows console window prompt or from a Unix shell prompt. In this guide, these commands are shown preceded by a shell> prompt. Some Windows-specific examples use a prompt that begins with C:. The prompt you will actually see on your own system depends on your command interpreter and the prompt settings you use. (The prompt is likely to be C:\> for a Windows console and % or $ for a Unix shell.)

SQL keywords such as SELECT or ORDER BY aren't case sensitive in MySQL and may be specified in any lettercase when you issue queries. However, for this guide, keywords are written in uppercase letters to help make it clear when they're being used as keywords and not in a merely descriptive sense. For example, "UPDATE statement" refers to a particular kind of SQL statement (one that begins with the keyword UPDATE), whereas "update statement" is a descriptive term that refers more generally to any kind of statement that updates or modifies data. The latter term includes UPDATE statements, but also other statements such as INSERT, REPLACE, and DELETE.

Sample commands generally omit options for specifying connection parameters, such as —host or —user to specify the server host or your MySQL username. It's assumed that you'll supply such options as necessary. Chapter 1, "Client/Server Concepts," discusses connection parameter options.

In answers to exercises that involve invocation of client programs, you might also have to provide options for connection parameters. Those options generally are not shown in the answers.

Running MySQL on Microsoft Windows

Windows-specific material in this Guide (and the certification exams) assumes a version of Windows that is based on Windows NT. This includes Windows NT, 2000, XP, and 2003. It does not include Windows 95, 98, or Me.

About the Exams

To take a MySQL certification exam, you must go to a Pearson VUE testing center. MySQL AB creates the exams and defines the content, the passing score, and so forth. Pearson VUE is responsible for delivering the exams to candidates worldwide.

Registering for an Exam

There are three ways to register for an exam:

  • You can use the Pearson VUE Web site, http://www.vue.com/mysql. Note that you must pre-register on the Web site to set up an account with VUE. VUE processes your application and notifies you when your account is ready. This process usually takes about 24 hours. After your account has been set up, you can register for the exam you want to take.

  • You can call one of the VUE call centers. The telephone numbers are listed in on the Pearson VUE Web site: http://www.vue.com/contact/mysql.

  • You can register directly at your local VUE test center on the day of the exam. A complete list of the test centers can be found on the Web at http://www.vue.com/mysql. Click on the Test Centers link about halfway down the page to find a testing center near you. Note that many test centers have limited hours of operation, so it's always a good idea to call ahead to ensure that you can be accommodated at the time you want to take the exam.

MySQL AB recommends that you use the VUE Web site for exam registration and payment, but you're welcome to use any method you choose.

If you register through the Web or a call center, a receipt will be sent to you as soon as the registration process is completed. If you register directly at the test center, please ask for your receipt when you submit payment.

Going to the Exam

On the day of your exam, you should ensure that you arrive at the test center well ahead of the appointed time (at least 15 minutes early is recommended). When you arrive at the testing center, you will be asked by the test administrator to:

  1. Sign the test log.

  2. Provide two forms of identification. One must contain your address, and one must be a photo ID.

  3. Sign a page explaining the test center rules and procedures.

After you've completed these steps, you'll be taken to your testing station. You'll be furnished with a pen and scratch paper, or an erasable plastic board. During the exam, the test administrator will be monitoring the testing room, usually through a glass partition in the wall. As you come to the testing station, your exam will be called up on the screen and the exam will start when you are ready. Remember to make any adjustments to your chair, desk, screen, and so forth before the exam begins. Once the exam has begun, the clock will not be stopped.

The first thing you will be asked on the exam is to accept the MySQL AB Certification Non-Disclosure and Logo Usage Agreement. As mentioned earlier, it's a good idea to have read the copy found in the MySQL Certification Candidate Guide before going to the exam, so you do not have to spend exam time reading and understanding what it says.

Figure IN.1
The Certification Non-Disclosure and Logo Usage Agreement as it will be presented at the testing station.

Taking the Exam

Each MySQL Certification Exam lasts 90 minutes. In that time, you must answer approximately 70 questions. Beta exams contain more questions, but also allow you more time to answer them. For more information on Beta exams and their availability, see the certification pages on http://www.mysql.com.

The questions and answers in any particular exam are drawn from a large question pool. Each section of the exam will have a different number of questions, approximately proportional to the percentages shown in the following tables. These were the percentages as planned at the time this book went to press; although they are unlikely to change, you should consult the MySQL Certification Candidate Guide for the exact details.

Table IN.1  Division of Questions on Exam Sections for the Developer Exams

MySQL Developer I Exam

MySQL Developer II Exam

Client/Server Concepts

5%

Joins

15%

The mysql Client Program

5%

Subqueries

10%

MySQL Query Browser

5%

Views

10%

MySQL Connectors

5%

Importing and Exporting Data

10%

Data Types

15%

User Variables

5%

Identifiers

5%

Prepared Statements

5%

Databases

5%

Stored Procedures and Functions

15%

Tables and Indexes

15%

Triggers

5%

Querying for Data

15%

Obtaining Database Metadata

5%

SQL Expressions

15%

Debugging MySQL Applications

5%

Updating Data

10%

Basic Optimizations

15%

Table IN.2  Division of Questions on Exam Sections for the DBA Exams

MySQL DBA I Exam

MySQL DBA II Exam

MySQL Architecture

10%

Using Stored Routines and Triggers for Administration

5%

Starting, Stopping, and Configuring MySQL

15%

User Management

15%

Client Programs for DBA Work

5%

Securing the MySQL Installation

10%

MySQL Administrator

10%

Upgrade-Related Security Issues

5%

Character Set Support

5%

Optimizing Queries

15%

Locking

10%

Optimizing Databases

15%

Storage Engines

15%

Optimizing the Server

15%

Table Maintenance

5%

Interpreting Diagnostic Messages

5%

The INFORMATION_SCHEMA Database

10%

Optimizing the Environment

5%

Data Backup and Recovery Methods

15%

Scaling MySQL

10%

This study guide organizes topic material into the sections shown in the Candidate Guide, but you shouldn't expect the exam to follow the same format. While you're taking the exam, questions may occur in any order. For example, on the Developer-I exam, you might be presented with a question about indexing, followed by a question pertaining to data types.

Some features in MySQL are version specific. The current exam and this book cover MySQL 5.0, and you should consider a feature available if it's available as of MySQL 5.0. For example, stored procedures and views were implemented for MySQL 5.0, so for purposes of the exam, you should consider them to be topics upon which you might be tested.

Reading Questions

The single most important factor in answering any exam question is first to understand what the question is asking. The questions are written in very concise language and are thoroughly checked for readability. But you also need to know how to interpret any additional information presented with the question.

On the exam, you will see some SQL statements followed by a semicolon, and some not. This occasionally confuses people. What you need to keep in mind is that SQL statements need only be terminated with a semicolon when used in the context of the mysql command-line client, not in any other contexts. So only when shown in the context of the command-line client should you expect to see a terminator.

One type of information that's often provided is a display of the structure of a table. Instructions for interpreting this information are given later in this introduction (see "Interpreting DESCRIBE Output").

Answering Questions

You should attempt to answer all exam questions, because an unanswered question counts as an incorrect answer. When taking the exam, you'll be able to move back and forth between questions. This enables you to initially skip questions you're unsure of and return to them as time permits. You'll also be able to mark a question "for review," if you want to spend more time on it later. When you've gone through all questions, a review screen will be presented that contains any questions that you've marked for review, as well as all unanswered questions.

All questions are multiple-choice questions, only varying in whether you need to choose single or multiple correct answers among those presented to you.

You select an answer to a question either by clicking with the mouse on the field to the left of the answer, or by pressing the corresponding letter on the keyboard.

For a single-answer question, only one response is correct and you must identify the correct answer from among the possible responses. Some of the responses provided might be partially correct, but only one will be completely correct. In a single-answer question, the fields that you can select are circles ("radio buttons") and the text in the status bar below the question says "select the best response."

Figure IN.2
A multiple-choice/single-answer question. Note that each answer key has a circle ("radio button") beside it, and the status bar says "select the best response.

For a multiple-answer question, you must choose all correct answers to get credit for your response. As with single-answer questions, there might be subtle differences between correct and incorrect answers; take your time to read each possible answer carefully before deciding whether it is correct. In multiple-answer questions, the fields that you can select are square ("check boxes") and the status line says "Select between 1 and n answers," where n is the total number of possible answers.

Figure IN.3
A multiple-choice/multiple-answer question. Note that each answer key has a square ("check box") beside it, and the status bar says "select between 1 and 6 answers.

After the Exam

Unless you're taking part in a Beta exam, you'll receive your grade as soon as you complete the exam. The test center will provide you with a score report.

If you pass, MySQL AB will mail your certificate four to six weeks after receiving your exam results from the test center.

Whether you pass or fail, after you've taken any MySQL certification exam, you'll receive a letter from MySQL AB telling you how to gain access to extra information at http://www.mysql.com. There are two main entry points into this area:

  • The candidate area: http://www.mysql.com/certification/candidate

    Here, you will find information specially set aside for MySQL certification candidates. For example, there might be special offers, information on pre-releases of new certifications, and so on.

  • The results area: http://www.mysql.com/certification/results

    In this area, potential clients and employers can confirm that your certificate is valid. Access for others to this area is controlled by you, using the candidate area.

Retaking Exams

If you get a failing grade on the exam, you have the option of retaking it. There is no limit set on when you are allowed to retake an exam. MySQL AB does not place restrictions on how soon you can retake an exam, but doing so is not advised until you've done some further study.

This isn't just a commonsense warning. The statistics show with great clarity that those who attempt to retake a failed exam within five days of the first exam are much more likely to fail once again rather than passing.

Warning

For every popular certification exam, there are always enterprising individuals who set up so-called "braindump" Internet sites, where people anonymously post questions and answers purported to be from the exam. Please note these cautions about using or contributing to these sites:

  • If you use such a site, you are very likely to be misled. We've seen these sites, and trust us: The answers they provide are more often wrong than correct. Worse, most of the questions shown have never been—and are so ludicrous that they never will be—on an exam; they exist only in the submitter's head. As a result, instead of being helpful, such sites lead to confusion.

  • If you contribute to such a site by posting your own exam questions and answers, you risk forfeiting not only the certification for the exam about which you have posted details, but your involvement in the entire MySQL Certification program. You might thus never be able to regain MySQL certification credentials.

Interpreting DESCRIBE Output

You should understand how to interpret the output of the DESCRIBE table_name statement. This is of particular importance both for this study guide and for taking certification exams. In both cases, when it's necessary that you know the structure of a table, it will be shown as the output of a DESCRIBE statement in the same format as that displayed by the mysql program. For example, assume that a question requires you to know about a table named City. The table's structure will be presented as follows:

mysql> DESCRIBE City;+——————-+—————+———+——-+————-+————————+ Field Type Null Key Default Extra +——————-+—————+———+——-+————-+————————+ ID int(11) NO PRI NULL auto_increment Name char(35) NO CountryCode char(3) NO District char(20) NO Population int(11) NO 0 +——————-+—————+———+——-+————-+————————+

The output of the DESCRIBE statement contains one row for each column in the table. The most important features of the output are as follows:

  • The Field value indicates the column name.

  • The Type value shows the column data type.

  • The Null indicator is the word YES if the column can contain NULL values and NO if it cannot. In the example shown, Null is NO for all columns of the City table. This indicates that none of that table's columns can contain NULL values.

  • The Key indicator may be empty or contain one of three non-empty values:

    • An empty Key value indicates that the column in question either isn't indexed or is indexed only as a secondary column in a multiple-column, non-unique index. For purposes of the exam, you should assume that if Key is empty, it's because the column is not indexed at all.

    • If the Key value is the keyword PRI (as in the output shown for the ID column), this indicates that the column is a PRIMARY KEY or is one of the columns in a multiple-column PRIMARY KEY.

    • If the Key value is the keyword UNI, this indicates that the column is the first column of a unique-valued index that cannot contain NULL values.

    • If the Key value is the keyword MUL, this indicates that the column is the first column of a non-unique index or a unique-valued index that can contain NULL values.

    It's possible that more than one of the Key values may apply to a given column of a table. For example, a column that is a PRIMARY KEY might also be part of other indexes. When it's possible for more than one of the Key values to describe an index, DESCRIBE displays the one with the highest priority, in the order PRI, UNI, MUL.

    Because a column can be part of several indexes, the Key values do not necessarily provide an exhaustive description of a table's indexes. However, for purposes of the exam, you should assume that the table descriptions given provide all the information needed to correctly answer the question.

  • Default shows the column's default value. This is the value that MySQL assigns to the column when a statement that creates a new record does not provide an explicit value for the column. (For example, this can happen with the INSERT, REPLACE, and LOAD DATA INFILE statements.)

  • The Extra value displays other details about the column. The only Extra detail about which you need be concerned for the exam is the value auto_increment. This value indicates that the column has the AUTO_INCREMENT attribute. The ID column shown in the example is such an instance.

You can read more about data types, default values, and the AUTO_INCREMENT column attribute in Chapter 5, "Data Types." Indexing is covered in Chapter 8, "Tables and Indexes." The DESCRIBE statement and other methods of obtaining table metadata are covered in more detail in Chapter 20, "Obtaining Database Metadata."

Sample Tables

This study guide uses several different database and table names in examples. However, one set of tables occurs repeatedly: the tables in a database named world. This section discusses the structure of these tables. Throughout this study guide, you're assumed to be familiar with them. To make it easier for you to try the examples, the accompanying CD-ROM includes the world database. MySQL AB also provides a downloadable copy of the world database that you can obtain at http://dev.mysql.com/doc.

The world database contains three tables, Country, City, and CountryLanguage:

  • The Country table contains a row of information for each country in the database:

    mysql> DESCRIBE Country;+————————+—————————-+———+——-+————-+———-+ Field Type Null Key Default Extra +————————+—————————-+———+——-+————-+———-+ Code char(3) NO PRI Name char(52) NO Continent enum('Asia',...) NO Asia Region char(26) NO SurfaceArea float(10,2) NO 0.00 IndepYear smallint(6) YES NULL Population int(11) NO 0 LifeExpectancy float(3,1) YES NULL GNP float(10,2) YES NULL GNPOld float(10,2) YES NULL LocalName char(45) NO GovernmentForm char(45) NO HeadOfState char(60) YES NULL Capital int(11) YES NULL Code2 char(2) NO +————————+—————————-+———+——-+————-+———-+

    The entire output of the DESCRIBE statement is too wide to display on the page, so the Type value for the Continent line has been shortened. The value enum('Asia',...) as shown actually stands for enum('Asia', 'Europe', 'North America', 'Africa', 'Oceania', 'Antarctica', 'South America').

  • The City table contains rows about cities located in countries listed in the Country table:

    mysql> DESCRIBE City;+——————-+—————+———+——-+————-+————————+ Field Type Null Key Default Extra +——————-+—————+———+——-+————-+————————+ ID int(11) NO PRI NULL auto_increment Name char(35) NO CountryCode char(3) NO District char(20) NO Population int(11) NO 0 +——————-+—————+———+——-+————-+————————+

  • The CountryLanguage table describes languages spoken in countries listed in the Country table:

    mysql> DESCRIBE CountryLanguage;+——————-+———————-+———+——-+————-+———-+ Field Type Null Key Default Extra +——————-+———————-+———+——-+————-+———-+ CountryCode char(3) NO PRI Language char(30) NO PRI IsOfficial enum('T','F') NO F Percentage float(4,1) NO 0.0 +——————-+———————-+———+——-+————-+———-+

The Name column in the Country table contains full country names. Each country also has a three-letter country code stored in the Code column. The City and CountryLanguage tables each have a column that contains country codes as well, though the column is named CountryCode in those tables.

In the CountryLanguage table, note that each country may have multiple languages. For example, Finnish, Swedish, and several other languages are spoken in Finland. For this reason, CountryLanguage has a composite (multiple-column) index consisting of both the Country and Language columns.


© Copyright Pearson Education. All rights reserved.

Read More Show Less

Customer Reviews

Average Rating 3.5
( 3 )
Rating Distribution

5 Star

(1)

4 Star

(0)

3 Star

(2)

2 Star

(0)

1 Star

(0)

Your Rating:

Your Name: Create a Pen Name or

Barnes & Noble.com Review Rules

Our reader reviews allow you to share your comments on titles you liked, or didn't, with others. By submitting an online review, you are representing to Barnes & Noble.com that all information contained in your review is original and accurate in all respects, and that the submission of such content by you and the posting of such content by Barnes & Noble.com does not and will not violate the rights of any third party. Please follow the rules below to help ensure that your review can be posted.

Reviews by Our Customers Under the Age of 13

We highly value and respect everyone's opinion concerning the titles we offer. However, we cannot allow persons under the age of 13 to have accounts at BN.com or to post customer reviews. Please see our Terms of Use for more details.

What to exclude from your review:

Please do not write about reviews, commentary, or information posted on the product page. If you see any errors in the information on the product page, please send us an email.

Reviews should not contain any of the following:

  • - HTML tags, profanity, obscenities, vulgarities, or comments that defame anyone
  • - Time-sensitive information such as tour dates, signings, lectures, etc.
  • - Single-word reviews. Other people will read your review to discover why you liked or didn't like the title. Be descriptive.
  • - Comments focusing on the author or that may ruin the ending for others
  • - Phone numbers, addresses, URLs
  • - Pricing and availability information or alternative ordering information
  • - Advertisements or commercial solicitation

Reminder:

  • - By submitting a review, you grant to Barnes & Noble.com and its sublicensees the royalty-free, perpetual, irrevocable right and license to use the review in accordance with the Barnes & Noble.com Terms of Use.
  • - Barnes & Noble.com reserves the right not to post any review -- particularly those that do not follow the terms and conditions of these Rules. Barnes & Noble.com also reserves the right to remove any review at any time without notice.
  • - See Terms of Use for other conditions and disclaimers.
Search for Products You'd Like to Recommend

Recommend other products that relate to your review. Just search for them below and share!

Create a Pen Name

Your Pen Name is your unique identity on BN.com. It will appear on the reviews you write and other website activities. Your Pen Name cannot be edited, changed or deleted once submitted.

 
Your Pen Name can be any combination of alphanumeric characters (plus - and _), and must be at least two characters long.

Continue Anonymously
Sort by: Showing 1 – 4 of 3 Customer Reviews
  • Anonymous

    Posted September 12, 2010

    600+ Pages - It's ALL THERE !

    Simply incredible ! Loaded with pertinent information !

    This Study Guide clearly presents everything you'll need to pass 310-810 & 310-811 ! (Those are the DBA tests. It also covers the Developer Tests, which I did not attempt.)

    All the nuances of MySQL 5.x are conveyed with useful examples. It also introduces some nuances - you won't need for the test, but you will want to know to fill the MySQL DBA role, and clearly identifies those items as such !
    {
    As as side note, Sun Certified MySQL DBA (transitioned to Oracle Certified Professional MySQL DBA) is a pre-requisite to Sun Certified Cluster MySQL DBA (transitioned to Oracle Certified Expert for MySQL Cluster DBA). The Sun/Oracle websites identify:

    MySQL 5.1 Cluster Certification Study Guide Jon Stephens, Mike Kruckenberg, Roland Bouman, Stewart Smith, Solomon Chang ISBN: 978-1-8479916-8-3

    as preparation material for 310-815...only that does not exist on THIS PLANET. After weeks of searching, and ordering, only to get emails 'order cancelled - not available'

    I finally scored. When the product finally arrived, the back cover indicates ISBN: 978-0-595-35250-0 !!! It was indeed composed by the authors listed above. This info is provided as anonymous GOOD DEED for those pursuing MySQL Cluster Cert !
    }

    Study dilligently and you will succeed.

    As Bon Jovi put it, 'Luck aint even lucky, gotta make your own breaks !'

    Peace be with you.

    Was this review helpful? Yes  No   Report this review
  • Anonymous

    Posted August 14, 2014

    No text was provided for this review.

  • Anonymous

    Posted January 29, 2013

    No text was provided for this review.

  • Anonymous

    Posted December 7, 2009

    No text was provided for this review.

Sort by: Showing 1 – 4 of 3 Customer Reviews

If you find inappropriate content, please report it to Barnes & Noble
Why is this product inappropriate?
Comments (optional)