Subquery vs CTE vs Views vs Temporary Table

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.

Subquery vs CTE vs Views vs Temporary Table

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