Mysql 5 Certification Study Guide - Softcover

Dubois, Paul; Hinz, Stefan; Pedersen, Carsten

Softcover

ISBN 10: 0672328127 ISBN 13: 9780672328121

Publisher: Mysql Pr, 2005

Synopsis

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.

About this title

About 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.

Excerpt. © Reprinted by permission. All rights reserved.:

Introduction

Introduction

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-I exam.

• 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.

About this edition

• Publisher: Mysql Pr
• Publication date: 2005
• ISBN 10: 0672328127
• ISBN 13: 9780672328121
• Binding: Paperback
• Number of pages: 643