O'Reilly logo

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

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