The value of certification
I recently arranged for
the developers at our company to get MySQL certified. From the employer’s point
of view, it was not the piece of paper I was after. Rather, the improved
productivity that properly-trained MySQL users could provide. The benefits have
been noticeable. The number of badly written queries has decreased, saving
everyone time and effort. The developers feel more confident using MySQL, and
can use features they previously had not known existed.
There are two sources of
MySQL certification that I know about. One is the Brainbench MySQL
3.23 certification, and the other one, much more well-known, and more
credible, is the official MySQL certification, which consists of two levels,
Core (for MySQL users) and Professional (for MySQL administrators). The
Brainbench one is rather outdated. Since the latest stable release, MySQL 4.1,
is two major versions away from 3.23, there is not much benefit to be gained
from the Brainbench certification. Also, Brainbench tests are seen as less
credible because they can be done online, without supervision, but this can
easily be overcome by an employer ensuring the test is done on their premises
(and Brainbench offers a free repeat test to employers if they wish to validate
an employees certification).
Nevertheless, the rest of
this article focuses on the official MySQL Core certification. What follows is
a reference for people studying for the Core certification, including a list of
topics covered in the Core exam, as well as resources from mainly the MySQL
site and Database Journal that you can use to enhance your studies.
MySQL Core Certification
The test covers MySQL
4.1, and tests the typical skills required by a MySQL usersomeone using MySQL,
such as a developer. In general, this includes data definition (creating,
modifying and deleting databases and tables) and data manipulation (inserting,
modifying, deleting and selecting data).
The complete list of exam
topics (as defined by MySQL in March 2005) follows, as well as links to
relevant Database Journal and other articles that can help you study this
topic:
-
Theory about
MySQL AB (the MySQL company) and the MySQL database server (5%). This includes
the difference between MySQL and MySQL AB, core values of the company and how
it operates, the GNU and commercial licenses, mailing lists and the reference
manual. -
Operating
Systems, MySQL distributions and other Software (10%). Includes major program
components used in MySQL, major OS families supported by MySQL, differences
between major distributions, client interfaces -
Using MySQL
Client Programs (10%). Includes all about the MySQL client, interactive usage, mysqlimport,
mysqldump. checking tables, using MySQL Connector/ODBC and MySQL Connector/J. -
Data
Definition Language (20%). Includes general database and table properties,
storage engines, size limits, identifier syntax, creating and dropping
databases, creating, modifying and dropping tables and indexes, primary keys,
column types, AUTO_INCREMENT, string and number formats (including MySQL 4.1
TIMESTAMP), viewing table structures.- CREATE DATABASE
- CREATE TABLE
- CREATE INDEX
-
Table
types in MySQL: Part 1 – HEAP tables (Database Journal) -
Table
Types in MySQL: Part 2 – InnoDB tables (Database Journal) -
MySQL Storage
Engines and Table Types - ALTER TABLE syntax
- DROP TABLE syntax
- DROP INDEX syntax
-
DROP DATABASE
syntax - String types
- Numeric types
-
Date and time
types -
Column type
storage requirements -
Identifier
qualifiers - SHOW Syntax
- DESCRIBE syntax
-
The DATETIME, DATE and
TIMESTAMP types -
TIMESTAMP
properties as of MySQL 4.1 -
Using
AUTO_INCREMENT -
Data
Definition Language, Part 1 (excerpt from the MySQL Certification Guide) -
Data
Definition Language, Part 2 (excerpt from the MySQL Certification Guide)
-
SELECT
Statements (15%). Includes selecting columns, the WHERE condition, ORDER BY,
LIMIT, GROUP BY, HAVING, DISTINCT and UNION, as well as the MySQL 4.1-specific
GROUP_CONCAT() and GROUP BY … WITH ROLLUP. -
Basic SQL
(10%). In particular, SQL expressions and functions, pattern matching with LIKE
and wildcards, the IN() function, case sensitivity, reserved words as
identifiers, NULLs and comments, prepared statements.- SQL statement syntax
-
String
comparison functions -
Comparison
functions and operators -
Working with
null values -
Problems with
NULL values -
Identifier
case-sensitivity -
Case-sensitivity
in searches -
Treatment of
reserved words in MySQL - Comment syntax
-
‘–‘ as the
start of a comment -
SQL syntax for prepared
statements
-
Update Statements
(10%), including UPDATE, INSERT, REPLACE, DELETE, TRUNCATE, handling duplicate
keys, ORDER BY and LIMIT -
Joins (15%).
In particular, INNER JOIN, LEFT JOIN, RIGHT JOIN, converting subqueries to
inner and outer joins, multi-table UPDATE and DELETE, and using qualifiers and
aliases.-
SQL Joins –
multi-table queries (Database Journal) -
MySQL
subqueries (Database Journal) - Join syntax
-
How MySQL
optimizes LEFT JOIN and RIGHT JOIN
-
SQL Joins –
-
Importing and
Exporting Data: (5%). This includes the LOAD DATA INFILE and SELECT INTO
OUTFILE statements, the privileges required for these statements, using files
on the server and client, limiting and ordering the results, and handling
duplicate values.
