SQL SERVER 2008:

CD 1 - SAMPLE DATABASES (2 hours 47 minutes) CD 2 - DATABASE DESIGN (2 hours 54 minutes)

1. Installing SQL Server Sample Databases

2. Conceptual Diagram of AW2008 Entity Model

3. Visio Diagram of AdventureWorks2008 Database

4. Primary Key and Foreign Key; RDBMS Basics

5. 1NF, 2NF & 3NF Normal Forms in AdventureWorks2008

6. AdventureWorks2008; Bike Manufacturer OLTP DB

7. AW2008 Business Scenarios & AW Data Dictionary

8. Reviewing AdventureWorks2008 New Features

9. AdventureWorksDW2008 Data Warehouse Database

10. HierarchyID Based Trees in AdventureWorks2008

11. Creating, Saving & Emailing Database Diagrams

12. pubs Book Publishing Sample Database Overview

13. Northwind Food & Drink Supplier Sample DB

14. Advanced Review of pubs Database Diagram

15. Advanced Review of Northwind Database Diagram

16. Accounting DB Review: GL, AP, AR & Inventory

1. Understanding Logical Data Modelling

2. Working with Table Column Data Types

3. New; DATE, TIME, DATETIMEOFFSET & DATETIME2

4. CREATE TABLE by Script & Using Object Explorer

5. Primary Key and Foreign Key Constraints

6. Database Diagram Design in Object Explorer

7. Logical Database Modelling with Visio

8. Relational Database Design with Visio

9. Reverse Engineering a Database with Visio

10. ALTER TABLE and Changing Tables by MS

11. Designing Compressed Tables & Indexes

12. Partitioned Table, Partition Function and Scheme

13. Create Partition & Manage Partition Wizards

14. The Data Compression Wizard

15. Code, Translate and Lookup Table Design

16. Database Design and Programming Standards


CD 3 - SELECT STATEMENT (3 hours 1 minute) CD 4 - MODIFY DATA (3 hours 2 minutes)

1. Inner Join, Self Join, Outer Join and Cross Join

2. Basic SQL SELECT and SELECT INTO Statements

3. Sorting SELECT Query Results with ORDER BY

4. Exploring CTEs, Nested & Correlated Subqueries

5. SELECT with UNION, UNION ALL, ROLLUP and CUBE

6. Date & Time, String and Math System Functions

7. Exploring Configuration & System Statistical Functions

8. Dynamic SQL for Automatic Query Generation

9. The GROUP BY Clause & Aggregate Functions

10. Working with Hierarchy ID and Metadata Functions

11. TOP, ROW_NUMBER, RANK, DENSE_RANK and NTILE

12. Understanding Recursive Queries & Tree Processing

13. Creating Comma-Delimited Lists with XML Path

14. Crosstab (Matrix) Query with CTE and PIVOT

15. Building Multidimensional Crosstab Query

16. Architecting Business Intelligence Dashboards

1. INSERT INTO Statement with VALUES Clause

2. Understanding INSERT and SELECT Subquery

3. INSERT and EXECUTE Statement

4. Data Removal with the DELETE Statement

5. Data Modification with the UPDATE Statement

6. Working with the MERGE Statement

7. Synchronizing Two Tables with MERGE

8. Modifying Data with Cursors, Subqueries & JOINs

9. Logged and Minimally-Logged Operations

10. Creating Audit Trail with the OUTPUT Clause

11. Combining INSERT with CTE and OUTPUT

12. UPDATE with CTE, OVER & PARTITION BY

13. Deleting Duplicate Rows with CTE

14. Updating Binary Mask and CSV List Columns

15. Using UPDATE with the FROM Clause

16. UPDATE with GROUP BY Aggregate Subquery


CD 5 - DATA INTEGRITY (3 hours) CD 6 - PROGRAMMABILITY (3 hours 4 minutes)

1. Data Integrity Constraints in AdventureWorks2008

2. Surrogate Key Architecture, Natural Key & GUID

3. PK, FK, UNIQUE & CHECK Constraints; Defaults

4. Data Integrity Enforcement with Triggers

5. Working with Data Integrity Templates

6. Stored Procedure vs. Ad-hoc SQL Script

7. Entity, Domain and Referential Database Integrity

8. Entity Integrity Definition Using Management Studio

9. Listing and Scripting Data Integrity Objects

10. Understanding Domain Integrity Enforcement

11. Implementing Referential Integrity Constraints

12. Creating User Defined Integrity Objects

13. Exploring Table-Level CHECK Constraints

14. Enterprise-Level Business Rules Enforcement

15. Transactions for Data Integrity Maintenance

16. Comparing and Synchronizing Databases

1. Stored Procedure Design and Programming

2. Exploring Table-Valued, XML & OUTPUT Parameters

3. Stored Procedures in AdventureWorks2008 Database

4. DML - Data Manipulation Language Triggers

5. Trigger Examples with DELETED & INSERTED Tables

6. Discovering INSTEAD OF Triggers & AFTER Triggers

7. DDL - Data Definition Language Triggers

8. Designing & Creating Views - Modifying Data

9. Table-Valued, Scalar-Valued & Inline Functions

10. Understanding Plan Guides for High Performance

11. Temporary Tables, Table Variables & tempdb

12. EXECUTE AS for Execution Context Definition

13. Multiple, Nested, Cascading & Recursive CTE-s

14. Grouping Sets for Multiple Groupings Definition

15. Spatial Data Types: Geography & Geometry

16. Applying Manual Debugging Techniques


CD 7 - TRANSACT-SQL (3 hours 1 minutes)

1. TRANSACT-SQL, Batch, and Scripts

2. BEGIN, COMMIT, ROLLBACK TRANSACTION

3. Using TRY…CATCH Blocks for Exception Handling

4. Locks, Isolation Levels and Deadlocks

5. Snapshot Isolation for OLTP Concurrency

6. PROGRAMMING STATEMENTS in Transact-SQL

7. User Defined and System Stored Procedures

8. Working with Linked Servers & 4-part Reference

9. Exploring CROSS APPLY & OUTER APPLY Operators

10. DBA Script Generator Using Query Editor

11. Discovering Undocumented T-SQL Features

12. Dynamic PIVOT and Matrix (Crosstab) Scripts

13. INTERSECT and EXCEPT Set Operators

14. FILESTREAM Data and SPARSE Columns

15. Optimizing with Database Engine Tuning Advisor

16. Debugging with the Transact-SQL Debugger