Final Review and Next Steps

Congratulations! 🎉

You've completed the SQL Basics course! You've built a complete task management system from scratch, demonstrating mastery of:

• ✅ Database design and schema creation
• ✅ Primary keys, foreign keys, and constraints
• ✅ CRUD operations (INSERT, UPDATE, DELETE, SELECT)
• ✅ JOINs (INNER, LEFT, RIGHT, FULL OUTER, SELF)
• ✅ Aggregate functions and GROUP BY
• ✅ Subqueries and CTEs
• ✅ Advanced queries with CASE, DISTINCT, LIMIT
• ✅ Indexes and performance optimization
• ✅ Normalization and best practices

What You've Built

Your task management system includes:

• 10 interconnected tables with proper relationships
• Foreign keys enforcing referential integrity
• Indexes on foreign keys and common query patterns
• Complex queries answering real-world business questions
• Analytical reports for dashboards and insights
• Audit trails with activity logging
• Soft deletes for data recovery

This is a production-ready schema that could power a real application!

Project Review Checklist

Review your work against these criteria:

Schema Design

• ✅ All tables have primary keys (BIGSERIAL)
• ✅ Foreign keys defined with appropriate ON DELETE actions
• ✅ Indexes on all foreign keys
• ✅ NOT NULL on required fields
• ✅ UNIQUE constraints on business keys
• ✅ CHECK constraints for validation
• ✅ Default values (created_at, status)
• ✅ Soft delete support (deleted_at)
• ✅ Consistent naming conventions

Queries

• ✅ Use appropriate JOIN types
• ✅ Aggregate data with GROUP BY
• ✅ Filter groups with HAVING
• ✅ Use CTEs for readability
• ✅ Handle NULLs with COALESCE
• ✅ Use subqueries when needed
• ✅ Optimize with indexes

Performance

• ✅ Indexes on foreign keys
• ✅ Composite indexes for common patterns
• ✅ Partial indexes for filtered queries
• ✅ EXPLAIN ANALYZE to verify performance
• ✅ No SELECT * in production queries

SQL Skills Assessment

Beginner Level ✅

• Write basic SELECT queries
• Filter with WHERE
• Sort with ORDER BY
• Insert, update, and delete data
• Understand data types

Intermediate Level ✅

• Use JOINs to combine tables
• Aggregate data with GROUP BY
• Write subqueries
• Use CTEs for complex queries
• Handle NULLs properly
• Design normalized schemas

Advanced Level ✅

• Optimize queries with indexes
• Design many-to-many relationships
• Use window functions (basics)
• Implement soft deletes
• Create audit trails
• Write complex analytical queries

Portfolio Piece

Add this project to your portfolio:

GitHub: Upload your SQL scripts

• 01-schema.sql
• 02-indexes.sql
• 03-sample-data.sql
• 04-queries.sql
• 05-reports.sql
• README.md documenting the project

README Template:

# Task Management System Database

A PostgreSQL database schema for a collaborative task management application.

## Features
- Multi-workspace support
- Projects and tasks with assignments
- Tags and comments
- Activity logging
- Soft deletes

## Schema
10 normalized tables with referential integrity

## Tech Stack
- PostgreSQL 15+
- SQL (DDL, DML, complex queries)

## Setup
`psql -d task_management -f 01-schema.sql`
`psql -d task_management -f 02-indexes.sql`
`psql -d task_management -f 03-sample-data.sql`

## Sample Queries
See `04-queries.sql` and `05-reports.sql`

What's Next? Advanced SQL Topics

1. Window Functions

-- Row numbering, ranking, running totals
SELECT
    title,
    status,
    ROW_NUMBER() OVER (PARTITION BY status ORDER BY created_at) AS row_num,
    RANK() OVER (ORDER BY priority DESC) AS priority_rank
FROM tasks;

2. Recursive CTEs

-- Hierarchical queries, org charts, nested comments
WITH RECURSIVE task_hierarchy AS (
    SELECT id, title, parent_task_id, 1 AS level
    FROM tasks
    WHERE parent_task_id IS NULL
    UNION ALL
    SELECT t.id, t.title, t.parent_task_id, th.level + 1
    FROM tasks t
    JOIN task_hierarchy th ON t.parent_task_id = th.id
)
SELECT * FROM task_hierarchy;

3. JSON and JSONB

-- Store and query JSON data
ALTER TABLE tasks ADD COLUMN metadata JSONB;

UPDATE tasks SET metadata = '{"labels": ["bug", "urgent"], "priority": 1}';

SELECT title FROM tasks
WHERE metadata->'labels' ? 'bug';

4. Full-Text Search

-- Search task content
ALTER TABLE tasks ADD COLUMN search_vector tsvector;

UPDATE tasks
SET search_vector = to_tsvector('english', title || ' ' || COALESCE(description, ''));

CREATE INDEX idx_tasks_search ON tasks USING gin(search_vector);

SELECT title FROM tasks
WHERE search_vector @@ to_tsquery('design & homepage');

5. Triggers and Functions

-- Automatically update updated_at
CREATE OR REPLACE FUNCTION update_timestamp()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER set_updated_at
BEFORE UPDATE ON tasks
FOR EACH ROW
EXECUTE FUNCTION update_timestamp();

6. Transactions and Concurrency

-- ACID transactions
BEGIN;

UPDATE tasks SET status = 'in_progress' WHERE id = 1;
INSERT INTO activity_log (action, entity_id) VALUES ('started_task', 1);

COMMIT;  -- Or ROLLBACK on error

7. Views and Materialized Views

-- Virtual table
CREATE VIEW my_tasks AS
SELECT t.* FROM tasks t
JOIN task_assignments ta ON t.id = ta.task_id
WHERE ta.user_id = current_user_id();

-- Cached results
CREATE MATERIALIZED VIEW workspace_stats AS
SELECT ...;

REFRESH MATERIALIZED VIEW workspace_stats;

Recommended Learning Path

Next Courses

Ready to take your SQL skills to the next level? Continue your learning journey with these courses:

1. SQL Architecture in the AI Era: Master database internals, indexing strategies, and schema design for AI applications. Learn how SQL powers production systems at TikTok, Uber, and Netflix.

2. Advanced SQL: Window functions, CTEs, recursive queries

3. PostgreSQL Deep Dive: JSONB, full-text search, extensions

4. Database Performance: Query optimization, explain plans, indexing strategies

Practice Projects

1. E-commerce Platform: Products, carts, orders, payments
2. Social Media Clone: Users, posts, likes, comments, followers
3. Blog Platform: Articles, categories, tags, comments
4. Analytics Dashboard: Time-series data, metrics, reports
5. API Backend: REST API with SQL database

Books

• SQL Performance Explained by Markus Winand
• PostgreSQL: Up and Running by Regina Obe
• Database Design for Mere Mortals by Michael J. Hernandez

Online Resources

• PostgreSQL Documentation
• Use The Index, Luke
• PostgreSQL Exercises
• SQL Zoo

Final Tips

• ✅ Practice daily: Build small projects, solve SQL puzzles
• ✅ Read others' code: Study open-source schemas
• ✅ Measure performance: Always EXPLAIN your queries
• ✅ Document your work: Clear names, comments, ERDs
• ✅ Stay curious: SQL evolves, keep learning!

Share Your Success

Completed the course? Share on social media!

🎉 Just completed the SQL Basics course! Built a complete task
management database with 10 tables, complex JOINs, and optimized
queries. #SQL #PostgreSQL #DatabaseDesign

Course Feedback

Help improve this course:

• What did you find most helpful?
• What was challenging?
• What topics need more coverage?

Thank You!

Thank you for completing SQL Basics! You now have solid SQL skills that will serve you throughout your development career. Whether you're building web apps, analyzing data, or architecting systems, SQL is foundational.

Keep practicing, keep building, and most importantly—keep querying!

Final Exercise

Before you go, try this final challenge:

Build a feature: Add recurring tasks to your task management system

• Design the schema (new table? columns?)
• Write queries to generate task instances
• Handle recurrence patterns (daily, weekly, monthly)

This exercise combines everything you've learned. Good luck!

---

Congratulations again on completing SQL Basics! 🚀

You're now ready for advanced SQL topics and real-world database projects. The journey continues—enjoy mastering databases!