Description

Develop your SQL skills, with useful insights into the MySQL syntax, SQL join statements, and more

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. All code and supporting files for this course are available at https://github.com/PacktPublishing/SQL-Beginner-to-Guru-MySQL-Edition---Master-SQL-with-MySQL

What You Will Learn

Learn SQL to create reports from realistic MySQL databases
Create your own database through the power of SQL
Understand how to leverage database transactions
Tune SQL statements for optimum performance
Avoid common mistakes with SQL

Audience

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

Approach

This interactive course takes you through SQL in a systematic manner. It is packed with step-by-step instructions and practical examples to help you learn effectively.

Key Features

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

Github Repo

https://github.com/packtpublishing/sql-beginner-to-guru-mysql-edition---master-sql-with-mysql

About the Author

John Thompson

John Thompson has been in the IT industry for over 20 years. Today, he specializes mainly in Java, the most successful and widely known programming language in use today. He also specializes in the Spring Framework, which the most popular open-source application framework for building enterprise-class applications on the Java platform. Just some of the organizations he worked with include Visa, Kohls, Federal Home Loan Bank, and Belk Department Stores. Through the courses he is providing, he hopes to leverage his years of experience to accelerate your learning of the Spring Framework. The Spring Framework is a big framework. The environment for enterprise Java is even bigger. If you're new to Java, you have a lot to learn.

Course Outline

1. SQL Beginner to Guru MySQL Edition Introduction

In this section, the author provides an overview of the course on SQL.

1. Getting the Most out of Your SQL Course

SQL Beginner to Guru MySQL Edition Introduction: Getting the Most out of Your SQL Course

2. SQL - Let's write our first SQL!

SQL Beginner to Guru MySQL Edition Introduction: SQL - Let's write our first SQL!

3. SQL Fiddle Assignment Review

SQL Beginner to Guru MySQL Edition Introduction: SQL Fiddle Assignment Review

2. Introduction to SQL

In this section, we learn about databases, SQL history, RDBMS and flashcards.

1. Introduction to SQL

Introduction to SQL: Introduction to SQL

2. What is a Database?

Introduction to SQL: What is a Database?

3. Relational Database Principles

Introduction to SQL: Relational Database Principles

4. Assignment: Create Tables and Insert Data

Introduction to SQL: Assignment: Create Tables and Insert Data

5. Assignment Review: Create Tables and Insert Data

Introduction to SQL: Assignment Review: Create Tables and Insert Data

6. History of SQL

Introduction to SQL: History of SQL

7. What is a Relational Database Management System?

Introduction to SQL: What is a Relational Database Management System?

8. History of MySQL

Introduction to SQL: History of MySQL

9. RDBMS Deployment Architectures

Introduction to SQL: RDBMS Deployment Architectures

10. Flashcards

Introduction to SQL: Flashcards

11. Conclusion

Introduction to SQL: Conclusion

3. Installation of MySQL

In this section, learn how to install MySQL on your computer.

1. Introduction

Installation of MySQL: Introduction

2. Review of MySQL Installation Options

Installation of MySQL: Review of MySQL Installation Options

3. MySQL Windows 10 Installation

Installation of MySQL: MySQL Windows 10 Installation

4. MySQL OSX Installation

Installation of MySQL: MySQL OSX Installation

5. MySQL Linux Installation Ubuntu

Installation of MySQL: MySQL Linux Installation Ubuntu

6. MySQL Linux Installation CentOS

Installation of MySQL: MySQL Linux Installation CentOS

4. Connecting to MySQL

In this section, learn how to install MySQL Workbench on your computer.

1. Introduction

Connecting to MySQL: Introduction

2. Connecting to MySQL Overview

Connecting to MySQL: Connecting to MySQL Overview

3. MySQL Command Line for OSX and Linux

Connecting to MySQL: MySQL Command Line for OSX and Linux

4. MySQL Command Line for Docker

Connecting to MySQL: MySQL Command Line for Docker

5. MySQL Workbench OSX Installation

Connecting to MySQL: MySQL Workbench OSX Installation

5. Selecting Data from a Single Table

Here, we see how to import the famous MySQL employee database into your MySQL database.

1. Introduction

Selecting Data from a Single Table: Introduction

2. Importing the MySQL Employee Database

Selecting Data from a Single Table: Importing the MySQL Employee Database

3. Assignment use SQL to get Row Counts

Selecting Data from a Single Table: Assignment use SQL to get Row Counts

4. Assignment Review - Use SQL to get Row Counts

Selecting Data from a Single Table: Assignment Review - Use SQL to get Row Counts

5. Review of the SQL Select Statement

Selecting Data from a Single Table: Review of the SQL Select Statement

6. Selecting Specific Columns from Tables

Selecting Data from a Single Table: Selecting Specific Columns from Tables

7. Using Aliases to Name Columns

Selecting Data from a Single Table: Using Aliases to Name Columns

8. Using the SQL CONCAT Function to Join Columns

Selecting Data from a Single Table: Using the SQL CONCAT Function to Join Columns

9. SQL Arithmetic Operators

Selecting Data from a Single Table: SQL Arithmetic Operators

10. Using SQL Arithmetic Operators in Selecting Data

Selecting Data from a Single Table: Using SQL Arithmetic Operators in Selecting Data

11. SQL Data Types

Selecting Data from a Single Table: SQL Data Types

12. Formatting Date Values with MySQL

Selecting Data from a Single Table: Formatting Date Values with MySQL

13. Using the LEFT and RIGHT MySQL Functions

Selecting Data from a Single Table: Using the LEFT and RIGHT MySQL Functions

14. Introduction to the SQL WHERE Clause

Selecting Data from a Single Table: Introduction to the SQL WHERE Clause

15. SQL WHERE Clause with Equals and Not Equals

Selecting Data from a Single Table: SQL WHERE Clause with Equals and Not Equals

16. Using AND and OR with SQL WHERE Clause

Selecting Data from a Single Table: Using AND and OR with SQL WHERE Clause

17. Using IN and NOT IN with with SQL WHERE Clause

Selecting Data from a Single Table: Using IN and NOT IN with with SQL WHERE Clause

18. Dealing with NULL Values in SQL WHERE Clause

Selecting Data from a Single Table: Dealing with NULL Values in SQL WHERE Clause

19. Using Greater Than and Less Than with SQL WHERE Clause

Selecting Data from a Single Table: Using Greater Than and Less Than with SQL WHERE Clause

20. Using LIKE and NOT LIKE with SQL WHERE Clause

Selecting Data from a Single Table: Using LIKE and NOT LIKE with SQL WHERE Clause

21. Using BETWEEN and NOT BETWEEN with SQL WHERE Clause

Selecting Data from a Single Table: Using BETWEEN and NOT BETWEEN with SQL WHERE Clause

22. Ordering Results with SQL ORDER BY

Selecting Data from a Single Table: Ordering Results with SQL ORDER BY

23. Limit Results with SQL LIMIT Clause

Selecting Data from a Single Table: Limit Results with SQL LIMIT Clause

24. Using SQL to Select DISTINCT Values

Selecting Data from a Single Table: Using SQL to Select DISTINCT Values

25. Using SQL Sub-selects with IN in SQL Where Clause

Selecting Data from a Single Table: Using SQL Sub-selects with IN in SQL Where Clause

26. Assignment - Fix SQL Statements

Selecting Data from a Single Table: Assignment - Fix SQL Statements

27. Assignment Review

Selecting Data from a Single Table: Assignment Review

28. Conclusion

Selecting Data from a Single Table: Conclusion

6. Grouping and Summarizing Data with SQL

In this section, get hands on experience generating SQL reports.

1. Introduction

Grouping and Summarizing Data with SQL: Introduction

2. Using the SQL GROUP By clause

Grouping and Summarizing Data with SQL: Using the SQL GROUP By clause

3. MySQL Aggregate Functions

Grouping and Summarizing Data with SQL: MySQL Aggregate Functions

4. Using the SQL HAVING Clause

Grouping and Summarizing Data with SQL: Using the SQL HAVING Clause

5. Using the SQL SUM Function

Grouping and Summarizing Data with SQL: Using the SQL SUM Function

6. Using the SQL Min and Max Functions

Grouping and Summarizing Data with SQL: Using the SQL Min and Max Functions

7. Using the SQL AVG Function

Grouping and Summarizing Data with SQL: Using the SQL AVG Function

8. Assignment - Write SQL Report for Salary Information by Year Excluding Managers

Grouping and Summarizing Data with SQL: Assignment - Write SQL Report for Salary Information by Year Excluding Managers

9. Assignment Review

Grouping and Summarizing Data with SQL: Assignment Review

10. Conclusion

Grouping and Summarizing Data with SQL: Conclusion

7. Using SQL to Select Data from More Than One Table

In this section, master SQL join statements.

1. Introduction

Using SQL to Select Data from More Than One Table: Introduction

2. Overview of SQL Table Joins

Using SQL to Select Data from More Than One Table: Overview of SQL Table Joins

3. Introduction to SQL Inner Joins

Using SQL to Select Data from More Than One Table: Introduction to SQL Inner Joins

4. Using Table Aliases with SQL Inner Joins

Using SQL to Select Data from More Than One Table: Using Table Aliases with SQL Inner Joins

5. Limiting Columns Displayed with SQL Inner Joins

Using SQL to Select Data from More Than One Table: Limiting Columns Displayed with SQL Inner Joins

6. Using a WHERE Clause with SQL Inner Joins

Using SQL to Select Data from More Than One Table: Using a WHERE Clause with SQL Inner Joins

7. Assignment - Create Department Employee Report

Using SQL to Select Data from More Than One Table: Assignment - Create Department Employee Report

8. Assignment Review - Create Department Employee Report

Using SQL to Select Data from More Than One Table: Assignment Review - Create Department Employee Report

9. Using SQL Natural Joins

Using SQL to Select Data from More Than One Table: Using SQL Natural Joins

10. Using SQL Equi-Joins

Using SQL to Select Data from More Than One Table: Using SQL Equi-Joins

11. Using SQL Union Joins

Using SQL to Select Data from More Than One Table: Using SQL Union Joins

12. Other MySQL Functions

Using SQL to Select Data from More Than One Table: Other MySQL Functions

13. Using SQL LEFT OUTER JOINS

Using SQL to Select Data from More Than One Table: Using SQL LEFT OUTER JOINS

14. Assignment - Write SQL LEFT OUTER JOIN

Using SQL to Select Data from More Than One Table: Assignment - Write SQL LEFT OUTER JOIN

15. Assignment Review - Write SQL LEFT OUTER JOIN

Using SQL to Select Data from More Than One Table: Assignment Review - Write SQL LEFT OUTER JOIN

16. Conclusion

Using SQL to Select Data from More Than One Table: Conclusion

8. Using SQL to Insert Data

Here, we create table joins using database tables with MILLIONS of records!

1. Introduction

Using SQL to Insert Data: Introduction

2. Overview of the SQL Insert Statement

Using SQL to Insert Data: Overview of the SQL Insert Statement

3. Example SQL Insert Statement

Using SQL to Insert Data: Example SQL Insert Statement

4. Specifying Columns in SQL Inserts

Using SQL to Insert Data: Specifying Columns in SQL Inserts

5. Assignment - Use SQL to create a record in DEPARTMENTS table

Using SQL to Insert Data: Assignment - Use SQL to create a record in DEPARTMENTS table

6. Assignment Review - Use SQL to create a record in DEPARTMENTS table

Using SQL to Insert Data: Assignment Review - Use SQL to create a record in DEPARTMENTS table

7. Using a SQL Sub-select with SQL Insert Statements

Using SQL to Insert Data: Using a SQL Sub-select with SQL Insert Statements

8. SQL Core Concept - Foreign Key Constraints

Using SQL to Insert Data: SQL Core Concept - Foreign Key Constraints

9. Assignment - Use SQL Inserts to create a new Employee

Using SQL to Insert Data: Assignment - Use SQL Inserts to create a new Employee

10. Assignment Review - Use SQL Inserts to create a new Employee

Using SQL to Insert Data: Assignment Review - Use SQL Inserts to create a new Employee

11. Conclusion

Using SQL to Insert Data: Conclusion

9. Using SQL to Update Data

Here, we see how easy it is to use SQL to update one record or many records in the database.

1. Introduction

Using SQL to Update Data: Introduction

2. Overview of the SQL Update Statement

Using SQL to Update Data: Overview of the SQL Update Statement

3. Example SQL Update Statement

Using SQL to Update Data: Example SQL Update Statement

4. Assignment - Use SQL Update Statement to Change Employee Name

Using SQL to Update Data: Assignment - Use SQL Update Statement to Change Employee Name

5. Assignment Review - Use SQL Update Statement to Change Employee Name

Using SQL to Update Data: Assignment Review - Use SQL Update Statement to Change Employee Name

6. Assignment - Using SQL Update and Insert to Create a New Salary Record

Using SQL to Update Data: 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

Using SQL to Update Data: Assignment Review - Using SQL Update and Insert to Create a New Salary Record

8. Using SQL to drop the employee database

Using SQL to Update Data: Using SQL to drop the employee database

9. Assignment - Re-import the employee database

Using SQL to Update Data: Assignment - Re-import the employee database

10. Assignment - Review re-import employee database

Using SQL to Update Data: Assignment - Review re-import employee database

11. Conclusion

Using SQL to Update Data: Conclusion

10. Using SQL to Delete Data

See how easy it is to use SQL to delete one record or many records in the database.

1. Introduction

Using SQL to Delete Data: Introduction

2. Overview of the SQL DELETE statement

Using SQL to Delete Data: Overview of the SQL DELETE statement

3. Example of the SQL DELETE Statement

Using SQL to Delete Data: Example of the SQL DELETE Statement

4. Assignment - Create, Update, and Delete an Employee record

Using SQL to Delete Data: Assignment - Create, Update, and Delete an Employee record

5. Assignment Review - Create, Update, and Delete an Employee Record

Using SQL to Delete Data: Assignment Review - Create, Update, and Delete an Employee Record

6. Conclusion

Using SQL to Delete Data: Conclusion

11. Using SQL Transactions

In this section, learn to write advanced SQL reports against a ecommerce database.

1. Introduction

Using SQL Transactions: Introduction

2. Introduction to SQL Transactions

Using SQL Transactions: Introduction to SQL Transactions

3. Demonstration of Database Transactions

Using SQL Transactions: Demonstration of Database Transactions

4. Assignment - Create Employee Using SQL Transactions

Using SQL Transactions: Assignment - Create Employee Using SQL Transactions

5. Assignment Review - Create Employee Using SQL Transactions

Using SQL Transactions: Assignment Review - Create Employee Using SQL Transactions

6. Demonstration of Database Locks

Using SQL Transactions: Demonstration of Database Locks

7. User Defined Variables in MySQL

Using SQL Transactions: User Defined Variables in MySQL

8. Demonstration of Lost SQL Updates

Using SQL Transactions: Demonstration of Lost SQL Updates

9. Using SQL Select for Update

Using SQL Transactions: Using SQL Select for Update

10. Conclusion

Using SQL Transactions: Conclusion

12. Advanced SQL Reporting

Understand what database transactions are, and the tools you have to control them with MySQL.

1. Introduction

Advanced SQL Reporting: Introduction

2. Overview of the Northwind Database

Advanced SQL Reporting: Overview of the Northwind Database

3. Assignment - Import the Northwind Database

Advanced SQL Reporting: Assignment - Import the Northwind Database

4. Assignment Review - Import the Northwind Database

Advanced SQL Reporting: Assignment Review - Import the Northwind Database

5. Assignment - Create Orders Report

Advanced SQL Reporting: Assignment - Create Orders Report

6. Assignment Review - Create Orders Report

Advanced SQL Reporting: Assignment Review - Create Orders Report

7. Assignment - Monthly Sales Report by State

Advanced SQL Reporting: Assignment - Monthly Sales Report by State

8. Assignment Review - Monthly Sales Report by State

Advanced SQL Reporting: Assignment Review - Monthly Sales Report by State

9. Assignment - Create Monthly Profit Report by Item

Advanced SQL Reporting: Assignment - Create Monthly Profit Report by Item

10. Assignment Review - Create Monthly Profit Report by Item

Advanced SQL Reporting: Assignment Review - Create Monthly Profit Report by Item

11. Assignment - Create a Weekly Sales Report by Employee

Advanced SQL Reporting: Assignment - Create a Weekly Sales Report by Employee

12. Assignment Review - Create a Weekly Sales Report by Employee

Advanced SQL Reporting: Assignment Review - Create a Weekly Sales Report by Employee

13. Weekly Sales Report by Employee Revisited

Advanced SQL Reporting: Weekly Sales Report by Employee Revisited

14. Conclusion

Advanced SQL Reporting: Conclusion

13. Introduction to DDL Statements - Creating SQL Views

In this section, we look into demonstration using SQL DDL.

1. Introduction to Views

Introduction to DDL Statements - Creating SQL Views: Introduction to Views

2. Demonstration using SQL DDL Creating a Database View

Introduction to DDL Statements - Creating SQL Views: Demonstration using SQL DDL Creating a Database View

3. Assignment - Create Orders View

Introduction to DDL Statements - Creating SQL Views: Assignment - Create Orders View

4. Assignment Review - Create Orders View

Introduction to DDL Statements - Creating SQL Views: Assignment Review - Create Orders View

5. Materialized Views in MySQL

Introduction to DDL Statements - Creating SQL Views: Materialized Views in MySQL

14. Using SQL to Create MySQL Database Tables

In this section, we use SQL to create tables in your MySQL database.

1. Overview of SQL Create Table Syntax

Using SQL to Create MySQL Database Tables: Overview of SQL Create Table Syntax

2. Creating a Table with SQL Select

Using SQL to Create MySQL Database Tables: Creating a Table with SQL Select

3. SQL Truncate Table Command

Using SQL to Create MySQL Database Tables: SQL Truncate Table Command

4. SQL Drop Table Command

Using SQL to Create MySQL Database Tables: SQL Drop Table Command

5. SQL Create Table Example

Using SQL to Create MySQL Database Tables: SQL Create Table Example

6. SQL Create Table Example with Primary Key

Using SQL to Create MySQL Database Tables: SQL Create Table Example with Primary Key

7. SQL Create Table with Auto-Increment Primary Key

Using SQL to Create MySQL Database Tables: SQL Create Table with Auto-Increment Primary Key

8. SQL Create Table with Foreign Keys

Using SQL to Create MySQL Database Tables: SQL Create Table with Foreign Keys

9. SQL Create Table with Indexes

Using SQL to Create MySQL Database Tables: SQL Create Table with Indexes

10. SQL Create Table with Unique Indexes

Using SQL to Create MySQL Database Tables: SQL Create Table with Unique Indexes

11. Assignment - SQL Create Table

Using SQL to Create MySQL Database Tables: Assignment - SQL Create Table

12. Assignment Review - SQL Create Table

Using SQL to Create MySQL Database Tables: Assignment Review - SQL Create Table

15. Using SQL to Update Tables

In this section, we learn the MySQL syntax to alter existing database tables.

1. Introduction to the SQL Alter Table Command

Using SQL to Update Tables: Introduction to the SQL Alter Table Command

2. Using SQL Alter Table Command to Add and Remove a Column

Using SQL to Update Tables: Using SQL Alter Table Command to Add and Remove a Column

3. Using SQL Alter Table to Reorder Columns

Using SQL to Update Tables: Using SQL Alter Table to Reorder Columns

4. Using SQL Alter Table Command to Add or Remove an Index

Using SQL to Update Tables: 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

Using SQL to Update Tables: 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

Using SQL to Update Tables: 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

Using SQL to Update Tables: Assignment Review - Add ASIN, UPC, EAN to Book Table using SQL Alter Table Cmd

8. Using SQL Alter Table for MySQL Generated Columns

Using SQL to Update Tables: Using SQL Alter Table for MySQL Generated Columns

Course Images

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

By Packt

Booking options

Highlights

Description

Course Content

About The Provider

Tags

Reviews