Introduction
Last year I wrote a
two-part series entitled the Database Journal Guide to MySQL certification. Part
one covered the core certification, and part
two the professional certification. Those two articles covered the MySQL 4
certification. Although it is still possible to achieve MySQL 4 certification,
MySQL development has been rapid over the last year, and MySQL 5 is now the
latest stable version. MySQL AB has released their MySQL 5 certification as
beta, and many developers and DBA’s are choosing the newer certification.
Consequently, it is time this month for a Database Journal Guide to MySQL 5
Certification.
Developer and DBA
MySQL has changed the
exam structure. Rather than having a simple hierarchical structure, with a core exam aimed at developers, and a professional exam aimed at DBA’s, with
it only being possible to sit for the professional after obtaining the core,
there is now a separate DBA
and a Developer stream. If a
candidate already has the MySQL 4 core certification, they can take a single
upgrade exam to achieve the MySQL 5 Developer certification. If a candidate has
the MySQL Professional Certification, they can take a single upgrade exam to
achieve MySQL 5 DBA certification.
Everyone else though has
to sit through two exams to obtain either certification. There are the
Developer 1 and Developer 2 exams, and the DBA 1 and DBA 2 exams. There is no
forced hierarchy though, and the exams can be taken in any order, although
logically the concepts tested in the second exam follow on from those tested in
the first exam. This month’s article focuses on the MySQL Developer Stream.
Why certify?
In brief, the benefits to
the employee are that they are more attractive to an employer. As someone who
hires staff, I can say that it is often little things that make the difference
on that resume, and certified developer/DBA does stand out. Perhaps the true
benefit for all parties is that the process of studying for the certification
results in a greater understanding of MySQL. Developers I know who have taken
the certification have commented that they feel more comfortable doing their
MySQL-related work after studying for the test. An employee feels more
confident working with MySQL, and an employer more confident that their staff
can do the job required. With the separation of the two certifications into two
streams, there is less pressure for a developer to do the more
impressive-sounding professional certification, when really the developer
stream may be more suitable. Developers are no less professional than DBA’s,
but their needs differ.
MySQL Developer 1
This section, and the next,
summarize all topics covered in the MySQL 5 developer exams (as of June 2006),
as well as links to relevant resources. The percentage figure indicates how
much of the total exam is devoted to that particular topic.
-
Client/Server
Concepts (5%). This section tests your knowledge of how MySQL works according
to the client server model, the different SQL server modes, MySQL architecture
and the different configurations such as MySQL-Max, invoking client
applications and the connection parameters. -
The mysql
Client Program (5%). Covers general issues about the mysql client, mysql prompts, use of
editing keys in MySQL, client commands and SQL statements, statement
terminators, use of the –safe-updates option, mysql output format, using
script files with MySQL, using mysql interactively and getting help on the
server-side. -
MySQL Query
Browser (5%). Covers use and capabilities of the query browser, the MySQL Table
editor, use of the query window, the options dialog and connection management. -
Connectors
(5%), Covers an overview of the MySQL client interfaces and the most
high-profile Connectors: Connector/J, Connector/ODBC and Connector/NET. -
Data Types
(15%). Tests knowledge of column attributes, the BIT Data Type, temporal,
numeric and string data types, use of the AUTO_INCREMENT column attribute, and
how MySQL handles handle missing or invalid data values -
Identifiers
(5%). Covers use of reserved words as identifiers, identifier syntax, use of
qualified names and case sensitivity. -
Databases
(5%). Covers database properties, creating, dropping and altering databases,
and obtaining database metadata. -
Tables and
Indexes (15%). Covers indexes in general, creating and dropping indexes, table
properties, how to obtain metadata about tables and indexes, creating,
dropping, altering and emptying tables -
Querying for
Data (15%). Includes the use of SELECT statements, aggregating results, the
UNION statement, specifying which rows and columns to retrieve and grouping
results. -
SQL
expressions (15%). Includes string, numeric and temporal expressions, the
components of SQL expressions, null values, comments in SQL statements and
functions in SQL expressions. -
Updating Data
(10%). Covers an overview of update operations, as well as the INSERT, UPDATE,
DELETE, TRUNCATE and REPLACE statements specifically, and the privileges
required to perform update statements.
MySQL Developer 2
There is no need to take
the MySQL Developer 2 exam after MySQL Developer 1 – the exams can be taken in
either order. However, Developer 2’s topics are more advanced, and when learning MySQL should only be tackled
after grasping the topics covered in Developer 1.
-
Joins (15%).
This module tests your knowledge of SQL joins, in particular multi-table UPDATE
and DELETE statements, outer joins, inner joins, and how to resolve name
clashes using qualifiers and aliases. -
Subqueries
(10%). Covers types of subqueries, subqueries as scalar expression, converting
subqueries to joins, comparison using row subqueries, correlated subqueries,
use of subqueries in updates as well as the FROM clause, and comparing subquery
results to outer query columns. -
Views (10%).
Includes creating, dropping, altering and checking views, displaying
information about views, when and why views should be used, and privileges
required for using views -
Importing and
Exporting Data (10%). Covers importing and exporting using SQL, using the
command line, and general import/export operations. -
User Variables
(5%). Covers user variable syntax and properties. -
Prepared
Statements (5%). Covers executing and preparing prepared statements, use of
prepared statements from the mysql client, deallocating prepared statements,
and the benefits of prepared statements.-
Connecting
and prepared statements with the mysqli extension (Database Journal) (only
the general parts about prepared statements are relevant) -
SQL syntax for
prepared statements
-
Connecting
-
Stored
Routines (15%). Includes stored routine definition, creating, altering and
dropping stored routines, finding out more information about a stored routine,
differences between stored routines and functions, stored routine privileges
and security, invoking stored routines.-
MySQL
Stored Procedures, Part 1 (Database Journal) -
MySQL
Stored Procedures, Part 2 (Database Journal) -
MySQL
Stored Procedures, Part 3 (Database Journal) -
MySQL
Stored Functions (Database Journal) -
Stored
Procedures and Functions -
CREATE
PROCEDURE and CREATE FUNCTION Syntax -
ALTER
PROCEDURE and ALTER FUNCTION Syntax -
DROP
PROCEDURE and DROP FUNCTION Syntax - CALL Statement Syntax
-
BEGIN … END
Compound Statement Syntax -
DECLARE Statement
Syntax -
Variables
in Stored Routines -
Conditions and
Handlers - Cursors
-
Flow
Control Constructs -
Stored
Procedure example
-
MySQL
-
Triggers (5%).
Covers creating and destroying triggers, reasons to use triggers, trigger
concepts and restrictions, privileges required to use triggers, how to refer to
old and new column values. -
Obtaining
Database Metadata (5%). Includes an overview of metadata access methods, the
use of mysqlshow, the INFORMATION_SCHEMA database, SHOW and DESCRIBE to obtain
metadata, -
Debugging
MySQL Applications (5%). Covers the SHOW ERRORS and SHOW WARNINGS statements,
the perror utility and interpreting error messages. -
Basic
Optimizations (15%). Covers an overview of the principles of optimization, the
use of indexes for optimization, choosing the most suitable storage engines,
normalization and general query enhancements.
Conclusion
If you have sufficient
practical experience in using MySQL, and with such a host of online and offline
resources, there is no reason you can’t succeed in achieving MySQL
certification the first time. Even if you decide not to take the test,
preparing for it can be a useful way to improve your skills. Good luck!
