SQL skills are in demand. Learn to master SQL using MySQL. Become a SQL Guru today!
About This Video
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 …
SQL skills are in demand. Learn to master SQL using MySQL. Become a SQL Guru today!
About This Video
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.
Chapter 1 : 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
Chapter 2 : 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
History of SQL
What is a Relational Database Management System?
History of MySQL
RDBMS Deployment Architectures
Flashcards
Conclusion
Chapter 3 : Installation of MySQL
Introduction
Review of MySQL Installation Options
MySQL Windows 10 Installation
MySQL OSX Installation
MySQL Linux Installation Ubuntu
MySQL Linux Installation CentOS
Chapter 4 : Connecting to MySQL
Introduction
Connecting to MySQL Overview
MySQL Command Line for OSX and Linux
MySQL Command Line for Docker
MySQL Workbench OSX Installation
Chapter 5 : Selecting Data from a Single Table
Introduction
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
SQL Data Types
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
Conclusion
Chapter 6 : Grouping and Summarizing Data with SQL
Introduction
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
Conclusion
Chapter 7 : Using SQL to Select Data from More Than One Table
Introduction
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
Conclusion
Chapter 8 : Using SQL to Insert Data
Introduction
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
Conclusion
Chapter 9 : Using SQL to Update Data
Introduction
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
Conclusion
Chapter 10 : Using SQL to Delete Data
Introduction
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
Conclusion
Chapter 11 : Using SQL Transactions
Introduction
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
Conclusion
Chapter 12 : Advanced SQL Reporting
Introduction
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
Conclusion
Chapter 13 : 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
Chapter 14 : 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
Chapter 15 : 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