Data Scholar

SQL Level 2 : Advanced

12 modules146 topics

Start course

Curriculum

M10. Continue Your SQL Journey1 topic
·Module overview
  1. 1.1Resources
M21. Data Modeling11 topics
·Module overview
  1. 2.11.1 Why Data Modeling Matters
  2. 2.21.2 Entities, Attributes and Relationships
  3. 2.31.3 Primary, Foreign, Natural and Surrogate Keys
  4. 2.41.4 One-to-One, One-to-Many and Many-to-Many Relationships
  5. 2.51.5 Junction / Bridge Tables
  6. 2.61.6 Normalization Concepts
  7. 2.71.7 First, Second and Third Normal Form
  8. 2.81.8 Denormalization
  9. 2.91.9 Referential Integrity
  10. 2.101.10 Reading an ER Diagram
  11. 2.111.11 Translating Business Requirements into Tables
M32. Data Warehousing13 topics
·Module overview
  1. 3.12.1 OLTP vs OLAP
  2. 3.22.2 What is a Data Warehouse?
  3. 3.32.3 Fact and Dimension Tables
  4. 3.42.4 Measures and Dimensions
  5. 3.52.5 Grain of a Fact Table
  6. 3.62.6 Star Schema
  7. 3.72.7 Snowflake Schema
  8. 3.82.8 Surrogate vs Natural Keys in Warehousing
  9. 3.92.9 Slowly Changing Dimensions
  10. 3.102.10 SCD Type 1
  11. 3.112.11 SCD Type 2
  12. 3.122.12 Date Dimensions
  13. 3.132.13 Common Fact Table Types
M43. Subqueries11 topics
·Module overview
  1. 4.13.1 What is a Subquery?
  2. 4.23.2 Scalar Subqueries
  3. 4.33.3 Single-Row and Multi-Row Subqueries
  4. 4.43.4 Subqueries in WHERE
  5. 4.53.5 Subqueries in SELECT
  6. 4.63.6 Subqueries in FROM
  7. 4.73.7 IN with Subqueries
  8. 4.83.8 EXISTS and NOT EXISTS
  9. 4.93.9 Correlated Subqueries
  10. 4.103.10 Subqueries vs Joins
  11. 4.113.11 Common Subquery Patterns
M54. Common Table Expressions9 topics
·Module overview
  1. 5.14.1 What is a CTE?
  2. 5.24.2 WITH Syntax
  3. 5.34.3 CTEs vs Subqueries
  4. 5.44.4 Multiple CTEs
  5. 5.54.5 Chaining CTEs
  6. 5.64.6 Using CTEs to Break Down Complex Queries
  7. 5.74.7 Recursive CTEs
  8. 5.84.8 Hierarchical Data with Recursive CTEs
  9. 5.94.9 Common CTE Mistakes
M65. Set Operations10 topics
·Module overview
  1. 6.15.1 Understanding Set Operations
  2. 6.25.2 UNION
  3. 6.35.3 UNION ALL
  4. 6.45.4 UNION vs UNION ALL
  5. 6.55.5 INTERSECT
  6. 6.65.6 EXCEPT / MINUS
  7. 6.75.7 Column Compatibility and Data Types
  8. 6.85.8 Duplicate Handling in Set Operations
  9. 6.95.9 Combining Set Operations
  10. 6.105.10 Set Operations vs Joins
M76. Window Functions15 topics
·Module overview
  1. 7.16.1 What is a Window Function?
  2. 7.26.2 Window Functions vs GROUP BY
  3. 7.36.3 Understanding OVER()
  4. 7.46.4 PARTITION BY
  5. 7.56.5 ORDER BY Inside a Window
  6. 7.66.6 ROW_NUMBER
  7. 7.76.7 RANK
  8. 7.86.8 DENSE_RANK
  9. 7.96.9 ROW_NUMBER vs RANK vs DENSE_RANK
  10. 7.106.10 Aggregate Window Functions
  11. 7.116.11 SUM, AVG, MIN, MAX and COUNT OVER
  12. 7.126.12 Running Totals
  13. 7.136.13 Running Averages
  14. 7.146.14 Percentage of Total
  15. 7.156.15 Ranking Within Groups
M87. Advanced Window Analytics15 topics
·Module overview
  1. 8.17.1 LAG
  2. 8.27.2 LEAD
  3. 8.37.3 FIRST_VALUE
  4. 8.47.4 LAST_VALUE
  5. 8.57.5 Comparing Current and Previous Rows
  6. 8.67.6 Period-over-Period Analysis
  7. 8.77.7 Window Frames
  8. 8.87.8 ROWS BETWEEN
  9. 8.97.9 Moving / Rolling Averages
  10. 8.107.10 Rolling Totals
  11. 8.117.11 Top N per Group
  12. 8.127.12 Deduplication with ROW_NUMBER
  13. 8.137.13 Finding First and Last Events
  14. 8.147.14 Gap Analysis
  15. 8.157.15 Consecutive Events and Streak Analysis
M98. DDL and Database Objects15 topics
·Module overview
  1. 9.18.1 DDL vs DML vs DQL
  2. 9.28.2 CREATE TABLE
  3. 9.38.3 Data Types and Column Definitions
  4. 9.48.4 PRIMARY KEY and FOREIGN KEY Constraints
  5. 9.58.5 NOT NULL
  6. 9.68.6 UNIQUE
  7. 9.78.7 CHECK
  8. 9.88.8 DEFAULT
  9. 9.98.9 ALTER TABLE
  10. 9.108.10 Adding, Modifying and Removing Columns
  11. 9.118.11 DROP vs TRUNCATE vs DELETE
  12. 9.128.12 Creating Views
  13. 9.138.13 Views vs Tables
  14. 9.148.14 Temporary Tables
  15. 9.158.15 Temporary Tables vs CTEs
M109. Advanced Data Manipulation12 topics
·Module overview
  1. 10.19.1 INSERT
  2. 10.29.2 INSERT Using SELECT
  3. 10.39.3 UPDATE
  4. 10.49.4 UPDATE with Conditions
  5. 10.59.5 UPDATE Using Other Tables
  6. 10.69.6 DELETE
  7. 10.79.7 DELETE Using Conditions and Joins
  8. 10.89.8 MERGE / Upsert Concepts
  9. 10.99.9 Conditional INSERT and UPDATE
  10. 10.109.10 Handling Duplicate Records
  11. 10.119.11 Deduplication Patterns
  12. 10.129.12 Safe Data Modification Practices
M1110. Transactions and Concurrency14 topics
·Module overview
  1. 11.110.1 What is a Transaction?
  2. 11.210.2 ACID Properties
  3. 11.310.3 BEGIN / START TRANSACTION
  4. 11.410.4 COMMIT
  5. 11.510.5 ROLLBACK
  6. 11.610.6 SAVEPOINT
  7. 11.710.7 What Happens When Multiple Users Modify Data?
  8. 11.810.8 Locks and Blocking
  9. 11.910.9 Dirty Reads
  10. 11.1010.10 Non-Repeatable Reads
  11. 11.1110.11 Phantom Reads
  12. 11.1210.12 Transaction Isolation Levels
  13. 11.1310.13 Deadlocks
  14. 11.1410.14 Avoiding Common Transaction Problems
M1211. Query Optimizer & Execution Internals20 topics
·Module overview
  1. 12.111.1 What Happens When SQL Executes?
  2. 12.211.2 Parsing, Optimization and Execution
  3. 12.311.3 What is a Query Optimizer?
  4. 12.411.4 Execution Plans
  5. 12.511.5 Reading a Basic Execution Plan
  6. 12.611.6 Table Scan vs Index Scan vs Index Seek
  7. 12.711.7 Introduction to Indexes
  8. 12.811.8 Clustered and Non-Clustered Index Concepts
  9. 12.911.9 How Joins Are Physically Executed
  10. 12.1011.10 Nested Loop Join
  11. 12.1111.11 Hash Join
  12. 12.1211.12 Merge Join
  13. 12.1311.13 Filtering Early and Reducing Data
  14. 12.1411.14 SARGability and Functions on Filter Columns
  15. 12.1511.15 SELECT * and Query Performance
  16. 12.1611.16 EXISTS vs IN vs JOIN from a Performance Perspective
  17. 12.1711.17 CTEs, Subqueries and Temporary Tables from a Performance Perspective
  18. 12.1811.18 Statistics and Cardinality Estimates
  19. 12.1911.19 Common Causes of Slow SQL
  20. 12.2011.20 Practical Query Optimization Workflow