MySQL 5.6 Developer Exam Prep

I couldn’t really find an exam prep tool for the MySQL 5.6 Developer Exam, so I decided to create my own.  I figured if I had something online, then I could study anywhere, without lugging around a book, or too much paper.

Hopefully, other people will find this useful as well.

The list below, comes directly from the Oracle site.  I will link to each of the sections as I complete them.

MySQL Exam Topics

• MySQL Architecture
  > Use MySQL client programs to interface with the MySQL Server interactively and in batch
  > Describe SQL Modes and their impact on behavior of MySQL
  > Identify characteristics which have session scope
  
• General MySQL Syntax
  > Explain MySQL implementation of identifiers including case sensitivity, qualified names, aliases and use of reserved words
  > Identify MySQL data type properties and appropriate usage
  > Recognize and use common functions and expressions for all MySQL data types
  > Identify and use comment syntax
  > Describe and utilize prepared statements
  > Describe transactions and transaction isolation levels and the impact they have on database behavior
  
• Creation and Design of MySQL Schema Objects
  > Design and create normalized databases
  > Create and modify tables using appropriate data types and indexing
  > Describe and create table constraints enforcing data integrity
  > Creating and modifying views
  > Identify and use various methods to obtain metadata for MySQL database objects
  
• Creation, Design and Use of MySQL Stored Programs
  > Describe and use triggers
  > Create and execute stored procedures
  > Create and use stored functions
  > Implement error handling within stored procedures
  
• Querying for Data
  > Execute a basic SELECT statement
  > Limit rows returned by a SELECT statement
  > Limit columns returned by a SELECT statement
  > Apply sorting to SELECT statement results
  > Execute SELECT statements which aggregate and group data
  
• Modifying Data
  > Describe and execute INSERT statements
  > Describe and execute REPLACE statements
  > Describe and execute UPDATE statements
  > Describe and execute TRUNCATE statements
  > Describe and execute LOAD DATA statements
  > Describe and execute DELETE statements
  
• Joins, subqueries and UNION
  > Identify, describe and use JOINs in MySQL commands
  > Describe and utilize subqueries in MySQL commands
  > Perform operations using UNION clause
  
• MySQL Application Development
  > Identify key characteristics, features and options for PHP, Java and .NET development using MySQL standard drivers
  > Write a basic Java application that uses MySQL
  > Write a basic PHP application that uses MySQL
  > Write a basic .NET application that uses MySQL
  > Interpret MySQL error messages
  > Collect available diagnostic information
  > Describe and use NoSQL and memcached API
  
• Basic Optimizations
  > Identify statements requiring optimization
  > Recognize and create optimal indexes for query optimization
  > Recognize and fix sub-optimal SQL commands
  > Identify appropriate optimization strategies for InnoDB usage
  > Optimize performance through data normalization
  

Most everything in these pages will be a summary of ideas that come from a combination of Paul Dubois’ MySQL 5.0 Certification Study Guide, the online documentation, and the MySQL Cookbook.