MYSQL for developers

Duration: 5 days

Students should have a basic understanding of database concepts

Please find the course objectives below:

Day 1

Database Overview (Quick)

  • MySQL Overview
  • MySQL Client/Server Model
  • Installation of the MySQL server
  • MySQL Server and Client Startup
  • Basics of Relational Databases
  • Entities and Relationships
  • SQL Language and MySQL
  • SQL data definition language
  • SQL data manipulation language

Database Concepts

  • Introduction to MySQL
  • Relationship
  • E-R Modeling
  • Constraints Types & Use (primary , foreign keys)
  • Overview of SQL and PL/SQL
  • Logging in to MySQL
  • Exiting MySQL
  • Existing utilities (overview)
  • Understand the database object and physical structure

Day 2

Data Retrieval and Ordering the Output

  • Simple data retrieval (SELECT)
  • Describing table structures
  • Conditional retrieval of data using operators (where clause)
  • Sorting on single and multiple columns
  • Group by statements
  • Searching character & data

Day 3

  • Continue from day 2 with practice and scenario based questions

Database Objects

  • Viewing and Evaluating a Database

Table Data Types

  • Data Types as Part of Database Design
  • Numeric Data Types
  • Character String Data Types
  • Binary String Data Types
  • Data Type Considerations
  • The Meaning of NULL

Basic Queries

  • The SELECT Statement with partial / full projection and calculated columns
  • Where clause combinations with logical operators, multiple condition queries
  • Grouping conditions using where clause
  • Data retrieval using sub-queries
  • Types of sub-queries
  • Sub-query operators
  • Understand Union and Union all
  • Troubleshooting
  • MySQL Workbench for SQL Development

Joining Tables

  • Combining Multiple Tables
  • Joining Tables with SELECT
  • Comma-Separated Joins
  • Inner Joins
  • Outer Joins
  • Table Name Aliases
  • Joins & Sub Queries
  • Table joins including ANSI join syntax
  • Grouping information using aggregate functions
  • Using IN, NOT IN, EXISTS and NOT EXISTS, BETWEEN

Table Subqueries

  • Advantages of Using a Subquery
  • Placement of Subqueries
  • Subquery Categories
  • Finding Mismatches
  • Modifying Tables using Subqueries
  • Converting Joins to Subqueries

Day 4

Database and Table Creation

  • Creating a Database
  • Creating a Table
  • Showing How a Table Was Created
  • Column Options
  • Table Options
  • Table Indexing
  • Table Constraints

Database and Table Maintenance

  • Deleting databases and tables
  • Creating a new table using an existing table
  • Confirming the creation of a new table
  • Copying an existing table structure
  • Creating a temporary table
  • Adding, removing and modifying table columns
  • Adding, removing and modifying indexes and constraints

Table Data Manipulation

  • Manipulating Table Row Data
  • The INSERT Statement
  • The REPLACE Statement
  • The UPDATE Statement
  • The DELETE Statement

Functions

  • Built-in Functions
  • Character and date functions
  • Conversion functions
  • Functions in MySQL Expressions
  • Using Functions
  • String Functions
  • Temporal Functions
  • Numeric Functions
  • Control Flow Functions
  • Aggregate Functions
  • Spaces in Function Names

Day 5

Exporting and Importing Data

  • Exporting with a Query
  • Exporting with a MySQL Utility
  • Importing from a Data File
  • Importing with a MySQL Utility

MySQL Graphical User Interface Tools

  • MySQL Workbench
  • MySQL Enterprise Monitor
  • Views
  • Understanding views
  • Creating views
  • Altering and dropping views
  • Manipulating data using views

Supplementary Information

  • Storage Engines
  • Creating Views
  • Transactions
  • Retrieving Metadata
For an onsite course please contact us