SQL Intermediate



Course Length: 2 Days

Learning Objectives:
  • 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 Expressions
  • 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
Target Student:
This course is designed for application developers.

Course Outline:
Section 1: Introduction
  • The ISO Standard
  • Learning about the database structure
  • Sampling a large database
  • Performance considerations
  • Minimize locking
  • Maximize index use
  • Statistics and Access Paths
  • Viewing a query execution plan
  • Getting performance help
  • Optimizer hints
Section 2: Techniques for Solving Complex SQL Problems
  • Creating temporary and query-scope tables and indexes
  • Review of inserting, updating, and deleting
  • Common Table Expressions (CTEs)
Section 3: Using Multiple Tables
  • Intersection: inner joins
  • Join performance considerations
  • Inner joins vs. Outer joins
  • Left, Right, and Full outer joins
  • Recursive joins and complex relationships
  • More complex relationships
  • The "bill of materials" problem
  • Recursive With
  • Oracle's Connect By
  • SQL Server HierarchyID data type
  • Difference: Not Exists and Not IN
  • Set operators: Union, Union All
Section 4: Text Handling Issues
  • Text handling functions
  • Unicode
  • Regular Expressions
  • Creating a full-text index using the Contains function
Section 5: Case Logic
  • Review of "simple" When clauses
  • Review of "searched" When clauses
  • Case in other contexts
  • Within functions and expressions
  • In a From clause
  • In an Order By clause
  • In an Update
  • With nested Selects
  • In Group By
Section 6: SQL Summarization
  • Review Group By
  • Filtering groups with Having
  • Additional summarizing examples
  • Using Pivot clause
  • Using Case logic
  • Analytic (OLAP) functions
  • Other considerations: logical and performance issues
Section 7: Stored Procedures
  • Overview of stored procedures
  • Basic stored procedures

This Course Comes With

PDF Manual

Take the manual anywhere and save the environment with an electronic copy of your training manual for use on any of your computers or devices


Customize Your Course

Tailored Training

Your manual will follow a standard course outline but your class can be tailored to focus on the subjects that are most important to you


Delivered by Our Trusted Partner Protech
Intermediate
Date(s) Price Qty
September 1 & 2, 2022 10am to 5pm EST $1,440.00 (CAD)*  
January 12 & 13, 2023 10am to 5pm EST $1,440.00 (CAD)*  
May 4 & 5, 2023 10am to 5pm EST $1,440.00 (CAD)*  

* 13% HST will be applied on checkout


Online registration closes two business days before the start of the class.
Questions?
Do you have questions about this course? Would you like a class sooner or on a different date? Are you interested in scheduling a private group class? Please submit the simple form below and we will respond promptly.
Private Training for GroupsIf you are training 4+ people, a private training session may be more cost-effective. Click to contact us today and learn more.
Register for this class here
Learn more about each course here
Contact us for more information here