Introduction to SQL Using MySQL

Training Materials

All SQL using MySQL training students receive comprehensive courseware and a related textbook.

Software Requirements

• Modern version of Windows, macOS, or Linux
• MySQL version 5, 8, or above (we will provide detailed installation instructions)
• Local admin privileges
• Related lab files and database content that the students will extract and install at the start of class

Outline

• Relational Database Basics
  • Brief History of SQL
  • Relational Databases
    • Tables
    • Rows
    • Columns
    • Relationships
    • Datatypes
    • Primary Keys
    • Foreign Keys
    • Relational Database Management System
  • Popular Databases
    • Commercial Databases
    • Popular Open Source Databases
    • Valid Object References
  • SQL Statements
    • Database Manipulation Language (DML)
    • Database Definition Language (DDL)
    • Database Control Language (DCL)
• Simple SELECTs
  • Introduction to the Northwind Database
  • Some Basics
    • Comments
    • Whitespace and Semi-colons
    • Case Sensitivity
  • SELECTing All Columns in All Rows
  • Exploring the Tables
  • SELECTing Specific Columns
  • SELECTing Specific Columns
  • Sorting Records
    • Sorting By a Single Column
    • Sorting By Multiple Columns
    • Sorting By Column Position
    • Ascending and Descending Sorts
  • Sorting Results
  • The WHERE Clause and Operator Symbols
    • Checking for Equality
    • Checking for Inequality
    • Checking for Greater or Less Than
    • Checking for NULL
    • WHERE and ORDER BY
  • Using the WHERE clause to check for equality or inequality
  • Using the WHERE clause to check for greater or less than
  • Checking for NULL
  • Using WHERE and ORDER BY Together
  • The WHERE Clause and Operator Words
    • The BETWEEN Operator
    • The IN Operator
    • The LIKE Operator
    • The NOT Operator
  • More SELECTs with WHERE
  • Checking Multiple Conditions
    • AND
    • OR
    • Order of Evaluation
  • Writing SELECTs with Multiple Conditions
• Advanced SELECTs
  • Calculated Fields
    • Concatenation
    • Mathematical Calculations
    • Aliases
  • Calculating Fields
  • Aggregate Functions and Grouping
    • Aggregate Functions
    • Grouping Data
    • Selecting Distinct Records
  • Working with Aggregate Functions
  • Built-in Data Manipulation Functions
    • Common Math Functions
    • Common String Functions
    • Common Date Functions
  • Data Manipulation Functions
• Subqueries, Joins and Unions
  • Subqueries
  • Subqueries
  • Joins
    • Table Aliases
    • Multi-table Joins
  • Using Joins
  • Outer Joins
  • Unions
    • UNION ALL
    • UNION Rules
  • Working with Unions
• Conditional Processing with CASE
  • Using CASE
  • Working with CASE
• Inserting, Updating and Deleting Records
  • INSERT
  • Inserting Records
  • UPDATE
  • DELETE
  • Updating and Deleting Records
• Creating and Modifying Tables
  • Data Types
  • Creating Tables
    • NULL Values
    • Primary Keys
    • Foreign Keys
  • Creating Tables
  • Adding and Dropping Columns
  • Renaming Tables
    • SQL Server
    • Oracle and MySQL
  • Dropping Tables
• Views
  • Creating Views InnoDB
  • Dropping Views
  • Benefits of Views
  • Creating a View
• Conclusion