Request new password

We Are All About You

XpertSkills - the parent company under which iCollege operates - was established in South Africa in 2003 and since then, we have grown globally in leaps and bounds with customers and partners in more than 120 countries. With employees on 3 continents and as a next-generation, virtual organization we are unconstrained by the limits that are usually associated with traditional “brick & mortar” companies.

This allows us to focus, without distraction, on reaching our ultimate goal – ensuring your ongoing success – by providing affordable, reliable, high quality and relevant learning products whilst remaining innovative and agile.

Contact Details:

Web: www.xpertskills.com
Skype: xpertskills
Mail: info@xpertskills.com

Physical Address:

Head Office - South Africa
16 Monte Carlo Crescent
Kyalami business Park
Johannesburg
South Africa 1684

You are here

Microsoft 70-761:Querying Data with Transact-SQL

LIVE LAB:

Microsoft 70-761:Querying Data with Transact-SQL

Practical exercises in a live environment to help you get real-world, hands-on experience.

Overview

The 70-761 Practice Lab will provide you with the necessary platform to gain hands on skills Microsoft SQL Server 2016. By completing the lab tasks you will improve your practical skills in managing data with Transact-SQL, querying data with advanced Transact-SQL components and programming databases using Transact-SQL. These same tasks will help you understand the objectives and competencies required by the Microsoft 70-761 Querying Data with Transact-SQL exam.

Vendor:   Microsoft
Vendor Id:   70-761
Version:   1
Level:   Entry
Learning Time:   610  Minutes

Exercises

  1. Working with SELECT Queries
  2. Exercises:
    • Introduction
    • Exercise 1 - Filter Data Based on Predicates
    • Exercise 2 - Fetch Unique Rows Using DISTINCT Clause
    • Exercise 3 - Fetch Rows from Multiple Tables Using Aliases
    • Exercise 4 - Use of TOP and OFFSET-FETCH Filters in SELECT Queries
    • Exercise 5 - Sort Query Results Using ORDER BY Clause
    • Exercise 6 - Use Simple CASE Expression with SELECT Statement
    • Summary
  3. Working with SET Operators
  4. Exercises:
    • Introduction
    • Exercise 1 - Use EXCEPT and INTERSECT Operators to Combine Query Results
    • Exercise 2 - Use UNION Operator to Combine Results of Two SELECT Statements
    • Exercise 3 - Use ALL and Parentheses to Combine Results of Multiple SELECT Statements
    • Summary
  5. Using Joins
  6. Exercises:
    • Introduction
    • Exercise 1 - Using INNER JOIN
    • Exercise 2 - Working with LEFT OUTER JOIN
    • Exercise 3 - Using RIGHT OUTER JOIN
    • Exercise 4 - Working with FULL OUTER JOIN
    • Exercise 5 - Using SELF JOIN
    • Exercise 6 - Working with CROSS JOIN
    • Exercise 7 - Demonstrating the Method to Use More Than One JOIN with AND Operator
    • Exercise 8 - Demonstrating the Method to Compare Fields to NULL Values Using JOIN
    • Summary
  7. Implementing Functions
  8. Exercises:
    • Introduction
    • Exercise 1 - Working with Scalar Functions
    • Exercise 2 - Using Table-Valued Functions in Transact-SQL
    • Exercise 3 - Demonstrating the Impact of Using Functions with WHERE clause on Query Performance
    • Summary
  9. Working with Aggregate Data
  10. Exercises:
    • Introduction
    • Exercise 1 - Using Aggregate Functions in SELECT Statement
    • Exercise 2 - Working with Mathematical and DateTime Functions
    • Exercise 3 - Working with System Functions
    • Summary
  11. Working with Built-in Functions
  12. Exercises:
    • Introduction
    • Exercise 1 - Using Built-in Conversion Functions
    • Exercise 2 - Working with Built-in Logical Functions
    • Exercise 3 - Using Functions that Work with NULL
    • Summary
  13. Modifying Data
  14. Exercises:
    • Introduction
    • Exercise 1 - Inserting New Records in the Table
    • Exercise 2 - Updating and Deleting Data from the Table
    • Exercise 3 - Using OUTPUT Clause with INSERT and DELETE Statements
    • Summary
  15. Working with Subqueries
  16. Exercises:
    • Introduction
    • Exercise 1 - Using Subqueries
    • Exercise 2 - Working with Correlated Subqueries
    • Exercise 3 - Working with Self-Contained Subqueries
    • Exercise 4 - Managing Nested Subqueries
    • Summary
  17. Working with Apply Operators
  18. Exercises:
    • Introduction
    • Exercise 1 - Using CROSS APPLY and OUTER APPLY Operators
    • Summary
  19. Working with Table Expressions
  20. Exercises:
    • Introduction
    • Exercise 1 - Working with Common Table Expression (CTE)
    • Exercise 2 - Using CTE with AVG Function
    • Exercise 3 - Working with Recursive CTE
    • Summary
  21. Grouping and Pivoting Data
  22. Exercises:
    • Introduction
    • Exercise 1 - Working with GROUP BY Clause
    • Exercise 2 - Using HAVING Clause with GROUP BY
    • Exercise 3 - Demonstrating the Use of Ranking Functions
    • Exercise 4 - Using PIVOT and UNPIVOT Operators
    • Summary
  23. Working with Temporal Table
  24. Exercises:
    • Introduction
    • Exercise 1 - Working with System-Versioned Temporal Tables
    • Exercise 2 - Querying Data in System-Versioned Temporal Tables
    • Summary
  25. Working with Non-relational Data
  26. Exercises:
    • Introduction
    • Exercise 1 - Working with JSON Functions and Clauses
    • Exercise 2 - Working with FOR XML Queries
    • Summary
  27. Working with Stored Procedures and Views
  28. Exercises:
    • Introduction
    • Exercise 1 - Working with Stored Procedures
    • Exercise 2 - Working with Views
    • Summary
  29. Implementing Error Handling
  30. Exercises:
    • Introduction
    • Exercise 1 - Working with Error Handling Blocks
    • Exercise 2 - Working with THROW Statement
    • Exercise 3 - Working with RAISERROR Statement
    • Exercise 4 - Using TRY…CATCH Block in SQL Stored Procedure
    • Summary
  31. Working with Data Types and Null Values
  32. Exercises:
    • Introduction
    • Exercise 1 - Comparing Two Values with Different Data Types
    • Exercise 2 - Working with COUNT Function on Columns with NULL Values
    • Exercise 3 - Working with AVG Function on Columns with NULL Values
    • Summary
LIVE LAB Access to real hardware and software in real time and with full administrator privileges to gain real-world practical experience.
  • 610
    + Minutes of practical exercises
  • Gain practical experience with real-world scenarios
  • Access to real hardware & software in real time
  • Experiment & put theory to practice
  • Includes exercises designed to prepare for exams.
  • 12 Months access (Unless indicated otherwise)
  • 10-Day money-back guarantee
Back to Top