Opleiding: Queries with MySQL

MySQL Intro

The course starts with an introduction of relational databases, the installation of MySQL and available tooling such as the MySQL Workbench. Also discussed are the different storage engines and the available connectors.

SQL Standard

Next the SQL Standard is discussed. Step by step the different parts of SQL such as Data Definition Language with CREATE TABLE, Data Manipulation Language with INSERT and UPDATE are treated.

Data Definition

Attention is then paid to the creation and manipulation of database tables with Primary Keys, Auto Increment, Indexes and Constraints on Columns. The relationship between tables with Foreign keys is also treated. And changing the table structure with ALTER TABLE and deleting tables with DROP is on the course program as well.

Data Manipulation

Next Data Manipulation with INSERT and UPDATE Queries is discussed. The course content also deals with passing parameters to queries by means of Prepared Statements.

Select and Grouping

Retrieving data with SELECT queries is a central part of the course program. The various clauses of SELECT such as FROM, WHERE, ORDER BY, BETWEEN, LIKE are covered. Also grouping data by means of GROUP and HAVING clauses is treated.

Functions

MySQL also has many functions that can be applied to perform calculations on data and to filter data. In addition to the standard SQL functions, the many MySQL specific functions are covered.

Join

Next the course program discusses how to combine data from different tables by means of joins. MySQL has many types of joins to combine data from tables and many of them are covered.

Advanced Topics

Finally attention is paid to the application of SET operators such as UNION and INTERSECT and the discussion of transactions with commits and rollbacks and stored procedures.

Audience Course Queries with MySQL

The course Queries with MySQL is designed for developers, database administrators and other interested parties who wish to learn and use MySQL SQL.

Prerequisites Course Queries with MySQL

This course has no specific requirements. General knowledge of system development and databases is beneficial to a good understanding.

Realization Training Queries with MySQL

The theory is treated on the basis of presentation slides. Demos are used to explain the theory. There is ample opportunity to practice. The course times are from 9.30 to 16.30.

Certification Queries with MySQL

Participants receive an official certificate Queries with MySQL after successful completion of the course.

Modules

Module 1 : MySQL Introduction

• MySQL Database
• MySQL Installation
• MySQL versus MariaDB
• MySQL Admin
• Database View
• MySQLWorkBench
• MySQL Storage Engines
• mysql Prompt
• MySQL Notifier
• Available Connectors
• MySQL versions
• MySQL Documentation

Module 2 : SQL Introduction

• SQL Foundations
• SQL Standard
• Relationships
• Primary Keys
• Foreign Keys
• SQL Query Types
• DDL Queries
• DML Queries
• MySQL Data Types
• Numeric Data Types
• String Data Types
• Date and Time Types

Module 3 : Data Definition

• CREATE TABLE
• Unique Constraint
• MySQL Sequences
• Column Attributes
• Auto Increment Columns
• AUTO_INCREMENT attribute
• LAST_INSERT_ID()
• NOT NULL Attribute
• KEY and INDEX
• ALTER TABLE
• DROP COLUMN
• DROP TABLE

Module 4 : Data Manipulation

• Data Manipulation Language
• Transaction Control
• Inserting Rows
• INSERT Statement
• Prepared Statements
• Updating Rows
• UPDATE Statement
• Check Constraint
• DELETE statement
• TRUNCATE TABLE
• Renaming Tables
• Locking Tables

Module 5 : SQL Queries

• Selecting Rows
• SELECT Statement
• FROM Clause
• Specifying Conditions
• WHERE Clause
• Sorting with ORDER BY
• NULLs, FIRST, LAST
• Removing Doubles
• BETWEEN and IN
• ANY Clause
• ALL Clause
• Using LIKE

Module 6 : Grouping

• GROUP BY Clause
• Filtering Groups
• Group by Single Column
• Group By More Columns
• HAVING Clause
• Operators
• String Operators
• Date Operators
• Concatenate Operator
• MySQL IF Statement
• MySQL CASE Statement
• IF versus CASE

Module 7 : Functions

• MySQL Aggregate Functions
• AVG, COUNT, SUM
• MIN, MAX, GROUP_CONCAT
• STD Functions
• STD, STDDEV, STDDEV_POP
• MySQL String Functions
• CONCAT, REPLACE, SUBSTRING
• Control Flow Functions
• Date and Time Functions
• Conversion Functions
• MySQL String Length
• Cast Operation

Module 8 : Joins

• Joining Multiple Tables
• ANSI Join Syntax
• Normal Joins
• Natural Join
• Left Join
• Right Join
• Full Join
• Full Outer Join
• Inner Join
• ANSI Outer Join
• Cross Join
• Multiple Join Conditions

Module 9 : Advanced Topics

• Combining Results
• SET Operatoren
• MySQL UNION
• UNION without Alias
• UNION ALL
• INTERSECT
• Transaction Control
• Commit and Rollback
• Implicit rollbacks
• Implicit commits
• Stored Procedures
• Passing Parameters