The course begins by reviewing the basic SQL queries, DDL and DML operations, data retrieval from multiple tables, and different types of storage engines in databases. It then introduces the aggregate, the index merge, data manipulation, and stored procedures in MySQL. You will learn to write complex queries and get hands-on experience with advanced features such as creating sub programs, data security, triggers, and dynamic SQL. You will also learn a performance tuning strategy, server configuration, loading techniques and the application architecture for efficient database design. This is a hands-on lab-based course designed to help students master MySQL features and tune for performance.
- Review of MYSQL and Oracle database essentials
- Understanding MYSQL storage engines, transactions and features of the database and how it differs from Oracle
- Performing DDL and DML operations using SQL commands
- Retrieving data from multiple tables using JOINS
- Writing complex queries using JOINS, SUBQUERIES and nested SUB QURIES
- MYSQL functions including single-row, multiple-row, group and aggregate functions
- Understanding Oracle and MYSQL optimizer and index merge method
- Developing and managing database stored procedures including best practices
- Exception handling and error handling capabilities in both Oracle and MYSQL
- Utilizing database triggers to automate database operations
- Oracle and MYSQL performance enhancements with queries and indexes
- Database loading techniques and their effects on performance
Skills Needed: Students should have prior knowledge of the installation and basic operation of MySQL.