Recently Microsoft released “Exam 70-761: Querying Data with Transact-SQL – Microsoft.” To help you study for this certification, I put together my 70-761 study guide.
This guide is an update from the prior, which covered the 70-461 certification.
The study guide includes every topic you should study to pass the 70-761. The guide is also organized in the order you should study each topic. This is key, as many concepts in SQL build upon themselves.
The guide is organized into ten sections which cover, as a group, twenty two topics:
- SQL Overview – Get started using SQL Server by downloading, installing SQL Server, and using the AdventureWorks database
- Writing Select Queries – Learn to write query a table and return one or more column values.
- Sorting Data – Sort data returned from a query.
- Filtering Data – Not all data is relevant, learn how to exclude those entries you don’t need.
- Data Types – Understand how SQL stored and organizes dates, numbers, and text. Data types define the characteristics of the data that can be stored in a location such as a database column. A data type defines the possible set of values that are accepted.
- Built-In Functions – Built-In functions are used in SQL SELECT expressions to calculate values and manipulate data. These functions can be used anywhere expressions are allowed. Common uses of functions include to change a name to all upper case.
- Grouping and Aggregating Data – Learn to group and summarize data. Use SQL to calculate averages and subtotals.
- Set Operators – Understand how to take rows from two or more queries and combine them into a single result.
- Subqueries – Subqueries provide a powerful means to combine data from two tables in to a single result. In this unit you learn and understand how to use subqueries in various parts of the SQL SELECT statements.
[thrive_leads id=’3900′]
- Common Table Expressions – Common table expressions are used to simplify a query. Since they can reference themselves, they are useful when working with hierarchies.
- Modifying Data – Create, remove, and modify rows within a table.
- Window Functions – Understand how to create running and sliding totals.
- Pivoting and Grouping – Transform table row values into columns and vice versa.
- Stored Procedures – Write procedural code to execute and process SQL statements
- Error Handling – Detect and manage errors encountered as stored procedures and user defined functions execute.
- Transaction and Isolation Levels – SQL operations can be run independently of other users, but to do so, requires some resources to be locked. This prevents two people from working on the same item. The degree you lock and isolate these operations, affects what others “see” of uncommitted transactions and how long they wait until they have full access to read or write to the same resources.
- Creating Tables and Views – Build database tables and views.
- Temporal Tables – System versioned temporal tables contain all changes made to a database table and allow for easy point-in-time analysis of data. The tables maintain a full history of changes.
- Database Integrity – Understand what methods are available to preserve database integrity.
- XML Data – Work with XML data within the context of SQL Server.
- JSON (JavaScript Object Notation) Data – Work with JSON data within the context of SQL Server
- Query Optimization – SQL is a declarative language. That mean you declare what you want the DBMS to do, but it is really up to it to figure out how. The query optimizer helps the DBMS make efficient decisions regarding this.
Now you can see why I create this guide for me and you to use. There is so much to learn! Why not get a copy for yourself?
Here is an example from one section of the exam:
For each main topic, such as temporal tables, the guide details what topics to learn. For instance, when studying temporal tables, it is important to understand how the operators FROMTO and BETWEENAND work.
These sections also correspond to the practice quizzes I’ve created. Together, you have a very powerful combination to help you study for the 70-761 certification exam.
[thrive_leads id=’3900′]
Read More:
