Course Introduction

• Administration and Course Materials
• Course Structure and Agenda
• Delegate and Trainer Introductions

Session 1: OBTAINING DATABASE METADATA

• What is metadata?
• The mysqlshow utility
• The show and describe commands
• Describing tables
• The information_schema
• Listing tables
• Listing columns
• Listing views
• Listing key_columns_usage
• Exercises:   Obtaining database metadata

Session 2: DEBUGGING

• MySQL error messages
• The show statement
• Show errors
• Show count(*) errors
• Show warnings
• Show count(*) warnings
• Note messages
• The perror utility
• Exercises:   Debugging

Session 3: JOINS

• Overview of inner joins
• Cartesian product
• Inner joins with original syntax
• Non equi-join
• Using table aliases to avoid name clashes
• Inner Joins With ISO/ANSI Syntax
• Outer Joins
• Left outer joins
• Right outer joins
• Full outer joins
• Updating multiple tables simultaneously
• Updating rows in one table based on a condition in another
• Updating rows in one table reading data from another
• Deleting from multiple tables simultaneously
• Deleting rows in one table based on a condition in another
• Exercises:   Coding joins

Session 4: SUBQUERIES

• Types of subquery
• Multiple-column subqueries
• Correlated subqueries
• Using the ANY, ALL and SOME operators
• Using the EXISTS operator
• Subqueries as scalar expressions
• Inline views
• Converting subqueries to joins
• Using subqueries in updates and deletes
• Exercises:   Coding subqueries

Session 5: VIEWS

• Why views are used
• Creating views
• View creation restrictions
• View algorithms
• Updateable views
• Altering and dropping views
• Displaying information about views
• Privileges for views
• Exercises:   Using views

Session 6: IMPORT AND EXPORT

• Exporting using SQL
• Privileges required to export data
• Importing using SQL
• Messages when loading data
• Privileges required to load data
• Exporting from the command line
• Mysqldump main options
• Importing from the command line
• Mysqlimport main options
• Exercises:   Importing and exporting

Session 7: USER VARIABLES AND PREPARED STATEMENTS

• Creating User variables
• User variables in a select
• Prepared statements
• The prepare statement
• The execute statement
• The deallocate statement
• Exercises:   Using variables and prepared statements

Session 8: INTRODUCTION TO STORED ROUTINES

• Types of stored routines
• Benefits of stored routines
• Stored routine features
• Differences between procedures and functions
• Introduction to the Block
• Declaring variables and constants
• Assigning values to variables
• Definer rights and invoker rights
• Using SELECT in stored routines
• Altering and Dropping stored routines
• Obtaining stored routine metadata
• Stored routine privileges and execution security
• Exercises:   Writing simple stored routines

Session 9: STORED ROUTINES - PROGRAM LOGIC

• The IF .. THEN .. ELSEIF construct
• The CASE statement
• The basic loop
• The while loop
• The repeat loop
• The iterate statement
• Nested loops
• Exercises:   Writing stored routines with program logic

Session 10: STORED ROUTINES - EXCEPTION HANDLERS & CURSORS

• Dealing with errors using Exception handlers
• What is a cursor?
• Cursor operations
• Declaring cursors
• Opening and closing cursors
• Fetching rows
• Status checking
• Exercises:   Writing stored routines with program logic

Session 11: PROCEDURES WITH PARAMETERS

• Creating procedures with parameters
• Calling Procedures With Parameters
• Exercises:   Writing stored routines with parameters

Session 12: FUNCTIONS

• What is a function?
• The create function statement
• Executing functions
• Executing functions from code
• Executing functions from SQL statements
• The deterministic and sql clauses
• Exercises:   Writing functions

Session 13: TRIGGERS

• Trigger creation
• Restrictions on triggers
• The create trigger statement
• Using the old and new qualifiers
• Managing triggers
• Destroying triggers
• Required privileges
• Exercises:   Writing triggers

Session 14: THE EVENT SCHEDULER

• Event scheduler concepts
• Event scheduler configuration
• Creating, altering and dropping events
• Event scheduler monitoring
• Events and privileges
• Exercises:   Creating and using events

Session 15: BASIC OPTIMIZATIONS

• Normalisation of data to third normal form
• Using indexes for optimization
• General query enhancement
• Using Explain to analyze queries
• Choosing an INNODB or MYISAM storage enginge
• Exercises:   Making use of basic optimizations

Session 16: MORE ABOUT INDEXES

• Indexes and joins
• Exercises: Investigating indexes and joins