Microsoft SQL Server: Querying Data for Report Developers

Course:   SSQFD
Duration:   3 Days
Level:   I
On our website at:   http://www.verhoef-training.com/courses/SSQFD.html
 
Course Summary

This 3 day course is intended for students who need to familiarize themselves with the various querying techniques available to retrieve data from a Microsoft SQL Server database in a report authoring environment, whether that be through an implementation of Microsoft SQL Server Reporting Services or similar reporting tool. No previous knowledge of the SQL language is assumed as the course is designed from the ground up, starting with basic queries and advancing to more complex querying concepts. The course also examines good query design and best practices by taking a look at how queries are processed by the Microsoft SQL Server database engine, and the implementation of programming objects such as Stored Procedures and User Defined Functions. On completion of the course students will be fully conversant with the SQL language and able to right effective queries for reporting purposes.

Topics Covered In This Course

Introduction to SQL Server Management Studio

  • Objects in a SQL Server Database
  • Database Diagrams
  • Components of a SQL Server Database Table
  • Native SQL Server Data Types

Introduction to SQL and Transact-SQL (T-SQL)

  • Categories of SQL Statements
  • T-SQL Scripts
  • Adding Comments to T-SQL Code
  • Working with Variables
  • Conditional Statements

Using the SELECT Statement to Retrieve Data

  • Examine the Basic Syntax of the SELECT Statement
  • Retrieving Columns of Data from a Table
  • Using Aliases for Column Names
  • Using Aliases for Table Names

Retrieving Specific Rows of Data Using the WHERE Clause

  • How to Use the WHERE Clause
  • Overview of Operators
  • Filter Data by Using Comparison Operators
  • Filter Data by Using String Comparisons
  • Filter Data by Using Logical Operators
  • Filter Data Using a Range of Values
  • Filter Data Using a List of Values
  • Working with NULL Values
  • Introducing Native SQL Server Functions
  • Functions Relating to NULL Values

Formatting and Sorting Result Sets

  • Sorting Data Using the ORDER BY Clause
  • Eliminating Duplicate Rows
  • Using String Literals
  • Using Expressions

Querying Data from Multiple Tables by Using Joins

  • Introduction to Joins
  • Using Inner Joins
  • Using Outer Joins
  • Joining More than Two Tables
  • Joining a Table to Itself (Self Join)

Summarizing Data Using Aggregate Functions

  • Using Aggregate Functions Native to SQL Server
  • Using Aggregate Functions with NULL Values
  • Grouping Summarized Data with GROUP BY
  • Filtering Grouped Data Using the HAVING Clause

Ranking Grouped Data

  • Ranking Data Using Ranking Functions
  • Using ROW_NUMBER
  • Using RANK
  • Using DENSE_RANK
  • Using NTILE
  • Summary of Ranking Functions Based on Their Functionality

Combining and Limiting Result Sets

  • Combining Result Sets Using the UNION Operator
  • Limiting Result Sets Using the TOP Operator

Working with Subqueries

  • Introduction to Subqueries
  • Using Subqueries as Expressions and Derived Tables
  • Using Correlated Subqueries
  • Using the EXISTS Clause with Correlated Subqueries

Performance Considerations for Writing Queries

  • How SQL Server Processes Queries
  • How the Query Optimizer Processes Search Arguments
  • Writing Efficient Search Arguments

Working with SQL Server Date and Time Data

  • Data Type Precedence
  • Implicit and Explicit Data Type Conversion
  • Querying Date and Time Data

Implementing Stored Procedures

  • Overview of Stored Procedures
  • How Stored Procedures Are Executed by SQL Server
  • Creating and Using a Stored Procedure
  • Parameterizing Stored Procedures

Implementing User Defined Functions

  • Overview of User Defined Functions (UDFs)
  • Creating and Modifying UDFs
  • Restrictions When Creating UDFs
  • Implementing Different Types of UDFs
What You Can Expect

Upon successful completion of this course, students will be able to:

Related Courses
Code Course Title Duration Level
DSSRPT
Maintaining, Troubleshooting, and Developing Solutions with Microsoft SQL Server 2008 Reporting Services
5 Days
I
Details
SSRS1
SQL Server Reporting Services with BI Tools Part 1 - Report Builder, Data Tools, Cubes, and Tabular Reporting
5 Days
I
Details
SSASTM
SQL Server Analysis Services 2014/2012 - Tabular model with DAX
3 Days
I
Details
SSASAA
SQL Server Analysis Services 2014/2012 - Advanced DAX Analytics
2 Days
II
Details
SSASAO
SQL Server Analysis Services 2014/2012 - Advanced Data Analysis and Optimization
3 Days
II
Details
SSRSXP
SQL Server Reporting Services 2014/2012 - Extensions and Programming
3 Days
II
Details
SSRSAR
SQL Server Reporting Services 2014/2012 - Advanced Reporting
3 Days
II
Details
SSRS2
SQL Server Reporting Services with BI Tools Part 2 - Report Server Admin, Extensive Tabular Modeling/Reporting, and C#/VB Extensions
5 Days
II
Details
SSRPT
Microsoft SQL Server 2008 Reporting Services
3 Days
II
Details
SSRSJM
SQL Server Reporting Services 2014/2012 - Jedi Master Level
2 Days
III
Details

Every student attending a Verhoef Training class will receive a certificate good for $100 toward their next public class taken within a year.

You can also buy "Verhoef Vouchers" to get a discounted rate for a single student in any of our public or web-based classes. Contact your account manager or our sales office for details.