The five-day Querying Microsoft SQL Server® training course will provide you with the technical skills required to write basic Transact-SQL queries for Microsoft SQL Server 2014. This Querying Microsoft SQL Server® training course is the foundation for all SQL Server related disciplines; namely, Database Administration, Database Development and Business Intelligence.
Module 1: Introduction To Microsoft SQL Server 2014
The Basic Architecture Of SQL Server
SQL Server Editions And Versions
Getting Started with SQL Server Management Studio
Lab: Working With SQL Server 2014 Tools
Module 2: Introduction To T-SQL Querying
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
Eliminate Duplicates With DISTINCT
Using Column And Table Aliases
Write Simple CASE Expressions
Lab: Writing Basic SELECT Statements
Module 4: Querying Multiple Tables
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
Filtering Data With Predicates
Filtering With The TOP And OFFSET-FETCH
Working With Unknown Values
Lab: Sorting And Filtering Data
Module 6: Working With SQL Server 2014 Data Types
Introducing SQL Server 2014 Data Types
Working With Character Data
Working With Date And Time Data
Lab: Working With SQL Server 2014 Data Types
Module 7: Using DML To Modify 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 Table Expressions
Using Inline Table-Valued Functions
Using Derived Tables
Using Common Table Expressions
Lab: Using Table Expressions
Module 12: Using Set Operators
Writing Queries With The UNION Operator
Using EXCEPT And INTERSECT
Lab: Using Set Operators
Module 13: Using Window Ranking, Offset, And Aggregate Functions
Creating Windows With OVER
Exploring Window Functions
Lab: Using Window Ranking, Offset and Aggregate Functions
Module 14: Pivoting And Grouping Sets
Writing Queries With PIVOT And UNPIVOT
Working With Grouping Sets
Lab: Pivoting and Grouping Sets
Module 15: Querying Data With Stored Procedures
Writing Queries with PIVOT And UNPIVOT
Passing Parameters To Stored Procedures
Creating Simple Stored Procedures
Working With Dynamic SQL
Lab: Executing Stored Procedures
Module 16: Programming With T-SQL
T-SQL Programming Elements
Controlling Program Flow
Lab: Programming With T-SQL
Module 17: Implementing Error Handling
Using TRY / CATCH Blocks
Working With Error Information
Lab: Implementing Error Handling
Module 18: Implementing Transactions
Transactions And The Database Engine
Lab: Implementing Transactions
Module 19: Appendix 1: Improving Query Performance
Factors In Query Performance
Displaying Query Performance Data
Lab: Improving Query Performance
Module 20: Appendix 2: Querying SQL Server Metadata
Querying System Catalog Views And Functions
Executing System Stored Procedures
Querying Dynamic Management Objects
Lab: Querying SQL Server Metadata
The Querying Microsoft SQL Server® training course is designed for those who are interested in learning SQL Server 2012 or SQL Server 2014. It covers the new features in SQL Server 2014 but also the important capabilities across the SQL Server data platform.
This interactive training session can be taken from any location, your office or home and is delivered by a tutor. Virtual students do not need to travel for the training course and Purple Griffon will send you all of the information needed before the start of the training course so you can test your login details.
The Querying Microsoft SQL Server® training course is intended for Database Administrators, Database Developers, and Business Intelligence Professionals.
The Querying Microsoft SQL Server® training course will very likely be well attended by SQL power users who aren’t necessarily database-focused or plan on taking the exam; namely, Report Writers, Business Analysts and Client Application Developers.
Security: SQL Server provides two security models including integration with Windows Authentication to provide robust security platform for managing your organisations information. Security can be managed across all database objects including tables, views, stored procedures and at a very granular level, to provide complete information security. Database encryption is now available as well to secure SQL Server database at rest.
Scalability: SQL Server is a highly scalable enterprise database platform capable of managing petabytes of data while delivery high performance. This allows organisations to deploy SQL Server databases with the confidence that SQL Server can continue to deliver superior performance even as their needs and businesses grow.
High Availability/Failover: SQL Server provides a variety of high availability features including fail over clustering available on the Standard and Enterprise editions, database mirroring, log shipping, and database snapshots to meet your organisations' needs.
Business Intelligence: SQL Server Standard and Enterprise Editions come with extensive business intelligence capabilities through SQL Server Analysis Services. Analytical models can be developed to breakdown business information to provide business insights for managing and forecasting business needs. SQL Server also comes with integrated reporting capabilities from SQL Server Reporting Services which allows organisations to access business critical information across the organisation. In more recent versions of SQL Server, PowerPivot for Microsoft Excel has been added to allow users another familiar tool for analysing business data.
Integration: SQL Server provides a robust set of integration tools call SQL Server Integration Services (SSIS) which can be used consolidate data from multiple heterogenous data sources for holistic data view and comprehensive data analysis. SSIS can also be used to migrate data from other database formats (Oracle, DBase, Paradox, legacy formats, Microsoft Access) to SQL Server.
The Querying Microsoft SQL Server® training course will keep your personal or professional skills up to date.
Working knowledge of relational databases.
Basic knowledge of the Microsoft Windows operating system and its core functionality
There is no official prereading with the Querying Microsoft SQL Server® training session.
The main purpose of the Querying Microsoft SQL Server® training course is to prepare you for the examination '70-461: Writing Queries Using Microsoft® SQL Server® 2014 Transact-SQL'. The examination will be the underlying exam for all SQL Server related disciplines; namely, Database Administration, Database Development and Business Intelligence.
The Querying Microsoft SQL Server® training course will give you credit towards the MCP, MCSA, MCSE certifications.
The five day Implementing A Data Warehouse With Microsoft SQL Server® (SSIS) training course will show you how to implement a data warehouse platform to support a BI solution. The training course is designed for those who are interested in learning SQL Server 2012 or SQL Server 2014 and covers the new features in SQL Server 2014 but also the important capabilities across the SQL Server data platform.
The Querying Microsoft SQL Server® training course will provide you with the technical skills required to write basic Transact-SQL queries for Microsoft SQL Server 2014 and to pass the examination.