Advertisements
Home > Certifications, SQL Server 2016 > Preparing for Exam 70-761, Querying Data with Transact-SQL | Certification

Preparing for Exam 70-761, Querying Data with Transact-SQL | Certification


 
This exam (70-761) will earn you MCP in SQL Server 2016 Querying Data with Transact-SQL. It is 1 out of the 2 exams to earn the “MCSA: SQL 2016 Database Development” certification.

I will discuss about the other “Exam 70-762, Developing SQL Databases” in my next post.
 

Training Kit Book:

For exam preparation you can use: Exam Ref 70-761 Querying Data with Transact-SQL
 

So, let’s go and understand this exam, links to study material and how to prepare for this.
 

–> This exam is targeted for students or professionals who want to learn about Transact-SQL or simply T-SQL, which includes:
1. Know about SQL Server and its components
2. Write single/multi table SELECT statements, with SET and Predicate logic
3. Apply Filtering, Sorting, JOIN, etc with SQL Queries
4. Write DDL, DML SQL Statements to Store and Retrieve data to & from Tables
5. Create Views, Stored Procedures, Functions, etc
6. Use inbuilt Functions and Transform data
7. Work with SQL Datatypes, Variables, Conditions, Loops and T-SQL code/scripts
 

The exam is divided into multiple modules:

1. Introduction to Microsoft SQL Server 2016
    – The Basic Architecture of SQL Server [Architecture]
    – SQL Server on-premise and cloud-based Editions and Versions [Editions]
    – Getting Started with SSMS [SSMS]

2. Introduction to T-SQL Querying
    – Introducing T-SQL with basic SELECT Statements [SELECT]
    – Understanding Sets and SET based Queries in relational DBs
    – Understanding Predicate Logic to Filter Data [WHERE clause, PREDICATES]
    – Understanding the Logical Order of Operations in SELECT statements [Logical Order]

3. Writing SELECT Queries
    – Writing Simple SELECT Statements, its structure and format [SELECT Queries]
    – Eliminating Duplicates with DISTINCT clause [DISTINCT]
    – Using Column and Table Aliases
    – Writing Simple CASE Expressions [CASE]

4. Querying Multiple Tables
    – Understanding Joins
    – Querying with Inner Joins, Outer Joins, Cross Joins and Self Joins

5. Sorting and Filtering Data
    – Sorting Data with ORDER BY clause
    – Filtering Data with WHERE clause Predicates
    – Filtering Data with TOP and OFFSET-FETCH
    – Working with Unknown/Missing Values or three-valued logic (NULL)

6. Working with SQL Server 2016 Data Types
    – Introducing SQL Server 2016 Data Types
    – Working with Character Data
    – Working with Date and Time Data
    – Working with data types conversion

7. Using DML to Modify Data
    – Inserting Data with INSERT and SELECT INTO statements
    – Modifying and Deleting Data with UPDATE, MERGE, DELETE, and TRUNCATE statements

8. Using Built-In Functions
    – Writing Queries with Built-In Functions
    – Using Conversion Functions (CAST & CONVERT)
    – Using Logical Functions
    – Using Functions to Work with NULL

9. Grouping and Aggregating Data
    – Using built-in Aggregate Functions
    – Using the GROUP BY Clause
    – Filtering Groups with HAVING

10. Using Subqueries
    – Writing Self-Contained Subqueries
    – Writing Correlated Subqueries
    – Using the EXISTS Predicate with Subqueries

11. Using Table Expressions
    – Using Views
    – Using Inline Table-Valued Functions (TVFs)
    – Using Derived Tables
    – Using Common Table Expressions (CTEs) [CTE]

12. Using Set Operators
    – Writing Queries with the UNION operator (and UNION ALL)
    – Using EXCEPT and INTERSECT operators
    – Using APPLY operators (CROSS APPLY and OUTER APPLY)

13. Using Windows Ranking, Offset, and Aggregate Functions
    – Creating Windows with OVER clause with partitioning, ordering, and framing
    – Exploring Window Aggregate and Ranking Functions

14. Pivoting and Grouping Sets
    – Writing Queries with [PIVOT and UNPIVOT]
    – Working with Grouping Sets, CUBE and ROLLUP Subclauses
    – Using the GROUPING_ID function

15. Executing Stored Procedures
    – Querying Data with Stored Procedures
    – Creating Simple Stored Procedures
    – Passing Parameters to Stored procedures
    – Stored Procedures with output parameters
    – Working with Dynamic SQL

16. Programming with T-SQL
    – T-SQL Programming Elements (Variables, Batches, )
    – Using variables in a Dynamic SQL Statement
    – Controlling Program Flow (IF-ELSE condition, WHILE loop)
    – Working with Synonyms

17. Implementing Error Handling
    – Implementing T-SQL error handling with TRY/CATCH block
    – Implementing structured exception handling with THROW keyword

18. Implementing Transactions
    – Transactions and the database engines
    – Differences between batches and transactions
    – Controlling transactions with BEGIN, COMMIT, and ROLLBACK
    – Error handling with the CATCH block
    – Use of SET XACT_ABORT while handling Transactions


 
Microsoft official link to this exam: https://www.microsoft.com/en-in/learning/course.aspx?cid=20761

Book available as a Training Kit on Amazon, buy it.

SQL Server 2016 articles on my blog: https://sqlwithmanoj.com/sql-server-2016-articles/


Advertisements
  1. August 27, 2018 at 3:23 pm

    Sir,
    Can you tell me where to register for this exam? The links thrown up by google are the prometrics which is no longer operational, as (i think) prometric is no longer a partner for MS certifications.

    • August 27, 2018 at 5:09 pm

      You can click on the “Schedule Exam” button/link and it will take you to another page with your personal details, and then to “Pearson VUE” partner site to schedule the exam with location.

  2. Sarah
    August 2, 2018 at 10:08 am

    I have only MTA certification so am I eligible to give this exam (70-761), Querying Data with Transact-SQL and can you please mention the fee for this exam.

    • August 2, 2018 at 10:20 am

      yes, you are eligible, for this certification there is no prerequisite.
      Best of luck !!!
      and don’t forget to share your experience 🙂

  3. Bahruz Ibrahimov
    December 20, 2017 at 11:22 pm

    Who can share this book with me for free?

    • August 2, 2018 at 10:22 am

      There is no free book I think, but you can search for individual content items in MSDN Docs and prepare for the exam, do check my blog post on “SQL Serer 2016” also for reference.

  1. January 15, 2018 at 7:24 pm

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: