Course: MySQL DML - Querying Data
Course Topics

Note: This information is included in a PDF file in the download package.

Introduction to SELECT syntax

  • Use of SELECT *
  • Use of LIMIT keyword
  • Selecting specific columns
  • Basic troubleshooting
  • Column alias names

Data Analysis 101

  • Tips & Tricks
  • Getting a count of total number of rows in a table
  • SELECT DISTINCT

Row Restriction

  • WHERE CLAUSE
  • Row restriction on numeric, string, and date/time data types
  • Using AND in row restriction
  • We continue to discuss data analysis in context to using row restriction
  • LIKE operator
  • NULL
  • Not operator
  • Row restriction using functions
  • Boolean logic and truth table theory as applied to SQL row restriction
  • Using OR in row restriction
  • IN operator

Query Optimization – Part One

  • Determining if you query is efficient
  • Explain Plans

SQL Functions

Numeric

  • Round()
  • Truncate()
  • Mathematical expressions used in functions

String

  • Case functions - Lower() Upper()
  • Trim()
  • Substring()
  • Concatenation()
  • Length()

Datetime

  • Date()
  • Monthname()
  • Dayname()
  • Month()
  • Date()
  • Now()
  • Year()
  • Adddate()
  • Datediff()
  • Time()
  • Hour()
  • Minute()
  • Second()
  • Addtime()
  • Nesting Functions

CASE

  • Case statement
  • Formatting data
  • Display data from different columns into a single column

Joining Tables

  • Basic Join Syntax
  • Joins and Left Joins
  • E-R Diagrams
  • 1 to 1 Entity Relationships
  • 1 to Many Entity Relationships
  • Many to Many Entity Relationships
  • Orphans
  • Understanding the importance of row count when joining tables
  • Joining multiple foreign keys back to a single entity
  • Determining query efficiency on joined tables

Group By and Aggregate Functions

  • Having
  • Group By
  • Aggregate Functions
  • Sum
  • Min/Max
  • Standard Deviation