0345 4506120

Advanced SQL Server Database Querying

This course is aimed at SQL Server database developers, administrators and analysts who need to take the basics of SQL Server SQL Data Manipulation Language (DML) statements to the next level. The aim of the course is to show the more advanced use of SELECT, UPDATE and DELETE statements providing delegates with the necessary knowledge and hands on experience to construct complex queries to satisfy complex information extraction requirements.

The course focuses on real world business needs and shows how to make the extensive SQL Server library of functions work together to manipulate and extract data values. It also explores the many different ways of retrieving information from multiple tables from simple multi-table SELECT statements through to complex correlated sub-queries as well as introducing the many ways of analysing and processing data through methods such as grouping of data and implementing computed columns. The course will do more than just teach each topic as an isolated subject; it will show delegates how to integrate all these query methods and more to help satisfy their business information extraction needs.

Covers SQL Server 2005, SQL Server 2008, SQL Server 2012, SQL Server 2014, and SQL Server 2016.

Learning Objectives

The course focuses on real world business needs and shows how to make the extensive SQL Server library of functions work together to manipulate and extract data values. It also explores the many different ways of retrieving information from multiple tables from simple multi-table SELECT statements through to complex correlated sub-queries as well as introducing the many ways of analysing and processing data through methods such as grouping of data and implementing computed columns. The course will do more than just teach each topic as an isolated subject; it will show delegates how to integrate all these query methods and more to help satisfy their business information extraction needs.

Pre-Requisites

Delegates should have SQL Server SQL language experience to the level of the PTR Querying SQL Server Databases.

Course Content

                CROSS Joins    
                INNER Join    
                OUTER Joins    
                FULL OUTER Join    
                Table Aliases    
                Self Join    
                Joining on non-key columns    
                Advanced Joins    
                Mixing INNER and OUTER Joins    
                Local Temporary Tables    
                Global Temporary Tables    
                Single Table Views    
                Multi-table Views    
                Updating Through Views    
                The GROUP BY Clause    
                The HAVING Clause    
                Ranking Grouped Data    
                ROW_NUMBER    
                RANK & DENSE_RANK    
                NTILE    
                The CUBE and ROLLUP options    
                GROUPING SETS    
                PIVOT    
                Simple Sub Queries    
                Using Aggregate Functions in Sub queries    
                Correlated Sub-Queries    
                Avoiding Sub queries    
                Derived Table Sub-Queries    
                Sub Queries in SELECT Lists    
                Simple Update & Delete Statements    
                UPDATES and DELETES With Sub queries    
                The FROM Clause    
                MERGE Statement    
                Simple CTEs    
                Recursive Data Sets    
                Hierarchical Data Sets    

Exams & Certification

This course, along with the Creating Databases in SQL Server and SQL Server Database Querying Courses, helps to prepare delegates for the Microsoft 70-461 exam: Querying Microsoft SQL Server 2012/2014/2016, exam 70-761 Querying Data with Transact-SQL (M20761), and exam 70-762:  Developing SQL Databases  (M20762), which credit towards MCSA and MCSE certifications. It should be noted, however, that all course attendances should be complemented by reference to the skills measured by the exam, a period of self-study and test exams before sitting the actual exam.

Privacy Notice

In order to provide you with the service requested we will need to retain and use your contact information in accordance with our Privacy Notice. If you choose to provide us with this information you explicitly consent to us using the information as necessary to provide the request service to you. If you do not agree please do not proceed to request the service from us.

Marketing Permissions

Would you like to receive our newsletter and other information on products and services which we think will be of interest to you by email. We will always treat your information with care and in accordance with our Privacy Notice. You are free to withdraw this permission at any time.

 

Our Customers Include