Exam Ref 70-761 Querying Data with Transact-SQL, First Edition

Exam Ref 70-761 Querying Data with Transact-SQL, First Edition

by Itzik Ben-Gan
Released March 2017
Publisher(s): Microsoft Press
ISBN: 9781509304349

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

Prepare for Microsoft Exam 70-761–and help demonstrate your real-world mastery of SQL Server 2016 Transact-SQL data management, queries, and database programming. Designed for experienced IT professionals ready to advance their status, Exam Ref focuses on the critical-thinking and decision-making acumen needed for success at the MCSA level.

Focus on the expertise measured by these objectives:

• Filter, sort, join, aggregate, and modify data

• Use subqueries, table expressions, grouping sets, and pivoting

• Query temporal and non-relational data, and output XML or JSON

• Create views, user-defined functions, and stored procedures

• Implement error handling, transactions, data types, and nulls

This Microsoft Exam Ref:

• Organizes its coverage by exam objectives

• Features strategic, what-if scenarios to challenge you

• Assumes you have experience working with SQL Server as a database administrator, system engineer, or developer

• Includes downloadable sample database and code for SQL Server 2016 SP1 (or later) and Azure SQL Database

Querying Data with Transact-SQL

About the Exam

Exam 70-761 focuses on the skills and knowledge necessary to manage and query data and to program databases

with Transact-SQL in SQL Server 2016.

About Microsoft Certification

Passing this exam earns you credit toward a Microsoft Certified Solutions Associate (MCSA) certification that demonstrates your mastery of essential skills for building and implementing on-premises and cloud-based databases across organizations. Exam 70-762 (Developing SQL Databases) is also required for MCSA: SQL 2016 Database Development certification.

See full details at: microsoft.com/learning

Table of contents

  1. Cover
  2. Title Page
  3. Copyright Page
  4. Dedication Page
  5. Contents at a glance
  6. Contents
  7. Introduction
    1. Organization of this book
    2. Microsoft certifications
    3. Acknowledgments
    4. Free ebooks from Microsoft Press
    5. Microsoft Virtual Academy
    6. Quick access to online references
    7. Errata, updates, & book support
    8. We want to hear from you
    9. Stay in touch
  8. Important: How to use this book to study for the exam
  9. Chapter 1. Manage data with Transact-SQL
    1. Skill 1.1: Create Transact-SQL SELECT queries
      1. Understanding the foundations of T-SQL
      2. Understanding logical query processing
      3. Getting started with the SELECT statement
      4. Filtering data with predicates
      5. Sorting data
      6. Filtering data with TOP and OFFSET-FETCH
      7. Combining sets with set operators
    2. Skill 1.2: Query multiple tables by using joins
      1. Cross joins
      2. Inner joins
      3. Outer joins
      4. Queries with composite joins and NULLs in join columns
      5. Multi-join queries
    3. Skill 1.3: Implement functions and aggregate data
      1. Type conversion functions
      2. Date and time functions
      3. Character functions
      4. CASE expressions and related functions
      5. System functions
      6. Arithmetic operators and aggregate functions
      7. Search arguments
      8. Function determinism
    4. Skill 1.4: Modify data
      1. Inserting data
      2. Updating data
      3. Deleting data
      4. Merging data
      5. Using the OUTPUT option
      6. Impact of structural changes on data
    5. Chapter summary
    6. Thought experiment
    7. Thought experiment answer
  10. Chapter 2. Query data with advanced Transact-SQL components
    1. Skill 2.1: Query data by using subqueries and APPLY
      1. Subqueries
      2. The APPLY operator
    2. Skill 2.2: Query data by using table expressions
      1. Table expressions, described
      2. Table expressions or temporary tables?
      3. Derived tables
      4. Common table expressions
      5. Views and inline table-valued functions
    3. Skill 2.3: Group and pivot data by using queries
      1. Writing grouped queries
      2. Pivoting and Unpivoting Data
      3. Using Window Functions
    4. Skill 2.4: Query temporal data and non-relational data
      1. System-versioned temporal tables
      2. Query and output XML data
      3. Query and output JSON data
    5. Chapter summary
    6. Thought experiment
    7. Thought experiment answer
  11. Chapter 3. Program databases by using Transact-SQL
    1. Skill 3.1: Create database programmability objects by using Transact-SQL
      1. Views
      2. User-defined functions
      3. Stored procedures
    2. Skill 3.2: Implement error handling and transactions
      1. Understanding transactions
      2. Error handling with TRY-CATCH
    3. Skill 3.3: Implement data types and NULLs
      1. Working with data types
      2. Handling NULLs
    4. Chapter summary
    5. Thought experiment
    6. Thought experiment answer
  12. Index
  13. About the author

Product information

  • Title: Exam Ref 70-761 Querying Data with Transact-SQL, First Edition
  • Author(s): Itzik Ben-Gan
  • Release date: March 2017
  • Publisher(s): Microsoft Press
  • ISBN: 9781509304349