Class Description

Class Overview

This MySQL training course is designed for students new to writing SQL queries using MySQL. The SQL learned in this course is standard to all modern databases, but MySQL will be used in class and syntax and functionality specific to MySQL will be pointed out.

Class Goals

·         Understand how MySQL works

·         Learn to use SQL to output reports with MySQL

·         Learn to write queries getting data from multiple tables.

Call Now +94777337279   Email  This email address is being protected from spambots. You need JavaScript enabled to view it. 

Top of Form

Bottom of Form

Introduction to SQL Training using MySQL 

1.     Relational Database Basics

A.    Brief History of SQL

B.    Relational Databases

    I.        Tables

   II.        Rows

  III.        Columns

 IV.        Relationships

  V.        Datatypes

 VI.        Primary Keys

VII.        Foreign Keys

VIII.        Relational Database Management System

C.    Popular Databases

 .        Commercial Databases

    I.        Popular Open Source Databases

   II.        Valid Object References

D.    SQL Statements

 .        Database Manipulation Language (DML)

    I.        Database Definition Language (DDL)

   II.        Database Control Language (DCL)

2.     Simple SELECTs

 .      Introduction to the Northwind Database

A.    Some Basics

 .        Comments

    I.        Whitespace and Semi-colons

   II.        Case Sensitivity

B.    SELECTing All Columns in All Rows

C.    Exploring the Tables

D.    SELECTing Specific Columns

E.    Sorting Records

 .        Sorting By a Single Column

    I.        Sorting By Multiple Columns

   II.        Sorting By Column Position

  III.        Ascending and Descending Sorts

F.    The WHERE Clause and Operator Symbols

 .        Checking for Equality

    I.        Checking for Inequality

   II.        Checking for Greater or Less Than

  III.        Checking for NULL

 IV.        WHERE and ORDER BY

G.   Using the WHERE clause to check for equality or inequality

H.    Using the WHERE clause to check for greater or less than

I.      Checking for NULL

J.     Using WHERE and ORDER BY Together

K.    The WHERE Clause and Operator Words

 .        The BETWEEN Operator

    I.        The IN Operator

   II.        The LIKE Operator

  III.        The NOT Operator

L.    More SELECTs with WHERE

M.   Checking Multiple Conditions

 .        AND

    I.        OR

   II.        Order of Evaluation

N.    Writing SELECTs with Multiple Conditions

3.     Advanced SELECTs

 .      Calculated Fields

 .        Concatenation

    I.        Mathematical Calculations

   II.        Aliases

A.    Calculating Fields

B.    Aggregate Functions and Grouping

 .        Aggregate Functions

    I.        Grouping Data

   II.        Selecting Distinct Records

C.    Working with Aggregate Functions

D.    Built-in Data Manipulation Functions

 .        Common Math Functions

    I.        Common String Functions

   II.        Common Date Functions

E.    Data Manipulation Functions

4.     Subqueries, Joins and Unions

 .      Subqueries

A.    Joins

 .        Table Aliases

    I.        Multi-table Joins

B.    Using Joins

C.    Outer Joins

D.    Unions

 .        UNION ALL

    I.        UNION Rules

E.    Working with Unions

5.     If time allows, one or more of the following may be covered:

 .      Conditional Processing with CASE

A.    INSERT, UPDATE, DELETE

B.    Student Challenges - Design your own reports

 

Advanced MySQL Queries 

1.     Conditional Processing

A.    The IF Function

B.    The IFNULL Function

C.    The NULLIF Function

D.    The Case Expression

2.     Enhancing Groups With GROUP_CONCAT and ROLLUP

 .      About Group Processing

A.    Simple GROUP BY

B.    GROUP_CONCAT

C.    ROLLUP Function

D.    ROLLUP with IFNULL Function

3.     SQL Functions (Character)

 .      What Are SQL Functions?

A.    Character Functions

B.    Regular Expressions

4.     SQL Functions (Non-Character)

 .      Numeric Functions

A.    Date/Time Functions

5.     The LIMIT clause

 .      Using the LIMIT clause to display the first n records

A.    ORDER BY and LIMIT Clauses in UPDATE

B.    ORDER BY and LIMIT Clauses in DELETE

6.     Retrieving Database Information

 .      The SHOW and DESCRIBE Statements

A.    The INFORMATION_SCHEMA Tables

 

MySQL Development Training 

Class Goals

·         Create views to reuse SELECT statements

·         Learn data retrieval using SELECT statement.

·         Troubleshoot typical warnings and errors.

·         Change or add data.

·         Understand MySQL data validation.

·         Delete data from tables.

·         Generate aggregated query data using various criteria.

·         Connect data from multiple table rows using various types of JOIN constructs.

·         Use several different types of sub-queries.

·         Extensive coverage of MySQL Functions and expressions.

·         Use expressions in SQL statements for more functional and flexible retrieval.

·         Learn to export and import data.

Class Outline

1.     DML and Data Manipulation Language

A.    DDL and DML Overview

B.    Data Values: Numbers

C.    Data Values: Strings

D.    Working with NULL Values

E.    Bulk Loading of Data

F.    Bulk Data Format

G.   Working with Special Values in Bulk

H.    Data

I.      Adding New Table Rows with INSERT

J.     Copying Rows

K.    UPDATE

L.    REPLACE

M.   Removing Table Rows

N.    Transactions

O.   InnoDB: Using Transactional Processing

P.    Locking Tables

2.     Queries and the SELECT Statement

 .      SELECT Syntax Summary

A.    Choosing Data Sources and Destinations

B.    for SELECT

C.    Presentation of Table Data with

D.    SELECT

E.    Being Selective About Which Rows are

F.    Displayed

G.   User-Defined Variables

H.    Expressions and Functions

I.      Control Flow Operators and Functions

J.     Function Names

K.    Comparison Operators and Functions

L.    String Functions

M.   Numeric Operators and Functions

N.    Date and Time Functions

O.   Forcing Data Interpretation

P.    Miscellaneous Functions

3.     Building a Result Set from Several

 .      Sources

A.    UNION

B.    Combining Data from Two Tables

C.    Using WHERE to Choose Matching

D.    Rows

E.    INNER JOIN

F.    OUTER JOINs

G.   Multiple Tables, Fields, Joins, and

H.    Ordering

I.      SELECT * and USING Columns

4.     Advanced SQL Techniques

 .      MySQL Pattern Matching

A.    Multipliers, Anchors, and Grouping

B.    GROUP BY

C.    Aggregates

D.    Subqueries

E.    Subquery Comparisons and Quantifiers

F.    Other Subqueries

G.   Subquery Alternatives and Restrictions

H.    InnoDB Multi-Table Updates and

I.      Deletes

J.     Building a VIEW

K.    Updatable VIEWs

 

SQL Database Definition Language (DDL) Training 

1.     DDL Overview

A.    Principles of Normalization

2.     Creating and Modifying Tables

 .      Data Types

A.    Creating Tables

I.        NULL Values

   II.        Primary Keys

  III.        Foreign Keys

B.    Adding and Dropping Columns

C.    Renaming Tables

 .        SQL Server

    I.        Oracle and MySQL

D.    Dropping Tables

3.     Views

 .      Creating Views

A.    Dropping Views

B.    Benefits of Views

C.    Creating a View

4.     Stored Procedures

 .      Creating Stored Procedures

A.    Dropping Stored Procedures

B.    Creating a Stored Procedure

C.    Benefits of Stored Procedures

 

Relational Database Design and Data Modeling 

1.     Data Modeling Concepts

A.    Introduction to Data Modeling

B.    Relational Database Components

C.    Data and Process Modeling

D.    Organizing Database Project Work

2.     Data Modeling Details

 .      Conceptual Data Modeling

A.    Logical Database Design Using Normalization

B.    Beyond Third Normal Form

C.    Physical Database Design

3.     Design Alternatives

 .      Alternatives for Incorporating Business Rules

A.    Alternatives for Handling Temporal Data

B.    Modeling for Analytical Databases

C.    Enterprise Data Modeling