Writing SQL Queries for MySQL™
Creating reports, analytics, or applications? And need to get data out of MySQL? Learn to write SQL queries like a pro !
Enroll now for $95 USD !Course Summary
Do you need to learn how to write SQL queries for MySQL?
- You know that the information that you need is stored in a MySQL database
- You need to find some data to extract it
- You need to create reports with reporting tools
- You are using analytic tools like Power BI, Tableau, QlikView, Excel, or Access and need to get data from MySQL
- You are building PHP or WordPress applications that depend upon MySQL databases
- You want to learn to write queries properly, using commercial coding standards
- You are new to writing queries, or you are self-taught and want to make sure you are doing it correctly
If so, this course is for you! And as well as detailed instruction, the course also offers optional practical exercises and quizzes to reinforce your learning.
We encourage you to complete the practical exercises. We have tried to make this as easy as possible. We provide video walkthroughs of the setup you need to perform the practical exercises, and they are easy.
Enroll now for $95 USD !
Modules and Lessons
Module 0: Getting started
Who is this course for?
Who is Greg?
What will I learn in this course?
Configuring your lab environment
Module 1: Introduction to querying MySQL
Module introduction
What is MySQL?
What is SQL?
Types of SQL Statements
What are databases?
What are database server instances?
Connecting to MySQL
Connecting to popkornkraze with MySQL Workbench
What is the command line interface?
Finding help and examples for MySQL syntax
Lab
Quiz
Module 2: Querying a single table
Module introduction
What are tables?
Finding out what’s in a table
What are schemas?
What is Popkorn Kraze?
SELECT statements
Using aliases for columns and tables
Quoting (delimiting) names
Using 2-part names
ORDER BY clause
Restricting output with LIMIT and OFFSET
Removing duplicates with DISTINCT
Querying literals, expressions, and functions
Lab
Quiz
Module 3: Filtering the rows to return
Module introduction
Filtering output with the WHERE clause
Finding patterns with LIKE
Logical operations with AND, OR, and NOT
Logical values with the boolean data type
Ranges of values with the BETWEEN operator
Checking lists of values by using the IN operator
Working with NULL values (the lack of data)
What are user-defined functions?
Lab
Quiz
Module 4: Working with queries
Module introduction
Using statement terminators
Working with scripts in MySQL Workbench
Using the snippets tab
Working with query results
Commenting code
Formatting your scripts for readability
Module 5: Querying numbers
Module introduction
What are data types?
Exact whole numbers
Exact decimal numbers
Approximate numbers
Numeric operators
Common numeric functions
Lab
Quiz
Module 6: Querying strings
Module introduction
String data types
Literal string values
String operators
Trimming strings with LTRIM, RTRIM, and TRIM
Changing case with UPPER, LOWER
Extracting parts of strings with LEFT, RIGHT, SUBSTRING
Determining the length of strings
Replacing substrings
Duplicating strings with REPEAT
Finding substrings with POSITION
Extracting delimited string tokens
Lab
Quiz
Module 7: Querying dates and times
Module introduction
Date and time data types
Literal date and time values
Current date and time
Extracting components from date and time values
Adding and subtracting date and time values
Calculating date and time differences
Calculating ages
Get string names for date parts
Calculating beginning and end of month
Constructing dates and times from components
Changing time zone offsets
Formatting date and time values
Lab
Quiz
Module 8: Converting between data types
Module introduction
Replacing NULL values with COALESCE
Replacing NULL values with IFNULL
Returning NULL When Equal with NULLIF
Comparing NULL values
Implicit vs explicit conversions
Converting data types with CAST
CAST target restrictions
Converting data types with CONVERT
Converting character sets with CONVERT
Other common data types
Lab
Quiz
Module 9: Aggregating data
Module introduction
Counting rows and columns with COUNT
Summarizing data with SUM, AVG, MIN, MAX
Summarizing data in sections with GROUP BY
Filtering returned groups with HAVING
Understanding logical query execution order
Numbering rows with window functions
Partitioning ranking and numbering with PARTITION BY
Other interesting aggregates
Lab
Quiz
Module 10: Implementing logic in scripts
Module introduction
Outputting messages with SIGNAL
Using conditional expressions with CASE
Using conditional expressions with IF
Simplifying conditionals with GREATEST, LEAST
Defining variables and assigning values
Querying system functions
Adding conditional logic with IF, ELSE
Creating loops with WHILE
Creating loops with REPEAT
Creating loops with labelled loops
Lab
Quiz
Module 11: Querying multiple tables
Module introduction
Using CROSS JOIN
What is a primary key?
What is a foreign key?
Using INNER JOIN
Using modern join syntax
Using LEFT OUTER JOIN
Other OUTER join types
Joining more than two tables
Joining a table to itself (self join)
Joins without equality (non-equi joins)
Lab
Quiz
Module 12: Applying set operations to tables
Module introduction
Using UNION ALL
Using UNION
Inner LATERAL joins
Outer LATERAL joins
Lab
Quiz
Module 13: Using subqueries
Module introduction
Using subqueries
Using scalar subqueries
Using lists from subqueries
Checking for existence with EXISTS subqueries
Using set-returning subqueries
Simplifying queries with common table expressions (CTEs)
Using multiple CTEs in a single query
Using CTEs for lists of values
Lab
Quiz
Module 14: Working with stored procedures and system schemas
Module introduction
What is a stored procedure?
Executing stored procedures
Executing dynamic SQL statements
MySQL system schemas
Querying the system schemas
Lab
Module 15: Modifying data
Module introduction
Inserting data into a table
Inserting multiple rows at once with VALUES
What are default constraints?
What are auto-insert columns?
Identifying the last values inserted
What is a check constraint?
What is a unique constraint?
Deleting rows from a table
Inserting rows with data from another table
Selecting rows into a new table
Truncating a table vs deleting all rows
Updating data in a table
Upsert data into a table
Lab
Quiz
Module 16: Next steps
Summary and further steps
Enroll now for $95 USD !