Call Us: +32 2 466 00 16
Email: info@u2u.be
Follow Us:

Querying SQL Server with Transact-SQL

3days
Training code
ms2778
Book this course

Module 1: Getting Started with Databases and Transact-SQL in SQL Server 2008

Lessons

  • Overview of SQL Server 2008
  • Overview of SQL Server Databases
  • Overview and Syntax Elements of T-SQL
  • Working with T-SQL Scripts
  • Using T-SQL Querying Tools

Lab : Using SQL Server Management Studio and SQLCMD

  • Exploring the Components and Executing Queries in SQL Server Management Studio
  • Starting and Using SQLCMD
  • Generating a Report from a SQL Server Database Using Microsoft Office Excel

After completing this module, students will be able to:


  • Describe the architecture and components of SQL Server 2008.
  • Describe the structure of a SQL Server database.
  • Explain the basics of the SQL language.
  • Describe the syntax elements of T-SQL.
  • Explain how to manage T-SQL scripts.
  • Use T-SQL querying tools to query SQL Server 2008 databases.

Module 2: Querying and Filtering Data

Lessons

  • Using the SELECT Statement
  • Filtering Data
  • Working with NULL Values
  • Formatting Result Sets
  • Performance Considerations for Writing Queries

Lab : Querying and Filtering Data

  • Retrieving Data by Using the SELECT Statement
  • Filtering Data by Using Different Search Conditions
  • Using Functions to Work with NULL Values
  • Formatting Result Sets

After completing this module, students will be able to:


  • Retrieve data by using the SELECT statement.
  • Filter data by using different search conditions.
  • Explain how to work with NULL values.
  • Format result sets.
  • Describe the performance considerations that affect data retrieval.

Module 3: Grouping and Summarizing Data

Lessons

  • Summarizing Data by Using Aggregate Functions
  • Summarizing Grouped Data
  • Ranking Grouped Data
  • Creating Crosstab Queries

Lab : Grouping and Summarizing Data

  • Summarizing Data by Using Aggregate Functions
  • Summarizing Grouped Data
  • Ranking Grouped Data
  • Creating Crosstab Queries

After completing this module, students will be able to:


  • Summarize data by using aggregate functions.
  • Summarize grouped data by using the GROUP BY and COMPUTE clauses.
  • Rank grouped data.
  • Create cross-tabulation queries by using the PIVOT and UNPIVOT clauses.

Module 4: Joining Data from Multiple Tables

Lessons

  • Querying Multiple Tables by Using Joins
  • Applying Joins for Typical Reporting Needs
  • Combining and Limiting Result Set

Lab : Joining Data from Multiple Tables

  • Querying Multiple Tables by Using Joins
  • Applying Joins for Typical Reporting Needs
  • Combining and Limiting Result Sets

After completing this module, students will be able to:


  • Query multiple tables by using joins.
  • Apply joins for typical reporting needs.
  • Combine and limit result sets.

Module 5: Working with Subqueries

Lessons

  • Writing Basic Subqueries
  • Writing Correlated Subqueries
  • Comparing Subqueries with Joins and Temporary Tables
  • Using Common Table Expressions

Lab : Working with Subqueries

  • Writing Basic Subqueries
  • Writing Correlated Subqueries
  • Comparing Subqueries with Joins and Temporary Tables
  • Using Common Table Expressions

After completing this module, students will be able to:


  • Write basic subqueries.
  • Write correlated subqueries.
  • Compare subqueries with joins and temporary tables.
  • Use common table expressions in queries.

Module 6: Modifying Data in Tables

Lessons

  • Inserting Data into Tables
  • Deleting Data from Tables
  • Updating Data in Tables
  • Overview of Transactions

Lab : Modifying Data in Tables

  • Inserting Data into Tables
  • Deleting Data from Tables
  • Updating Data in Tables
  • Working with Transactions

After completing this module, students will be able to:


  • Insert data into tables.
  • Delete data from tables.
  • Update data in tables.
  • Describe transactions.

Module 7: Querying Metadata, XML, and Full-Text Indexes

Lessons

  • Querying Metadata
  • Overview of XML
  • Querying XML Data
  • Overview of Full-Text Indexes
  • Querying Full-Text Indexes

Lab : Querying Metadata, XML, and Full-Text Indexes

  • Querying Metadata
  • Querying XML Data
  • Creating and Querying Full-Text Indexes

After completing this module, students will be able to:


  • Query metadata.
  • Describe the functionality of XML.
  • Query XML data.
  • Describe the functionality of full-text indexes.
  • Query full-text indexes.

Module 8: Using Programming Objects for Data Retrieval

Lessons

  • Overview of Views
  • Overview of User-Defined Functions
  • Overview of Stored Procedures
  • Overview of Triggers
  • Writing Distributed Queries

Lab : Using Programming Objects for Data Retrieval

  • Creating Views
  • Creating User-Defined Functions
  • Creating Stored Procedures
  • Writing Distributed Queries

After completing this module, students will be able to:


  • Encapsulate queries by using views.
  • Encapsulate expressions by using user-defined functions.
  • Explain how stored procedures encapsulate T-SQL logic.
  • Define triggers, types of triggers, create a trigger.
  • Write distributed queries.

Module 9: Using Advanced Querying Techniques

Lessons

  • Considerations for Querying Data
  • Working with Data Types
  • Cursors and Set-Based Queries
  • Dynamic SQL
  • Maintaining Query Files

Lab : Using Advanced Querying Techniques

  • Using Execution Plans
  • Converting Data Types
  • Implementing a Hierarchy
  • Using Cursors and Set-Based Queries

After completing this module, students will be able to:


  • Explain the recommendations for querying complex data.
  • Query complex table structures.
  • Write efficient queries.
  • Use various techniques when working with complex queries.
  • Maintain query files.

This 3-day instructor led course provides students with the technical skills required to write basic Transact-SQL queries for Microsoft SQL Server 2008.


This course is intended for SQL Server database administrators, implementers, system engineers, and developers who are responsible for writing queries.


© 2018 U2U All rights reserved.