IIRF Online > Development > Data Science > SQL > SQL - MySQL for Data Analytics and Business Intelligence
SQL - MySQL for Data Analytics and Business Intelligence by Udemy
SQL that will get you hired – SQL for Data Science, Data Engineering, Business Analysis, Marketing, and Data Management
Course Highlights
- Become an expert in SQL
- Learn how to code in SQL
- Boost your resume by learning an in-demand skill
- Create, design, and operate with SQL databases
- Start using MySQL – the #1 Database Management System
- Prepare for SQL developer, Database administrator, Business Analyst, and Business Intelligence job opportunities
- Adopt professionally tested SQL best practices
- Gain theoretical insights into relational databases
- Work with a sophisticated real-life database throughout the course
- Get maximum preparation for real-life database management
- Add data analytical tools to your skillset
- Develop business intuition while solving tasks with big data
- Study relational database management theory that you will need in your workplace every day
- Learn how to create a database from scratch
- The ability to take control of your dataset – insert, update, and delete records from your database
- Be confident while working with constraints and relating data tables
- Become a proficient MySQL Workbench user
- Acquire top-notch coding techniques and best practices
- Know how to answer specific business questions by using SQL’s aggregate functions
- Handle complex SQL joins with ease
- Approach more advanced topics in programming like SQL’s triggers, sequences, local and global variables, indexes, and more
- Merge coding skills and business acumen to solve complex analytical problems
- Become a proficient SQL user by writing flawless and efficient queries
- Tons of exercises that will solidify your knowledge
- The freedom to query anything you like from a database
Skills you will learn!
Curriculum
7 Topics
What does the course cover?
Why SQL?
Why SQL?
Why MySQL?
Why MySQL?
Introduction to databases
Introduction to databases
12 Topics
SQL as a declarative language
SQL as a declarative language
Data definition language (DDL)
Data definition language (DDL)
SQL keywords
SQL keywords
Data manipulation language (DML)
Data manipulation language (DML)
Data control language (DCL)
Data control language (DCL)
Transaction control language (TCL)
Transaction control language (TCL)
14 Topics
Relational database essentials
Relational database essentials
Databases vs spreadsheets
Databases vs spreadsheets
Database terminology
Database terminology
Relational schemas - Primary key
Relational schemas - Primary key
Relational schemas - Foreign key
Relational schemas - Foreign key
Relational schemas - Unique key and null values
Relational Schemas - Unique key
Relationships
Relational Schemas – Relationships
8 Topics
Installing MySQL
Additional note – Installing – Visual C
Installing MySQL on macOS and Unix systems
The Client-Server Model
Setting up a connection
Read me!!!
New Authentication Plugin - Creating a New User
Introduction to the MySQL interface
26 Topics
Creating a Database - Part I
SQL files
Creating a Database - Part I - exercise
Creating a Database - Part I - solution
Creating a Database - Part II
Creating a Database - Part II - exercise
Creating a Database - Part II - solution
Introduction to data types
Introduction to data types
String data types
String data types
Integers
Integers
Fixed and floating-point data types
Fixed and floating-point data types
Other useful data types
Other useful data types
Creating a table
Creating a table - exercise
Creating a table - solution
Using databases and tables
Using databases and tables - exercise
Using databases and tables - solution
Additional notes on using tables
Additional notes on using tables
Additional notes on using tables - exercise
17 Topics
PRIMARY KEY constraint
PRIMARY KEY Constraint
PRIMARY KEY constraint - exercise
PRIMARY KEY constraint - solution
FOREIGN KEY constraint - Part I
FOREIGN KEY constraint - Part II
FOREIGN KEY constraint - Part II - exercise
FOREIGN KEY constraint - Part II - solution
UNIQUE Constraint
UNIQUE Constraint - exercise
DEFAULT Constraint
DEFAULT Constraint - exercise
DEFAULT Constraint - solution
NOT NULL Constraint - Part I
NOT NULL Constraint - Part I - exercise
NOT NULL Constraint - Part I - solution
NOT NULL Constraint - Part II
4 Topics
Coding techniques and best practices - Part I
Coding techniques and best practices – Part I
Coding techniques and best practices - Part II
Coding techniques and best practices – Part II
2 Topics
Loading the 'employees' database
Loading the 'employees' database
58 Topics
SELECT - FROM
SELECT - FROM - exercise
SELECT - FROM - solution
WHERE
WHERE - exercise
WHERE - solution
AND
AND - exercise
AND - solution
OR
OR - exercise
OR - solution
Operator precedence
Operator precedence - exercise
Operator precedence - solution
IN - NOT IN
IN - NOT IN - exercise 1
IN - NOT IN - solution 1
IN - NOT IN - exercise 2
IN - NOT IN - solution 2
LIKE - NOT LIKE
LIKE - NOT LIKE - exercise
LIKE - NOT LIKE - solution
Wildcard characters
Wildcard characters - exercise
Wildcard characters - solution
BETWEEN - AND
BETWEEN - AND - exercise
BETWEEN - AND- solution
IS NOT NULL - IS NULL
IS NOT NULL - IS NULL - exercise
IS NOT NULL - IS NULL - solution
Other comparison operators
Other comparison operators - exercise
Other comparison operators - solution
SELECT DISTINCT
SELECT DISTINCT - exercise
SELECT DISTINCT - solution
Introduction to aggregate functions
Introduction to aggregate functions - exercise
Introduction to aggregate functions - solution
ORDER BY
ORDER BY - exercise
ORDER BY - solution
GROUP BY
Using Aliases (AS)
Using Aliases (AS) - exercise
Using Aliases (AS) - solution
HAVING
HAVING - exercise
HAVING - solution
WHERE vs HAVING- Part I
WHERE vs HAVING- Part II
WHERE vs HAVING - Part II - exercise
WHERE vs HAVING - Part II - solution
LIMIT
LIMIT - exercise
LIMIT - solution
9 Topics
The INSERT statement - Part I
The INSERT statement - Part II
The INSERT statement - exercise 1
The INSERT statement - solution 1
The INSERT statement - exercise 2
The INSERT statement - solution 2
Inserting data INTO a new table
Inserting Data INTO a New Table - exercise
Inserting Data INTO a New Table - solution
6 Topics
TCL's COMMIT and ROLLBACK
The UPDATE Statement - Part I
ROLLBACK; ARTICLE
The UPDATE Statement - Part II
The UPDATE Statement – Part II - exercise
The UPDATE Statement – Part II - solution
6 Topics
The DELETE Statement - Part I
The DELETE Statement - Part II
The DELETE Statement – Part II - exercise
The DELETE Statement – Part II - solution
DROP vs TRUNCATE vs DELETE
DROP vs TRUNCATE vs DELETE
23 Topics
COUNT()
COUNT() - exercise
COUNT() - solution
SUM()
SUM() - exercise
SUM() - solution
MIN() and MAX()
MIN() and MAX() - exercise
MIN() and MAX() - solution
AVG()
AVG() - exercise
AVG() - solution
ROUND()
ROUND() - exercise
ROUND() - solution
COALESCE() - Preamble
IFNULL() and COALESCE()
Another Example of Using COALESCE()
Another example of using COALESCE() - exercise 1
Another example of using COALESCE() - solution 1
Another example of using COALESCE() - exercise 2
Another example of using COALESCE() - solution 2
Another example of using COALESCE()
37 Topics
Introduction to JOINs
Intro to JOINs - exercise 1
Intro to JOINs - solution 1
Intro to JOINs - exercise 2
INNER JOIN - Part I
INNER JOIN - Part II
INNER JOIN - Part II - exercise
INNER JOIN - Part II - solution
A Note on Using Joins
Duplicate Records
LEFT JOIN - Part I
LEFT JOIN - Part II
LEFT JOIN - Part II - exercise
LEFT JOIN - Part II - solution
RIGHT JOIN
The new and the old join syntax
The new and the old join syntax - exercise
The new and the old join syntax - solution
JOIN and WHERE Used Together
Important – Prevent Error Code: 1055!
JOIN and WHERE Used Together - exercise
JOIN and WHERE Used Together - solution
CROSS JOIN
CROSS JOIN - exercise 1
CROSS JOIN - solution 1
CROSS JOIN - exercise 2
CROSS JOIN - solution 2
Using Aggregate Functions with Joins
JOIN more than two tables in SQL
Join more than two tables in SQL - exercise
Join more than two tables in SQL - solution
Tips and tricks for joins
Tips and tricks for joins - exercise
Tips and tricks for joins - solution
UNION vs UNION ALL
UNION vs UNION ALL - exercise
UNION vs UNION ALL - solution
11 Topics
SQL Subqueries with IN nested inside WHERE
SQL Subqueries with IN nested inside WHERE - exercise
SQL Subqueries with IN nested inside WHERE - solution
SQL Subqueries with EXISTS-NOT EXISTS nested inside WHERE
SQL Subqueries with EXISTS-NOT EXISTS nested inside WHERE - exercise
SQL Subqueries with EXISTS-NOT EXISTS nested inside WHERE - solution
SQL Subqueries nested in SELECT and FROM
SQL Subqueries nested in SELECT and FROM - exercise 1
SQL Subqueries nested in SELECT and FROM - solution 1
SQL Subqueries nested in SELECT and FROM - exercise 2
SQL Subqueries nested in SELECT and FROM - solution 2
2 Topics
Self Join
Self join
3 Topics
Views
Views - exercise
Views - solution
21 Topics
Introduction to stored routines
Introduction to Stored Routines
The MySQL syntax for stored procedures
Stored procedures - Example - Part I
Stored procedures - Example - Part II
Stored procedures - Example - Part II - exercise
Stored procedures - Example - Part II - solution
Another way to create a procedure
Stored procedures with an input parameter
Stored procedures with an output parameter
Stored procedures with an output parameter - exercise
Stored procedures with an output parameter - solution
Variables
Variables - exercise
Variables - solution
User-defined functions in MySQL
Error Code: 1418.
User-defined functions in MySQL - exercise
User-defined functions in MySQL - solution
Stored routines - conclusion
Stored routines - conclusion
24 Topics
Types of MySQL Variables - Local Variables
Types of MySQL Variables - Local Variables
Session Variables
Session Variables
Global Variables
Global Variables
User-Defined vs System Variables
User-Defined vs System Variables
MySQL Triggers
MySQL Triggers
MySQL Triggers - exercise
MySQL Triggers - solution
MySQL Indexes
MySQL Indexes - exercise 1
MySQL Indexes - solution 1
MySQL Indexes - exercise 2
MySQL Indexes - solution 2
The CASE Statement
The CASE Statement - exercise 1
The CASE Statement - solution 1
The CASE Statement - exercise 2
The CASE Statement - solution 2
The CASE Statement - exercise 3
The CASE Statement - solution 3
28 Topics
Introduction to MySQL Window Functions
The ROW_NUMBER() Ranking Window Function and the Relevant MySQL Syntax
The ROW_NUMBER() Ranking Window Function - Exercises
The ROW_NUMBER() Ranking Window Function - Solution
A Note on Using Several Window Functions in a Query
A Note on Using Several Window Functions - Exercise
A Note on Using Several Window Functions - Solution
MySQL Window Functions Syntax
MySQL Window Functions Syntax - Exercise
MySQL Window Functions Syntax - Solution
The PARTITION BY Clause VS the GROUP BY Clause
The PARTITION BY Clause VS the GROUP BY Clause - Exercise
The PARTITION BY Clause VS the GROUP BY Clause - Solution
The MySQL RANK() and DENSE_RANK() Window Functions
The MySQL RANK() and DENSE_RANK() Window Functions - Exercise
The MySQL RANK() and DENSE_RANK() Window Functions - Solution
Working with MySQL Ranking Window Functions and Joins Together
Working with MySQL Ranking Window Functions and Joins Together - Exercise
Working with MySQL Ranking Window Functions and Joins Together - Solution
The LAG() and LEAD() Value Window Functions
The LAG() and LEAD() Value Window Functions - Exercise
The LAG() and LEAD() Value Window Functions - Solution
MySQL Aggregate Functions in the Context of Window Functions - Part I
MySQL Aggregate Functions in the Context of Window Functions - Part I-Exercise
MySQL Aggregate Functions in the Context of Window Functions - Part I-Solution
MySQL Aggregate Functions in the Context of Window Functions - Part II
MySQL Aggregate Functions in the Context of Window Functions - Part II-Exercise
MySQL Aggregate Functions in the Context of Window Functions - Part II-Solution
9 Topics
MySQL Common Table Expressions - Introduction
An Alternative Solution to the Same Task
An Alternative Solution to the Same Task-Exercise
An Alternative Solution to the Same Task-Solution
Using Multiple Subclauses in a WITH Clause - Part I
Using Multiple Subclauses in a WITH Clause - Part II
Using Multiple Subclauses in a WITH Clause-Exercise
Using Multiple Subclauses in a WITH Clause-Solution
Referring to Common Table Expressions in a WITH Clause
7 Topics
MySQL Temporary Tables - Introduction
MySQL Temporary Tables in Action
MySQL Temporary Tables in Action-Exercise
MySQL Temporary Tables in Action-Solution
Other Features of MySQL Temporary Tables
Other Features of MySQL Temporary Tables-Exercise
Other Features of MySQL Temporary Tables-Solution
5 Topics
The Advantages of Software Integration
Why Combining SQL and Tableau is useful
Installing Tableau Public
Loading the 'employees_mod' Database
Loading the 'employees_mod' Database
8 Topics
Task 1
Task 1 - Text
Important clarification!
Task 1: SQL Solution
Task 1: SQL Solution - Code
Transferring a Dataset from SQL to Tableau
Chart 1: Visualizing and Analyzing the Solution in Tableau - Part I
Chart 1: Visualizing and Analyzing the Solution in Tableau - Part II
5 Topics
Task 2
Task 2 - Text
Task 2: SQL Solution
Task 2: SQL Solution - Code
Chart 2: Visualizing and Analyzing the Solution in Tableau
5 Topics
Task 3
Task 3 - Text
Task 3: SQL Solution
Task 3: SQL Solution - Code
Chart 3: Visualizing and Analyzing the Solution in Tableau
5 Topics
Task 4
Task 4 - Text
Task 4: Solution in SQL
Task 4: Solution in SQL - Code
Chart 4: Visualizing and Analyzing the Solution in Tableau
1 Topic
Task 5: Organizing Charts 1-4 into a Beautiful Dashboard using Tableau
1 Topic
Practice SQL – 10 Final Query Questions
1 Topic
Bonus Lecture: Next Steps
SQL - MySQL for Data Analytics and Business Intelligence
Related SQL Courses
Thank You!
Your review has been submitted successfully.