- 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.
- Databases and relational databases.
- SQL commands: SELECT, INSERT, UPDATE, DELETE.
- Filtering data using WHERE, ORDER BY, and LIMIT.
- 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.
- HackerRank Test: Complete basic SQL query challenges based on the day’s lessons.
- Understand SQL Data Types (INT, VARCHAR, DATE, etc.).
- Learn data type conversion in SQL.
- Use arithmetic, comparison, and logical operators in SQL queries.
- Data types in SQL: INT, VARCHAR, DATE.
- Data type conversion using CAST() and CONVERT().
- Arithmetic operators, comparison operators, and logical operators (AND, OR, NOT).
- 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.).
- HackerRank Test: Focus on solving SQL problems related to operators and data types.
- Use aggregate functions: COUNT(), SUM(), AVG(), MIN(), MAX().
- Learn to group data with GROUP BY and filter groups using HAVING.
- Using aggregate functions.
- Understanding and using GROUP BY and HAVING clauses.
- HackerRank Exercises: Complete challenges using aggregate functions and GROUP BY.
- Notes: Examples of aggregate functions and how GROUP BY and HAVING are used.
- HackerRank Test: Solve challenges on grouping and aggregating data.
- 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.
- INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN.
- Self joins, cross joins, and joining multiple tables.
- 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.
- HackerRank Test: Solve multiple challenges based on joins (e.g., inner, outer joins).
- Understand roles and responsibilities in database migration.
- Learn how to prepare data for migration and ensure data integrity.
- Overview of migration responsibilities.
- Ensuring data integrity during migration.
- Handling errors in migration.
- 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.
- Case Study: Provide a sample data migration plan, covering preparation, error handling, and integrity checks.
- Learn how to check data consistency post-migration.
- Validate table structures and relationships after migration.
- Verifying data integrity post-migration (using row counts, hash checks, etc.).
- Validating tables with the same columns, checking indexes, constraints, and relationships.
- Project Work: Use SQL queries to check data consistency post-migration.
- Notes: Techniques for checking data consistency and validating table structures.
- SQL Practice: Run SQL queries to check data consistency (e.g., row counts, hash checks).
- Reinforce concepts learned so far with hands-on practice.
- 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.
- SQL Murder Mystery: Solve the mystery using SQL queries.
- Learn the purpose of CTEs in SQL.
- Understand CTE syntax and use cases.
- Apply CTEs with ranking functions like ROW_NUMBER(), RANK(), DENSE_RANK().
- Introduction to CTEs and their benefits over subqueries.
- Using CTEs for ranking data (e.g., ROW_NUMBER(), RANK(), DENSE_RANK()).
- HackerRank Exercises: Complete challenges that use CTEs and ranking functions.
- Notes: Summary of CTEs, their advantages, and examples using ranking functions.
- HackerRank Test: Focus on solving problems with CTEs and ranking functions.
- Learn how to use CTEs for data aggregation.
- Handle partitioned data for better performance using CTEs.
- Aggregating data using CTEs with functions like AVG(), COUNT(), etc.
- Partitioning data within CTEs to improve performance.
- Project Work: Practice aggregating data using CTEs and partitioning.
- Notes: How to use CTEs for aggregation and partitioning.
- SQL Practice: Write queries that aggregate data using CTEs.
- Understand the role of temporary tables.
- Learn to create, insert, and use temporary tables in SQL queries.
- Differences between temporary and permanent tables.
- Use cases for temporary tables in large queries.
- HackerRank Exercises: Practice using temporary tables in SQL queries.
- Notes: Summary of temporary tables and when to use them.
- SQL Practice: Solve problems using temporary tables.
- Learn how indexing improves query performance.
- Understand different types of indexes (unique, composite, full-text).
- Analyze execution plans and optimize queries using indexes.
- Types of indexes and their impact on performance.
- How to read and interpret execution plans.
- Query optimization with indexes.
- HackerRank Exercises: Practice query optimization with indexes.
- Notes: Explanation of indexing concepts and their impact on performance.
- SQL Practice: Solve challenges on indexing and query optimization.
- 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.
- Analyzing query performance using EXPLAIN plans.
- Techniques for writing efficient and optimized queries.
- Project Work: Analyze and optimize queries using EXPLAIN plans.
- Notes: Techniques for optimizing SQL queries.
- SQL Practice: Write optimized SQL queries.
- Practice advanced SQL concepts with interactive tests and challenges.
- Apply optimization techniques and indexing knowledge.
- SQL Murder Mystery Game: Solve the mystery using SQL queries.
- Project Work: Complete 2-3 interactive SQL challenges related to advanced topics.
- SQL Challenge: Engage in an interactive challenge game to apply skills.
- Review key concepts learned over the past two weeks.
- Finalize notes and prepare for future SQL applications.
- Final Project Submission: Submit all project work, including SQL queries and migration plans.
- Reflection: Review everything learned, write a summary of key takeaways.
- SQL Knowledge Check: Take a final knowledge test on all SQL topics covered