Module 19: SQL and Database Fundamentals

Master SQL and database management for data science.

Teaching stage: This is Stage 1.5 in the recommended path, start in parallel with Module 01–02 if you target data analyst, data scientist, ML engineer, or BI roles. Do not wait until after GenAI (Module 25). See FOUNDATION_AND_JOB_READINESS.md.

What You'll Learn

• Database Fundamentals
• SQL DDL and DML Commands
• SQL Joins and Subqueries
• Window Functions
• Advanced SQL Topics
• SQL with Python

Topics Covered

1. Database Fundamentals

• What is a Database?
• Types of Databases (Relational, NoSQL)
• OLAP vs OLTP: Understanding Database Types
• Database Relationships
• CRUD Operations

2. SQL DDL Commands

• CREATE DATABASE and TABLE
• ALTER TABLE
• DROP TABLE
• Data Types

3. SQL DML Commands

• INSERT
• SELECT (with WHERE, ORDER BY, GROUP BY)
• UPDATE
• DELETE

4. SQL Functions

• Aggregate Functions (COUNT, SUM, AVG, MIN, MAX)
• String Functions
• Date Functions

5. SQL Joins

• INNER JOIN
• LEFT/RIGHT JOIN
• FULL OUTER JOIN
• CROSS JOIN
• SELF JOIN

6. Subqueries

• Scalar Subqueries
• Correlated Subqueries
• EXISTS

7. Window Functions

• ROW_NUMBER, RANK, DENSE_RANK
• LAG, LEAD
• Aggregate Window Functions

8. Advanced Topics

• Common Table Expressions (CTE)
• Views
• Stored Procedures
• SQL with Python

Learning Objectives

By the end of this module, you should be able to:

• Design and create database schemas
• Write complex SQL queries
• Use joins effectively
• Apply window functions for analytics
• Integrate SQL with Python

Projects

1. Database Design: Design schema for a business application
2. Data Analysis with SQL: Analyze dataset using SQL queries
3. SQL + Python Integration: Build data pipeline with SQL and Python

Key Concepts

• Tables: Collections of related data
• Relationships: How tables connect
• Joins: Combining data from multiple tables
• Aggregations: Summarizing data
• Subqueries: Queries within queries

Documentation & Learning Resources

Official Documentation:

• MySQL Documentation
• PostgreSQL Documentation
• SQLite Documentation

Free Courses:

• SQL Tutorial (W3Schools)
• SQL Course (Mode Analytics)
• SQL for Data Science (Coursera)

Practice:

• SQLZoo
• LeetCode SQL
• HackerRank SQL

Complete Detailed Guide →

Additional Resources:

• Advanced Topics →: Advanced window functions, optimization, indexes
• Project Tutorial →: Step-by-step SQL project
• Quick Reference →: Quick lookup guide for SQL

Previous (folder order): 18-projects-advanced
Next (folder order): 20-handling-imbalanced-data

Recommended stage order: Start after Module 01 (lessons 2–3) in parallel with Stages 1–2. This is Stage 1.5, not a capstone after projects or GenAI.