Exercise: CTE, Temporary Tables

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.

Exercise: CTE, Temporary Tables

Lecture Content Locked

Get Notified for Next Cohort →

Contents

1: SQL Basics: Complex Queries

1.1: Subqueries

1.2: ANY, ALL Operators

1.3: Co-Related Subquery

1.4: Exercise: Subqueries

1.5: Common Table Expression (CTE)

1.6: CTE Benefits & Other Applications

1.7: Exercise: CTE

1.8: Peter Pandey Flexes his SQL Skills

1.9: Quiz

1.10: Chapter Summary

2: SQL Basics: Database Creation & Updates

2.1: Database Normalization and Data Integrity

2.2: Entity Relationship Diagram (ERD)

2.3: Mentor Talk: Art of Googling

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

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

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

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

2.8: Luck Favors the LinkedIn Post

2.9: Primary key

2.10: Foreign Key

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

2.12: Import Data From a CSV File Into a Database

2.13: Insert Statement

2.14: Update and Delete

2.15: I Need a Favour

2.16: Expect the Unexpected: The Intermission Scene

2.17: Quiz

2.18: Chapter Summary

3: AtliQ Hardware & Problem Statement

3.1: The Rise of Databases at AtliQ

3.2: Relational vs No-SQL Database

3.3: AtliQ Hardware’s Business Model

3.4: Profit & Loss Statement

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

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

3.7: Simplified: What is Kanban?

3.8: Quiz

3.9: Chapter Summary

4: SQL Advanced: Finance Analytics

4.1: Backlog Grooming Meeting: Gross Sales Report

4.2: User-Defined SQL Functions

4.3: Exercise: User-Defined SQL Functions

4.4: Gross Sales Report: Monthly Product Transactions

4.5: Gross Sales Report: Total Sales Amount

4.6: Exercise: Yearly Sales Report

4.7: Stored Procedures: Monthly Gross Sales Report

4.8: Stored Procedure: Market Badge

4.9: Benefits of Stored Procedures

4.10: Quiz

5: SQL Advanced: Top Customers, Products, Markets

5.1: Problem Statement and Pre-Invoice Discount Report

5.2: Performance Improvement # 1

5.3: Performance Improvement # 2

5.4: Database Views: Introduction

5.5: Database Views: Post Invoice Discount, Net Sales

5.6: Exercise: Database Views

5.7: Top Markets and Customers

5.8: Exercise: Top Products

5.9: The Two Most Important Skills for the Data Analyst

5.10: Window Functions: OVER Clause

5.11: Window Functions: Using it in a Task

5.12: Exercise: Window Functions: OVER Clause

5.13: Window Functions: ROW_NUMBER, RANK, DENSE_RANK

5.14: Exercise: Window Functions: ROW_NUMBER, RANK, DENSE_RANK

5.15: 5 Ways SQL is Used in the Industry

5.16: Quiz

6: SQL Advanced: Window Functions

6.1: Window Functions: OVER Clause

6.2: Window Functions: ROW_NUMBER, RANK, DENSE_RANK

6.3: Quiz

7: SQL Advanced: Supply Chain Analytics

7.1: Supply Chain Basics : Simplified

7.2: Problem Statement

7.3: Create a Helper Table

7.4: Database Triggers

7.5: Database Events

7.6: Temporary Tables & Forecast Accuracy Report