Microsoft
Microsoft Certified Solutions Associate (MCSA): SQL 2016 Database Development
70-761 Querying Data with Transact-SQL
Aggregating Data in SQL Server 2016
df_sqlq_a05_it_enus
Database Programmability Objects and Non-Relational Data
df_sqlq_a08_it_enus
Filter and Modify Data in SQL Server 2016
df_sqlq_a03_it_enus
Operators and Expressions in SQL Server 2016
df_sqlq_a07_it_enus
Querying and Manipulating Data
df_sqlq_a02_it_enus
Subqueries and Predicate
df_sqlq_a06_it_enus
T-SQL Querying
df_sqlq_a01_it_enus
Using Functions in SQL Server 2016
df_sqlq_a04_it_enus
TestPrep 70-761 Querying Data with Transact SQL
ep_dfsqlq_a01_tp_enus
70-762 Developing SQL Databases
Columnstore Indexes
df_sqld_a03_it_enus
Database Instances
df_sqld_a09_it_enus
Managing Database Concurrency
df_sqld_a07_it_enus
Optimize SQL Database Objects and Infrastructure
df_sqld_a08_it_enus
Performance Tuning
df_sqld_a10_it_enus
Programmability Objects
df_sqld_a04_it_enus
SQL Server 2016 Database Objects
df_sqld_a01_it_enus
SQL Server 2016 Indexes and Views
df_sqld_a02_it_enus
Transactions and Isolation Levels
df_sqld_a06_it_enus
Triggers and Functions
df_sqld_a05_it_enus
Aggregating Data in SQL Server 2016
Lesson Objectives
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
- list the built-in aggregate function MAX in SQL Server 2016
- list the built-in aggregate function 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
- describe the GROUP BY workflow process
- describe 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
Overview/Description
Aggregate functions in SQL Server 2016 are used to summarize data in multiple rows. This course will cover various types of aggregate functions such as SUM, MIN, MAX, AVG, and COUNT, as well as the GROUP BY clause, which is used to arrange your data into subsets before summarizing it. Lastly, this course will demonstrate how to change a table-valued expression into another table using the PIVOT and UNPIVOT operators. This course is one of a series of courses that cover the objectives for 70-761: Querying Data with Transact-SQL exam.
Aggregate functions in SQL Server 2016 are used to summarize data in multiple rows. This course will cover various types of aggregate functions such as SUM, MIN, MAX, AVG, and COUNT, as well as the GROUP BY clause, which is used to arrange your data into subsets before summarizing it. Lastly, this course will demonstrate how to change a table-valued expression into another table using the PIVOT and UNPIVOT operators. This course is one of a series of courses that cover the objectives for 70-761: Querying Data with Transact-SQL exam.
Database Programmability Objects and Non-Relational Data
Lesson Objectives
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
Overview/Description
SQL Server 2016 offers various database programmable objects, such as stored procedures and functions, used to enhance and increase your database performance as well as ease administrative workload. This course will demonstrate how to implement various database programmability objects, as well as how to work with temporal and non-relational data in SQL Server 2016. This course is one of a series of courses that cover the objectives for 70-761: Querying Data with Transact-SQL exam.
SQL Server 2016 offers various database programmable objects, such as stored procedures and functions, used to enhance and increase your database performance as well as ease administrative workload. This course will demonstrate how to implement various database programmability objects, as well as how to work with temporal and non-relational data in SQL Server 2016. This course is one of a series of courses that cover the objectives for 70-761: Querying Data with Transact-SQL exam.
Filter and Modify Data in SQL Server 2016
Lesson Objectives
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
Overview/Description
SQL Server 2016 offers various clauses that work in conjunction with SELECT statements. These clauses, which include ORDER BY, TOP, OFFSET, and FETCH, determine how the results of a particular query are presented. In addition to clauses, the data manipulation language (DML) will be introduced in this course. The DML contains additional commands to modify data within rows and tables in SQL Server 2016. This course is one of a series of courses that cover the objectives for 70-761: Querying Data with Transact-SQL exam.
SQL Server 2016 offers various clauses that work in conjunction with SELECT statements. These clauses, which include ORDER BY, TOP, OFFSET, and FETCH, determine how the results of a particular query are presented. In addition to clauses, the data manipulation language (DML) will be introduced in this course. The DML contains additional commands to modify data within rows and tables in SQL Server 2016. This course is one of a series of courses that cover the objectives for 70-761: Querying Data with Transact-SQL exam.
Operators and Expressions in SQL Server 2016
Lesson Objectives
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
- demonstrate how to write SQL Server 2016 queries that use the UNION ALL operator
- demonstrate how to 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
- use CROSS APPLY operators in SQL Server 2016
- use 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
Overview/Description
Set operators in SQL Server 2016 are used to compare rows between input sets. This course covers the UNION, INTERSECT, and EXCEPT set operators, as well as the APPLY operator, which is used to evaluate rows in one input set against the expression defining the second input set. This course also introduces the basic components of common table expressions. This course is one of a series of courses that cover the objectives for 70-761: Querying Data with Transact-SQL exam.
Set operators in SQL Server 2016 are used to compare rows between input sets. This course covers the UNION, INTERSECT, and EXCEPT set operators, as well as the APPLY operator, which is used to evaluate rows in one input set against the expression defining the second input set. This course also introduces the basic components of common table expressions. This course is one of a series of courses that cover the objectives for 70-761: Querying Data with Transact-SQL exam.
Querying and Manipulating Data
Lesson Objectives
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
- demonstrate the proper usages of outer joins in SQL Server 2016
- demonstrate the proper usages of outer joins in SQL Server 2016
- demonstrate the proper usages of cross and self joins
- demonstrate the use of multiple join operators
- demonstrate how to write queries with NULLs on joins
- demonstrate how to query and manipulate data in SQL Server 2016
Overview/Description
SELECT statements in SQL Server 2016 are used to query tables and views. Further, you can manipulate data using SELECT statements to customize the returned results. This course focuses on the fundamentals of using SELECT statements, including how to use aliases and case expressions, as well as the DISTINCT clause. Lastly, this course covers how to query multiple tables using joins. This course is one of a series of courses that cover the objectives for 70-761: Querying Data with Transact-SQL exam.
SELECT statements in SQL Server 2016 are used to query tables and views. Further, you can manipulate data using SELECT statements to customize the returned results. This course focuses on the fundamentals of using SELECT statements, including how to use aliases and case expressions, as well as the DISTINCT clause. Lastly, this course covers how to query multiple tables using joins. This course is one of a series of courses that cover the objectives for 70-761: Querying Data with Transact-SQL exam.
Subqueries and Predicate
Lesson Objectives
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
- describe how to write correlated subqueries in a HAVING clause
- describe how the EXISTS predicate evaluates data in SQL Server 2016
- describe how 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
Overview/Description
Subqueries in SQL Server 2016 are nested SELECT statements used to created more effective queries in T-SQL. This course will introduce subqueries and correlated subqueries, and cover the EXISTS predicate, a mechanism used in SQL Server 2016 to check whether any results will be returned from a specific query. This course is one of a series of courses that cover the objectives for 70-761: Querying Data with Transact-SQL exam.
Subqueries in SQL Server 2016 are nested SELECT statements used to created more effective queries in T-SQL. This course will introduce subqueries and correlated subqueries, and cover the EXISTS predicate, a mechanism used in SQL Server 2016 to check whether any results will be returned from a specific query. This course is one of a series of courses that cover the objectives for 70-761: Querying Data with Transact-SQL exam.
T-SQL Querying
Lesson Objectives
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
- describe the set theory and how it relates to SQL Server 2015
- describe 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
Overview/Description
SQL Server 2016 delivers many new features and enhancements. This course provides an overview of the SQL Server 2016 architecture, as well as the functions and capabilities available in each edition. The fundamentals of T-SQL querying is covered in this course, as well as topics such as sets and predicate logic. This course is one of a series of SkillSoft courses that cover the objectives for 70-761: Querying Data with Transact-SQL exam.
SQL Server 2016 delivers many new features and enhancements. This course provides an overview of the SQL Server 2016 architecture, as well as the functions and capabilities available in each edition. The fundamentals of T-SQL querying is covered in this course, as well as topics such as sets and predicate logic. This course is one of a series of SkillSoft courses that cover the objectives for 70-761: Querying Data with Transact-SQL exam.
TestPrep 70-761 Querying Data with Transact SQL
Objectives:
Overview/Description
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.
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.
Target Audience
Individuals seeking practice in a structured testing environment, covering the skills and competencies measured by the vendor certification exam.
Individuals seeking practice in a structured testing environment, covering the skills and competencies measured by the vendor certification exam.
Prerequisites: none
Using Functions in SQL Server 2016
Lesson Objectives
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
Overview/Description
SQL Server 2016 offers various built-in functions that provide data type conversion, comparison, and NULL handling. This course covers the various types of built-in functions, as well as demonstrates how to write queries using built-in functions. This course is one of a series of courses that cover the objectives for 70-761: Querying Data with Transact-SQL exam.
SQL Server 2016 offers various built-in functions that provide data type conversion, comparison, and NULL handling. This course covers the various types of built-in functions, as well as demonstrates how to write queries using built-in functions. This course is one of a series of courses that cover the objectives for 70-761: Querying Data with Transact-SQL exam.
Columnstore Indexes
Lesson Objectives
Columnstore Indexes
- start the course
- recognize when to use columnstore indexes
- determine when to implement a columnstore index
- create a clustered columnstore index
- describe columnstore index data loading
- describe columnstore versioned feature summary
- improve columnstore index query performance
- implement real-time operational analytics
- describe columnstore index data warehousing
- implement columnstore index defragmentation
- differentiate between clustered and nonclustered columnstore indexes in SQL Server 2016
- create a nonclustered columnstore index
- change data in a nonclustered columnstore index
- describe in-memory nonclustered hash indexes
- create tables with nonclustered hash indexes
- describe in-memory index performance considerations
- describe XML indexes
- describe secondary XML indexes
- describe XML index performance considerations
- implement and configure columnstore indexes
Overview/Description
Columnstore indexes work differently from traditional indexes in SQL Server 2016. This course covers how to implement columnstore indexes in SQL Server 2016, as well as the various columnstore performance considerations that can impact operations. This course also covers nonclustered indexes, in-memory indexes, and XML indexes. This course is one of a series of courses that cover the objectives for 70-762: Developing SQL Databases exam.
Columnstore indexes work differently from traditional indexes in SQL Server 2016. This course covers how to implement columnstore indexes in SQL Server 2016, as well as the various columnstore performance considerations that can impact operations. This course also covers nonclustered indexes, in-memory indexes, and XML indexes. This course is one of a series of courses that cover the objectives for 70-762: Developing SQL Databases exam.
Database Instances
Lesson Objectives
Database Instances
- start the course
- describe workloads
- manage workloads and system resource consumption using Resource Governor
- enable Resource Governor
- configure Resource Governor properties
- create a resource pool
- create a workload group
- disable Resource Governor
- optimize memory configuration
- describe service tiers
- describe common service tier use cases
- describe elastic database pools
- describe elastic scaling
- optimize tempdb configuration files
- demonstrate how to create an Azure SQL Server 2016 database
- demonstrate how to connect to an Azure SQL Server 2016 database
- describe the data collector service for monitoring SQL Server 2016 databases
- enable and configure a management data warehouse for the data collector service
- configure a data collection
- monitor and diagnose scheduling and wait statistics using dynamic management objects
- troubleshoot storage, IO, and cache issues
- demonstrate how to monitor Azure SQL Database query plans
- demonstrate how to use the SQL Server Monitor
Overview/Description
SQL Server Resource Governor is a feature used to manage SQL Server workloads and resource consumption by specifying limits on resources such as CPU, IO, and memory. This course covers the Resource Governor and the cloud database service, Microsoft Azure Cloud SQL Database. In addition, monitoring and troubleshooting database instances is also covered. This course is one of a series of courses that cover the objectives for 70-762: Developing SQL Databases exam.
SQL Server Resource Governor is a feature used to manage SQL Server workloads and resource consumption by specifying limits on resources such as CPU, IO, and memory. This course covers the Resource Governor and the cloud database service, Microsoft Azure Cloud SQL Database. In addition, monitoring and troubleshooting database instances is also covered. This course is one of a series of courses that cover the objectives for 70-762: Developing SQL Databases exam.
Managing Database Concurrency
Lesson Objectives
Managing Database Concurrency
- start the course
- provide an overview of locking
- discuss the types of concurrency and their effects
- describe deadlock behavior
- minimize deadlocks
- demonstrate how to handle deadlocks
- demonstrate how to remediate, detect, and end deadlocks
- demonstrate how to troubleshoot locking issues
- describe lock escalation behaviors
- provide an overview of memory-optimized tables and discuss use cases for memory-optimized tables vs. traditional disk-based tables
- create memory-optimized tables
- optimize performance by adjusting durability settings
- describe considerations for statistics on memory-optimized tables
- describe the methods used to query memory-optimized tables
- describe the characteristics of natively compiled stored procedures
- recognize best-case usage scenarios for natively compiled stored procedures
- describe atomic blocks in natively compiled stored procedures
- demonstrate how to create a natively compiled stored procedure
- demonstrate how to monitor performance for natively compiled stored procedures
- describe the best practices for calling natively compiled stored procedures
- demonstrate how to troubleshoot deadlocks
Overview/Description
Locking is a feature in SQL Server 2016 that helps ensure the physical integrity of each transaction. This course covers how to implement and troubleshoot locking, as well as how to minimize, handle, and remediate deadlocks. This course also covers memory-optimized tables, which are used to improve the performance of transaction processing, as well as native stored procedures - a feature used to enable efficient execution of the queries. This course is one of a series of courses that cover the objectives for 70-762: Developing SQL Databases exam.
Locking is a feature in SQL Server 2016 that helps ensure the physical integrity of each transaction. This course covers how to implement and troubleshoot locking, as well as how to minimize, handle, and remediate deadlocks. This course also covers memory-optimized tables, which are used to improve the performance of transaction processing, as well as native stored procedures - a feature used to enable efficient execution of the queries. This course is one of a series of courses that cover the objectives for 70-762: Developing SQL Databases exam.
Optimize SQL Database Objects and Infrastructure
Lesson Objectives
Optimize SQL Database Objects and Infrastructure
- start the course
- create statistics and determine accuracy of results
- modify existing statistics
- view statistic properties
- update query optimization statistics
- describe available maintenance tasks to perform
- perform database tuning using the database engine tuning advisor
- demonstrate how to create a maintenance plan
- perform index defragmentation
- review current index usage and identify missing indexes
- identify and consolidate overlapping indexes
- capture query plans using extended events and traces
- describe logical operators and how they are used
- describe physical operators and how they are used
- recognize best practices to follow with the Query Store
- describe when and where to use the Query Store
- determine how to start query performance troubleshooting
- identify underperforming query plan operators
- compare query plans and related metadata
- configure the Azure SQL Database Performance Insight
- use statistics objects and index optimization methods
Overview/Description
Optimizing statistics helps ensure the most efficient means of executing statements in SQL Server 2016. This course covers how to create, modify, and troubleshoot statistics and indexes in SQL Server 2016. This course also covers how to analyze and troubleshoot query plans in SQL Server 2016. This course is one of a series of courses that cover the objectives for 70-762: Developing SQL Databases exam.
Optimizing statistics helps ensure the most efficient means of executing statements in SQL Server 2016. This course covers how to create, modify, and troubleshoot statistics and indexes in SQL Server 2016. This course also covers how to analyze and troubleshoot query plans in SQL Server 2016. This course is one of a series of courses that cover the objectives for 70-762: Developing SQL Databases exam.
Performance Tuning
Lesson Objectives
Performance Tuning
- start the course
- describe performance metrics
- establish a server performance baseline
- demonstrate how to use perfmon
- demonstrate how to use dynamic management objects
- compare baseline metrics to observed metrics in order to troubleshoot performance issues
- monitor Azure SQL Database performance
- demonstrate how to enable and view live query statistics
- use the activity monitor in SQL Server 2016
- demonstrate how to use the task manager windows monitoring tool
- demonstrate how execute database consistency checker commands
- use system stored procedures for monitoring tasks
- describe the benefits of using extended events
- describe extended events tasks
- differentiate between extended events packages, targets, and sessions
- distinguish between extended events targets
- describe the SQL trace architecture
- create a trace using transact-sql
- optimize SQL trace to minimize performance costs
- demonstrate how to schedule tracing in SQL Server
- using various tools to monitor performance
Overview/Description
SQL Server 2016 provides a comprehensive set of tools for monitoring and tuning to ensure optimal performance for an environment. This course covers performance metrics and monitoring, as well as the various logging tools available with SQL Server, such as perfmon and dynamic management objects. In addition, this course covers Extended Events, and the event-handling system used for monitoring SQL Server instances. This course is one of a series of courses that cover the objectives for 70-762: Developing SQL Databases exam.
SQL Server 2016 provides a comprehensive set of tools for monitoring and tuning to ensure optimal performance for an environment. This course covers performance metrics and monitoring, as well as the various logging tools available with SQL Server, such as perfmon and dynamic management objects. In addition, this course covers Extended Events, and the event-handling system used for monitoring SQL Server instances. This course is one of a series of courses that cover the objectives for 70-762: Developing SQL Databases exam.
Programmability Objects
Lesson Objectives
Programmability Objects
- start the course
- recognize the three basic forms of data integrity: domain, entity, referential
- use the various mechanisms used to enforce data integrity
- describe entity and referential integrity
- describe the use of the PRIMARY Key constraint
- use the FOREIGN KEY constraint
- describe the UNIQUE constraint I
- describe the IDENTITY constraint
- recognize the common considerations to be aware of when working with constraints
- demonstrate how to write Transact-SQL statements to add constraints to tables
- demonstrate how to identify results of DML statements given exiting tables and constraints
- describe stored procedures and their benefits
- design stored procedures components and structure based on business requirements
- describe how to use the input and output parameters
- demonstrate how to use table-valued parameters
- implement return codes
- streamline existing stored procedure logic
- implement error handling and transaction control logic within stored procedures
- alter a stored procedure using the Transact-SQL ALTER PROCEDURE statement
- obfuscate stored procedures using the WITH ENCRYPTIONS clause
- configure constraints in SQL Server 2016
Overview/Description
Constraints can be used in SQL Server 2016 to ensure data integrity. This covers how to design and implement constraints for tables and columns. This course also covers stored procedures, a SQL Server feature that allows you to reuse the code over and over, providing advantages such as performance, productivity, and ease of use. This course is one of a series of courses that cover the objectives for 70-762: Developing SQL Databases exam.
Constraints can be used in SQL Server 2016 to ensure data integrity. This covers how to design and implement constraints for tables and columns. This course also covers stored procedures, a SQL Server feature that allows you to reuse the code over and over, providing advantages such as performance, productivity, and ease of use. This course is one of a series of courses that cover the objectives for 70-762: Developing SQL Databases exam.
SQL Server 2016 Database Objects
Lesson Objectives
SQL Server 2016 Database Objects
- start the course
- create tables in SQL Server 2016
- use the DROP TABLE statement to drop a table from a database
- use the ALTER TABLE statement to modify a table
- add and removing columns from a table
- duplicate a table
- rename a table
- use constraints to ensure integrity of data
- describe the best practices to follow when selecting a naming convention
- describe the numeric data type category in SQL Server 2016
- describe the date and time data type category in SQL Server 2016
- describe the character and unicode data type category in SQL Server 2016
- describe the binary strings data type category in SQL Server 2016
- describe the special data types category in SQL Server 2016
- recognize the process of normalization in SQL Server 2016
- describe the first normal form level of normalization
- describe the second normal form level of normalization
- describe the third normal form level of normalization
- discuss when to denormalize a database
- demonstrate how to create a SQL Table
Overview/Description
In order to properly design and implement a relational database schema, it is important to have an understanding of the fundamentals of SQL Server 2016. This course covers creating and altering tables, designing normalization, as well as the various data types available in SQL Server 2016. This course is one of a series of courses that cover the objectives for 70-762: Developing SQL Databases exam.
In order to properly design and implement a relational database schema, it is important to have an understanding of the fundamentals of SQL Server 2016. This course covers creating and altering tables, designing normalization, as well as the various data types available in SQL Server 2016. This course is one of a series of courses that cover the objectives for 70-762: Developing SQL Databases exam.
Target Audience
Database professionals and developers looking to become skilled in SQL Server 2016 database
Database professionals and developers looking to become skilled in SQL Server 2016 database
SQL Server 2016 Indexes and Views
Lesson Objectives
SQL Server 2016 Indexes and Views
- start the course
- recognize the basic guidelines to follow when designing and implementing indexes
- describe when to use tables without clustered indexes
- recognize the basic guidelines to follow when designing and implementing indexes
- create clustered indexes
- create nonclustered indexes
- create unique indexes
- create filtered indexes
- create indexes with included columns
- delete indexes
- modify indexes
- describe the use of views
- recognize how to design and implement views
- describe the various types of views
- create views
- create an indexed view
- modify views
- modify data using a view
- create partitioned views
- rename and delete a view
- create an index with included columns
Overview/Description
SQL Server 2016 uses indexes to speed up the performance of queries and improve overall database performance. This course covers how to design and implement the various index types available in SQL Server 2016. In addition, this course covers enhancing usability in SQL Server 2016 by using views. This course is one of a series of courses that cover the objectives for 70-762: Developing SQL Databases exam.
SQL Server 2016 uses indexes to speed up the performance of queries and improve overall database performance. This course covers how to design and implement the various index types available in SQL Server 2016. In addition, this course covers enhancing usability in SQL Server 2016 by using views. This course is one of a series of courses that cover the objectives for 70-762: Developing SQL Databases exam.
Transactions and Isolation Levels
Lesson Objectives
Transactions and Isolation Levels
- start the course
- recognize when to use transactions
- demonstrate how to use the BEGIN TRANSACTION statement
- use the COMMIT TRANSACTION to mark the end of a successful implicit or explicit transaction
- use the COMMIT WORK statement to mark the end of a transaction
- use the ROLLBACK TRANSACTION to roll back an explicit or implicit transaction
- use the ROLLBACK WORK statement to roll back a user-specified transaction
- use the SAVE TRANSACTION statement to set a savepoint within a transaction
- identify DML statement results based on transaction behavior
- determine the role of transactions in high-concurrency SQL Server 2016 databases
- describe explicit transactions
- describe implicit transactions
- describe snapshot isolation and row versioning
- use the READ UNCOMMITTED isolation level
- use the READ COMMITTED isolation level
- use the REPEATABLE READ isolation level
- use the SERIALIZED isolation level
- use the SNAPSHOT isolation level
- define the results of concurrent queries based on isolation levels
- describe performance impact considerations of given isolation levels
- demonstrate how to administer transactions
Overview/Description
SQL Server transactions allow you to group together a number of statements to form a single logical unit of work. This course covers the various types of transactions, and explains how to implement them in SQL Server 2016. This course also covers isolation levels, a SQL Server 2016 feature that helps control the way locking works between transactions. This course is one of a series of courses that cover the objectives for 70-762: Developing SQL Databases exam.
SQL Server transactions allow you to group together a number of statements to form a single logical unit of work. This course covers the various types of transactions, and explains how to implement them in SQL Server 2016. This course also covers isolation levels, a SQL Server 2016 feature that helps control the way locking works between transactions. This course is one of a series of courses that cover the objectives for 70-762: Developing SQL Databases exam.
Triggers and Functions
Lesson Objectives
Triggers and Functions
- start the course
- create DML triggers
- create CLR triggers
- recognize best practices regarding DML trigger security
- implement DDL triggers
- gather information regarding DDL triggers
- describe DDL events and event groups
- recognize when to use the various types of triggers
- implement first and last triggers
- manage transactions within a logon trigger
- disable logon triggers
- demonstrate how to use the AFTER trigger
- demonstrate how to use the INSTEAD trigger
- describe user-defined functions
- recognize the various components of user-defined functions
- use scalar-valued functions
- use table-valued functions
- use built-in functions
- use deterministic functions
- use nondeterministic functions
- configure triggers in SQL Server 2016
Overview/Description
Triggers in SQL Server 2016 are sets of statements that execute automatically when a specific event occurs in a database. This course covers the design and implementation of the various types of triggers found in SQL Server 2016. In addition, this course cover functions, including scalar-valued, table-valued, deterministic, and nondeterministic. This course is one of a series of courses that cover the objectives for 70-762: Developing SQL Databases exam.
Triggers in SQL Server 2016 are sets of statements that execute automatically when a specific event occurs in a database. This course covers the design and implementation of the various types of triggers found in SQL Server 2016. In addition, this course cover functions, including scalar-valued, table-valued, deterministic, and nondeterministic. This course is one of a series of courses that cover the objectives for 70-762: Developing SQL Databases exam.
