SQL Beginner to Guru: MySQL Edition - Master SQL with MySQL

SQL Beginner to Guru: MySQL Edition - Master SQL with MySQL

by John Thompson
Released May 2019
Publisher(s): Packt Publishing
ISBN: 9781838982829

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Video description

SQL skills are in demand. Learn to master SQL using MySQL. Become a SQL Guru today!

About This Video

  • Learn to aggregate and analyze data using SQL functions
  • Get up to speed with SQL using MySQL

In Detail

SQL is the programming language used to get data in and out of databases. Relational databases are extremely popular with companies of all sizes.

Employers are also constantly searching for people with SQL skills, which is ideal if you’re looking to pursue a career in this domain.

In this course, you will learn how to use SQL using the MySQL relational database - one of the most popular databases in the world. You’ll start by installing MySQL workbench on your computer. Next, you’ll understand how to import the famous MySQL employee database into your MySQL database. Gradually, you’ll get hands-on with generating SQL reports, before going on to exploring all about SQL join statements. In addition to this, you’ll create table joins using database tables with millions of records. You’ll discover how easy it is to use SQL to update one record or multiple records in the database. As you progress, you’ll delve into using SQL sub-selects and writing advanced SQL reports against an e-commerce database.

Later, you’ll focus on what database transactions are, and the tools you have to control them with MySQL. The course will then take you through using SQL to create tables in your MySQL database. You’ll also learn the MySQL syntax to alter existing database tables. Finally, you’ll tackle challenging real-world-style assignments for SQL reporting and database maintenance tasks, along with using SQL views for common reports.

By the end of this course, you will be well-versed with SQL and have the skills to use it effectively for your database management needs.

Who this book is for

This course is designed for anyone who wants to learn how to use SQL.

Publisher resources

Download Example Code

Table of contents

  1. Chapter 1 : SQL Beginner to Guru MySQL Edition Introduction
    1. Getting the Most out of Your SQL Course
    2. SQL - Let's write our first SQL!
    3. SQL Fiddle Assignment Review
  2. Chapter 2 : Introduction to SQL
    1. Introduction to SQL
    2. What is a Database?
    3. Relational Database Principles
    4. Assignment: Create Tables and Insert Data
    5. Assignment Review: Create Tables and Insert Data
    6. History of SQL
    7. What is a Relational Database Management System?
    8. History of MySQL
    9. RDBMS Deployment Architectures
    10. Flashcards
    11. Conclusion
  3. Chapter 3 : Installation of MySQL
    1. Introduction
    2. Review of MySQL Installation Options
    3. MySQL Windows 10 Installation
    4. MySQL OSX Installation
    5. MySQL Linux Installation Ubuntu
    6. MySQL Linux Installation CentOS
  4. Chapter 4 : Connecting to MySQL
    1. Introduction
    2. Connecting to MySQL Overview
    3. MySQL Command Line for OSX and Linux
    4. MySQL Command Line for Docker
    5. MySQL Workbench OSX Installation
  5. Chapter 5 : Selecting Data from a Single Table
    1. Introduction
    2. Importing the MySQL Employee Database
    3. Assignment use SQL to get Row Counts
    4. Assignment Review - Use SQL to get Row Counts
    5. Review of the SQL Select Statement
    6. Selecting Specific Columns from Tables
    7. Using Aliases to Name Columns
    8. Using the SQL CONCAT Function to Join Columns
    9. SQL Arithmetic Operators
    10. Using SQL Arithmetic Operators in Selecting Data
    11. SQL Data Types
    12. Formatting Date Values with MySQL
    13. Using the LEFT and RIGHT MySQL Functions
    14. Introduction to the SQL WHERE Clause
    15. SQL WHERE Clause with Equals and Not Equals
    16. Using AND and OR with SQL WHERE Clause
    17. Using IN and NOT IN with with SQL WHERE Clause
    18. Dealing with NULL Values in SQL WHERE Clause
    19. Using Greater Than and Less Than with SQL WHERE Clause
    20. Using LIKE and NOT LIKE with SQL WHERE Clause
    21. Using BETWEEN and NOT BETWEEN with SQL WHERE Clause
    22. Ordering Results with SQL ORDER BY
    23. Limit Results with SQL LIMIT Clause
    24. Using SQL to Select DISTINCT Values
    25. Using SQL Sub-selects with IN in SQL Where Clause
    26. Assignment - Fix SQL Statements
    27. Assignment Review
    28. Conclusion
  6. Chapter 6 : Grouping and Summarizing Data with SQL
    1. Introduction
    2. Using the SQL GROUP By clause
    3. MySQL Aggregate Functions
    4. Using the SQL HAVING Clause
    5. Using the SQL SUM Function
    6. Using the SQL Min and Max Functions
    7. Using the SQL AVG Function
    8. Assignment - Write SQL Report for Salary Information by Year Excluding Managers
    9. Assignment Review
    10. Conclusion
  7. Chapter 7 : Using SQL to Select Data from More Than One Table
    1. Introduction
    2. Overview of SQL Table Joins
    3. Introduction to SQL Inner Joins
    4. Using Table Aliases with SQL Inner Joins
    5. Limiting Columns Displayed with SQL Inner Joins
    6. Using a WHERE Clause with SQL Inner Joins
    7. Assignment - Create Department Employee Report
    8. Assignment Review - Create Department Employee Report
    9. Using SQL Natural Joins
    10. Using SQL Equi-Joins
    11. Using SQL Union Joins
    12. Other MySQL Functions
    13. Using SQL LEFT OUTER JOINS
    14. Assignment - Write SQL LEFT OUTER JOIN
    15. Assignment Review - Write SQL LEFT OUTER JOIN
    16. Conclusion
  8. Chapter 8 : Using SQL to Insert Data
    1. Introduction
    2. Overview of the SQL Insert Statement
    3. Example SQL Insert Statement
    4. Specifying Columns in SQL Inserts
    5. Assignment - Use SQL to create a record in DEPARTMENTS table
    6. Assignment Review - Use SQL to create a record in DEPARTMENTS table
    7. Using a SQL Sub-select with SQL Insert Statements
    8. SQL Core Concept - Foreign Key Constraints
    9. Assignment - Use SQL Inserts to create a new Employee
    10. Assignment Review - Use SQL Inserts to create a new Employee
    11. Conclusion
  9. Chapter 9 : Using SQL to Update Data
    1. Introduction
    2. Overview of the SQL Update Statement
    3. Example SQL Update Statement
    4. Assignment - Use SQL Update Statement to Change Employee Name
    5. Assignment Review - Use SQL Update Statement to Change Employee Name
    6. Assignment - Using SQL Update and Insert to Create a New Salary Record
    7. Assignment Review - Using SQL Update and Insert to Create a New Salary Record
    8. Using SQL to drop the employee database
    9. Assignment - Re-import the employee database
    10. Assignment - Review re-import employee database
    11. Conclusion
  10. Chapter 10 : Using SQL to Delete Data
    1. Introduction
    2. Overview of the SQL DELETE statement
    3. Example of the SQL DELETE Statement
    4. Assignment - Create, Update, and Delete an Employee record
    5. Assignment Review - Create, Update, and Delete an Employee Record
    6. Conclusion
  11. Chapter 11 : Using SQL Transactions
    1. Introduction
    2. Introduction to SQL Transactions
    3. Demonstration of Database Transactions
    4. Assignment - Create Employee Using SQL Transactions
    5. Assignment Review - Create Employee Using SQL Transactions
    6. Demonstration of Database Locks
    7. User Defined Variables in MySQL
    8. Demonstration of Lost SQL Updates
    9. Using SQL Select for Update
    10. Conclusion
  12. Chapter 12 : Advanced SQL Reporting
    1. Introduction
    2. Overview of the Northwind Database
    3. Assignment - Import the Northwind Database
    4. Assignment Review - Import the Northwind Database
    5. Assignment - Create Orders Report
    6. Assignment Review - Create Orders Report
    7. Assignment - Monthly Sales Report by State
    8. Assignment Review - Monthly Sales Report by State
    9. Assignment - Create Monthly Profit Report by Item
    10. Assignment Review - Create Monthly Profit Report by Item
    11. Assignment - Create a Weekly Sales Report by Employee
    12. Assignment Review - Create a Weekly Sales Report by Employee
    13. Weekly Sales Report by Employee Revisited
    14. Conclusion
  13. Chapter 13 : Introduction to DDL Statements - Creating SQL Views
    1. Introduction to Views
    2. Demonstration using SQL DDL Creating a Database View
    3. Assignment - Create Orders View
    4. Assignment Review - Create Orders View
    5. Materialized Views in MySQL
  14. Chapter 14 : Using SQL to Create MySQL Database Tables
    1. Overview of SQL Create Table Syntax
    2. Creating a Table with SQL Select
    3. SQL Truncate Table Command
    4. SQL Drop Table Command
    5. SQL Create Table Example
    6. SQL Create Table Example with Primary Key
    7. SQL Create Table with Auto-Increment Primary Key
    8. SQL Create Table with Foreign Keys
    9. SQL Create Table with Indexes
    10. SQL Create Table with Unique Indexes
    11. Assignment - SQL Create Table
    12. Assignment Review - SQL Create Table
  15. Chapter 15 : Using SQL to Update Tables
    1. Introduction to the SQL Alter Table Command
    2. Using SQL Alter Table Command to Add and Remove a Column
    3. Using SQL Alter Table to Reorder Columns
    4. Using SQL Alter Table Command to Add or Remove an Index
    5. Using SQL Alter Table Command to Add or Remove a Foreign Key Constraint
    6. Assignment - Add ASIN, UPC, and EAN to Book Table using SQL Alter Table Command
    7. Assignment Review - Add ASIN, UPC, EAN to Book Table using SQL Alter Table Cmd
    8. Using SQL Alter Table for MySQL Generated Columns

Product information

  • Title: SQL Beginner to Guru: MySQL Edition - Master SQL with MySQL
  • Author(s): John Thompson
  • Release date: May 2019
  • Publisher(s): Packt Publishing
  • ISBN: 9781838982829