ICT-cursus CertKit – 70-761 (Querying Data with Transact-SQL) | De inhoud

Inhoud van de ICT-cursus CertKit - 70-761 (Querying Data with Transact-SQL) | De Kantooropleider

ICT-cursus CertKit – 70-761 (Querying Data with Transact-SQL) | Welke onderwerpen worden behandeld?

T-SQL Querying

  • Start the course
  • Recognize features of SQL Server 2016
  • List SQL Server 2016 editions and components
  • Identify SQL Server 2016 functions and capabilities
  • List features of SQL Server Management Studio
  • Configure T-SQL statements in SQL Server 2016
  • Work with T-SQL Language functions in SQL Server 2016
  • Create T-SQL Variables in SQL Server 2016
  • Demonstrate how to implement T-SQL expressions in SQL Server 2016
  • Work with T-SQL control of flow, errors, and transactions in SQL Server 2016
  • Use T-SQL comments in SQL Server 2016
  • Work with T-SQL batch separators
  • Describe:
    • The characteristics of sets in SQL Server 2016
    • The set theory and how it relates to SQL Server 2015
    • Applying set theory to SQL server queries
  • Define predicate logic in SQL Server 2016
  • Recognize the elements of a SELECT statement in SQL Server 2016
  • Define the logical order of operations
  • Demonstrate the logical order of operations when writing SELECT statements
  • Demonstrate querying data in SQL Server 2016

Querying and Manipulating Data

  • Start the course
  • Recognize elements of simple SQL Server 2016 SELECT queries
  • Demonstrate how to write simple SELECT statements in SQL Server 2016
  • Demonstrate how to use calculations in the SELECT clause
  • Describe the purpose of DISTINCT in SQL Server 2016
  • Describe how to execute a SELECT DISTINCT
  • Demonstrate how to eliminate duplicates using DISTINCT
  • Describe how to refer to columns using aliases
  • Specify how to refer to tables using aliases
  • Describe SQL Server 2016 CASE expressions
  • Demonstrate how to use simple CASE expressions
  • Recognize how and when to use joins in SQL Server 2016
  • Demonstrate the proper usage of:
    • Inner joins in SQL Server 2016
    • Outer joins in SQL Server 2016
    • Cross and self joins
  • demonstrate:
    • The use of multiple join operators
    • How to write queries with NULLs on joins
    • How to query and manipulate data in SQL Server 2016

Filter and Modify Data in SQL Server 2016

  • Start the course
  • Describe the ORDER BY clause in SQL Server 2016
  • Demonstrate how to sort data in SQL Server 2016 using the ORDER BY clause
  • Recognize how to filter data in SQL Server 2016
  • Demonstrate how to filter data in SQL Server 2016 using the WHERE clause
  • Describe how to use predicates and operators in SQL Server 2016
  • Describe three-valued logic in SQL Server 2016
  • Work with NULL in Queries in SQL Server 2016
  • Describe how to test for NULL in SQL Server 2016
  • Describe how SQL Server 2016 uses data types
  • Recognize when data type conversions occur in SQL Server 2016
  • Demonstrate implicit and explicit conversions
  • Describe different types of character data in SQL Server 2016
  • Demonstrate how to manipulate character data in SQL Server 2016
  • List the benefits of using data manipulation language (DML) in SQL Server 2016
  • Describe how to populate tables in SQL Server 2016 using the SELECT INTO statement
  • Demonstrate how to add data using INSERT statement
  • Describe how to use UPDATE and MERGE statements to modify data
  • Describe how to use DELETE and TRUNCATE statements to remove data
  • Demonstrate how to use the OUTPUT clause to monitor data in SQL Server 2016
  • Demonstrate how to filter and modify data in SQL Server 2016

Using Functions in SQL Server 2016

  • Start the course
  • List the built-in function types used in SQL Server 2016
  • Work with scalar functions in SQL Server 2016
  • Describe aggregate functions in SQL Server 2016
  • List Windows functions in SQL Server 2016
  • Describe rowset functions in SQL Server 2016
  • Work with implicit and explicit data type conversions in SQL Server 2016
  • Demonstrate how to convert a value using the CAST function
  • Demonstrate how to convert a value using the CONVERT function
  • Describe how to convert a value using the PARSE function
  • Demonstrate how to convert a value using the TRY_Parse function
  • Describe how to convert a value using the TRY_Convert function
  • Demonstrate how to validate data types using the ISNUMERIC function
  • Work the IIF logical function in SQL Server
  • Work with the CHOOSE function in SQL Server 2016
  • Describe the NULL value in SQL Server 2016
  • Demonstrate how to use ISNULL to replace NULL
  • Work with the COALESCE function in SQL Server 2016
  • Describe how to use the NULLIF function in SQL Server 2016
  • Use functions in SQL Server 2016

Aggregating Data in SQL Server 2016

  • Start the course
  • Recognize the aggregate functions available using Transact-SQL
  • Describe the built-in aggregate function SUM in SQL Server 2016
  • Describe the built-in aggregate function AVG in SQL Server 2016
  • List the built-in aggregate function:
    • In MIN Server 2016
    • MAX in SQL Server 2016
    • COUNT in SQL Server 2016
  • Describe how to aggregate distinct values using DISTINCT
  • Demonstrate how to use aggregate functions with NULL
  • Recognize when and where to use the GROUP BY clause
  • Demonstrate how to group and rank the results of a query using the windowing functions in SQL Server 2016
  • Describe:
    • How to process queries using the logical order of operations
    • The GROUP BY workflow process
    • How to use GROUP BY with aggregate functions in SQL Server 2016
  • Demonstrate using complex GROUP BY clauses using GROUPING sets
  • Recognize how to filter groups using the HAVING clause
  • Recognize how to use PIVOT and UNPIVOT relational operators in SQL Server 2016
  • Demonstrate how to use PIVOT in SQL Server 2016
  • Demonstrate how to use UNPIVOT in SQL Server 2016
  • Describe how to determine the impact on NULL values in PIVOT and UNPIVOT queries
  • Use aggregate functions in SQL Server 2016

Subqueries and Predicate

  • Start the course
  • Recognize when and where to use subqueries in SQL Server 2016
  • Describe the benefits of using scalar subqueries
  • Demonstrate how to write scalar subqueries in SQL Server 2016
  • Recognize how to write multi-valued subqueries in SQL Server 2016
  • Work with correlated subqueries in SQL Server 2016
  • Describe how to write correlated subqueries
  • Recognize how to write correlated subqueries with aliases
  • Describe how:
    • To write correlated subqueries with aliases
    • To write correlated subqueries in a HAVING clause
    • The EXISTS predicate evaluates data in SQL Server 2016
    • To write queries using EXISTS with subqueries
  • Demonstrate how to use EXISTS and NULLs in SQL Server 2016
  • Describe how to use EXISTS and INNER JOINS in SQL Server 2016
  • Demonstrate how to use NOT EXISTS with OUTER JOINs
  • Use subqueries in SQL Server 2016

Operators and Expressions in SQL Server 2016

  • Start the course
  • Distinguish when to use the UNION and UNION All operators in SQL Server 2016
  • Demonstrate how to:
    • Write SQL Server 2016 queries that use the UNION operator
    • Write SQL Server 2016 queries that use the UNION ALL operator
    • Use the UNION and UNION ALL operators together
  • Recognize when to use the EXCEPT operator in SQL Server 2016
  • Demonstrate how to use the EXCEPT and INTERSECT operators in SQL Server 2016
  • Determine the results of reversing the order of input with the EXCEPT operator
  • Determine the effect of NULL values on the INTERSECT operator
  • Recognize when to use the APPLY operator in SQL Server 2016
  • Use:
    • APPLY statements that return given data based on supplied data in SQL Server 2016
    • CROSS APPLY operators in SQL Server 2016
    • OUTER APPLY operators in SQL Server 2016
  • Recognize the basic components of table expressions in SQL Server 2016
  • Describe the difference between table expressions and temporary tables
  • Create recursive table expressions in SQL Server 2016
  • Use operators in SQL Server 2016

Database Programmability Objects and Non-Relational Data

  • Start the course
  • Describe stored procedures in SQL Server 2016
  • Use and create stored procedures in SQL Server 2016
  • Demonstrate how to input parameters in SQL Server 2016
  • Demonstrate how to output parameters in SQL Server 2016
  • Recognize table-valued functions in SQL Server 2016
  • Describe scalar-valued user-defined functions in SQL Server 2016
  • Demonstrate TRY…CATCH error handling
  • Demonstrate how to use RAISERROR in SQL 2016
  • Describe temporal data types in SQL Server 2016
  • Work with temporal tables in SQL Server 2016
  • Describe XML data
  • Describe JSON data
  • Demonstrate how to query and output JSON data in SQL Server 2016
  • Demonstrate how to query and output XML data in SQL Server 2016
  • Use database programmability objects in SQL Server 2016

TestPrep 70-761 Querying Data with Transact SQL

Test your knowledge on the skills and competencies measured by the vendor certification exam.

TestPrep can be taken in either Study or Certification mode.

  • Study mode is designed to maximize learning by not only testing your knowledge of the material, but also by providing additional information on the topics presented.
  • Certification mode is designed to test your knowledge of the material within a structured testing environment, providing valuable feedback at the end of the test.
Print pagina