Intermediate SQL for Microsoft SQL Server (SQLINT)

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.

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.

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

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

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