Books & Videos

Table of Contents

Chapter: Introduction

MCSA Querying Microsoft SQL Server 2012 (Exam 70-461) LiveLessons: Introduction

08m 50s

Chapter: Module 1: Create Database Objects

Create Database Objects

00m 37s

Chapter: Lesson 1: Create and Alter Tables Using T-SQL Syntax

Learning Objectives

00m 26s

1.1 Creating Tables Using the CREATE Statement

05m 22s

1.2 Specifying Schema in Database, Table, and Column Names

04m 12s

1.3 Choosing Column Data Types

12m 26s

1.4 Altering Tables and Columns

04m 40s

1.5 Dropping Tables

02m 20s

1.6 Demo – Examining the Syntax of the CREATE TABLE Statement and Comparing with a Sample

11m 57s

1.7 Demo – Best Practices in Naming Tables and Columns

02m 55s

1.8 Demo – Guideline Samples for Selecting Column Types

05m 15s

1.9 Demo – Using ALTER TABLE to Add and Modify Columns

03m 11s

1.10 Demo – Using DROP TABLE and Noting Consequences

01m 13s

Chapter: Lesson 2: Design, Create, and Alter Views

Learning Objectives

00m 26s

2.1 Analyzing the CREATE VIEW Statement and Options

06m 8s

2.2 Demo – Comparing the CREATE VIEW Syntax with a Sample VIEW Statement and Options

03m 13s

2.3 Creating and Implementing Indexed Views

04m 41s

2.4 Using ALTER VIEW and DROP VIEW

02m 52s

2.5 Using a View to Modify Data

02m 27s

2.6 Demo – Altering a View Using ORDER BY

04m 23s

2.7 Demo – Updating a Table Using a View

04m 5s

Chapter: Lesson 3: Create and Modify Constraints

Learning Objective

00m 30s

3.1 Creating a Primary Key Constraint

06m 12s

3.2 Demo – Defining a Primary Key Constraint Using CREATE TABLE and ALTER TABLE

01m 42s

3.3 Defining and Creating Unique Constraints

03m 5s

3.4 Defining and Creating Foreign Key Constraints

05m 58s

3.5 Defining and Creating Check Constraints

03m 13s

3.6 Defining and Creating Default Constraints

04m 4s

3.7 Demo – Working with Constraints to Enforce Data Integrity

04m 14s

Chapter: Lesson 4: Create and Alter DML Triggers

Learning Objectives

00m 26s

4.1 Using DML Triggers

06m 28s

4.2 Creating and Using AFTER Triggers

05m 1s

4.3 Demo - Using AFTER Trigger

04m 26s

4.4 Defining Nested AFTER Triggers

01m 56s

4.5 Creating and Using INSTEAD OF triggers

03m 37s

4.6 Using Trigger Update Functions

01m 53s

4.7 Handling Multiple Rows in a Session

02m 24s

4.8 Understanding the Performance Implications of Triggers

02m 37s

4.9 Demo - Understanding Inserted and Deleted Tables When Using an AFTER Trigger

04m 32s

Chapter: Module 2: Work With Data

Work With Data

00m 38s

Chapter: Lesson 5: Query Data by Using SELECT Statements

Learning Objectives

00m 36s

5.1 Creating Queries That Filter Data Using Predicates

04m 3s

5.2 Creating Queries to Correctly Handle NULLs

01m 40s

5.3 Writing Queries That Combine Predicates

03m 57s

5.4 Demo - Reviewing Non-Efficient and Efficient Sample Queries

05m 57s

5.5 Controlling the Order of the Query Result Using Best Practices

02m 29s

5.6 Demo - Using ORDER BY, DISTINCT, and Aliases

05m 9s

5.7 Creating Deterministic Query Results

02m 13s

5.8 Ranking Query Results Using TOP(x) and OFFSET – FETCH

02m 29s

5.9 Demo - Using TOP(x) and OFFSET – FETCH to Filter Data

03m 59s

5.10 Using Joins to Query Data over Multiple Tables

07m 37s

5.11 Writing Complex Queries – Joins versus Derived Tables

04m 32s

5.12 Demo - Using Joins to Access Data

07m 42s

5.13 Accessing Data Using CASE, COALESCE, and ISNULL

04m 36s

5.14 Using Statements That Load a Table – INSERT VALUES, INSERT SELECT, INSERT EXEC, and SELECT INTO

05m 30s

5.15 Demo - Using Statements That Load or Create Tables

07m 22s

Chapter: Lesson 6: Implement Subqueries

Learning Objectives

00m 33s

6.1 Using Self-Contained and Correlated Subqueries

05m 1s

6.2 Using Table Expressions – Derived Tables and Common Table Expressions (CTEs)

05m 0s

6.3 Using the APPLY Operator

03m 37s

6.4 Demo – Using Subqueries and Table Expressions in Queries

03m 45s

6.5 Pivoting and Unpivoting Data

06m 20s

6.6 Demo – Using PIVOT and UNPIVOT Operators in Queries

04m 33s

Chapter: Lesson 7:Implement Data Types

Learning Objectives

00m 32s

7.1 Understanding the Uses and Limitations of Each Data Type

08m 6s

7.2 Using Data Types for Columns and Keys

04m 51s

7.3 Using Numeric Data Types

04m 51s

7.4 Using Date and Time Functions

10m 39s

7.5 Demo - Using Data Types for Columns, Keys, and Dates

10m 42s

7.6 Using Binary String Data Types

05m 1s

7.7 Working with Other Data Types such as Uniqueidentifier and XML

01m 7s

7.8 Demo - Using Binary String and Other Data Types

10m 6s

Chapter: Lesson 8: Implement Aggregate Queries

Learning Objectives

00m 30s

8.1 Using a Single Grouping Set

03m 6s

8.2 Using Group Functions

02m 35s

8.3 Defining Multiple Grouping Sets

10m 37s

8.4 Demo - Grouping Data in Queries

14m 23s

8.5 Using Window Aggregate Functions

06m 44s

8.6 Applying Ranking and Offset Functions

04m 18s

8.7 Demo - Using Window Functions in Queries

11m 47s

Chapter: Lesson 9: Query and Manage XML Data

Learning Objectives

00m 29s

9.1 Returning Results as XML

02m 55s

9.2 Demo - Using FOR XML RAW, FOR XML AUTO, and FOR XML PATH

11m 11s

9.3 Querying XML Data Using XQUERY

06m 22s

9.4 Demo - Using XQUERY to Extract Data

06m 55s

9.5 Applying Best Practices to Use the XML Data Type

04m 50s

9.6 Creating XML Indexes

03m 48s

9.7 Demo - Using XML Data Type Methods

04m 5s

Chapter: Module 3: Modify Data

Modify Data

00m 33s

Chapter: Lesson 10: Modify Data by Using INSERT, UPDATE, and DELETE Statements

Learning Objectives

00m 24s

10.1 Understanding Methods Used to Insert Data

04m 21s

10.2 Demo - Using the INSERT SELECT and SELECT INTO Statements

03m 4s

10.3 Understanding Methods Used to Update Data

05m 4s

10.4 Demo - Using the UPDATE Statement with Constraints

07m 30s

10.5 Understanding Methods Used to Delete Data

05m 7s

10.6 Demo - Using the DELETE and TRUNCATE Statements

06m 19s

Chapter: Lesson 11: Combine Datasets

Learning Objectives

00m 23s

11.1 Using the CASE Expression versus ISNULL versus COALESCE

11m 23s

11.2 Understanding the Difference Between UNION and UNION ALL Set Operators

06m 22s

11.3 Demo - Using UNION and UNION ALL to Combine Datasets

04m 41s

11.4 Understanding the MERGE Statement

03m 45s

11.5 Demo - Using the MERGE Statement to Merge Source Data to a Target

05m 0s

Chapter: Lesson 12: Create and Alter Stored Procedures

Learning Objectives

00m 28s

12.1 Creating a Stored Procedure

07m 3s

12.2 Writing a Stored Procedure to Meet a Given Set of Requirements

08m 22s

12.3 Demo – Creating and Executing Stored Procedures with Input and Output Parameters

03m 53s

12.4 Using Branching Logic in a Stored Procedure

05m 49s

12.5 Demo – Using IF/ELSE, WHILE, WAITFOR, GOTO, and RETURN Statements to Control the Procedure Flow

09m 26s

12.6 Implementing a Stored Procedure Used for Data Access Layer

04m 45s

Chapter: Lesson 13: Work with Functions

Learning Objectives

00m 32s

13.1 Understanding Deterministic and Non-Deterministic Functions

05m 12s

13.2 Applying Built-In Scalar Functions

07m 40s

13.3 Creating and Altering User-Defined Functions (UDFs)

08m 23s

13.4 Demo - Creating and Altering User-Defined Functions

06m 13s

13.5 Demo - Implementing Scalar and Table-Valued UDFs

05m 3s

Chapter: Module 4: Troubleshoot and Optimize

Troubleshoot and Optimize

00m 39s

Chapter: Lesson 14: Optimize Queries

Learning Objectives

00m 25s

14.1 Understanding Query Optimizer

06m 2s

14.2 Reading and Analyzing Query Plans

08m 40s

14.3 Understanding Statistics

06m 8s

14.4 Using SET Options

03m 22s

14.5 Demo - Analyzing Queries Using SET Options

08m 32s

14.6 Using Dynamic Management Objects (DMOs) for Query Tuning

03m 4s

14.7 Demo - Using Dynamic Management Objects

08m 34s

14.8 Using Optimizer Hints and Plan Guides

06m 5s

14.9 Describing Joins and Their Applications

09m 27s

14.10 Demo - Applying Optimizer Hints

05m 53s

Chapter: Lesson 15: Manage Transactions

Learning Objectives

00m 29s

15.1 Applying Transaction Commands and Syntax Including Implicit and Explicit Transactions

08m 49s

15.2 Using WITH MARK Statement

02m 32s

15.3 Demo - Controlling Transactions Using BEGIN TRANSACTION, COMMIT TRANSACTION, ROLLBACK TRANSACTION, and @@TRANCOUNT

06m 8s

15.4 Understanding Locking Protocols Including Shared Locks and Exclusive Locks

02m 56s

15.5 Understanding the Isolation Levels in SQL Server 2012 to Control Locking and Row Versioning Behavior

06m 22s

15.6 Demo - Working with Locking Protocols and Transaction Isolation Levels

03m 21s

Chapter: Lesson 16: Evaluate the Use of Row-based Operations vs. Set-Based Operations

Learning Objectives

00m 30s

16.1 Implementing Row-Based Cursor Commands - DECLARE, OPEN, FETCH, DECLARE, and DEALLOCATE

05m 29s

16.2 Demo - Using Cursors to Perform Row Operations

03m 3s

16.3 Built-In Cursor Functions

02m 30s

16.4 Impact of Cursors in Scalar User-Defined Functions

01m 28s

16.5 Implementing Row-Based Operations Without Cursors

02m 30s

16.6 Demo - Computing an Aggregate Using a Cursor and Compute an Aggregate Using a Set-Based Solution

03m 45s

Chapter: Lesson 17: Implement Error Handling

Learning Objectives

00m 28s

17.1 Implementing Try and Catch Blocks to Redirect Errors

06m 36s

17.2 Demo - Using Try and Catch Blocks

02m 42s

17.3 Using the Error Object Function to Return Detailed Error Information

02m 58s

17.4 Implementing THROW

02m 7s

17.5 Demo - Using THROW to Raise an Exception

02m 15s

17.6 Implementing XACT_ABORT

03m 3s

17.7 Demo - Using XACT_ABORT

06m 52s

17.8 Evaluating Set-Based Solutions versus Row-Based Cursor Solutions

03m 56s

17.9 Demo - Using a Set-Based Solution

04m 53s

Chapter: Summary

MCSA Querying Microsoft SQL Server 2012 (Exam 70-461) LiveLessons: Summary

03m 20s