Richard Stibbard's "Practical MySQL"

Course Curriculum

Chapter 1: Preliminaries

Lecture 1: Download the Working Files

Lecture 2: 00-01 System requirements

Lecture 3: 00-02 Introduction

Chapter 2: MySQL Queries and Built-in Functions

Lecture 1: 01-01 Create database, create table

Lecture 2: 01-02 Insert data

Lecture 3: 01-03 Coding style and convention

Lecture 4: 01-04 Import data from SQL file

Lecture 5: 01-05 Select all records from a table, order and limit results

Lecture 6: 01-06 Select records matching search criteria

Lecture 7: 01-07 Revision – Create new table and insert data with SQL query

Lecture 8: 01-08 Numerical comparison operators

Lecture 9: 01-09 MySQL aggregate functions

Lecture 10: 01-10 MySQL ROUND function

Lecture 11: 01-11 MySQL string functions

Lecture 12: 01-12 MySQL aliases

Lecture 13: 01-13 Functions within functions

Lecture 14: 01-14 Update existing records

Lecture 15: 01-15 Update part of existing records

Lecture 16: 01-16 Alter table structure – Add columns

Lecture 17: 01-17 Alter table structure – Change datatype

Lecture 18: 01-18 Export data to SQL file

Lecture 19: 01-19 Delete selected records

Lecture 20: 01-20 Delete (drop) columns

Lecture 21: 01-21 Delete all records from a table

Lecture 22: 01-22 Delete (drop) table and database

Chapter 3: Users and Privileges

Lecture 1: 02-01 Create new MySQL user and assign privileges – localhost

Lecture 2: 02-02 Create new MySQL user and assign privileges – cPanel

Chapter 4: MySQL on the Command Line

Lecture 1: 03-01 Connecting to MySQL at the Windows Command Prompt

Lecture 2: 03-02 Revision – MySQL queries on the command line

Lecture 3: 03-03 Import and Export Data on the MySQL Command Line

Lecture 4: 03-04 Automation of database maintenance tasks in cPanel

Chapter 5: Relational Database Design

Lecture 1: 04-01 The problem with flat files

Lecture 2: 04-02 The solution – linking tables

Lecture 3: 04-03 Assignment 1 – Create linking sales table

Lecture 4: 04-04 Solution to Assignment 1 – Linking sales table

Lecture 5: 04-05 Assignment 2 – Insert data into sales table

Lecture 6: 04-06 Solution to Assignment 2 – Insert sales data

Lecture 7: 04-07 Selecting matching records from multiple tables with WHERE … AND ,..

Lecture 8: 04-08 Selecting matching records from multiple tables with INNER JOIN

Lecture 9: 04-09 JOIN query syntax with table aliases

Lecture 10: 04-10 Selecting matching records with IN

Lecture 11: 04-11 Creating relationships between tables Primary Key/Foreign Key Constraints

Lecture 12: 04-12 Assignment 3 – Create constraint between sales and items tables

Lecture 13: 04-13 Solution to Assignment 3 – Sales items item_id constraint

Lecture 14: 04-14 Constraint action SET NULL

Lecture 15: 04-15 Constraint action RESTRICT or NO ACTION

Lecture 16: 04-16 Set constraint when creating table

Chapter 6: Stored Procedures

Lecture 1: 05-01 Introduction to MySQL stored procedures

Lecture 2: 05-02 Create MySQL stored procedure in SQL input box

Lecture 3: 05-03 Security settings for stored procedures

Lecture 4: 05-04 IN parameters in stored procedures

Lecture 5: 05-05 SQL wildcards in stored procedures

Lecture 6: 05-06 OUT parameters in stored procedures

Lecture 7: 05-07 INOUT parameters in stored procedures

Lecture 8: 05-08 Assignment 4 – Insert, update and delete records in stored procedures

Lecture 9: 05-09 Solutions to Assignment 4 – Insert

Lecture 10: 05-10 Solutions to assignment 4 – Update

Lecture 11: 05-11 Solutions to Assignment 4 – Delete

Chapter 7: Automation with MySQL Triggers

Lecture 1: 06-01 Introduction to MySQL triggers

Lecture 2: 06-02 Trigger after insert

Lecture 3: 06-03 Assignment 5 – Trigger after delete

Lecture 4: 06-04 Solution to Assignment 5

Chapter 8: User-defined Functions

Lecture 1: 07-01 Introduction to user-defined functions

Lecture 2: 07-02 Assignment 6 – Create a User-Defined Function

Lecture 3: 07-03 Solution to Assignment 6 – Create a user-defined function

Chapter 9: MySQL Views

Lecture 1: 08-01-Introduction to MySQL Views

Lecture 2: 08-02 Assignment 7 – Create view to display price with tax

Lecture 3: 08-03 Solution to Assignment 7 – Create view to display price with tax

Lecture 4: 08-04 Create view in dialog box

Chapter 10: Automation with MySQL Events

Lecture 1: 09-01 Introduction to events

Lecture 2: 09-02 Events on the command line

Lecture 3: 09-03 Assignment 8 – Create an event on the command line

Lecture 4: 09-04 Solution to Assignment 8 – Create an event on the command line

Chapter 11: MySQL in PHP

Lecture 1: 10-01 Create project directory

Lecture 2: 10-02 Database connection script with PDO

Lecture 3: 10-03 Controlling error messages with try – catch

Lecture 4: 10-04 Displaying all records in the browser

Lecture 5: 10-05 Create path to include files

Lecture 6: 10-06 Coding a simple search form

Lecture 7: 10-07 Returning the results of a search

Lecture 8: 10-08 Assignment 9 – Search with partial matching

Lecture 9: 10-09 Solution to Assignment 9 – Search with partial matching

Lecture 10: 10-10 Delete data via the browser

Lecture 11: 10-11 Assignment 10 – Insert new record via the browser

Lecture 12: 10-12 Solution to Assignment 10 – Insert new record

Lecture 13: 10-13 Update existing data via the browser (1)

Lecture 14: 10-14 Update existing data via the browser (2)

Lecture 15: 10-15 Update existing data via the browser (3)

Lecture 16: 10-16 Assignment 11 – Update existing data via the browser in one file