Module 1: SQL Fundamentals Refresher & Core Concepts

• Goal: Ensure a rock-solid understanding of the basics.
• Topics:
  • Relational Database Concepts (Tables, Rows, Columns, Relationships, Keys)
  • Core SQL Statements Review:
    • SELECT (Filtering with WHERE, Sorting with ORDER BY, Limiting results)
    • INSERT, UPDATE, DELETE (Data Manipulation Language – DML)
  • Data Types (Common types like INT, VARCHAR, DATE, DECIMAL, BOOLEAN, etc.)
  • Constraints (PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, CHECK) – Importance and usage.
  • NULL Handling (What NULL means, IS NULL, IS NOT NULL, functions like COALESCE/ISNULL)

Module 2: Advanced Querying Techniques

• Goal: Learn how to retrieve and combine data in complex ways.
• Topics:
  • JOIN Operations In-Depth:
    • INNER JOIN
    • LEFT JOIN (and RIGHT JOIN)
    • FULL OUTER JOIN
    • CROSS JOIN
    • Joining multiple tables
    • Self-Joins
  • Aggregation and Grouping:
    • Aggregate Functions (COUNT, SUM, AVG, MIN, MAX)
    • GROUP BY clause (How it works with aggregates)
    • HAVING clause (Filtering after grouping)
  • Subqueries (Nested Queries):
    • Scalar subqueries (return a single value)
    • Multi-row subqueries (used with IN, ANY, ALL, EXISTS)
    • Correlated subqueries (inner query refers to outer query)
  • Set Operations:
    • UNION (Combine results, remove duplicates)
    • UNION ALL (Combine results, keep duplicates)
    • INTERSECT (Get common rows)
    • EXCEPT / MINUS (Get rows in first set but not second)
  • Conditional Logic:
    • CASE statements (Simple and Searched)

Module 3: Database Objects – Views & Stored Procedures

• Goal: Understand reusable database structures for simplification, security, and logic encapsulation.
• Topics:
  • Views: is a stored query, can be treated like a virtual table, it’s to simplify complex queries;
  • Stored Procedures: is pre-compiled SQL codes that can be executed on demand.
  • Views vs. Stored Procedures – Comparison:

Module 4: Database Design & Normalization

• Goal: Learn how to structure tables efficiently to reduce redundancy and improve data integrity.
• Topics:
  • Why Normalize? Problems with data redundancy (Update, Insert, Delete Anomalies).
  • Functional Dependencies: Understanding how attribute values relate to each other (X -> Y means knowing X determines Y).
  • Keys: Candidate Keys, Primary Keys, Foreign Keys, Superkeys, Composite Keys.
  • Normal Forms (NF):
    • First Normal Form (1NF): Atomic values (no repeating groups or multi-valued columns).
    • Second Normal Form (2NF): 1NF + No partial dependencies (all non-key attributes fully dependent on the entire primary key – relevant for composite keys).
    • Third Normal Form (3NF): 2NF + No transitive dependencies (non-key attributes don’t depend on other non-key attributes).
    • (Briefly) Boyce-Codd Normal Form (BCNF): A stricter version of 3NF.
    • (Awareness) Higher Normal Forms (4NF, 5NF): Address multi-valued dependencies and join dependencies (less commonly applied in practice).
  • Normalization Process: Step-by-step examples of normalizing a poorly designed table.
  • Denormalization: When and why you might intentionally introduce redundancy (usually for performance reasons, e.g., in data warehouses or for specific query optimization). Trade-offs involved.

Module 5: Indexing and Performance Optimization

• Goal: Understand how to make queries run faster.
• Topics:
  • What are Indexes? Data structures that speed up data retrieval operations.
  • How Indexes Work (Conceptual): B-Trees, Hash Indexes (less common for general use).
  • Types of Indexes:
    • Clustered vs. Non-clustered
    • Single-column vs. Composite
    • Unique Indexes
    • Covering Indexes
  • Syntax: CREATE INDEX, DROP INDEX.
  • When to Index: Columns used frequently in WHERE, JOIN, ORDER BY. Trade-offs (Indexes speed up reads but slow down writes – INSERT/UPDATE/DELETE).
  • Query Execution Plans: Using EXPLAIN, EXPLAIN ANALYZE, or SQL Server Execution Plans to understand how the database executes a query and identify bottlenecks.
  • Basic Query Tuning: Writing SARGable queries (Search ARGument Able – allowing index use), avoiding SELECT *, understanding index selectivity.

Module 6: Transactions and Concurrency Control

• Goal: Understand how the database ensures data integrity when multiple operations or users interact simultaneously.
• Topics:
  • ACID Properties: Atomicity, Consistency, Isolation, Durability.
  • Transactions: BEGIN TRANSACTION, COMMIT, ROLLBACK. Grouping operations into atomic units.
  • Concurrency Issues: Lost Updates, Dirty Reads, Non-repeatable Reads, Phantom Reads.
  • Isolation Levels: Understanding different levels (READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE) and their trade-offs between consistency and concurrency.
  • Locking: Conceptual understanding of how databases use locks (Shared, Exclusive) to manage concurrency (Pessimistic Locking). Optimistic Locking concepts.
  • Deadlocks: What they are and how databases typically handle them.

Module 7: Data Control Language (DCL) & Security

• Goal: Understand how to manage permissions.
• Topics:
  • GRANT: Assigning permissions (e.g., SELECT, INSERT, EXECUTE) on objects (tables, views, procedures) to users or roles.
  • REVOKE: Removing permissions.
  • Roles: Grouping permissions for easier management.
  • Basic User Management concepts (if applicable to your role).

Note When data in the underlying base tables of an indexed view changes (due to INSERT, UPDATE, DELETE), SQL Server automatically maintains the indexed view’s stored data and its associated indexes in real-time. You do not need to manually “re-index” the view just to make it reflect the new data from the base tables. This automatic maintenance is a core feature and benefit of indexed views – they stay synchronized with the base data.

However, the process of refreshing the underlying data daily (especially if it involves large numbers of inserts, deletes, or updates) can cause index fragmentation within the indexed view’s indexes, just like it can with indexes on regular tables. It can also lead to outdated statistics on the view.