on-demand course

SQL Server 70-761: Querying Data with Transact-SQL

with Marilyn White

August 2017

Beginner to advanced

12h 51m

English

Pearson

Closed Captioning available in English, Japanese, Korean, Chinese (Simplified), Chinese (Traditional)

Watch now

Unlock full access

Includes

Earns Badge

Course outline

SQL Server 70-761: Introduction
4m 35s
Module Introduction
57s
Learning objectives
58s
1.1 Transact-SQL Overview
5m 55s
1.2 Identifying Proper SELECT Query Structure—Predicates and Operators
13m 38s
1.3 Identifying Proper SELECT Query Structure—Elements and Execution
9m 37s
1.4 Lab: Writing Queries That Filter Data Using Predicates
6m 52s
1.5 Writing Specific Queries to Satisfy Business Requirements
10m 56s
1.6 Lab: Reviewing Non-Efficient and Efficient Sample Queries
2m 35s
1.7 Lab: Controlling the Order of the Query Result
5m 37s
1.8 Lab: Understanding All-at-once Operations
7m 28s
1.9 Constructing Results from Multiple Queries Using Set Operators
6m 22s
1.10 Lab: Creating Queries Using Set Operators
4m 2s
1.11 Lab: Examining Precedence Among Set Operators
3m 41s
1.12 Distinguishing Between UNION and UNION ALL Behavior
2m 21s
1.13 Lab: Creating Queries with UNION and UNION ALL
1m 46s
1.14 Identifying the Query That Would Return Expected Results Based on Provided Table Structure and/or Data
3m 5s
Lesson 1 Summary
58s
Learning objectives
46s
2.1 Writing Queries With Join Statements Based on Provided Tables, Data, and Requirements
5m 46s
2.2 Determining Proper Usage of INNER JOIN, LEFT/RIGHT/FULL OUTER JOIN, and CROSS JOIN
9m 50s
2.3 Lab: Implementing Join Statements on Provided Tables
8m 7s
2.4 Lab: Implementing CROSS JOIN on Provided Tables
7m 4s
2.5 Constructing Multiple JOIN Operators Using AND and OR
2m 58s
2.6 Lab: Using Queries with Multiple JOIN Operators
5m 57s
2.7 Determining the Correct Results When Presented with Multi-table SELECT Statements and Source Data
7m 33s
2.8 Writing Queries with NULLs on Joins
4m 14s
2.9 Lab: Using Joins with NULLS
6m 51s
Lesson 2 Summary
1m 19s
Learning objectives
54s
3.1 Constructing Queries Using Scalar-Valued and Table-Valued Functions: Function Components
9m 52s
3.2 Constructing Queries Using Scalar-Valued and Table-Valued Functions: Function Creation and Implementation
7m 53s
3.3 Lab: Implementing Queries Using Scalar-Valued Functions
5m 3s
3.4 Lab: Implementing Queries Using Table-Valued Functions
3m 5s
3.5 Identifying the Impact of Function Usage to Query Performance and WHERE Clause Sargability
2m 56s
3.6 Lab: Analyzing the Query Performance Impact of Function Usage and WHERE Clause
5m 6s
3.7 Identifying the Differences Between Deterministic and Non-Deterministic Functions
1m 59s
3.8 Using Built-In Aggregate Functions
2m 44s
3.9 Using Arithmetic and Date-Related Functions
11m 29s
3.10 Using Logical and System Functions
6m 56s
3.11 Lab: Using Built-In Functions in Queries
9m 8s
Lesson 3 Summary
54s
Learning objectives
43s
4.1 Writing INSERT Statements
6m 19s
4.2 Writing UPDATE Statements
5m 23s
4.3 Writing DELETE Statements and Best Practices for Data Modification
5m 1s
4.4 Determining Which Statements Can Be Used to Load Data to a Table Based on Its Structure and Constraints
5m 48s
4.5 Lab: Using INSERT, UPDATE, and DELETE to Modify the Contents of a Table
22m 41s
4.6 Constructing Data Manipulation Language (DML) Statements Using the OUTPUT Statement
6m 9s
4.7 Lab: Creating DML Statements Using the OUTPUT Statement
6m 26s
4.8 Determining the Results of Data Definition Language (DDL) on Supplied Tables and Data
4m 28s
4.9 Lab: Using DDL Statements
5m 13s
Lesson 4 Summary
1m 1s
Module Introduction
42s
Learning objectives
44s
5.1 Determining the Results of Queries Using Subqueries and Table Joins
6m 38s
5.2 Evaluating Performance Differences Between Table Joins and Correlated Subqueries Based on Provided Data and Query Plans
8m 32s
5.3 Lab: Comparing Subqueries and Table Joins
9m 33s
5.4 Distinguishing Between the Use of CROSS APPLY and OUTER APPLY
3m 33s
5.5 Writing APPLY Statements That Return a Given Data Set Based on Supplied Data
5m 0s
5.6 Lab: Using the APPLY Operator
4m 41s
Lesson 5 Summary
41s
Learning objectives
44s
6.1 Identifying Basic Components of Table Expressions
10m 44s
6.2 Defining Usage Differences Between Table Expressions and Temporary Tables
3m 15s
6.3 Lab: Using Table Expressions
9m 47s
6.4 Constructing Recursive Table Expressions to Meet Business Requirements
5m 21s
6.5 Lab: Using Recursive Table Expressions
5m 0s
Lesson 6 Summary
36s
Learning objectives
55s
7.1 Using Windowing Functions to Group and Rank the Results of a Query
15m 45s
7.2 Distinguishing Between Using Windowing Functions and GROUP BY
2m 24s
7.3 Lab: Using Windowing Functions and GROUP BY
10m 51s
7.4 Constructing Complex GROUP BY Clauses Using GROUPING SETS and CUBE
10m 14s
7.5 Lab: Using GROUPING SETS and CUBE
11m 14s
7.6 Constructing PIVOT and UNPIVOT Statements to Return Desired Results Based on Supplied Data
9m 26s
7.7 Lab: Using PIVOT and UNPIVOT
9m 30s
7.8 Determining the Impact of NULL Values in PIVOT and UNPIVOT Queries
2m 52s
7.9 Lab: Using PIVOT and UNPIVOT in Queries with NULL Values
5m 58s
Lesson 7 Summary
1m 0s

Content preview from SQL Server 70-761: Querying Data with Transact-SQL

7.7 Lab: Using PIVOT and UNPIVOT

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Start your free trial

SQL Server 70-768: Developing SQL Data Models

Scot J. Reagin

SQL Server 70-767: Implementing a Data Warehouse Using SQL

Scot J. Reagin

Securing SQL Server: DBAs Defending the Database

Peter A. Carter

SQL Server 2016 Fundamentals for the Accidental DBA LiveLessons

Eric Johnson

Publisher Resources

ISBN: 9780134769851Supplemental Content

SQL Server 70-761: Querying Data with Transact-SQL

with Marilyn White

Introduction

Module 1: Manage Data with Transact-SQL

Lesson 1: Create Transact-SQL SELECT Queries

Lesson 2: Query Multiple Tables by Using Joins

Lesson 3: Implement Functions and Aggregate Data

Lesson 4: Modify Data

Module 2: Query Data With Advanced Transact-SQL Components

Lesson 5: Query Data by Using Subqueries and Apply

Lesson 6: Query Data by Using Table Expressions

Lesson 7: Group and Pivot Data by Using Queries

Lesson 8: Query Temporal Data and Non-Relational Data

Module 3: Program Databases by Using Transact-SQL

Lesson 9: Create Database Programmability Objects by Using Transact-SQL

Lesson 10: Implement Error Handling and Transactions

Lesson 11: Implement Data Types and NULLS

Summary

Content preview from SQL Server 70-761: Querying Data with Transact-SQL

7.7 Lab: Using PIVOT and UNPIVOT

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

You might also like

SQL Server 70-768: Developing SQL Data Models

SQL Server 70-767: Implementing a Data Warehouse Using SQL

Securing SQL Server: DBAs Defending the Database

SQL Server 2016 Fundamentals for the Accidental DBA LiveLessons

Publisher Resources

Introduction

Module 1: Manage Data with Transact-SQL

Lesson 1: Create Transact-SQL SELECT Queries

Lesson 2: Query Multiple Tables by Using Joins

Lesson 3: Implement Functions and Aggregate Data

Lesson 4: Modify Data

Module 2: Query Data With Advanced Transact-SQL Components

Lesson 5: Query Data by Using Subqueries and Apply

Lesson 6: Query Data by Using Table Expressions

Lesson 7: Group and Pivot Data by Using Queries

Lesson 8: Query Temporal Data and Non-Relational Data

Module 3: Program Databases by Using Transact-SQL

Lesson 9: Create Database Programmability Objects by Using Transact-SQL

Lesson 10: Implement Error Handling and Transactions

Lesson 11: Implement Data Types and NULLS

Summary

Content preview from SQL Server 70-761: Querying Data with Transact-SQL

7.7 Lab: Using PIVOT and UNPIVOT

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,and much more.

You might also like

SQL Server 70-768: Developing SQL Data Models

SQL Server 70-767: Implementing a Data Warehouse Using SQL

Securing SQL Server: DBAs Defending the Database

SQL Server 2016 Fundamentals for the Accidental DBA LiveLessons

Publisher Resources

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.