Tag: Microsoft SQL Server

Duration

21 hours (usually 3 days including breaks)

Requirements

• NO previous SQL or database experience is required.

Overview

Microsoft SQL Server is a relational database management system (RDBMS) for storing and retrieving data.

In this instructor-led, live training (onsite or remote), participants will learn the essentials of the SQL language needed to query a Microsoft SQL Server database.

By the end of this training, participants will be able to:

• Install and configure Microsoft SQL Server
• Query a Microsoft SQL Server database using Microsoft’s Transact-SQL (T-SQL) language
• Understand the essentials of database design
• Optimize a database through normalization
• Use aggregate functions to generate results similar to those of “pivot-tables”
• Access Microsoft SQL Server through Excel

Audience

• Data analysts
• Business Intelligence professionals
• Business managers
• Excel experts who wish to expand their analysis skill set

Format of the Course

• Interactive lecture and discussion
• Lots of exercises and practice
• Hands-on implementation in a live-lab environment

Course Customization Options

• This training is based on the latest version of Microsoft SQL Server
• To request a customized training for this course, please contact us to arrange.

Course Outline

Introduction

Installing and Configuring Microsoft SQL Server

Overview of Microsoft SQL Server Features

Overview of Transact-SQL Concepts

Querying a Database using SELECT

Filtering the Data with WHERE

Sorting the Data with ORDER BY

Querying Multiple Tables with Joins

Grouping Data with GROUP BY

Using Aggregate Functions to Perform Calculations on Resulting Data

Filtering Groups with HAVING

Creating a New Database

Understanding the Database Model

Optimizing the Database through Normalization

Enhancing your Queries with Procedures

Writing Functions and Sub-routines

Using Conditions to Control the Flow

Connecting to Microsoft SQL Server through Excel

Troubleshooting

Summary and Conclusion

Duration

14 hours (usually 2 days including breaks)

Overview

As a core of the Microsoft data platform, SQL Server is a leading operational database management system.

Course Outline

Performanace and Management

• Enhanced Database Caching
• Query data store
• In-Memory OLTP in SQL Server 2016

Development

• Temporal Database
• Temporary Table and Variable Table in-memory
• Native JSON

High Availability and Security

• Enhanced AlwaysOn
• Always Enrypted
• Row-level Security
• Dynamic Data Masking

Data Insight and Business Intelligence

• Operational Analytics
• New functionality Columnstore Index
• Direct Query in SSAS Tabular
• R Integration (language R in SQL Server)
• Enhanced SSIS
• Enhanced MDS

Reporting Services

• New Report Server
• Mobil Reports
• SQL Server Mobile Report Publisher

Cloud and Hybrid

• Stretch Database
• Enhanced backup to Azure
• Migration SQL Server to Azure
• SSIS and Data Factory

Duration

14 hours (usually 2 days including breaks)

Requirements

Good SQL knowledge in Microsoft SQL Server 2008/2012 environment.

Overview

This course has been created for delegates already acquainted with SQL in Microsoft SQL Server Environment 2008/2012. The course focuses on set-based querying and query tuning, working with indexes and analyzing execution plans.

The training also covers table expressions, ranking functions and how to deal with partitioned tables.

Course Outline

Module 1. Query Tuning

• Tools for Query Tuning
• Cached Query Execution Plans
• Clearing the Cache
• Analyzing Execution Plans
• Hints
• Using the Database Engine Tuning Advisor
• Index Tuning
• Table and Index Structures
• Index Access Methods
• Indexing Strategies

Module 2. Subqueries, Table Expression, and Ranking Functions

• Writing Subqueries
• Using Table Expressions
• Using Ranking Functions

Module 3. Optimizing Joins and Set Operations

• Fundamental Join Types
• Join Algorithm
• Set Operations
• Using INTO with Set Operation

Module 4. Aggregating and Pivoting Data

• Using the OVER Clause
• Different types of aggregations (Cumulative, Sliding and Year-To-Date)
• Pivoting and Unpivoting
• Setup Custom Aggregations
• Using GROUPING SETS Subclause
• CUBE and RULLUP Subclauses
• How to materialize Grouping Sets

Module 5. Using TOP and APPLY

• SELECT TOP
• Using the APPLY table operator
• TOP n at the Group Level
• Implementing Paging

Module 6. Optimizing Data Transformation

• Inserting data with Enhanced VALUES Clause
• Using the BULK Rowset Provider
• Using INSERT EXEC
• The Sequence Mechanisms
• DELETE with joins
• UPDATE with joins
• MERGE statement
• The OUTPUT Clause with INSERT
• The OUTPUT Clause with DELETE
• The OUTPUT Clause with UPDATE
• The OUTPUT Clause with MERGE

Module 7. Querying Partitioned Tables

• Partitioning in SQL Server
• How to write queries on partitioned tables
• How to write queries on partitioned views

Duration

21 hours (usually 3 days including breaks)

Requirements

Knowledge of computer and Windows operating system

Overview

The course is designed for administrators, developers and database developers.

The objectives of the training:

• acquire and strengthen the skills to create and manage databases
• knowledge of the syntax and use SQL to retrieve and modify data
• apply safety rules in the database
• the use of advanced elements (replication, automation, BI)
• the use of Microsoft SQL Server capabilities to create complex reports and solutions for developers

Course Outline

Basic information about databases

• Database files, Database Client / Server
• Relational database management systems (RDBMS)

SQL Server versions and the differences between them

• Express, Standard, Enterprise
• Workgroup, Mobile , Developer

SQL Server Tools

• SQL Server Management Studio
• SQL Server Agent

Services in SQL Server

• Database Services
• Analysis Services
• Reporting Services
• Integration Services

Base system

• Master
• Model
• Msdb
• Tempdb
• Distribution
• Resource      

Create a database

• The database files and their location
• File size and its growth
• Partitioning tables

Data Modification Language (DML)

• INSERT
• UPDATE
• DELETE

Data Definition Language (DDL)

Designing Tables

• Columns and Attributes
• Determine the type of data
• Indexes and Keys

Indexes

• Clustered Indexes
• Non-Clustered Indexes
• REBUILD vs. REORGANIZE 

Creating a database application

Server-side programming

• Procedures
• Views
• Functions
• Triggers

Models play

• Simple
• Full
• Bulk logged

Backup

• Full
• Differential
• Transaction Log

Backup Strategies

• Strategy full copy of the database
• Strategy of full backups and transaction log
• The strategy of incremental database backup
• Strategy copy of the database files

Basic security and administration of SQL Server

Automation

• Maintenance Plan
• Jobs

Basic security and administration of SQL Server

• Server Roles
• Database Roles
• SA Account
• Creating Accounts
• Schemes

High Availability

• Log Shipping
• Database Mirroring
• Server Clustering

Replication

• Snapshot Replication
• Transactional Replication
• Merge Replication

Activity Monitor SQL Server

• SQL Server Profiler

Upgrade

• In-Place vs. Side-By-Side
• Service-Pack
• Cluster

Best Practices

Duration

14 hours (usually 2 days including breaks)

Requirements

Delegates should be familiar with the Microsoft SQL Server SQL Language

Overview

The aim of this course is to provide a clear understanding of the advanced use of (SQL) for Microsoft SQL Server and the advanced use of Transact-SQL. For more in depth coverage of the topics this course can be run as a three day course.

Course Outline

Review of Structured Query Language

• DQL, DML, DDL
• The GROUP BY, HAVING Clause
• Sub-queries and Correlated Sub-queries
• Advanced Update & Delete Statements
• Sub-queries
• Correlated Sub-queries

Procedural Programming

• Variables
• Control-Of-Flow Statements
• IF, WHILE, CASE, GOTO, RETURN

Managing Errors

• Responding To Errors
• RAISERROR
• PRINT

Using Transactions

• Introduction To Transactions
• Transaction Isolation Levels
• Deadlocks
• Transactional Error Handling

Implementing Cursors

• Declaring Cursors
• OPEN, FETCH, CLOSE
• DEALLOCATE
• CURRENT OF

Stored Procedures

• Creating Stored Procedures
• Passing values into a Stored Procedure
• Returning Information From Stored Procedures
• Altering Stored Procedures

Triggers

• Creating Triggers
• Transactional Error Handling
• Using Inserted and Deleted Tables