Subquery vs CTE vs Views vs Temporary Table

10.1: Final Quiz Mandatory

11.1: Transition to Python & Interviews

Dhaval Patel

Data Entrepreneur | YouTuber | Ex - Bloomberg, Nvidia

I have 17 years of experience in Programming and Data Science working for big tech companies like NVIDIA and Bloomberg. I also run a famous YouTube channel called Codebasics where I pursue my passion for teaching. Codebasics is one of the top channels on YouTube when it comes to data science, machine learning, data structures, etc. I firmly believe that “Anyone can code” and I use analogies, simple explanations, and step-by-step storytelling to explain difficult concepts in such a way that even a high school student can understand them easily.

Hemanand Vadivel

Ex- Data Analytics Manager, 8+ Years in Europe, Microsoft Certified, Certified Supply Chain Professional

I’m a mechanical engineer who transitioned to a full time Data & Analytics manager in the UK & Germany by teaching myself Power BI, excel & anything else that was required to solve the problem. I have worked with complex data across Supply Chain, Sales, Marketing, Revenue Management, Finance and HR functions over the last 8 years to deliver effective solutions. To me, Analytics is an extra sensory organ that anyone can develop to become a superhuman at work. I have conducted several workshops and internal training to help non-technical people to become superhuman at work and now, really excited for this opportunity to share all that knowledge with you in this course.

Contents

1: Welcome to The SQL Experience

1.1: Course Outline

1.2: How Learning SQL can Help You in Your Career

1.3: Who is Peter Pandey?

1.4: Peter Pandey’s Early Life and SQL

2: SQL Basics: Data Retrieval - Single Table

2.1: Install MySQL: Windows

2.2: Install MySQL: Linux, Mac

2.3: Import Movies Dataset in MySQL

2.4: Retrieve Data Using Text Query (SELECT, WHERE, DISTINCT, LIKE)

2.5: Exercise - Retrieve Data Using Text Query (SELECT, WHERE, DISTINCT, LIKE)

2.6: Retrieve Data Using Numeric Query (BETWEEN, IN, ORDER BY, LIMIT, OFFSET)

2.7: Exercise - Retrieve Data Using Numeric Query (BETWEEN, IN, ORDER BY, LIMIT, OFFSET)

2.8: Summary Analytics (MIN, MAX, AVG, GROUP BY)

2.9: Exercise - Summary Analytics (MIN, MAX, AVG, GROUP BY)

2.10: HAVING Clause

2.11: Calculated Columns (IF, CASE, YEAR, CURYEAR)

2.12: Exercise - Calculated Columns (IF, CASE, YEAR, CURYEAR)

2.13: The Data God’s Blessing

2.14: Quiz

2.15: Chapter Summary

3: SQL Basics: Data Retrieval - Multiple Tables

3.1: Why do We Need Multiple Tables?

3.2: SQL Joins (INNER, LEFT, RIGHT, FULL)

3.3: Exercise - SQL Joins (INNER, LEFT, RIGHT, FULL)

3.4: Cross Join

3.5: Analytics on Tables

3.6: Join More Than Two Tables

3.7: Exercise - Join More Than Two Tables

3.8: Quiz

3.9: Chapter Summary

4: SQL Basics: Complex Queries

4.1: Subqueries

4.2: ANY, ALL Operators

4.3: Co-Related Subquery

4.4: Exercise: Subqueries

4.5: Common Table Expression (CTE)

4.6: CTE Benefits & Other Applications

4.7: Exercise: CTE

4.8: Peter Pandey Flexes his SQL Skills

4.9: Quiz

4.10: Chapter Summary

5: SQL Basics: Database Creation & Updates

5.1: Database Normalization and Data Integrity

5.2: Entity Relationship Diagram (ERD)

5.3: Mentor Talk: Art of Googling

5.4: Data Types: Numeric (INT, DECIMAL, FLOAT, DOUBLE)

5.5: Data Types: String (VARCHAR, CHAR, ENUM)

5.6: Data Types: Date, Time (DATETIME, DATE, TIME, YEAR, TIMESTAMP)

5.7: Data Types: JSON, Spatial (JSON, GEOMETRY)

5.8: Luck Favors the LinkedIn Post

5.9: Primary key

5.10: Foreign Key

5.11: Create a Database From an Entity Relationship Diagram - ERD

5.12: Import Data From a CSV File Into a Database

5.13: Insert Statement

5.14: Update and Delete

5.15: I Need a Favour

5.16: Expect the Unexpected: The Intermission Scene

5.17: Quiz

5.18: Chapter Summary

6: AtliQ Hardware & Problem Statement

6.1: The Rise of Databases at AtliQ

6.2: Relational vs No-SQL Database

6.3: AtliQ Hardware’s Business Model

6.4: Profit & Loss Statement

6.5: ETL, Data Warehouse, OLAP vs OLTP, Data Catalog

6.6: Fact vs Dimension Table, Star vs Snowflake Schema, Data Import

6.7: Simplified: What is Kanban?

6.8: Quiz

6.9: Chapter Summary

7: SQL Advanced: Finance Analytics

7.1: Backlog Grooming Meeting: Gross Sales Report

7.2: User-Defined SQL Functions

7.3: Exercise: User-Defined SQL Functions

7.4: Gross Sales Report: Monthly Product Transactions

7.5: Gross Sales Report: Total Sales Amount

7.6: Exercise: Yearly Sales Report

7.7: Stored Procedures: Monthly Gross Sales Report

This lecture is a part of Data Analytics Bootcamp 2.0.
Upgrade now to access enhanced modules.