Course Highlights
  • Create, query and update databases
  • Use triggers, views and stored procedures
  • Understand transactions and database isolation levels.
  • Understand database users and security.
Curriculum

1 Topic
Introducing MySQL

11 Topics
Installing MySQL and the Workbench
Using the Command Line
Creating Tables
Multiple Columns int and Data Types
Null Values and Not Null
Mysql Storage Engines and Configuration
SQL Modes
Deleting All the Data in a Table
Primary Keys
Auto Increment
Narrowing Down Select Statements

3 Topics
Exporting and Importing Data
Exporting Using the Command Line
Importing Using the Command Line

6 Topics
Some Test Data
Comparison Operators
Logical Operators
018 Exclusive Or
Exercises Part 1
Solutions Part 1

3 Topics
Beginning Update Statements
Order By
Limiting Results

12 Topics
Mysql Types
Text Types
Floating Point Types and Ints
The Bit Type
Bool
Blobs
Time Date and Year
Timestamp and Datetime
Enumerations
Brackets and Conditions Revisited
Exercises 2
Solutions 2

9 Topics
The distinct keyword
Counting Distinct Values
Aggregate Functions
Arithmetic in MySQL
The Health Survey Dataset
Group By
Having- Restricting Groups By Aggregate Functions
Exercises 3
Solutions 3

15 Topics
Naming Columns and Tables
Foreign Keys
ER Diagrams
Joins and Cartesian Products
Inner Joins
Left and Right Outer Joins
Joins on Multiple Tables
Querying Chains of Tables
One to Many and One to One
Many to Many
Joining Tables to Themselves
Restrict Foreign Keys
Cascade Foreign Keys
Exercises 4
Solutions 4

3 Topics
Union and Union All
Subqueries with "in"
Inline Views

4 Topics
Adding Columns
Adding Foreign Keys
Adding Indexes
Indexes on Multiple Columns

2 Topics
Creating Users
Granting Privileges

3 Topics
Creating a Simple View
View Algorithms
With Check Option

17 Topics
Exclusive Table Locks
Shared Table Locks
Using Variables
Setting Variables with Selects
A Select-Update Example
Fixing Select-Updates with Table Locks
ACID
A Simple Transaction
Start Transaction
InnoDB Row Locking and Isolation
ACID Isolation Levels
Serializable and Row vs Table Locking
Demonstrating the Other Isolation Levels
Rolling Back to Savepoints
The Account Transfer Problem
Select for Update
Lock in Share Mode

8 Topics
String Functions
Dates Intervals and Queries
Date Diff and Dayname
Str_To_Date and Date_Format
Control Flow Functions
Casting
Functions Exercises
Functions Solution

27 Topics
Introducing Stored Procedures
Changing the Delimiter
Hello World
Editing Stored Procedures 2
Setting the Definer
Procedure Permission
Passing Parameters
Multiple Parameters
Select Into Variables
Out Parameters
Inout Parameters
The If Statement
Local Variables
Implementing Account Withdrawal
Transactional Withdrawal
Errors and Warnings
Handling Errors
While Loops
Labelled Loops
Generating Random Data
A Data Generating Procedure
Cursors
Fetching Cursor Data in a Loop
The Case Expression
Variable Scope and Multiple Cursors
Exercises 6
Solutions 6

5 Topics
Introducing Triggers
Validating with Triggers
Triggers and Transactions
Triggers Exercise
Triggers Solution

2 Topics
Functions vs Procedures
User-Defined Functions

3 Topics
Designing Databases Visually
Connecting with Java
Retrieving Data with Java

2 Topics
Final Exercise
Conclusion

  Write a Review

MySQL, SQL and Stored Procedures from Beginner to Advanced

Go to Paid Course