Intermediate SQL for Microsoft SQL Server

Intermediate SQL for Microsoft SQL Server (SQLINT)

Course Length: 1 day
Delivery Methods: Available as private class only
Course Overview

This Intermediate SQL for SQL Server course builds on foundational SQL skills, focusing on advanced querying techniques, pattern matching, and data manipulation to enhance your SQL Server proficiency. Designed for those with basic SQL knowledge, this course will deepen your understanding of SQL functions and provide hands-on experience with complex query structures.

The course begins with a Review of common functionality from the introductory SQL course, ensuring that participants have a solid grasp of basic SQL concepts before advancing to more complex topics.

In Creating Totals and Subtotals, you will learn how to generate grand totals and subtotals within your queries. This module covers techniques for specifying labels on total lines and adding subtotals, helping you better summarize and present data results in SQL Server.

The Creating Common Table Expressions (CTEs) module dives into the creation and use of CTEs, a powerful tool for simplifying complex queries. You’ll learn when and why to use CTEs, understand the differences between CTEs and views, and explore alternative in-line view methods for achieving similar results.

Performing Pattern Matching focuses on techniques for identifying specific patterns within data. You’ll learn to use the LIKE operator in WHERE clauses and the PATINDEX function in SELECT statements to perform flexible and powerful pattern-matching operations.

In Creating Cross-tabular Results, you will explore how to pivot data within SQL Server, transforming rows into columns to create cross-tabular results that are ideal for reporting and data analysis.

The Ranking Results module teaches you to rank data using SQL Server’s window functions. You will use the RANK function to determine top values and display specific numbers or percentages of rows based on sorted data, making it easier to identify trends and key insights.

Using Global Variables introduces the concept of variables in SQL, showing you how to create and utilize variables for code substitution. This capability enhances query flexibility and reusability, allowing for dynamic code execution.

Finally, Creating Loops covers the creation of loops to execute repetitive code blocks within your SQL scripts. This section equips you with the skills to automate repetitive tasks, increasing efficiency and reducing manual intervention in your database operations.

By the end of this course, participants will have gained advanced SQL skills, including creating dynamic queries, performing complex data transformations, and automating database tasks. These skills will empower you to work more efficiently with SQL Server, enhancing both your data analysis capabilities and overall database management practices.

Course Benefits

At the end of this course students will be able to:

  • Create Totals and Subtotals
  • Create and use Common Table Expressions (CTEs) and Derived Tables
  • Perform Pattern Matching 
  • Create a Cross-tabular report using the PIVOT function
  • Rank Results using a Windows Function
  • Create and Use Global Variables
  • Loop through code repetitively 
Course Outline
  1. Review
    1. Review common functionality from Introduction to SQL course.
  2. Creating Totals and Subtotals
    1. Create grand totals.
    2. Specify a label on the total line.
    3. Create subtotals and labels.
  3. Creating Common Table Expression
    1. Define why to create Common Table Expression (CTE).
    2. Define the difference between a CTE and a view.
    3. Create and use a CTE.
    4. Use an in-line view as an alternative to a CTE.
  4. Performing Pattern Matching
    1. Use the LIKE operator for pattern matching in a WHERE clause.
    2. Use the PATINDEX function for pattern matching in a SELECT statement.
  5. Creating Cross-tabular Results
    1. Pivot the data in a table.
  6. Ranking Results
    1. Use ranking to determine top values using the Windows Function of RANK.
    2. Display an absolute number of rows or percentage of rows based on the sorted data.
  7. Using Global Variables
    1. Create variables to be used for code substitution.
  8. Creating Loops
    1. Create loops to repetitively execute code.
Class Materials

Each student will receive a comprehensive set of materials, including course notes and all the class examples.

Class Prerequisites

Experience in the following is required for this Microsoft SQL Server class:

  • Write simple SQL queries using the SELECT statement.
  • Sort data using an ORDER BY clause. 
  • Filter data using the WHERE clause.
  • Use the IN operator on a WHERE clause to select multiple values. 
  • Create aggregates using a summary function and a GROUP BY clause. 

Experience in the following would be useful for this Microsoft SQL Server class:

  • Use character functions such as SUBSTRING.
  • Create aggregates using a summary function and a more than one column on the GROUP BY clause.
Live Private Class
  • Private Class for your Team
  • Live training
  • Online or On-location
  • Customizable
  • Expert Instructors
Request Proposal