Week 1: SQL Basics, Querying Fundamentals, and Migration Processes
Day 1: Introduction to SQL & SQL Syntax
Objectives
- Overview of databases and SQL.
- Introduction to relational databases and their structures.
- Learn SQL basic commands: SELECT, INSERT, UPDATE, DELETE.
- Understand SQL query structure and filtering with WHERE.
Topics Covered
- Databases and relational databases.
- SQL commands: SELECT, INSERT, UPDATE, DELETE.
- Filtering data using WHERE, ORDER BY, and LIMIT.
Deliverables
- HackerRank Exercises: Complete 5-10 challenges on basic SQL syntax (focusing on SELECT, INSERT, UPDATE, DELETE, WHERE).
- Notes: Summary of SQL syntax, basic commands, and how WHERE, ORDER BY, and LIMIT work.
End of Day Test
- HackerRank Test: Complete basic SQL query challenges based on the day’s lessons.
Day 2: Working with Data Types & Basic Operators
Objectives
- Understand SQL Data Types (INT, VARCHAR, DATE, etc.).
- Learn data type conversion in SQL.
- Use arithmetic, comparison, and logical operators in SQL queries.
Topics Covered
- Data types in SQL: INT, VARCHAR, DATE.
- Data type conversion using CAST() and CONVERT().
- Arithmetic operators, comparison operators, and logical operators (AND, OR, NOT).
Deliverables
- HackerRank Exercises: Complete exercises focused on data types and operators.
- Notes: Explanation of common data types and examples of using operators (AND, OR, arithmetic, etc.).
End of Day Test
- HackerRank Test: Focus on solving SQL problems related to operators and data types.
Day 3: Aggregate Functions & Grouping Data
Objectives
- Use aggregate functions: COUNT(), SUM(), AVG(), MIN(), MAX().
- Learn to group data with GROUP BY and filter groups using HAVING.
Topics Covered
- Using aggregate functions.
- Understanding and using GROUP BY and HAVING clauses.
Deliverables
- HackerRank Exercises: Complete challenges using aggregate functions and GROUP BY.
- Notes: Examples of aggregate functions and how GROUP BY and HAVING are used.
End of Day Test
- HackerRank Test: Solve challenges on grouping and aggregating data.
Day 4: Joins and Relationships
Objectives
- Learn and apply different types of joins: INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN.
- Understand self joins and cross joins.
- Learn how to join multiple tables.
Topics Covered
- INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN.
- Self joins, cross joins, and joining multiple tables.
Deliverables
- HackerRank Exercises: Complete 5-7 challenges focusing on different types of joins.
- Notes: Detailed explanation of the different types of joins and when to use them.
End of Day Test
- HackerRank Test: Solve multiple challenges based on joins (e.g., inner, outer joins).
Day 5: Migration Process & Data Validation (Part 1)
Objectives
- Understand roles and responsibilities in database migration.
- Learn how to prepare data for migration and ensure data integrity.
Topics Covered
- Overview of migration responsibilities.
- Ensuring data integrity during migration.
- Handling errors in migration.
Deliverables
- Project Work: Draft a migration plan based on sample data (Project details can be found in the course materials or provided by your instructor).
- Notes: Key steps in the database migration process.
End of Day Test
- Case Study: Provide a sample data migration plan, covering preparation, error handling, and integrity checks.
Day 6: Migration Process & Data Validation (Part 2)
Objectives
- Learn how to check data consistency post-migration.
- Validate table structures and relationships after migration.
Topics Covered
- Verifying data integrity post-migration (using row counts, hash checks, etc.).
- Validating tables with the same columns, checking indexes, constraints, and relationships.
Deliverables
- Project Work: Use SQL queries to check data consistency post-migration.
- Notes: Techniques for checking data consistency and validating table structures.
End of Day Test
- SQL Practice: Run SQL queries to check data consistency (e.g., row counts, hash checks).
Day 7: Project 1 – SQL Basics Practice
Objectives
- Reinforce concepts learned so far with hands-on practice.
Deliverables
- SQL Murder Mystery Game: Complete the SQL Murder Mystery challenge (interactive SQL challenge where you solve a crime using SQL queries).
- Project Work: Solve 2-3 practical SQL problems that include basic queries, joins, and aggregation.
- Notes: Reflection on the week’s learning, highlighting key concepts and areas of difficulty.
End of Day Test
- SQL Murder Mystery: Solve the mystery using SQL queries.
Week 2: Advanced SQL Concepts, CTEs, and Optimization Techniques
Day 8: Common Table Expressions (CTEs)
Objectives
- Learn the purpose of CTEs in SQL.
- Understand CTE syntax and use cases.
- Apply CTEs with ranking functions like ROW_NUMBER(), RANK(), DENSE_RANK().
Topics Covered
- Introduction to CTEs and their benefits over subqueries.
- Using CTEs for ranking data (e.g., ROW_NUMBER(), RANK(), DENSE_RANK()).
Deliverables
- HackerRank Exercises: Complete challenges that use CTEs and ranking functions.
- Notes: Summary of CTEs, their advantages, and examples using ranking functions.
End of Day Test
- HackerRank Test: Focus on solving problems with CTEs and ranking functions.
Day 9: Aggregating Data with CTEs
Objectives
- Learn how to use CTEs for data aggregation.
- Handle partitioned data for better performance using CTEs.
Topics Covered
- Aggregating data using CTEs with functions like AVG(), COUNT(), etc.
- Partitioning data within CTEs to improve performance.
Deliverables
- Project Work: Practice aggregating data using CTEs and partitioning.
- Notes: How to use CTEs for aggregation and partitioning.
End of Day Test
- SQL Practice: Write queries that aggregate data using CTEs.
Day 10: Temporary Tables
Objectives
- Understand the role of temporary tables.
- Learn to create, insert, and use temporary tables in SQL queries.
Topics Covered
- Differences between temporary and permanent tables.
- Use cases for temporary tables in large queries.
Deliverables
- HackerRank Exercises: Practice using temporary tables in SQL queries.
- Notes: Summary of temporary tables and when to use them.
End of Day Test
- SQL Practice: Solve problems using temporary tables.
Day 11: Indexing in SQL
Objectives
- Learn how indexing improves query performance.
- Understand different types of indexes (unique, composite, full-text).
- Analyze execution plans and optimize queries using indexes.
Topics Covered
- Types of indexes and their impact on performance.
- How to read and interpret execution plans.
- Query optimization with indexes.
Deliverables
- HackerRank Exercises: Practice query optimization with indexes.
- Notes: Explanation of indexing concepts and their impact on performance.
End of Day Test
- SQL Practice: Solve challenges on indexing and query optimization.
Day 12: Query Optimization
Objectives
- Learn how to optimize queries using EXPLAIN plans and query best practices.
- Understand how to improve query performance using proper WHERE, JOIN conditions, and limiting data retrieval.
Topics Covered
- Analyzing query performance using EXPLAIN plans.
- Techniques for writing efficient and optimized queries.
Deliverables
- Project Work: Analyze and optimize queries using EXPLAIN plans.
- Notes: Techniques for optimizing SQL queries.
End of Day Test
- SQL Practice: Write optimized SQL queries.
Day 13: Project 2 – SQL Practice Tests and Games
Objectives
- Practice advanced SQL concepts with interactive tests and challenges.
- Apply optimization techniques and indexing knowledge.
Deliverables
- SQL Murder Mystery Game: Solve the mystery using SQL queries.
- Project Work: Complete 2-3 interactive SQL challenges related to advanced topics.
End of Day Test
- SQL Challenge: Engage in an interactive challenge game to apply skills.
Day 14: Final Review and Reflection
Objectives
- Review key concepts learned over the past two weeks.
- Finalize notes and prepare for future SQL applications.
Deliverables
- Final Project Submission: Submit all project work, including SQL queries and migration plans.
- Reflection: Review everything learned, write a summary of key takeaways.
End of Day Test
- SQL Knowledge Check: Take a final knowledge test on all SQL topics covered
References
SQL Games
Others