Advanced SQL for SQL Server Course

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.

Delegates should have SQL Server SQL language experience to the level of the PTR SQL for SQL Server course.

Topics covered on the 2 day Advanced SQL for SQL Server course

The SELECT Statement

• Review of SELECT Statement Basics
• String Expressions
• Arithmetic Expressions
• Numeric Functions
• Mathematical Functions
• String Functions
• Date Functions
• ISNULL Function
• Nesting Functions
• Making Functions Work Together

Review of WHERE Clause

• IS NOT NULL
• Functions in WHERE Clauses
• NOT
• LIKE & BETWEEN..AND
• Multiple Conditions
• IN

The ORDER BY Clause

• TOP
• WITH TIES
• TOP n PERCENT

COMPUTE Clause

Review of Multiple Table SELECTs

• CROSS Joins
• INNER Join
• OUTER Joins
• FULL OUTER Join
• Table Aliases
• Self Join

Advanced Joins

• Multiple INNER JOINS
• Multiple OUTER Joins
• Mixing INNER & OUTER Joins
• UNION Selects
• Temporary Tables
• Table Names
• UNION SELECTs

Advanced SELECT Statements

• The GROUP BY Clause
• The Having Clause
• Subqueries
• Using Aggregate Functions In Subqueries
• The CUBE and ROLLUP options
• Correlated Sub-Queries
• Avoiding Subqueries
• Derived Table Sub-Queries

Advanced Update & Delete Statements

• Simple Update & Delete Statements
• UPDATES and DELETES With Subqueries
• The FROM Clause