SQL Beginner to Guru: MySQL Edition - Master SQL with MySQL [Video]

Book Image

SQL Beginner to Guru: MySQL Edition - Master SQL with MySQL [Video]

By : John Thompson

5 (1)

Book Image

SQL Beginner to Guru: MySQL Edition - Master SQL with MySQL [Video]

5 (1)

By: John Thompson

Overview of this book

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

Free Chapter

SQL Beginner to Guru MySQL Edition Introduction

Getting the Most out of Your SQL Course

SQL - Let's write our first SQL!

SQL Fiddle Assignment Review

Introduction to SQL

Introduction to SQL

What is a Database?

Relational Database Principles

Assignment: Create Tables and Insert Data

Assignment Review: Create Tables and Insert Data

What is a Relational Database Management System?

History of MySQL

RDBMS Deployment Architectures

Installation of MySQL

Review of MySQL Installation Options

MySQL Windows 10 Installation

MySQL OSX Installation

MySQL Linux Installation Ubuntu

MySQL Linux Installation CentOS

Connecting to MySQL

Connecting to MySQL Overview

MySQL Command Line for OSX and Linux

MySQL Command Line for Docker

MySQL Workbench OSX Installation

Selecting Data from a Single Table

Importing the MySQL Employee Database

Assignment use SQL to get Row Counts

Assignment Review - Use SQL to get Row Counts

Review of the SQL Select Statement

Selecting Specific Columns from Tables

Using Aliases to Name Columns

Using the SQL CONCAT Function to Join Columns

SQL Arithmetic Operators

Using SQL Arithmetic Operators in Selecting Data

Formatting Date Values with MySQL

Using the LEFT and RIGHT MySQL Functions

Introduction to the SQL WHERE Clause

SQL WHERE Clause with Equals and Not Equals

Using AND and OR with SQL WHERE Clause

Using IN and NOT IN with with SQL WHERE Clause

Dealing with NULL Values in SQL WHERE Clause

Using Greater Than and Less Than with SQL WHERE Clause

Using LIKE and NOT LIKE with SQL WHERE Clause

Using BETWEEN and NOT BETWEEN with SQL WHERE Clause

Ordering Results with SQL ORDER BY

Limit Results with SQL LIMIT Clause

Using SQL to Select DISTINCT Values

Using SQL Sub-selects with IN in SQL Where Clause

Assignment - Fix SQL Statements

Assignment Review

Grouping and Summarizing Data with SQL

Using the SQL GROUP By clause

MySQL Aggregate Functions

Using the SQL HAVING Clause

Using the SQL SUM Function

Using the SQL Min and Max Functions

Using the SQL AVG Function

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

Assignment Review

Using SQL to Select Data from More Than One Table

Overview of SQL Table Joins

Introduction to SQL Inner Joins

Using Table Aliases with SQL Inner Joins

Limiting Columns Displayed with SQL Inner Joins

Using a WHERE Clause with SQL Inner Joins

Assignment - Create Department Employee Report

Assignment Review - Create Department Employee Report

Using SQL Natural Joins

Using SQL Equi-Joins

Using SQL Union Joins

Other MySQL Functions

Using SQL LEFT OUTER JOINS

Assignment - Write SQL LEFT OUTER JOIN

Assignment Review - Write SQL LEFT OUTER JOIN

Using SQL to Insert Data

Overview of the SQL Insert Statement

Example SQL Insert Statement

Specifying Columns in SQL Inserts

Assignment - Use SQL to create a record in DEPARTMENTS table

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

Using a SQL Sub-select with SQL Insert Statements

SQL Core Concept - Foreign Key Constraints

Assignment - Use SQL Inserts to create a new Employee

Assignment Review - Use SQL Inserts to create a new Employee

Using SQL to Update Data

Overview of the SQL Update Statement

Example SQL Update Statement

Assignment - Use SQL Update Statement to Change Employee Name

Assignment Review - Use SQL Update Statement to Change Employee Name

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

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

Using SQL to drop the employee database

Assignment - Re-import the employee database

Assignment - Review re-import employee database

Using SQL to Delete Data

Overview of the SQL DELETE statement

Example of the SQL DELETE Statement

Assignment - Create, Update, and Delete an Employee record

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

Using SQL Transactions

Introduction to SQL Transactions

Demonstration of Database Transactions

Assignment - Create Employee Using SQL Transactions

Assignment Review - Create Employee Using SQL Transactions

Demonstration of Database Locks

User Defined Variables in MySQL

Demonstration of Lost SQL Updates

Using SQL Select for Update

Advanced SQL Reporting

Overview of the Northwind Database

Assignment - Import the Northwind Database

Assignment Review - Import the Northwind Database

Assignment - Create Orders Report

Assignment Review - Create Orders Report

Assignment - Monthly Sales Report by State

Assignment Review - Monthly Sales Report by State

Assignment - Create Monthly Profit Report by Item

Assignment Review - Create Monthly Profit Report by Item

Assignment - Create a Weekly Sales Report by Employee

Assignment Review - Create a Weekly Sales Report by Employee

Weekly Sales Report by Employee Revisited

Introduction to DDL Statements - Creating SQL Views

Introduction to Views

Demonstration using SQL DDL Creating a Database View

Assignment - Create Orders View

Assignment Review - Create Orders View

Materialized Views in MySQL

Using SQL to Create MySQL Database Tables

Overview of SQL Create Table Syntax

Creating a Table with SQL Select

SQL Truncate Table Command

SQL Drop Table Command

SQL Create Table Example

SQL Create Table Example with Primary Key

SQL Create Table with Auto-Increment Primary Key

SQL Create Table with Foreign Keys

SQL Create Table with Indexes

SQL Create Table with Unique Indexes

Assignment - SQL Create Table

Assignment Review - SQL Create Table

Using SQL to Update Tables

Introduction to the SQL Alter Table Command

Using SQL Alter Table Command to Add and Remove a Column

Using SQL Alter Table to Reorder Columns

Using SQL Alter Table Command to Add or Remove an Index

Using SQL Alter Table Command to Add or Remove a Foreign Key Constraint

Assignment - Add ASIN, UPC, and EAN to Book Table using SQL Alter Table Command

Assignment Review - Add ASIN, UPC, EAN to Book Table using SQL Alter Table Cmd

Using SQL Alter Table for MySQL Generated Columns

Customer Reviews

5 (1)

5 star

100%

4 star

0

3 star

0

2 star

0

1 star

0

Chapter 1

SQL Beginner to Guru MySQL Edition Introduction

Section 1

Getting the Most out of Your SQL Course

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