Course Code: 5673

MCSA SQL Server 2016 Database Development Bootcamp

Class Dates:
1/21/2019
12/3/2018
3/18/2019
Length:
6 Days
Cost:
$3495
Class Time:
Technology:
Server
Delivery:

Overview

  • 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
  • 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.

Prerequisites

Course Details

  • 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