📊 Data Science Week 1 of 14 BSc · Y2 S1 ⏱ ~50 min

Week 1: Relational Models, SQL Mastery & Query Optimization

Master SQL from basic SELECTs to complex joins, subqueries, window functions — and understand how databases store and retrieve data efficiently.

University of America

DS202 — Lecture 1 · BSc Y2 S1

🎬 CC Licensed Lecture

0:00 / —:—— 📺 Creative Commons Licensed

🎯 Learning Objectives

Write complex SQL queries with joins, subqueries, CTEs
Use window functions for advanced analytics
Design normalized database schemas (3NF)
Understand query execution plans and indexing strategies

Topics Covered This Lecture

SQL Fundamentals & Joins

Aggregations & Window Functions

Database Design & Normalization

Indexing & Query Optimization

📖 Lecture Overview

This first lecture establishes the foundational framework for Database Systems & SQL. By the end of this session, you will have the conceptual grounding and practical starting point needed for the rest of the course.

        Why this matters
        Master SQL from basic SELECTs to complex joins, subqueries, window functions — and understand how databases store and retrieve data efficiently. This lecture sets up everything that follows — make sure you understand the core concepts before proceeding to Week 2.
      

Key Concepts

The lecture introduces the four main pillars of this course: SQL Fundamentals & Joins, Aggregations & Window Functions, Database Design & Normalization, Indexing & Query Optimization. Each will be explored in depth over the 14-week curriculum, with hands-on projects reinforcing theory at every stage.

# Quick Start: verify your environment is ready for DS202
import sys
print(f"Python {sys.version}")

# Check key libraries are installed
try:
    import numpy, pandas, matplotlib
    print("✅ Core libraries ready")
except ImportError as e:
    print(f"❌ Missing: {e} — run: pip install numpy pandas matplotlib")

This Week's Focus

Focus on mastering: SQL Fundamentals & Joins and Aggregations & Window Functions. These are the prerequisites for everything in Week 2. The concepts build on each other — do not skip the practice exercises.

📋 Project 1 of 3 50% of Final Grade

DS202 Project 1: Analytics Database Design

Design and implement a relational database for an e-commerce business. Write SQL queries to answer 10 business questions, including window functions and CTEs.

Entity-Relationship diagram (3NF schema)
SQL DDL: CREATE TABLE with constraints
10 analytical SQL queries with explanations
Query plan analysis for 3 complex queries

50%

3 Projects

20%

Midterm Exam

30%

Final Exam

📝 Sample Exam Questions

These represent the style and difficulty of questions you'll see on the midterm and final. Start thinking about them now.

Conceptual Short Answer

Explain the difference between INNER JOIN, LEFT JOIN, and FULL OUTER JOIN with examples.

Analysis Short Answer

Write a SQL window function to compute a 7-day rolling average of daily sales.

Applied Code / Proof

What is a database index and how does it improve query performance? What are the trade-offs?