Advanced SQL: Hands-On

Target Audience:

  • Those involved with developing and managing systems using SQL Server databases, or who are using SQL to extract and analyze data from SQL Server databases. 
  • Audience could include those responsible for development, operation and quality of systems.


Objectives.

You will learn how to:

  • Develop complex and robust SQL queries, test queries and avoid common pitfalls
  • Leverage SQL to apply best practices for solving problems
  • Query multiple tables with inner joins, outer joins and self-joins
  • Transform data with built-in functions
  • Summarise data using aggregation and grouping
  • Effectively utilise Performance Monitoring Tools and Dynamic Management Objects
  • Use Indexes to Improve Performance


Course Outline.

Introduction

  • Brief Overview of SELECT Clauses:
    • SELECT, FROM, WHERE, ORDER BY
    • TOP and DISTINCT
  • Using Built-In Functions:
    • String, number functions
    • Date manipulation functions
    • CASE Operator

Multi-table queries

  • Joins:
    • Cross Joins
    • Inner Joins
    • Outer Joins
  • Other Multi-table constructs:
    • Common Table Expressions
    • Subqueries
    • APPLY operator
  • Set Operators:
    • UNION
    • INTERSECT
    • EXCEPT

Grouping and Windowing

  • Grouped Queries:
    • Basic Grouping
    • Multiple Grouping Sets
  • PIVOT and UNPIVOT operators:
    • Pivoting and unpivoting
  • Windowing Functions:
    • Aggregate Functions
    • Ranking Functions
    • Offset Functions

Querying XML data

  • Relational to/from XML:
    • FOR XML
    • Shredding XML to Tables
  • XQuery Basics:
    • XPath
    • FLWOR Expressions

Creating Tables and Constraints

  • Creating and Managing:
    • Permanent Tables
    • Temporary Tables
    • Data Types
  • Constraint Types:
    • Implementing primary key and unique constraints
    • Foreign key constraints
    • Check constraints
    • Default constraints

Implementing CRUD operations

  • Basic Usage:
    • INSERT, UPDATE, DELETE, MERGE
    • Using the OUTPUT option
  • Complex Usage:
    • Update based on a JOIN and Table Expression
    • DELETE based on a JOIN and Table Expression

Procedural Programming with T-SQL

  • Basics:
    • Branching Logic
    • Looping Logic
    • Variables
    • Transactions
    • Error Handling
  • Creating T-SQL Routines and Objects:
    • Implementing Stored Procedures
    • Implementing Triggers
    • Implementing User-Defined Functions

Performance Monitoring Tools

  • Tool based monitors:
    • SQL Profiler
    • SQL Trace
    • Extended Events
  • Using SET Options:
    • Execution plans

Dynamic Management Objects

  • Useful DMO’s

Using Indexes to Improve Performance

  • Implementing:
    • Clustered and nonclustered index
    • Indexed Views
    • Statistics
  • Querying Considerations:
    • SARGs
    • Cursors vs Set-based

My Course Notes

You will receive a full set of course notes and all supporting materials for your course. Hard Copy Delivered to your premises or Downloaded to a chosen device.
 

     OR  

To book this course please call +44 (0) 1444 410296 or email at Info@kplknowledge.co.uk

© Copyright 2019 KPL KnowledgeSolutionsWeb Design By Toolkit Websites