Course Objectives

Upon completion of this course, students will be able to:

• Master advanced SQL query techniques for complex data retrieval and analysis.
• Understand and implement database objects such as views, stored procedures, and triggers.
• Optimize database performance through indexing, query plan analysis, and normalization strategies.
• Utilize server-side programming with T-SQL (or PL/SQL) for building robust and reusable database components.
• Design and implement effective data integrity constraints and security measures.

Detailed Weekly Course Schedule

Week 1: Design and Develop Database Objects

• Topic 1: Design And Develop Transactional Database
• Design and Implement Schemas.
• Design and Implement Data Integrity Constraints.
• Topic 2: Design And Develop Datawarehouse
• Design and Implement Fact Tables.
• Design and Implement Dimension Tables.

Week 2: Advanced Querying and Data Manipulation

• Topic 1: Subqueries & Common Table Expressions (CTEs)
• Review of basic subqueries and an in-depth look at correlated subqueries.
• Creating and using recursive CTEs for hierarchical data.
• Topic 2: Window Functions
• Learning and applying ranking functions (ROW_NUMBER, RANK, DENSE_RANK).
• Using aggregation functions with PARTITION BY (SUM, AVG, COUNT).
• Analytical functions like LEAD and LAG.

Week 3: Database Objects and Views

• Topic 1: Views
• The purpose and benefits of views.
• Creating, modifying, and dropping standard and indexed views.
• WITH CHECK OPTION and updatable views.
• Topic 2: Materialized Views (if applicable to the chosen database system)
• Understanding the benefits of materialized views for performance.
• Creating and refreshing materialized views.

Week 4: Stored Procedures and Functions

• Topic 1: T-SQL/PL-SQL Stored Procedures
• Creating stored procedures with input and output parameters.
• Implementing control flow logic (IF...ELSE, CASE, WHILE).
• Best practices for modular and reusable code.
• Topic 2: User-Defined Functions (UDFs)
• Scalar vs. Table-Valued functions.
• INLINE vs. MULTI-STATEMENT table-valued functions.

Week 5: Triggers and Transactions

• Topic 1: Triggers
• Introduction to triggers (AFTER, BEFORE, INSTEAD OF).
• Creating and managing triggers for data validation and audit logging.
• The use of INSERTED and DELETED tables.
• Topic 2: Transaction Management
• Understanding COMMIT, ROLLBACK, and SAVEPOINT.
• Using the TRY...CATCH block for error handling in transactions.

Week 6: Performance Tuning and Indexing

• Topic 1: Indexing Strategies
• Clustered vs. Non-Clustered indexes.
• Creating and optimizing indexes for query performance.
• Understanding index fragmentation and maintenance.
• Topic 2: Query Optimization
• Reading and interpreting query execution plans.
• Identifying performance bottlenecks and rewriting inefficient queries.
• Use of EXPLAIN ANALYZE or similar tools.

Week 7: Advanced Data Integrity and Security

• Topic 1: Constraints
• Review of primary key and foreign key constraints.
• Advanced constraints: CHECK constraints, UNIQUE constraints.
• Deferrable constraints.

Week 1 to Week 7: Project Works