Session 1
Section A: Database Tables
Tables
Representing Data with Tables
SQL Server Management Studio
Section B: Database Relationships
Flat File Databases
Relational Databases
RDBMS Benefits
Related Tables
Review
Recursive Relationships
M:N Relationships
Section C: Understanding DML
Structured Query Language
DDL/DML
Data Control Language
Section D: Database Data Types
Data Types
Choosing a Data Type
SQL Data Types
Integers
Exact Numbers
Money Data Types
Exceeding Data Type Limits
Approximate Numerics
Character Data Types
Variable vs. Fixed
Dates and Times
Numeric and Alphabetic Date Formats
DateTime2(n)
ISO 8601 Format
DateTimeOffset(n)
Section E: Creating and Manipulating Tables
Table Types
Simple Syntax
NULL or NOT NULL
Creating a Table
Adding Columns using ALTER TABLE
Adding Columns with Default Values
Changing Columns using ALTER TABLE
UNIQUE Constraints
Adding UNIQUE Contraints
Section F: Selecting Data
SELECT Statement
Performing a SELECT
Selecting Data using Expressions
Specifiying Column Order and Aliases
Special Character Considerations
Using Operators and Expressions
Schema Defined
Character Operators
Arithmetic Expressions
Using Table Aliases
Section G: Filtering Data using Comparisons
WHERE Clauses
Comparison Operators
Logical Operators
Using WHERE Clauses
Using Comparison Operators
Combining using AND
Combining using OR and NOT
Session 2
Section A: Filtering Data using Lists and Ranges
Range of Values
Selecting a Range of Values
Selecting Values in a List
Section B: Filtering Data using Pattern Matching
Understanding String Pattern Matching
String Comparison Operators
Using LIKE Operators
Percent (%) Wildcards
Underscore (_)Wildcards
Braces ([]) Wildcards
Section C: Filtering Data with NULLs
NULLs Defined
NULL Handling
Section D: Selecting Data using Functions
Aggregate Function Types
Section E: Sorting Data
Sorting Data with ORDER BY
Ordinal Column Position
Using ORDER BY
ORDER BY Ordinal Column Position
Section F: Working with Duplicates
Eliminating Duplicates
Using DISTINCT
Section G: Joining Data
Introduction to JOINs
JOIN Process
INNER JOIN
INNER JOIN Example
Joining Data with Table Aliases
INNER JOIN with DISTINCT
Section H: OUTER and CROSS JOINs
OUTER JOIN Defined
OUTER JOIN Example
FULL OUTER JOIN Example
CROSS JOIN Defined
CROSS JOIN Example
Section I: Combining and Limiting Result Sets
UNIONs
UNION ALL vs. UNION
UNION Correct Syntax
Limiting Rows using TOPN
INTERSECT Defined
EXCEPT Defined
INTERSECT and EXCEPT Example
Session 3
Section A: Adding Data
Inserting Data
INSERT Example
Inserting Multiple Rows
Inserting Partial Values
Section B: Updating and Deleting Data
Updating Data
UPDATE Example
Updating Multiple Rows and Columns
UPDATE Example Continued
Deleting Data
DELETE Example
Deleting Data using Subqueries
Understanding Transactions
Creating and Commiting a Transaction
Section C: Working with Views
View Defined
View Types
Standard Views
CREATE VIEW Example
Using a View to Rename Columns
Filtering Data with Views
Creating Views Graphically
Section D: Stored Procedures and Functions
Stored Procedures Defined
SP Parameters and Best Practices
CREATE PROC Example
Late Binding
Using Parameters with Stored Procedures
Built-in vs. User-Defined Functions
Function Parameters
Sample Scalar Function Syntax
Proper Function Body Syntax
Calling Scalar Functions
Table-Valued Functions
Scalar Function Example
Section E: Data Normalization
Database Design Phases
Understanding Database Normalization
Design Problems
Row INSERT Anomalies
DELETE Anomalies
UPDATE Anomalies
Section F: First Normal Form
Overview of Normal Forms
First Normal Form Rules
First Normal Form Example 1
Candidate Keys Defined
Primary Keys Defined
Non-Prime Attributes Defined
First Normal Form Example 2
First Normal Form Anomalies
Section G: Second and Third Normal Forms
Functional Dependency Defined
Second Normal Form Rules
Second Normal Form Example
Second Normal Form Rules Continued
Transitive Dependencies
Third Normal Form
Section H: Fourth and Fifth Normal Forms
BCNF
Fourth Normal Form Example
Fifth Normal Form Example
Session 4
Section A: Primary, Foreign, and Composite Keys
Primary Keys Defined
Foreign Keys
Composite Keys
Creating a Table with a Primary Key
Section B: Clustered Indexes
Overview
Indexes Improve Table Scans
Seek
Clustered Index Benefits
Data Storage Considerations
Clustered Index on a Heap Table
Section C: Non-Clustered Indexes
Non-Clustered Index Basics
Non-Clustered Index on a Heap Table
Non-Clustered Index Benefits
Index Creation
Dropping an Index
Non-Indexed Table Execution Plan
Indexed Table Execution Plan
Section D: Database Security Concepts
SQL Security Steps
Fixed Server Roles
Permissions Hierarchy
Adding Database Users
Special Database Users
Permissions
Database Roles
Creating a Role
Role Usage
Section E: Database Backup and Restore
SQL Backup Methods
Backup Rights and Permissions
Backing Up to Disk
Backup Frequency Considerations
Full Backups
Full Backup Syntax
Restore vs. Recovery
Automatic Recovery
Performing a Full Backup
Backup with Compression
Restore Types
Restore Process
Simple Restore
Section F: Incremental Backups
Incremental Backup Usage
Incremental Backups Defined
Normal Log Backup
Backup Log Options
Restoring the Log
Transaction Log Backup Example
Transaction Log Restore Example