SQL Server

Fast Track offer a range of courses ….

  • Introduction to SQL Server
  • Writing SQL Queries
  • Advanced Querying SQL Databases using T-SQL
  • SQL Server Analysis Services
  • SQL Server Integration Services
  • SQL Server Reporting Services
  • SQL Server Database Administration – call our team for course outline
  • SQL Server Transact-SQL Programming – call our team for course outline

Introduction to SQL Server

Target Audience

The course is designed as an introduction to the important features of relational databases and specifically Microsoft SQL Server.

The course is designed for delegates who need to understand the basic concepts of SQL Server.

pdf download

The Content

Relational Database Concepts

  • What is a Database?
  • Logical and Physical Design
  • The Relational Database
  • Primary Keys & Foreign Keys
  • Entity Relationship Diagrams
  • Design Normalisation

Relational Database Architecture

  • The RDBMS
  • Structured Query Language – SQL
  • Stored Queries
  • Indexes
  • Transactions and Log Files

SQL Server and Architecture

  • Overview of SQL Server
  • Server Fundamentals
  • Users and Security

SQL Server Tools

  • SQL Management Studio
  • SQL Server Books Online

Viewing Data

  • Accessing Data within Database Tables
  • The SELECT Statement
  • Manipulating Data
  • Selecting Rows
  • Sorting Data using ORDER BY
  • Aggregate Functions
  • Querying Multiple Tables Using Joins

 

Database and Table Structures

  • Creating Databases using SQL Server Creating Tables in SQL Server
  • Entering and Editing Data
  • Modifying Tables and Columns
  • Database Diagrams
  • Creating Database Diagrams

Improving Data Access

  • Using Views
  • Speeding up Data Access with Indexes
  • Opening databases with Excel and Access

 

Delegate Pre-Requisites

No previous experience of SQL Server or relational databases is required.

Course Duration

This is a One Day Course

Course Details

Courses commence at 9:30 a.m. through to 4:30 p.m.

A laptop computer is provided for each delegate attending on this packed programme to try out the tips and techniques demonstrated

All delegates will receive comprehensive Training Courseware to refer to during the course, together with a Course Attendance Certificate

Writing SQL Queries

Target Audience

This course is designed to allow delegates to write queries using TSQL looking at common query problems and avoiding coding pitfalls

pdf download

The Content

Introduction and Overview

  • SQL Fundamentals
  • Retrieving data with SELECT
  • Expressions
  • Literals
  • Handling NULLs properly
  • Executing Queries
  • Analysing query plans
  • Enhancing query performance
  • Selecting the best alternatives
  • Avoiding errors and pitfalls

Querying Multiple Tables

  • Implementing various types of joins
  • Inner joins
  • Cross joins
  • Left, right and full outer joins
  • Equijoins vs. theta joins
  • Adding filter conditions to outer joins
  • Writing self-joins
  • Joining a table to itself
  • Chaining self-joins
  • Solving time-interval problems
  • Combining queries with set operators
  • UNION, UNION ALL, INTERSECT, EXCEPT

Scalar and Aggregate Functions

  • Converting data types
  • Performing calculations on dates and times
  • Extracting date and time components
  • Manipulating strings
  • Choosing the right function for the job
  • Summarising data with aggregate functions
  • COUNT, SUM, AVG, MIN, MAX
  • Managing NULLs
  • Suppressing duplicates
  • Grouping data – GROUP BY & GROUP BY ALL
  • Applying conditions with HAVING
  • Extending group queries
  • Nested grouped aggregates
  • Joins and grouping
  • Building crosstab reports
  • Using CASE to turn rows into columns
  • Applying PIVOT

Performing Analysis with Analytic Functions

  • The OVER clause
  • Specifying the ordering before applying the function
  • Splitting the result set into logical partitions
  • Calculating ranks
  • RANK and DENSE_RANK
  • ROW_NUMBER with ordered sets
  • Extending the use of aggregates
  • Partitioning in multiple levels
  • Comparing rows and aggregate values

Building Subqueries

  • Simple subqueries
  • Subqueries in conditions and column expressions
  • Creating multilevel subqueries
  • Avoiding problems with subqueries return NULLS
  • Correlated subqueries
  • Accessing values from the outer query
  • Avoiding accidental correlation
  • Common table expressions
  • Reusable and recursive subqueries
  • Traversing hierarchies

Breaking Down Complex Queries

  • Overcoming SQL limitations
  • Reducing complexity and improving performance
  • Exploring alternatives for decomposing: temporary tables, views, common table expressions

Delegate Pre-Requisites

Experience gained on the SQL Server Introduction course or equivalent, including a basic understanding of databases and an awareness of SQL Servers Management studio is required.

Course Duration

This is a Two Day Course

Course Details

Courses commence at 9:30 a.m. through to 4:30 p.m.

A laptop computer is provided for each delegate attending on this packed programme to try out the tips and techniques demonstrated

All delegates will receive comprehensive Training Courseware to refer to during the course, together with a Course Attendance Certificate

Advanced Querying SQL Databases using T-SQL

Target Audience

The course is aimed at both IT Professionals and non-technical ‘power users’ who already have a solid foundational understanding and experience of Microsoft T-SQL but are looking to attain more advanced T-SQL skills.

The course includes writing queries for use with Microsoft SQL Server to be able to further analyze and manipulate data including more advanced T-SQL knowledge for those who have attended the Writing SQL Queries course.

pdf download

The Content

Using Table Expressions

This module introduces T-SQL expressions which return a valid relational table, typically for further use in the query. The module discusses views, derived tables, common table expressions and inline table-valued functions and Temporary Tables

  • Using Derived Tables
  • Using Common Table Expressions
  • Using Views
  • Using Inline Table-Valued Functions
  • Creating Temporary Tables

Using Set Operators

This module introduces the set operators UNION, INTERSECT, and EXCEPT to compare rows between two input sets.

  • *Writing Queries with the UNION Operator
  • Using EXCEPT and INTERSECT
  • Using APPLY

Advanced use of Window Ranking, Offset, and Aggregate Functions

This module introduces window functions including ranking, aggregate and offset functions. Much of this functionality is new to SQL Server 2012. It will cover the use of T-SQL functions such as ROW_NUMBER, RANK, DENSE_RANK, NTILE, LAG, LEAD, FIRST_VALUE and LAST_VALUE to perform calculations against a set, or window, of rows.

  • Creating Windows with OVER
  • Partitioning Sets with Ranking Functions
  • Exploring Window Functions
  • Writing Queries with PIVOT and UNPIVOT
  • Working with Grouping Sets

Programming with T-SQL

This module provides a basic introduction to T-SQL programming concepts and objects. It discusses batches, variables, control of flow elements such as loops and conditionals, how to create and execute dynamic SQL statements, and how to use synonyms.

  • T-SQL Programming Elements
  • Declaring variables
  • IF conditions
  • While Loops
  • Controlling Program Flow

Implementing Error Handling

This module introduces the use of error handlers in T-SQL code. It will introduce the difference between compile errors and run-time errors, and will cover how errors affect batches. The module will also cover how to control error handling using TRY/CATCH blocks, the use of the ERROR class of functions, and the use of the new THROW statement.

  • Building Simple stored procedures
  • Adding input and output parameters
  • Return Values
  • Using TRY / CATCH Blocks
  • Working with Error Information

Implementing Transactions

This module introduces the concepts of transaction management in SQL Server. It will provide a high-level overview of transaction properties, cover the basics of marking transactions with BEGIN, COMMIT and ROLLBACK.

  • Transactions and the Database Engine
  • Controlling Transactions
  • Isolation Levels
  • Understanding Locks and contention issues

Delegate Pre-Requisites

Delegates should have knowledge to the level of T-SQL courses. This is not a beginners course. For those requiring a basic level of T-SQL for reporting then please review the Writing SQL Queries course first.

Course Duration

This is a Two Day Course

Course Details

Courses commence at 9:30 a.m. through to 4:30 p.m.

A laptop computer is provided for each delegate attending on this packed programme to try out the tips and techniques demonstrated

All delegates will receive comprehensive Training Courseware to refer to during the course, together with a Course Attendance Certificate

SQL Server Analysis Services (SSAS)

Target Audience

The course introduces SSAS as a tool to store data in a cube format and is designed for people who are familiar with Relational Database Management Systems such as SQL Server, but not familiar with OLAP technologies.  It covers the concepts of designing and implementing a data cube for better analysis of business data

pdf download

The Content

Introduction to Data Warehousing & BI

  • Defining Data Warehouse and Business Intelligence (BI)
  • Understanding the requirements for BI
  • Overview of Data Mining

Building and Modifying an OLAP Cube

  • Designing a Unified Dimension Model (UDM)
  • Identifying measures and their suitable granularities
  • Adding new measure groups and creating custom measures
  • Creating dimensions
  • Implementing a Star and Snowflake Schema
  • Identifying role-play dimensions
  • Adding dimension attributes and properties

Extending the Cube with Hierarchies

  • Creating hierarchies
  • Building natural hierarchies and creating attribute relationships
  • Discretising attribute values with the Clusters and Equal Areas algorithms
  • Parent-child relationships
  • Defining parent and key attributes

Exploiting Advanced Dimension Relationships

  • Storing dimension data in fact tables
  • Building a degenerate dimension
  • Configuring fact relationships
  • Saving space with referenced dimension relationships
  • Identifying candidates for referenced relationships
  • Utilising the Dimension Usage tab to configure referenced relationships
  • Including dimensions with many-to-many relationships
  • Implementing intermediate measure groups and dimensions
  • Reporting on many-to-many dimensions without double counting

Managing Cubes

  • Designing storage and aggregations
  • ROLAP, MOLAP, HOLAP
  • Partitioning cubes for improved performance
  • Designing aggregations with Aggregation Design Wizard
  • Leveraging Usage-Based Optimisation Wizard
  • Automating processing and deployment
  • Exploiting XMLA scripts and SSIS
  • Refreshing cubes with Proactive Caching

Performing Advanced Analysis with MDX

  • Retrieving data with MDX
  • Defining tuples, sets and calculated members
  • Querying cubes with MDX
  • Monitoring business performance with KPIs
  • Building goal, status and trend expressions
  • Using PARALLELPERIOD to compare past time periods
  • Simplifying KPI definitions using KPIValue and KPIGoal
  • Enhancing cubes with MDX
  • Adding runtime calculations to the cube
  • Adding drill-through and URL actions

Gaining Business Advantage with Data Mining

  • Determining the correct model
  • Identifying business tasks for data mining
  • Training and testing data-mining algorithms
  • Comparing algorithms with the accuracy chart
  • Performing real-world predictions
  • Classifying with Decision Trees, Neural Network and Naive Bayes algorithms
  • Predicting with the Time Series algorithm

Delegate Pre-Requisites

Delegates should be familiar with Relational Database Management Systems such as SQL Server, but not familiar with OLAP technologies

Course Duration

This is a Three Day Course

Course Details

Courses commence at 9:30 a.m. through to 4:30 p.m.

A laptop computer is provided for each delegate attending on this packed programme to try out the tips and techniques demonstrated

All delegates will receive comprehensive Training Courseware to refer to during the course, together with a Course Attendance Certificate

SQL Server Integration Services (SSIS)

Target Audience

This course is for people who want to be able extract, transform and load data between SQL Server and other systems.  Targets and sources include CSV files, Excel spread sheets and Access databases.

pdf download

The Content

Introduction to Integration Services

  • Defining SQL Server Integration Services (SSIS)
  • Exploring the need for migrating diverse data
  • The role of Business Intelligence (BI)

SSIS Architecture and Tools

  • Managing heterogeneous data
  • Leveraging the Extract, Transform and Load (ETL) capabilities of SSIS
  • Running wizards for basic migrations
  • Creating packages for complex tasks
  • Illustrating SSIS architecture
  • Distinguishing between data flow pipeline and package runtime
  • Executing packages on the client side or hosted in the SSIS service
  • Simplifying deployments
  • Switching between project and package deployment modes
  • Deploying packages to the SSISDB
  • Running packages from SQL Server
  • Leveraging package parameters

Implementing Tasks and Containers

  • Utilizing basic SSIS objects
  • Configuring connection managers
  • Adding data flow tasks to packages
  • Reviewing progress with data viewers
  • Assembling tasks to perform complex data migrations
  • Migrating multiple files with FOREACH container
  • Operating system-level tasks
  • Copying, moving and deleting files
  • Communicating with external sources

*

Deploying and Delivering Reports

* Deploying reports to the server

* Publishing reports and configuring product
properties

Extending Capabilities with Scripting

  • Writing expressions
  • Making properties dynamic with variables
  • Utilizing expressions in loop iterations
  • Debugging, breakpoints and watches
  • Accessing and controlling reports using URLs

Transforming with the Data Flow Task

  • Performing transforms on columns
  • Converting and calculating columns
  • Transforming with Character Map
  • Profiling, combining and splitting data
  • Merge, Union and Conditional Split
  • Multicasting and converting data
  • Aggregate, sort, audit and look up data
  • Redirecting error rows
  • Performing database operations
  • Executing a SQL task
  • Bulk inserting data from text files

Error Handling, Logging and Transactions

  • Organizing package workflow
  • Defining success, failure, completion and expression precedence constraints
  • Handling events and event bubbling

Delegate Pre-Requisites

Delegates should be familiar with SQL Server databases and able to write basic SQL Select statements.

Course Duration

This is a Three Day Course

Course Details

Courses commence at 9:30 a.m. through to 4:30 p.m.

A laptop computer is provided for each delegate attending on this packed programme to try out the tips and techniques demonstrated

All delegates will receive comprehensive Training Courseware to refer to during the course, together with a Course Attendance Certificate

SQL Server Reporting Services (SSRS)

Target Audience

The course introduces SSRS as a tool to create and deliver reports to the business and is designed for people who are familiar with Relational Database Management Systems such as SQL Server.  It is designed to impart the concepts of designing, implementing and deploying reports for better analysis of business data.

pdf download

The Content

Introduction to SQL Server Reporting Services

  • Identifying deployment: native, integrated or single-server
  • Managing web farm deployment with/without SharePoint

Developing Reports

  • Leveraging Reporting Services tools
  • Web reporting using Report Builder
  • Designing fundamental reports
  • Connecting to relational and multidimensional sources
  • Generating a Tablix reporting structure

Composing expressions

  • Computing custom fields
  • Linking expressions to properties
  • Arranging and sorting data
  • Multiple-level grouping and categorizing the results
  • Applying aggregate functions
  • Producing various outputs from a Tablix
  • Creating parallel dynamic group report formats
  • Combining dynamic and static columns

Integrating Parameters and Filters

  • Incorporating parameters into reports
  • Yielding subsets of data with query parameters
  • Constructing cascading report parameters
  • Transmitting parameters to stored procedures
  • Applying filters to report data
  • Augmenting performance with filters
  • Determining filters vs. query parameters

Implementing Interactive Features

  • Combining multiple data regions in one report
  • Applying sequential and nested regions
  • Creating master/detail reports and linking sub-reports
  • Showing robust data with relevant detail
  • Drilling through report detail and drilling down
  • Navigating reports with document maps

*

Deploying and Delivering Reports

* Deploying reports to the server

* Publishing reports and configuring product
properties

Deploying and Delivering Reports

  • Deploying reports to the server
  • Publishing reports and configuring project properties
  • Verifying results with Report Manager
  • Identifying delivery options
  • Enhancing performance with cached instances
  • Configuring snapshot history
  • Dispatching subscription reports
  • Publishing reports via e-mail and file share

Safeguarding Reporting Services

  • Structuring content security
  • Leveraging existing Windows authentication
  • Establishing permission levels on report items
  • Ensuring the RS system
  • Instituting varying levels of administrative roles
  • Granting and revoking system-level permissions

Extended Reporting and Data Visualization

  • Improving data visualization with sparklines, data bars, maps and indicators
  • Implementing Key Performance Indicators (KPIs)
  • Delivering reports to users
  • Accessing and controlling reports using URLs

Delegate Pre-Requisites

Delegates should be capable of writing basic SQL Statements.

Course Duration

This is a Three Day Course

Course Details

Courses commence at 9:30 a.m. through to 4:30 p.m.

A laptop computer is provided for each delegate attending on this packed programme to try out the tips and techniques demonstrated

All delegates will receive comprehensive Training Courseware to refer to during the course, together with a Course Attendance Certificate