The MySQL syllabus will cover relational database concepts, SQL querying, data manipulation, and database administration. This curriculum draws heavily from Oracle’s comprehensive SQL certification objectives, recognizing the universal applicability of core SQL principles across relational database systems.
Module 1: Relational Database Concepts & SQL Basics
This module introduces the theoretical and physical aspects of a relational database. It describes the fundamental relationship between a database and SQL, and explains how to relate clauses in SQL SELECT statements to components of an Entity-Relationship Diagram (ERD).
Objective: Students will understand the fundamentals of relational databases and the foundational role of SQL.
Module 2: Retrieving & Manipulating Data (DML)
This module focuses on Data Manipulation Language (DML) operations.
SQL SELECT Statement: Covers the use of the SELECT statement, column aliases, the concatenation operator, literal character strings, the alternative quote operator, and the DISTINCT keyword.
Restricting & Sorting Data: Teaches how to limit rows returned using the WHERE clause, utilize Substitution Variables, and employ DEFINE and VERIFY commands. Students will also learn to sort data using the ORDER BY clause.
Arithmetic & Functions: Explores how to calculate output using arithmetic expressions and handle NULL values in the SELECT statement, along with understanding rules of precedence for operators. Various functions are covered, including character functions to manipulate strings, arithmetic with date data, and the ROUND, TRUNC, MOD, and DATE functions. It also introduces NVL, NULLIF, and COALESCE functions.
Data Type Conversion: Explains implicit and explicit data type conversion.
Managing Tables using DML: Covers INSERT, UPDATE, and DELETE operations, including multi-table inserts and MERGE statements.
Database Transactions: Addresses the management and control of database transactions using COMMIT and ROLLBACK commands.
Objective: Students will master data retrieval, manipulation, and transaction control using SQL, including the application of various functions and operators.
Module 3: Advanced SQL Queries
This module delves into more complex SQL querying techniques.
Displaying Data from Multiple Tables: Covers the use of SELF-JOINS, various types of JOINs (INNER, OUTER, NON EQUIJOINS), and understanding CARTESIAN PRODUCTS.
Using Subqueries: Teaches how to use single-row and multiple-row subqueries, and how to update and delete rows using correlated subqueries.
Using SET Operators: Focuses on matching SELECT statements and using the ORDER BY clause in set operations. It covers the INTERSECT, MINUS, UNION, and UNION ALL operators.
Objective: Students will be able to write complex SQL queries involving multiple tables, subqueries, and set operations.
Module 4: Data Definition Language (DDL) & Database Objects
This module focuses on Data Definition Language (DDL) and the management of database objects.
Managing Tables with DDL: Covers working with tables, columns, and data types, including CREATE TABLE statements, ALTER TABLE (dropping columns, setting UNUSED), and TRUNCATE TABLE.
Temporary & External Tables: Teaches how to create and use temporary and external tables.
Constraints: Focuses on applying and managing various constraints (e.g., PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, CHECK).
Views: Explains how to manage views (creating, updating, deleting) and compares them with materialized views.
Managing Indexes, Synonyms, Sequences: Covers the management of indexes, synonyms, and sequences.
Controlling User Access: Contrasts system privileges and object privileges, and demonstrates how to grant privileges on tables and create roles.
Managing Objects with Data Dictionary Views: Teaches the use of data dictionary views.
Data in Different Time Zones: Explores the use of CURRENT_DATE, CURRENT_TIMESTAMP, LOCALTIMESTAMP, and INTERVAL data types.
Objective: Students will be able to design and manage database schema, control user access, and understand various database objects and their administration.