MCSA SQL Server 2016 Database Development Bootcamp
CLASS DATE(s):
6/15/2020 - 6/20/2020
8/24/2020 - 8/29/2020

COURSE LENGTH: 6 Days

COURSE COST: $3495

COURSE TIMES: 9:00am - 5:00pm

Printable version of this course
print
Register for this course
register

COURSE OVERVIEW

Boot camps are geared towards providing students with the necessary skills and knowledge to not only pass the Microsoft Certification exams, but to also excel in their IT career paths. All of our boot camps are all-inclusive and include benefits such as:

  • All course materials, practice exams and official certification exams
  • Onsite PearsonVue Testing Center
  • Hands-on instruction by a certified instructor
  • Breakfast snacks and Lunch provided each day
  • Exams Included:
  • 70-761: Querying with Transact-SQL
  • 70-762: Developing SQL Databases

AUDIENCE AND PREREQUISITES

This boot camp is intended for SQL Server database administrators, system engineers, and developers with two or more years of experience who are seeking to validate their skills and knowledge in writing queries.
Students should have taken classes below or have equivalent experience:

  • Querying with Transact-SQL
  • Developing SQL Databases

PREREQUISITE COURSESFOLLOW UP COURSES  

What's included: Exam Vouchers, Microsoft Training Courseware Books, Microsoft Labs and Prep Test. Onsite Testing Center, Breakfast Pastries,and afternoon snacks.

Course is subject to a minimum enrollment to run. Course may run virtually as a Virtual Instructor-Led (VILT) class if the minimum enrollment is not met.

COURSE TOPICS:


At Course Completion
Manage data with Transact-SQL
Query data with advanced Transact-SQL components
Program databases by using Transact-SQL
Design and implement database objects
Implement programmability objects
Manage database concurrency
Optimize database objects and SQL infrastructure

Module 1: Introduction to Microsoft SQL Server 2016
This module introduces SQL Server, the versions of SQL Server, including cloud versions, and how to connect to SQL Server using SQL Server Management Studio
The Basic Architecture of SQL Server
SQL Server Editions and Versions
Getting Started with SQL Server Management Studio
Lab : Working with SQL Server 2016 Tools

Module 2: Introduction to T-SQL Querying
This module introduces the elements of T-SQL and their role in writing queries, describes the use of sets in SQL Server, describes the use of predicate logic in SQL Server, and describes the logical o
Introducing T-SQL
Understanding Sets
Understanding Predicate Logic
Understanding the Logical Order of Operations in SELECT statements
Lab : Introduction to Transact-SQL Querying

Module 3: Writing SELECT Queries
Writing Simple SELECT Statements
Eliminating Duplicates with DISTINCT
Using Column and Table Aliases
Writing Simple CASE Expressions
Lab : Writing Basic SELECT Statements

Module 4: Querying Multiple Tables
Understanding Joins
Querying with Inner Joins
Querying with Outer Joins
Querying with Cross Joins and Self Joins
Lab : Querying Multiple Tables

Module 5: Sorting and Filtering Data
Sorting Data
Filtering Data with Predicates
Filtering with the TOP and OFFSET-FETCH Options
Working with Unknown Values
Lab : Sorting and Filtering Data

Module 6: Working with SQL Server 2016 Data Types
Introducing SQL Server 2016 Data Types
Working with Character Data
Working with Date and Time Data
Lab : Working with SQL Server 2016 Data Types

Module 7: Using DML to Modify Data
Inserting Data
Modifying and Deleting Data
Lab : Using DML to Modify Data

Module 8: Using Built-In Functions
Writing Queries with Built-In Functions
Using Conversion Functions
Using Logical Functions
Using Functions to Work with NULL
Lab : Using Built-In Functions

Module 9: Grouping and Aggregating Data
Using Aggregate Functions
Using the GROUP BY Clause
Filtering Groups with HAVING
Lab : Grouping and Aggregating Data

Module 10: Using Subqueries
Writing Self-Contained Subqueries
Writing Correlated Subqueries
Using the EXISTS Predicate with Subqueries
Lab : Using Subqueries

Module 11: Using Set Operators
Writing Queries with the UNION Operator
Using EXCEPT and INTERSECT
Using APPLY
Lab : Using SET Operators

Exam 2: Module 1: Introduction to Database Development
Introduction to the SQL Server Platform
SQL Server Database Development Tasks
Lab : SQL Server Database Development Tasks

Module 2: Designing and Implementing Tables
Designing Tables
Data Types
Working with Schemas
Creating and Altering Tables
Lab : Designing and Implementing Tables

Module 3: Advanced Table Designs
Partitioning data
Compressing Data
Temporal Tables
Lab : Using Advanced Table Designs

Module 4: Ensuring Data Integrity through Constraints
Enforcing data Integrity
Implementing Domain Integrity
Implementing Entity and Referential Integrity
Lab : Ensuring Data Integrity through Constraints

Module 5: Introduction to Indexes
Core Indexing Concepts
Data Types and Indexes
Single Column and Composite Indexes
Lab : Implementing Indexes

Module 6: Designing Optimized Index Strategies
Covering Indexes
Managing Indexes
Execution Plans
Using the DTE
Lab : Designing Optimized Index Strategies

Module 7: Columnstore Indexes
Introduction to Columnstore indexes
Creating Columnstore indexes
Working Columnstore indexes
Lab : Using Columnstore indexes

Module 8: Designing and Implementing Views
Introduction to views
Creating and managing views
Performance considerations for views
Lab : Designing and Implementing Views

Module 9: Designing and Implementing Stored Procedures
Introduction to Stored Procedures
Working with Stored Procedures
Implementing Parameterized Stored Procedures
Controlling Execution Context
Lab : Designing and Implementing Stored Procedures

Module 10: Designing and Implementing User-Defined Functions
Overview of Functions
Designing and Implementing Scalar Functions
Designing and Implementing Table-Valued Functions
Implementation Considerations for Functions
Alternatives to Functions
Lab : Designing and Implementing User-defined Functions

Module 11: Responding to Data Manipulation via Triggers
Designing DML Triggers
Implementing DML Triggers
Advanced Trigger Concepts
Lab : Responding to Data Manipulation via Triggers

Module 12: Using In-Memory Tables
In-Memory tables
Native Stored Procedures
Lab : In Memory OLTP

.
Module 13: Implementing Managed Code in SQL Server
Module 14: Storing and Querying XML Data in SQL Server
Module 15: Working with SQL Server Spatial Data
Module 16: Storing and Querying Blobs and Text Documents in SQL Server