Sql For Data Analysis: Students & Professionals

The Ultimate Guide to Master SQL for Data Analysis by Solving Real-World Business Problems

What you'll learn

Installing MySQL Server and Workbench for a seamless database environment setup.

Create and manage databases, including creating and dropping databases, managing tables, and verifying data integrity.

Utilize SQL for loading and writing data frames, extracting data, and crafting complex scripts to address business questions

Understand various SQL business use cases and apply best practices to tackle real company data problems

Master SQL joins, including INNER, LEFT, RIGHT, FULL OUTER, and CROSS joins, to combine data from multiple tables.

Implement set operations like UNION, INTERSECT, and EXCEPT to combine results from different queries.

Perform advanced data manipulation with SQL functions and aggregations, such as SUM, AVG, COUNT, GROUP BY, and HAVING.

Develop complex subqueries and utilize Common Table Expressions (CTEs) for improved query readability and performance.

Modify data with SQL commands for inserting, updating, and deleting records.

Handle errors in transactions, implement error handling and ensure data integrity.

Requirements

Operating Systems: 64-bit versions of Microsoft Windows 7, 8.1 and 10 or Mac

MySQL Server and Workbench.

Some fundamental knowledge of data structure concepts will be beneficial.

No prior experience in other tools like R or Python.

Description

Are you interested in becoming a Data Analyst? Do you want to gain practical skills and solve real-world business problems using SQL? Then this is the perfect course for you! Created by a Senior Data Analyst with extensive experience in the Insurance and Health Care sectors, this course will equip you with foundational knowledge and help you master key SQL functions and techniques for data analysis.I will guide you step-by-step into the world of SQL for data analysis. With every lecture and lab exercise, you will develop a comprehensive understanding of these concepts to tackle real data problems! This course is designed to be engaging and practical, with a logical flow through essential modules:Module 1: Creating and Managing DatabasesLearn how to create and drop databases, manage tables, load data into tables, and verify data integrity.Module 2: SELECT StatementsMaster basic SELECT queries, filter data with WHERE clauses, sort results with ORDER BY, and handle NULL values.Module 3: Joins – INNER, LEFT/RIGHT, CROSS, etc.Understand and apply different types of joins: INNER, LEFT, RIGHT and CROSS joins to combine data from multiple tables.Module 4: Set OperatorsUtilize set operators like UNION, UNION ALL, INTERSECT, and EXCEPT to combine results from multiple queries.Module 5: Functions and AggregationsLearn and apply aggregate functions SUM, AVG, COUNT, and perform grouping of data with GROUP BY and HAVING clauses.Module 6: SubqueriesWrite subqueries in SELECT, WHERE, and FROM clauses and use them for complex filtering.Module 7: Common Table Expressions (CTEs)Discover the power of CTEs, use them for recursion, and combine them with joins and subqueries.Module 8: Grouping Sets and Pivot TablesImplement grouping sets, create and use pivot tables, and apply advanced aggregation techniques.Module 9: Modifying DataPerform data modifications with SQL commands for inserting, updating, and deleting recordsModule 10: Programming T-SQLWrite variables/parameters and stored procedures;Module 11: Error HandlingManage errors in SQL, debug and optimize queries, handle exceptions, and ensure data integrity.Module 12: Capstone Projectso Project 1: Airbnb Data Insights: Analyze listing availability, neighborhood popularity, and pricing variations.o Project 2: E-Commerce Sales Analysis: Analyze sales data by province, compare monthly sales, and identify top products.Each module contains independent content, allowing you to start from the beginning or jump into specific topics of interest. However, it is recommended to follow the course from Module 1 to Module 9 to fully prepare for the capstone project challenge.This course is packed with real-world business problems solved during my career as a senior data analyst. You will not only learn theoretical concepts but also gain practical, hands-on experience. Enroll today and take the first step towards mastering data analysis using SQL.

Overview

Section 1: Welcome to the Course

Lecture 1 What You Will Learn: Module 0

Lecture 2 0_1. Lecture 0A: Course Intro

Lecture 3 0_2. Lecture 0B: Download, Install and Launch MySQL

Lecture 4 DOWNLOAD COURSE PACK: Datasets, Coding Exercises, Course Outline and Handout

Lecture 5 0_4. Demo: Overview of Course Folder Structure

Lecture 6 0_5. Demo: Download MySQL and Install MySQL

Section 2: Databases, Data Modeling and Create Database

Lecture 7 What You Will Learn: Module 1

Lecture 8 1_1. Lecture 1A: Databases and Data Modeling

Lecture 9 1_2. Lecture 1B: Data Types and SQL Syntax

Lecture 10 1_3. Lecture 1C: Create Database and ERD Model

Lecture 11 1_4. Lab 1A - Create Database and Load Data

Lecture 12 1_5. Lab 1B - How to Create ERD Data Model

Section 3: SELECT Statements

Lecture 13 What You Will Learn: Module 2

Lecture 14 2_1. Lecture 2: SELECT STATEMENTS

Lecture 15 2_2. Lab 2A - Select Statements: ORDER BY

Lecture 16 2_3. Lab 2B - Select Statements: WHERE

Section 4: Joins: Inner, Outer (Left), Cross, Self and Conditional

Lecture 17 What You Will Learn: Module 3

Lecture 18 3_1. Lecture 3: Join Operations

Lecture 19 3_2. Lab 3A - Join Operations: Inner and Outer (Left)

Lecture 20 3_3. Lab 3B - Join Operations: Cross and Self

Lecture 21 3_4. Lab 3C - Join Operations: Conditional (Temporal)

Section 5: Set Operators

Lecture 22 What You Will Learn: Module 4

Lecture 23 4_1. Lecture 4: Set Operators

Lecture 24 4_2. Lab 4A: Set Operators: Union vs Union All

Lecture 25 4_3. Lab 4B: Set Operators: Intersect

Lecture 26 4_4. Lab 4C: Set Operators: Except

Section 6: Functions and Aggregations

Lecture 27 What You Will Learn: Module 5

Lecture 28 5_1. Lecture 5: Functions & Aggregations Part 1 - Date, String, Logical and Cond

Lecture 29 5_2. Lecture 5: Functions & Aggregations Part 2 - Window Function and Agg.

Lecture 30 5_3. Lab 5A - Functions & Aggregations: Scalar and String

Lecture 31 5_4. Lab 5B - Functions & Aggregations: Logical

Lecture 32 5_5. Lab 5C - Functions & Aggregations: Window

Lecture 33 5_6. Lab 5D - Functions & Aggregations: Group By and Having

Section 7: Sub-Queries

Lecture 34 What You Will Learn: Module 6

Lecture 35 6_1. Lecture 6: Sub-Queries

Lecture 36 6_2. Lab 6A - Sub-Queries: Scalar

Lecture 37 6_3. Lab 6B - Sub-Queries: Correlated

Lecture 38 6_4. Lab 6C - Sub-Queries: Derived Table

Section 8: Views, Temp Tables and CTEs

Lecture 39 What You Will Learn: Module 7

Lecture 40 7_1. Lecture 7: Views, Temp Tables and CTEs

Lecture 41 7_2. Lab 7A - Create Views

Lecture 42 7_3. Lab 7B - Create Temp Tables

Lecture 43 7_4. Lab 7C - Create Common Table Expressions (CTEs)

Section 9: Grouping Sets and Pivot Tables

Lecture 44 What You Will Learn: Module 8

Lecture 45 8_1. Lecture 8: Grouping Sets and Pivot Tables

Lecture 46 8_2. Lab 8A - Grouping Sets: RollUp

Lecture 47 8_3. Lab 8B - Create Pivot Tables

Lecture 48 8_4. Lab 8C - Create Unpivot Tables

Section 10: Modifying Data

Lecture 49 What You Will Learn: Module 9

Lecture 50 9_1. Lecture 9: Modifying Data

Lecture 51 9_2. Lab 9A - Create Backup Tables

Lecture 52 9_3. Lab 9B - Insert Records

Lecture 53 9_4. Lab 9C - Update Records

Lecture 54 9_5. Lab 9D - Delete Records

Section 11: Programming T-SQL

Lecture 55 What You Will Learn: Module 10

Lecture 56 10_1. Lecture 10: Programming T-SQL

Lecture 57 10_2. Lab 10A - Commenting

Lecture 58 10_3. Lab 10B - Variable/Parameters

Lecture 59 10_4. Lab 10C - Stored Procedures

Section 12: Error Handling in SQL

Lecture 60 What You Will Learn: Module 11

Lecture 61 11_1. Lecture 11: Error Handling in SQL

Lecture 62 11_2. Lab 11A - Error Handling

Lecture 63 11_3. Lab 11B - Intro to Transactions

Section 13: Capstone Projects

Lecture 64 Capstone Project 1: OpenAirBnB

Lecture 65 12_2. Capstone Project 1: Setup Database

Lecture 66 12_3. Capstone Project 1: Question 1

Lecture 67 12_4. Capstone Project 1: Question 2

Lecture 68 12_5. Capstone Project 1: Question 3

Lecture 69 12_6. Capstone Project 1: Question 4

Lecture 70 12_7. Capstone Project 1: Question 5

Lecture 71 12_8. Capstone Project 1: Question 6

Lecture 72 12_9. Capstone Project 1: Question 7

Lecture 73 12_10. Capstone Project 1: Question 8

Lecture 74 12_11. Capstone Project 1: Question 9

Lecture 75 12_12. Capstone Project 1: Question 10

Lecture 76 12_13. Capstone Project 1: Question 11

Lecture 77 12_14. Capstone Project 1: Question 12

Lecture 78 12_15. Capstone Project 1: Question 13

Lecture 79 12_16. Capstone Project 1: Question 14

Lecture 80 12_17. Capstone Project 1: Question 15

Lecture 81 12_18. Capstone Project 1: Question 16

Lecture 82 12_19. Capstone Project 1: Question 17

Lecture 83 12_20. Capstone Project 1: Question 18

Lecture 84 Capstone Project 2: E-Commerce (Extra)

Lecture 85 12_21. Capstone Project 2: E-Commerce - Solution (Text Reminder)

Section 14: CONGRATULATIONS!!!

Lecture 86 Course Wrap Up

Lecture 87 Congratulations!!!

Lecture 88 Bonus Lecture

Individuals with no prior experience in data analysis tools.,New graduates considering a data analytics career.,Career switchers aiming to become data analysts or upgrade their skills in SQL and database management.