Intermediate SQL for Microsoft SQL Server (SQLINT)

The Intermediate SQL for Microsoft SQL Server course is intended for SQL users in programming roles who have taken an Introduction to SQL course.

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

1- Review

• Review common functionality from Introduction to SQL course.

2- Creating Totals and Subtotals

• Create grand totals.
• Specify a label on the total line.
• Create subtotals and labels.

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

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

5- Creating Cross-tabular Results

• Pivot the data in a table.

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

7- Using Global Variables

• Create variables to be used for code substitution.

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

None

None