Skip to content

SQL_LEVELS_PLAN.md

Latest commit

 

History

History
331 lines (269 loc) · 12.1 KB

SQL_LEVELS_PLAN.md

File metadata and controls

331 lines (269 loc) · 12.1 KB

SQL Interactive Coding Levels Plan

Overview

Complete SQL course with 45 levels, progressing from database basics to advanced query optimization and database administration. Each level includes detailed explanations, practical exercises, and real-world database scenarios.

Level Structure

  • Database Fundamentals (Levels 1-10): Basic concepts, simple queries
  • Intermediate SQL (Levels 11-25): Joins, functions, subqueries
  • Advanced SQL (Levels 26-35): Complex queries, optimization, procedures
  • Database Administration (Levels 36-45): Design, security, performance

DATABASE FUNDAMENTALS (1-10)

Level 1: Database Introduction

  • Title: "Welcome to Databases"
  • Exercises: 3
  • Topics: What are databases, tables, rows, columns
  • Key Concepts: Database concepts, relational model, data organization

Level 2: SQL Basics

  • Title: "Structured Query Language"
  • Exercises: 3
  • Topics: SQL syntax, statement types, basic commands
  • Key Concepts: SQL fundamentals, query structure, command categories

Level 3: SELECT Statement

  • Title: "Retrieving Data"
  • Exercises: 5
  • Topics: SELECT syntax, column selection, basic queries
  • Key Concepts: Data retrieval, column specification, result sets

Level 4: WHERE Clause

  • Title: "Filtering Data"
  • Exercises: 5
  • Topics: WHERE conditions, comparison operators, logical operators
  • Key Concepts: Data filtering, conditional logic, query refinement

Level 5: Sorting and Limiting

  • Title: "Organizing Results"
  • Exercises: 4
  • Topics: ORDER BY, ASC/DESC, LIMIT, TOP
  • Key Concepts: Result ordering, data pagination, result limiting

Level 6: Working with Text

  • Title: "String Operations"
  • Exercises: 5
  • Topics: LIKE operator, wildcards, string functions
  • Key Concepts: Pattern matching, text searching, string manipulation

Level 7: Numeric Operations

  • Title: "Mathematical Calculations"
  • Exercises: 4
  • Topics: Arithmetic operators, numeric functions, calculations
  • Key Concepts: Mathematical operations, numeric data types

Level 8: Date and Time

  • Title: "Temporal Data Handling"
  • Exercises: 5
  • Topics: Date functions, date arithmetic, formatting
  • Key Concepts: Date operations, temporal calculations, date formatting

Level 9: NULL Values

  • Title: "Handling Missing Data"
  • Exercises: 4
  • Topics: NULL concept, IS NULL, IS NOT NULL, COALESCE
  • Key Concepts: Missing data, NULL handling, data completeness

Level 10: Basic Aggregation

  • Title: "Summary Statistics"
  • Exercises: 4
  • Topics: COUNT, SUM, AVG, MIN, MAX functions
  • Key Concepts: Data aggregation, statistical functions, summary data

INTERMEDIATE SQL (11-25)

Level 11: GROUP BY Clause

  • Title: "Grouping Data"
  • Exercises: 5
  • Topics: GROUP BY syntax, grouping concepts, aggregate with groups
  • Key Concepts: Data grouping, categorical analysis, group aggregation

Level 12: HAVING Clause

  • Title: "Filtering Groups"
  • Exercises: 4
  • Topics: HAVING vs WHERE, group filtering, aggregate conditions
  • Key Concepts: Group filtering, aggregate conditions, query optimization

Level 13: Multiple Tables Introduction

  • Title: "Relational Data Concepts"
  • Exercises: 3
  • Topics: Table relationships, foreign keys, normalization basics
  • Key Concepts: Relational design, data relationships, referential integrity

Level 14: INNER JOIN

  • Title: "Combining Related Data"
  • Exercises: 5
  • Topics: INNER JOIN syntax, join conditions, multi-table queries
  • Key Concepts: Table joining, relationship navigation, data combination

Level 15: LEFT and RIGHT JOIN

  • Title: "Outer Joins"
  • Exercises: 5
  • Topics: LEFT JOIN, RIGHT JOIN, preserving unmatched rows
  • Key Concepts: Outer joins, data preservation, optional relationships

Level 16: FULL OUTER JOIN

  • Title: "Complete Data Combination"
  • Exercises: 4
  • Topics: FULL OUTER JOIN, union of all data, comprehensive joining
  • Key Concepts: Complete data sets, comprehensive analysis

Level 17: Self Joins

  • Title: "Joining Tables to Themselves"
  • Exercises: 4
  • Topics: Self-join concepts, hierarchical data, recursive relationships
  • Key Concepts: Self-referencing, hierarchical structures, recursive queries

Level 18: Multiple Joins

  • Title: "Complex Multi-Table Queries"
  • Exercises: 5
  • Topics: Joining multiple tables, join order, performance considerations
  • Key Concepts: Complex relationships, query planning, join optimization

Level 19: Subqueries Introduction

  • Title: "Queries Within Queries"
  • Exercises: 5
  • Topics: Subquery concepts, WHERE subqueries, scalar subqueries
  • Key Concepts: Nested queries, query composition, data filtering

Level 20: Correlated Subqueries

  • Title: "Dynamic Subqueries"
  • Exercises: 4
  • Topics: Correlated subqueries, EXISTS operator, dynamic filtering
  • Key Concepts: Dynamic queries, row-by-row processing, existence checking

Level 21: UNION Operations

  • Title: "Combining Query Results"
  • Exercises: 4
  • Topics: UNION, UNION ALL, combining result sets
  • Key Concepts: Result combination, set operations, data consolidation

Level 22: Advanced Functions

  • Title: "Built-in SQL Functions"
  • Exercises: 5
  • Topics: String functions, date functions, mathematical functions
  • Key Concepts: Function usage, data transformation, built-in capabilities

Level 23: CASE Statements

  • Title: "Conditional Logic in SQL"
  • Exercises: 5
  • Topics: CASE expressions, conditional columns, data transformation
  • Key Concepts: Conditional logic, data categorization, dynamic columns

Level 24: Window Functions Introduction

  • Title: "Analytical Functions"
  • Exercises: 5
  • Topics: ROW_NUMBER, RANK, window function concepts
  • Key Concepts: Analytical processing, ranking, window operations

Level 25: Advanced Window Functions

  • Title: "Complex Analytical Queries"
  • Exercises: 5
  • Topics: LAG, LEAD, running totals, moving averages
  • Key Concepts: Time series analysis, comparative analysis, running calculations

ADVANCED SQL (26-35)

Level 26: Common Table Expressions (CTEs)

  • Title: "Temporary Named Result Sets"
  • Exercises: 5
  • Topics: WITH clause, CTE syntax, recursive CTEs
  • Key Concepts: Query organization, temporary results, recursive processing

Level 27: Advanced Subqueries

  • Title: "Complex Nested Queries"
  • Exercises: 5
  • Topics: Multiple subqueries, subquery optimization, performance tuning
  • Key Concepts: Query complexity, optimization strategies, performance

Level 28: Data Modification

  • Title: "INSERT, UPDATE, DELETE"
  • Exercises: 5
  • Topics: Data insertion, updates, deletions, transaction concepts
  • Key Concepts: Data manipulation, transaction safety, data integrity

Level 29: Advanced Joins

  • Title: "Complex Join Patterns"
  • Exercises: 4
  • Topics: Cross joins, natural joins, join algorithms
  • Key Concepts: Join varieties, performance implications, algorithm understanding

Level 30: Indexes and Performance

  • Title: "Query Optimization Basics"
  • Exercises: 4
  • Topics: Index concepts, query execution plans, performance tuning
  • Key Concepts: Performance optimization, index usage, execution planning

Level 31: Query Reusability (SQLite-Adapted)

  • Title: "Reusable SQL Patterns"
  • Exercises: 4
  • Topics: Views, CTEs for reusability, temporary tables, query patterns
  • Key Concepts: Code reusability, query organization, modular SQL
  • Note: SQLite alternative to stored procedures

Level 32: Constraints and Validation (SQLite-Adapted)

  • Title: "Data Integrity Rules"
  • Exercises: 4
  • Topics: CHECK constraints, FOREIGN KEY, UNIQUE, DEFAULT constraints
  • Key Concepts: Data validation, integrity rules, constraint types
  • Note: SQLite alternative to triggers for data validation

Level 33: Transaction Management

  • Title: "Data Consistency and ACID"
  • Exercises: 4
  • Topics: Transactions, COMMIT, ROLLBACK, isolation levels
  • Key Concepts: Data consistency, transaction properties, concurrency control

Level 34: Advanced Aggregation (SQLite-Adapted)

  • Title: "Complex Statistical Analysis"
  • Exercises: 5
  • Topics: Multiple aggregations, conditional aggregation, statistical functions, nested aggregations
  • Key Concepts: Multi-dimensional analysis, CASE with aggregates, pivot-style reporting
  • Note: SQLite doesn't support ROLLUP/CUBE, using alternative techniques

Level 35: Query Optimization Techniques

  • Title: "Performance Tuning Mastery"
  • Exercises: 5
  • Topics: Query analysis, index strategies, query rewriting, EXPLAIN QUERY PLAN
  • Key Concepts: Performance analysis, optimization strategies, efficient queries

DATABASE DESIGN & ADVANCED TOPICS (36-40)

Level 36: Database Design Principles

  • Title: "Relational Database Design"
  • Exercises: 5
  • Topics: Normalization (1NF, 2NF, 3NF), entity-relationship modeling, design patterns
  • Key Concepts: Database design, normalization forms, design methodology, denormalization

Level 37: Table Creation & DDL

  • Title: "Database Structure Definition"
  • Exercises: 5
  • Topics: CREATE TABLE, ALTER TABLE, DROP TABLE, constraints, schema modification
  • Key Concepts: Schema definition, DDL operations, table management

Level 38: Data Types & Storage

  • Title: "Understanding Data Types"
  • Exercises: 5
  • Topics: SQLite data types, type affinity, storage classes, BLOB, JSON
  • Key Concepts: Type system, storage optimization, data type selection

Level 39: Views & Virtual Tables

  • Title: "Data Abstraction Layers"
  • Exercises: 5
  • Topics: CREATE VIEW, updatable views, view optimization, security views
  • Key Concepts: Data abstraction, view benefits, query simplification

Level 40: SQL Best Practices & Patterns

  • Title: "Professional SQL Development"
  • Exercises: 5
  • Topics: Code organization, naming conventions, query patterns, anti-patterns
  • Key Concepts: Best practices, maintainable SQL, professional development

Course Completion Summary

Total Levels: 40 Total Exercises: ~180 Skill Progression: Beginner → Intermediate → Advanced → Professional

SQLite-Specific Adaptations:

  • Level 31: Views/CTEs instead of stored procedures
  • Level 32: Constraints instead of triggers
  • Level 34: CASE-based aggregation instead of ROLLUP/CUBE
  • Level 38: SQLite type system (not generic SQL types)
  • Level 40: Best practices instead of user management (SQLite has no users)

Removed Levels (41-45 - Not applicable for browser-based SQLite):

  • Level 41: Backup and Recovery (file-based operations)
  • Level 42: Database Monitoring (server-based)
  • Level 43: Advanced Analytics (OLAP not in SQLite)
  • Level 44: NoSQL Integration (beyond scope)
  • Level 45: Enterprise Patterns (distributed systems)

Implementation Notes

Exercise Structure

Each exercise should include:

  • Detailed instruction with database concepts
  • Pre-populated sample database
  • Clear SQL requirements
  • Expected result sets
  • XP rewards (15-50 per exercise)

Database Environment

  • SQLite for client-side execution
  • Pre-loaded sample databases (employees, products, orders)
  • Real-world data scenarios
  • Interactive query execution
  • Result visualization

Validation System

  • SQL syntax validation
  • Result set comparison
  • Performance considerations
  • Query structure analysis
  • Best practice recommendations

Sample Databases

  • Employees Database: HR scenarios, hierarchical data
  • E-commerce Database: Orders, products, customers
  • Library Database: Books, authors, borrowers
  • Hospital Database: Patients, doctors, appointments
  • Financial Database: Accounts, transactions, balances

Real-World Applications

  • Business reporting queries
  • Data analysis scenarios
  • Performance optimization challenges
  • Database design problems
  • Administrative tasks simulation