Loading...

Course Description

This course is intended to give experienced application developers better query testing techniques, more depth with complex SQL syntax, a good grasp of performance considerations, and an overview of writing stored procedures, functions and triggers.

Related Courses

Who Should Attend

Application developers who have taken SQL Basics or the equivalent work, and had several weeks of experience using SQL. The course covers ANSI/ISO standard SQL, with examples in SQL Server, Oracle, DB2 luw and z/os, and MySQL, with emphasis on SQL Server and Oracle.

Prerequisites

SQL for application developers, recent training in SQL, or at least six months of hands-on experience with SQL in any database environment.

Attend and You Will Learn / Expected Outcome

Expected Outcomes

  • Sample a very large database to have accurate knowledge of the structure and contents
  • Use several tools to monitor and possibly improve the performance of complex queries on large databases, including using the query execution plan, optimizer hints, and other tools
  • Know when and how to use temporary tables, query-scope tables, Common Table Expressions, and subqueries of various types to solve complex problems
  • Handle all logical and performance issues with joins and subqueries on large tables
  • Handle recursive relationships with recursive With, the Oracle Connect By, and/or the SQL Server HierarchyID data type
  • Handle character data with built-in functions, Soundex, full-text searches, and Regular Expression
  • Use Case logic to control how sorting, grouping and other operations work
  • Summarize data, including handling missing values, creating pivot reports using Pivot or Case logic, using Rollup and Cube, and using basic analytic (OLAP) functions
 

Attend And You Will Learn

Introduction

  • Techniques for sampling a large database
  • Performance issues
  • Viewing and interpreting query execution plans, choice of access paths by the Optimizer
  • Minimizing locking, using Isolation levels, preventing deadlocks
  • Minimizing sorting
  • Maximizing index use
  • Using optimizer hints

Techniques for solving complex problems

  • Creating and using temporary tables
  • Using query-scope tables
  • Using Common Table Expressions (CTEs)

Advanced use of multiple tables

  • Reviewing of joins:  inner, outer and self-joins
  • Logical and performance comparison of joins and subqueries;  viewing query execution plans, using optimizer hints
  • Recursive joins in simple and complex relationships
  • Recursive With
  • Oracle's Connect By
  • HierarchyID data type in SQL Server 2008+
  • Logical and performance issues in Difference queries, using Not In and Not Exists

Text handling issues

  • Overview/review of character-handling functions in SQL, e.g. extraction, replacement, upper/lower case, Soundex, etc
  • Using Unicode data
  • Using Regular Expressions
  • Creating a full-text index, using the Contains function

Advanced Case logic

  • Overview/review of "simple" and "searched" cases in the Select or Where clause
  • Using Case in various situations
  • Inside a function
  • In the From clause to choose a table
  • In the Order By clause to modify sorting
  • In the Group By clause to modify grouping
  • In an Update
  • With nested Selects

Advanced Summarization

  • Overview/review of Group By and Having clauses
  • Grouping when there are missing values:  using a temporary or query-scope table to fill in missing values
  • Creating Pivot reports
  • Using the Pivot clause
  • Using Case logic
  • Creating sub- and grand totals with Rollup and Cube
  • Overview of Analytic (OLAP) functions
  • The Over clause, Partition By and Order By
  • The Rank and Dense_rank functions
  • The Lead and Lag functions

Overview of Stored Procedures

  • Why and how to create a stored procedure
  • Using parameters
  • Creating a simple user-defined function
Loading...

Enroll Now - Select a section to enroll in

Section Title
SQL Level 2
Type
Online
Days
Th, F
Time
9:00AM to 4:30PM
Dates
Oct 15, 2020 to Oct 16, 2020
Schedule and Location
Contact Hours
12.0
Location
  • Off Campus
Delivery Options
Course Fee(s)
Registration Fee non-credit $899.00