MySQL Certification Reference Links: See http://marcpatterson.com/2011/12/how-do-i-get-certified-in-mysql/ http://gavinsoorma.com/2011/02/passing-the-11g-ocm-exam-some-thoughts/ Example questions comes with guide : http://www.georgiecasey.com/captchas/exercises-dev-i.pdf http://www.georgiecasey.com/captchas/exercises-dev-ii.pdf Overview ~ MySQL 5 DBA Certified Professional Exam, Part I Sylabus ~ MySQL 5 DBA Certified Professional Exam, Part II Sylabus ~ 1Z0-047 Oracle Database SQL Expert ~ 1Z0-871 MySQL Dev Part 1 ~ Overview ~ * MySQL Certified Associate is optional, so skip it. Directly take: * 1Z0-871 MySQL 5 Developer Certified Professional Exam, Part I * 1Z0-872 MySQL 5 Developer Certified Professional Exam, Part II * MySQL DBA Part I (Exam Number: 1Z0-873) OR Part II( also can be taken directly. =================================================================== MySQL 5 DBA Certified Professional Exam, Part I Sylabus ~ =================================================================== Exam Topics Section 1: MySQL Architecture (10%) Client/Server Overview Communication Protocols The SQL Parser and Storage Engine Tiers How MySQL Uses Disk Space How MySQL Uses Memory Section 2: Starting, Stopping, and Configuring MySQL (20%) Types of MySQL Distributions Starting and Stopping MySQL Server on Windows Starting and Stopping MySQL Server on Unix Runtime MySQL Configuration Log and Status Files Loading Time Zone Tables Security-Related Configuration Setting the Default SQL Mode Upgrading MySQL Section 3: Client Programs for DBA Work (5%) Overview of Administrative Clients mysql mysqladmin mysqlimport mysqldump Client Program Limitations Section 4: Character Set Support (5%) Performance Issues Choosing Data Types for Character Columns Section 5: Locking (10%) Locking Concepts Explicit Table Locking Advisory Locking Section 6: Storage Engines (20%) MySQL Storage Engines The MyISAM Engine The MERGE Engine The InnoDB Engine The MEMORY Engine The FEDERATED Engine The Cluster Storage Engine Other Storage Engines Section 7: Data (Table) Maintenance (10%) Types of Table Maintenance Operations SQL Statements for Table Maintenance Client and Utility Programs for Table Maintenance Repairing InnoDB Tables Enabling MyISAM Auto-Repair Section 8: The INFORMATION_SCHEMA Database (5%) INFORMATION_SCHEMA Access Syntax INFORMATION_SCHEMA Versus SHOW Limitations of INFORMATION_SCHEMA Section 9: Data Backup and Recovery Methods (15%) Introduction Binary Versus Textual Backups Making Binary Backups Making Text Backups Backing Up Log and Status Files Replication as an Aid to Backup MySQL Cluster as Disaster Prevention Data Recovery =================================================================== MySQL 5 DBA Certified Professional Exam, Part II Sylabus ~ =================================================================== Exam Topics Section 1: Stored Routines (5%) Using Stored Routines and Triggers for Security Purposes Using Stored Routines to Enhance Performance Section 2: User Management (20%) User Account Management Client Access Control Section 3: Securing the Server (10%) Security Issues Operating System Security Filesystem Security Log Files and Security Network Security FEDERATED Table Security Section 4: Upgrade-Related Security Issues (5%) Upgrading the Privilege Tables Security-Related SQL Mode Values Section 5: Optimizing Queries (15%) Identifying Candidates for Query Analysis Using EXPLAIN to Analyze Queries Using SHOW WARNINGS for Optimization MyISAM Index Caching Section 6: Optimizing Schemas (15%) General Table Optimizations Normalization MyISAM-Specific Optimizations InnoDB-Specific Optimizations MERGE-Specific Optimizations MEMORY-Specific Optimizations Section 7: Optimizing the Server (10%) Interpreting mysqld Server Information Measuring Server Load Tuning Memory Parameters Using the Query Cache Section 8: Interpreting Error Messages (5%) Sources of Diagnostic Information Using the Error Log for Diagnostic Purposes Using The Slow Query Log for Diagnostic Purposes Section 9: Optimizing the Environment (5%) Choosing Hardware for MySQL Use Configuring Disks for MySQL Use Network Issues Optimizing the Operating System for MySQL Use Section 10: Scaling MySQL (10%) Using Multiple Servers Replication =================================================================== Centers ~ =================================================================== Test Center: Global Knowledge Network (India) Pvt. Ltd. Address: #78/1 Royal Barter, 1st Floor Residency Road Bangalore, KAR 560025 Telephone: +91 80 4050 0999 Directions: Near Hotel Ballal Residency, =================================================================== Test Center: CEGON SOFT Pvt. Ltd Address: #58/4,8th Main Road,17th ,"A" Cross Street, Malleswaram Bangalore, KAR 560055 Telephone: +91-80-41110955 Directions: 8th main,17th A cross ,Malleshwaram , Near MES College . Direct Conduct No : selva :09036490933 =================================================================== Test Center: NIIT Limited- Malleswaram, Bangalore Address: No 25, 1st Floor, Padamashree Mansion Sampige Road, Malleswaram Bangalore Bangalore, KAR 560003 Telephone: +91-080-41621912 =================================================================== Telephone: +91-80-25421686 Directions: SIBYL SOLUTIONS # 410, 5th 'A' Cross, 2nd Block, HRBR Layout, Kalyan Nagar, Bangalore - 43. Office No : 080 -25421686 / 080 - 42161675 We are located on the Outer Ring Road LANDMARKS : Opposite to Kalyan Nagar Bus Depot on the Outer Ring Road Close to Girias Showroom which is located on the Outer Ring Road =================================================================== 1Z0-047 Oracle Database SQL Expert ~ =================================================================== Exam Number: 1Z0-047 Associated Certifications: Oracle Database: SQL Certified Expert Exam Price: US$ 195 Duration: 120 minutes Number of Questions: 70 Passing Score: 66% Exam Topics Retrieving Data Using the SQL SELECT Statement ~ List the capabilities of SQL SELECT statements Execute a basic SELECT statement Describe how schema objects work Restricting and Sorting Data ~ Limit the rows that are retrieved by a query Sort the rows that are retrieved by a query Using Single-Row Functions to Customize Output ~ Describe various types of functions that are available in SQL Use character, number, and date functions in SELECT statements Describe the use of conversion functions Reporting Aggregated Data Using the Group Functions ~ Identify the available group functions Describe the use of group functions Group data by using the GROUP BY clause Include or exclude grouped rows by using the HAVING clause Displaying Data from Multiple Tables ~ Write SELECT statements to access data from more than one table using equijoins and nonequijoins Join a table to itself by using a self-join View data that generally does not meet a join condition by using outer joins Generate a Cartesian product of all rows from two or more tables Using Subqueries to Solve Queries ~ Define subqueries Describe the types of problems that subqueries can solve List the types of subqueries Write single-row and multiple-row subqueries Using the Set Operators ~ Describe set operators Use a set operator to combine multiple a single query Control the order of rows returned Manipulating Data ~ Describe each data manipulation language (DML) statement Insert rows into a table Update rows in a table Delete rows from a table Control transactions Using DDL Statements to Create and Manage Tables ~ Categorize the main database objects Review the table structure List the data types that are available for columns Create a simple table Explain how constraints are created at the time of table creation Creating Other Schema Objects ~ Create simple and complex views Retrieve data from views Create, maintain, and use sequences Create and maintain indexes Create private and public synonyms Managing Objects with Data Dictionary Views ~ Use the data dictionary views to research data on your objects Query various data dictionary views Controlling User Access ~ Differentiate system privileges from object privileges Grant privileges on tables View privileges in the data dictionary Grant roles Distinguish between privileges and roles Managing Schema Objects ~ Add constraints Create indexes Create indexes using the CREATE TABLE statement Creating function-based indexes Drop columns and set column UNUSED Perform FLASHBACK operations Create and use external tables Manipulating Large Data Sets ~ Manipulate data using subqueries Describe the features of multitable INSERTs Use the following types of multitable INSERTs (Unconditional, Conditional and Pivot) Merge rows in a table Track the changes to data over a period of time Generating Reports by Grouping Related Data ~ Use the ROLLUP operation to produce subtotal values Use the CUBE operation to produce crosstabulation values Use the GROUPING function to identify the row values created by ROLLUP or CUBE Use GROUPING SETS to produce a single result set Managing Data in Different Time Zones ~ Use Various datetime functions Retrieving Data Using Subqueries ~ Write a multiple-column subquery Use scalar subqueries in SQL Solve problems with correlated subqueries Update and delete rows using correlated subqueries Use the EXISTS and NOT EXISTS operators Use the WITH clause Hierarchical Retrieval ~ Interpret the concept of a hierarchical query Create a tree-structured report Format hierarchical data Exclude branches from the tree structure Regular Expression Support ~ Using Meta Characters Regular Expression Functions Replacing Patterns Regular Expressions and Check Constraints 1Z0-871 MySQL Dev Part 1 ~ Exam Topics Section 1: Client/Server Concepts (5%) General MySQL Architecture Invoking Client Programs Server SQL Modes Section 2: The mysql Client Program (10%) Using mysql Interactively Statement Terminators The mysql Prompts 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 Section 3: Connectors (5%) MySQL Client Interfaces Connector/ODBC Connector/J Connector/NET Section 4: Data Types (15%) Numeric Data Types The BIT Data Type String Data Types Temporal Data Types Column Attributes Using the AUTO_INCREMENT Column Attribute Handling Missing or Invalid Data Values Section 5: Identifiers (5%) Identifier Syntax Case Sensitivity Using Qualifed Names Reserved Words as Identifiers Section 6: Databases (5%) Database Properties Creating Databases Altering Databases Dropping Databases Obtaining Database Metatdata Section 7: Tables and Indexes (15%) Table Properties Creating Tables Altering Tables Emptying Tables Dropping Tables Indexes Dropping Indexes Obtaining Table and Index Metadata Section 8: Querying for Data (15%) Using SELECT to Retrieve Data Specifying Which Columns to Retrieve Specifying Which Rows to Retrieve Aggregating Results Grouping Results Using UNION Section 9: SQL Expressions (15%) ^ Components of SQL Expressions Numeric Expressions String Expressions Temporal Expressions NULL Values Functions in SQL Expressions Comments in SQL Statements Section 10: Updating Data (10%) Update Operations The INSERT Statement The REPLACE Statement The UPDATE Statement The DELETE and TRUNCATE TABLE Statements Privileges Required for Update Statements ========================================================================