MySQL Certified Database Specialist

MySQL Certified Database Specialis

Domain 1: Architecture, Relational Concepts, and Design (15%)

The fundamental theoretical knowledge required before writing any code.

• Relational Database Concepts: Tables, rows (tuples), columns (attributes), and the concept of logical relationships.
• Data Integrity: Definition and application of CONSTRAINTS: PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, and CHECK.
• Modeling and Normalization: Practical understanding of First (1NF), Second (2NF), and Third Normal Form (3NF). Identifying update anomalies.
• MySQL Data Types:
  • Numeric (INT, DECIMAL, FLOAT).
  • String and text (VARCHAR, CHAR, TEXT).
  • Temporal (DATE, DATETIME, TIMESTAMP).
  • Performance impact of selecting the appropriate data type.
• Storage Engines: Key differences between InnoDB(ACID compliance, row-level locking) and MyISAM(fast read-only, non-transactional).

Domain 2: Data Definition Language (DDL) (20%)

Creating and managing the structures that store information.

• Database Management: CREATE DATABASE, ALTER DATABASE, and DROP DATABASE commands. Using the USE statement.
• Table Management:
  • Comprehensive CREATE TABLE syntax.
  • Modifying schemas with ALTER TABLE (adding, modifying, or dropping columns; renaming tables).
  • Safe removal with DROP TABLE and fast clearing with TRUNCATE TABLE.
• View Management: Creating (CREATE VIEW), updating, and dropping views. Understanding views as security and query-simplification mechanisms.
• Basic Indexing: Indexing concepts. Creating (CREATE INDEX) and dropping indexes to optimize search performance.

Domain 3: Data Manipulation Language (DML) and Transactions (20%)

Inserting, modifying, and protecting data in a production environment.

• Data Insertion:
  • INSERT INTO for single and multiple rows.
  • Advanced techniques: INSERT IGNORE and INSERT ... ON DUPLICATE KEY UPDATE.
• Updating and Deleting:
  • Conditional UPDATE and DELETE (critical precautions using the WHERE clause).
  • Using the REPLACE statement.
• Transaction Control Language (TCL):
  • ACID properties (Atomicity, Consistency, Isolation, Durability).
  • Managing changes with START TRANSACTION, COMMIT to save, and ROLLBACK to undo operations.
  • Understanding the autocommit variable.

Domain 4: Data Retrieval and Querying (SELECT) (30%)

The core skill of the specialist: extracting the exact information efficiently.

• Basic Query Structure: Using SELECT, FROM, and table/column aliases (AS).
• Filtering and Operators:
  • The WHERE clause.
  • Comparison (=, <>, >, <) and logical (AND, OR, NOT) operators.
  • Pattern matching with LIKE (wildcards % and _), and identifying ranges and lists (BETWEEN, IN).
  • Handling null values (IS NULL, IS NOT NULL).
• Sorting and Paging: Using ORDER BY (ASC/DESC) and limiting results with LIMIT and OFFSET.
• Table Joins:
  • Theoretical and practical differences between INNER JOIN, LEFT JOIN, and RIGHT JOIN.
  • Using CROSS JOIN and Self Join combinations.

Domain 5: Built-in Functions and Data Grouping (15%)

Data transformation at runtime and information summarization.

• String Functions: CONCAT(), SUBSTRING(), LENGTH(), UPPER(), LOWER(), TRIM().
• Numeric Functions: ROUND(), CEILING(), FLOOR(), ABS().
• Date and Time Functions: NOW(), CURDATE(), DATE_ADD(), DATEDIFF(), EXTRACT().
• Grouping and Aggregation:
  • Using aggregate functions: COUNT(), SUM(), AVG(), MIN(), MAX().
  • Grouping result sets using the GROUP BY clause.
  • Filtering grouped data using the HAVING clause (and understanding its difference from WHERE).