MOC 20461C: Querying Microsoft SQL Server Course Overview
This course provides students with the knowledge and skills to query Microsoft SQL Server. Students will learn about T-SQL querying, SQL Server 2014 data types, using built-in functions, executing stored procedures, and more.
Course Introduction 4m Course Introduction
Module 01 - Introduction to Microsoft SQL Server 2014 55m
Lesson 1: The Basic Architecture of SQL Server Relational Databases
About the Course Sample Database Client Server Databases
Queries
Lesson 2: SQL Server Editions and Versions SQL Server Versions
SQL Server Editions SQL Server in the Cloud
Lesson 3: Getting Started with SQL Server Management Studio Starting SSMS
Connecting to SQL Server Working with Object Explorer Working with Script Files and Projects Executing Queries
Using Books Online
Demo - Introducing Microsoft SQL Server 2014 Module 01 Review
Module 02 - Introduction to T-SQL Querying 1h 3m
Lesson 1: Introducing T-SQL About T-SQL
Categories of T-SQL Statements T-SQL Language Elements
T-SQL Language Elements: Predicates and Operators T-SQL Language Elements: Functions
T-SQL Language Elements: Variables T-SQL Language Elements: Expressions
T-SQL Language Elements: Control of Flow, Errors, and Transactions T-SQL Language Elements: Comments
T-SQL Language Elements: Batch Separators Demo - T-SQL Language Elements
Lesson 2: Understanding Sets Set Theory and SQL Server
Set Theory Applied to SQL Server Queries Lesson 3: Understanding Predicate Logic Predicate Logic and SQL Server
Predicate Logic Applied to SQL Server Queries
Lesson 4: Understanding the Logical Order of Operations in SELECT Statements Elements of a SELECT Statement
Logical Query Processing
Applying the Logical Order of Operations to Writing SELECT Statements Demo - Logical Query Processing
Module 02 Review
Module 03 - Writing SELECT Queries 40m
Lesson 1: Writing Simple SELECT Statements Elements of the SELECT Statement
Retrieving Columns from a Table or View Displaying Columns
Using Calculations in the SELECT Clause Demo - Writing Simple SELECT Statements Lesson 2: Eliminating Duplicates with DISTINCT SQL Sets and Duplicate Rows
Understanding DISTINCT SELECT DISTINCT Syntax
Demo - Eliminating Duplicates with DISTINCT Lesson 3: Using Column and Table Aliases Using Aliases to Refer to Columns Using Aliases to Refer to Tables
The Impact of Logical Processing Order on Aliases Demo - Using Column and Table Aliases
Lesson 4: Writing Simple CASE Expressions Using CASE Expressions in SELECT Clauses Forms of CASE Expressions
Demo - Using a Simple CASE Expression Module 03 Review
Module 04 - Querying Multiple Tables 53m
Lesson 1: Understanding Joins The FROM Clause and Virtual Tables Join Terminology: Cartesian Product Overview of Join Types
T-SQL Syntax Choices Demo - Understanding Joins Lesson 2: Querying with Inner Joins Understanding Inner Joins
Inner Join Syntax Inner Join Examples
Demo - Querying with Inner Joins Lesson 3: Querying with Outer Joins Understanding Outer Joins
Outer Join Syntax Outer Join Examples
Demo - Querying with Outer Joins
Lesson 4: Querying with Cross Joins and Self Joins Understanding Cross Joins
Cross Join Syntax Cross Join Examples Understanding Self Joins Self Join Examples
Demo - Querying with Cross Joins and Self Joins Module 04 Review
Module 05 - Sorting and Filtering Data 44m Lesson 1: Sorting Data
Using the ORDER BY Clause ORDER BY Clause Syntax ORDER BY Clause Examples Demo - Sorting Data
Lesson 2: Filtering Data with Predicates
Filtering Data in the WHERE Clause with Predicates WHERE Clause Syntax
Demo - Filtering Data with Predicates
Lesson 3: Filtering Data with TOP and OFFSET-FETCH Filtering in the SELECT Clause Using the TOP Option Filtering in the ORDER BY Clause Using OFFSET-FETCH OFFSET-FETCH Syntax
Demo - Filtering Data with TOP and OFFSET-FETCH Lesson 4: Working with Unknown Values
Three-Valued Logic Handling NULL in Queries Demo - Working with NULL Module 05 Review
Module 06 - Working with SQL Server 2014 Data Types 1h
Lesson 1: Introducing SQL Server 2014 Data Types SQL Server Data Types
Numeric Data Types Binary String Data Types Other Data Types Data Type Precedence
When Are Data Types Converted?
Demo - SQL Server Data Types Lesson 2: Working with Character Data Character Data Types
Collation
String Concatenation Character String Functions The LIKE Predicate
Demo - Working with Character Data Lesson 3: Working with Date and Time Data Date and Time Data Types
Date and Time Data Types: Literals Working with Date and Time Separately Querying Date and Time Values Date and Time Functions
Demo - Working with Date and Time Data Module 06 Review
Module 07 - Using DML to Modify Data 40m
Lesson 1: Adding Data to Tables Using INSERT to Add Data
Using INSERT with DEFAULT Constraints Using INSERT with SELECT and EXEC Using SELECT INTO
Demo - Inserting Data Into Tables Lesson 2: Modifying and Removing Data Using UPDATE to Modify Data
Using MERGE to Modify Data Using DELETE to Remove Data
Using TRUNCATE TABLE to Remove Data
Demo - Modifying and Removing Data From Tables Lesson 3: Generating Numbers
Using IDENTITY Using Sequences Module 07 Review
Module 08 - Using Built-In Functions 44m
Lesson 1: Writing Queries with Built-In Functions SQL Server Built-In Function Types
Scalar Functions Aggregate Functions Window Functions Rowset Functions
Demo - Writing Queries Using Built-In Functions Lesson 2: Using Conversion Functions
Implicit and Explicit Data Type Conversions Converting with CAST
Converting with CONVERT Converting Strings with PARSE
Converting with TRY_PARSE and TRY_CONVERT Demo - Using Conversion Functions
Lesson 3: Using Logical Functions Writing Logical Tests with Functions Performing Conditional Tests with IIF Selecting Items from a List with CHOOSE Demo - Using Logical Functions
Lesson 4: Using Functions to Work with NULL Converting NULL with ISNULL
Using COALESCE to Return Non-NULL Values Using NULLIF to Return NULL If Values Match Demo - Using Functions to Work with NULL Module 08 Review
Module 09 - Grouping and Aggregating Data 35m
Lesson 1: Using Aggregate Functions Working with Aggregate Functions Built-In Aggregate Functions
Using DISTINCT with Aggregate Functions Using Aggregate Functions with NULL Demo - Using Aggregate Functions Lesson 2: Using the GROUP BY Clause Using the GROUP BY Clause
GROUP BY and the Logical Order of Operations GROUP BY Workflow
Using GROUP BY with Aggregate Functions Demo - Using GROUP BY
Lesson 3: Filtering Groups with HAVING
Filtering Grouped Data Using the HAVING Clause Compare HAVING to WHERE
Demo - Filtering Groups with HAVING Module 09 Review
Module 10 - Using Subqueries 22m Lesson 1: Writing Self-Contained Subqueries
Working with Subqueries Writing Scalar Subqueries Writing Multi-Valued Subqueries
Demo - Writing Self-Contained Subqueries Lesson 2: Writing Correlated Subqueries Working with Correlated Subqueries Writing Correlated Subqueries Demo - Writing Correlated Subqueries
Lesson 3: Using the EXISTS Predicate with Subqueries Working with EXISTS
Writing Queries Using EXISTS with Subqueries Demo - Writing Subqueries Using EXISTS Module 10 Review
Module 11 - Using Table Expressions 56m
Lesson 1: Using Views
Writing Queries That Return Results from Views Creating Simple Views
Demo - Using Views Lesson 2: Using Inline TVFs
Writing Queries That Use Inline TVFs Creating Simple Inline TVFs
Retrieving from Inline TVFs Demo - Inline TVFs
Lesson 3: Using Derived Tables Writing Queries with Derived Tables Guidelines for Derived Tables
Using Aliases for Column Names in Derived Tables Passing Arguments to Derived Tables
Nesting and Reusing Derived Tables Demo - Using Derived Tables Lesson 4: Using CTEs Writing Queries with CTEs
Creating Queries with Common Table Expressions Demo - Using CTEs
Module 11 Review
Module 12 - Using Set Operators 33m
Lesson 1: Writing Queries with the UNION Operator Interactions Between Sets
Using the UNION Operator Using the UNION ALL Operator Demo - Using UNION and UNION ALL Lesson 2: Using EXCEPT and INTERSECT Using the INTERSECT Operator
Using the EXCEPT Operator
Demo - Using EXCEPT and INTERSECT Lesson 3: Using APPLY
Using the APPLY Operator Using the CROSS APPLY Operator Using the OUTER APPLY Operator
Demo - Using CROSS APPLY and OUTER APPLY Module 12 Review
Module 13 - Using Window Ranking, Offset, and Aggregate Functions 43m Lesson 1: Creating Windows with OVER
SQL Windowing
Windowing Components Using OVER
Partitioning Windows Ordering and Framing
Demo - Using OVER and Partitioning Lesson 2: Exploring Window Functions Defining Window Functions
Window Aggregate Functions Window Ranking Functions Window Distribution Functions Window Offset Functions
Demo - Exploring Windows Functions Module 13 Review
Module 14 - Pivoting and Grouping Sets 28m
Lesson 1: Writing Queries with PIVOT and UNPIVOT What Is Pivoting?
Elements of PIVOT
Writing Queries with UNPIVOT
Demo - Writing Queries with PIVOT and UNPIVOT Lesson 2: Working with Grouping Sets
Writing Queries with Grouping Sets CUBE and ROLLUP
GROUPING_ID
Demo - Using Grouping Sets Module 14 Review
Module 15 - Executing Stored Procedures 56m
Lesson 1: Querying Data with Stored Procedures Examining Stored Procedures
Executing Stored Procedures
Demo - Querying Data with Stored Procedures Lesson 2: Passing Parameters to Stored Procedures Passing Input Parameters to Stored Procedures Working with OUTPUT Parameters
Demo - Passing Parameters to Stored Procedures Lesson 3: Creating Simple Stored Procedures Creating Procedures to Return Rows Creating Procedures That Accept Parameters Demo - Creating Simple Stored Procedures Lesson 4: Working with Dynamic SQL Constructing Dynamic SQL
Writing Queries with Dynamic SQL Demo - Working with Dynamic SQL Module 15 Review
Module 16 - Programming with T-SQL 44m Lesson 1: T-SQL Programming Elements
Introducing T-SQL Batches Working with Batches Introducing T-SQL Variables Working with Variables Working with Synonyms
Demo - T-SQL Programming Elements Lesson 2: Controlling Program Flow
Understanding T-SQL Control-of-Flow Language Working with IF…ELSE
Working with WHILE
Demo - Controlling Program Flow Module 16 Review
Module 17 - Implementing Error Handling 42m
Lesson 1: Implementing T-SQL Error Handling Errors and Transactions
Raising Errors Using RAISERROR Raising Errors Using THROW Using @@Error
Transaction Nesting Errors Raising Custom Errors
Creating Alerts When Errors Occur Demo - Handling Errors Using T-SQL
Lesson 2: Implementing Structured Exception Handling TRY/CATCH Block Programming
Error Handling Functions
Catchable vs. Non-catchable Errors Rethrowing Errors Using THROW TRY/CATCH and Transactions Errors in Managed Code
Demo - Applying Retry Logic to Deadlocks Module 17 Review
Module 18 - Implementing Transactions 29m
Lesson 1: Transactions and the Database Engine Defining Transactions
The Need for Transactions: Issues with Batches Transactions Extend Batches
Demo - Transactions and the Database Engine Lesson 2: Controlling Transactions
BEGIN TRANSACTION COMMIT TRANSACTION ROLLBACK TRANSACTION Using XACT_ABORT
Demo - Controlling Transactions Module 18 Review
Module 19 - Improving Query Performance 48m
Lesson 1: Factors in Query Performance Writing Well-Performing Queries Indexing in SQL Server
SQL Server Index Basics: Clustered Indexes SQL Server Index Basics: Nonclustered Indexes
SQL Server Indexes: Performance Considerations Distribution Statistics
Defining Cursors Avoiding Cursors
Demo - Factors in Query Performance Lesson 2: Displaying Query Performance Data What Is an Execution Plan?
Actual and Estimated Execution Plans Viewing Graphical Execution Plans Interpreting the Execution Plan Displaying Query Statistics
Demo - Displaying Query Performance Data Module 19 Review
Module 20 - Querying SQL Server Metadata 38m
Lesson 1: Querying System Catalog Views and Functions System Catalog Views
Information Schema Views System Metadata Functions
Demo - Querying System Catalog Views and Functions Lesson 2: Executing System Stored Procedures
Executing Stored Procedures Executing System Stored Procedures Common System Stored Procedures
Demo - Executing System Stored Procedures Lesson 3: Querying Dynamic Management Objects About Dynamic Management Objects
Categorizing DMVs
Querying Dynamic Management Views and Functions Demo - Querying Dynamic Management Objects Module 20 Review
Course Closure
