Intermediate SQL for Microsoft SQL Server

Intermediate SQL for Microsoft SQL Server Courseware (SQLINT)

Microsoft SQL ServerMicrosoftSQLData

The Intermediate SQL for Microsoft SQL Server course is intended for SQL users in programming roles who have taken an Introduction to SQL course. This course covers creating CTEs, using a Windows function, creating and using Global Variables, and pivoting data. This course is applicable to Microsoft SQL Server. Some topics may be applicable to other Database Management Systems, but most topics will not be supported in other DBMSs. 

This course use data from the Adventureworks and OGCBOOKS databases.

Publisher: MMB Training Solutions

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 

Outline

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

Required Prerequisites

Before attending this course, you need to be able to do the following:

  • 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. 

Useful Prerequisites

Before attending this course, it is useful to know the following:

  • Use character functions such as SUBSTRING.
  • Create aggregates using a summary function and a more than one column on the GROUP BY clause.
License

Length: 1 day | $50.00 per copy

LicenseRequest More InformationDownload Sample CopyRequest Trainer Evaluation Copy
What is Included?
  • Student Manual
  • Student Class Files