This five-day instructor-led course is intended for IT professionals who wish to use the Transact-SQL language to query and configure Microsoft SQL Server. Students are typically database developers and database administrators, but might also be Business Intelligence developers, report creators, or application developers. In this course, students learn how to query single tables, join data from multiple tables, filter and sort data, modify data, use procedures and functions, and implement error handling.

starstarstarstarstar

* Actual course outline may vary depending on offering center. Contact your sales representative for more information.

Learning Objectives

At Course Completion, you should be able to
• Create single table SELECT queries
• Create multiple table SELECT queries
• Filter and sort data
• Insert, update, and delete data
• Query data using built-in functions
• Create queries that aggregate data
• Create subqueries
• Create queries that use table expressions
• Use UNION, INTERSECT, and EXCEPT on multiple sets of data
• Implement window functions in queries
• Use PIVOT and GROUPING SETS in queries
• Use stored procedures in queries
• Add error handling to queries
• Use transactions in queries

1

  • MODULE 1: INTRODUCTION TO TRANSACT-SQL

  • What is Transact-SQL

    The SELECT statement

    The WHERE clause

    Sorting results

    Calculations

    CASE expressions

2

  • MODULE 2: JOINING TABLES WITH TRANSACT-SQL

  • The JOIN clause

    Inner joins

    Outer joins

    Self joins and cross joins

3

  • MODULE 3: FILTERING AND SORTING RESULTS

  • Implement the ORDER BY clause

    Filter data with the WHERE clause

    Limit the number of rows returned by a query

    Implement NULL logic

4

  • MODULE 4: SQL SERVER DATA TYPES

  • Understand data types?

    Implement string data types

    Implement temporal data types

5

  • MODULE 5: INSERTING, UPDATING AND DELETING DATA

  • Insert new records

    Update existing records

    Delete data

6

  • MODULE 6: USING SQL SERVER FUNCTIONS WITH TRANSACT-SQL

  • Understand function types in SQL Server

    Convert data using functions

    Implement logical functions

    Work with NULL data using functions

7

  • MODULE 7: AGGREGATING DATA WITH TRANSACT-SQL

  • Implement aggregation in SQL Server

    Group records in SQL Server

    Filter aggregated data

8

  • MODULE 8: IMPLEMENT SUBQUERIES WITH TRANSACT-SQL

  • Implement scalar and multi-valued sub-queries

    Implement correlated subqueries

    Implement existence checks with subqueries

9

  • MODULE 9: CREATE QUERIES THAT USE TABLE EXPRESSIONS

  • Create views

    Create table-valued functions

    Implement derived tables

    Implement common table expressions

10

  • MODULE 10: USE UNION, INTERSECT, EXCEPT AND APPLY ON MULTIPLE SETS OF DATA

  • Write queries with the UNION operator

    Write queries with the INTERSECT and EXCEPT operators

    Write queries with the APPLY operator

11

  • MODULE 11: IMPLEMENT WINDOW FUNCTIONS IN QUERIES

  • Understand window functions

    Impement window functions

12

  • MODULE 12: USE PIVOT AND GROUPING SETS IN QUERIES

  • Implement PIVOT in queries

    Implement grouping sets in queries

13

  • MODULE 13: USE STORED PROCEDURES IN QUERIES

  • Query data with stored procedures

    Interact with stored procedures using input and output parameters

    Write simple stored procedures

    Pass dynamic SQL to SQL Server

14

  • MODULE 14: IMPLEMENT PROGRAMMING FEATURES IN TRANSACT-SQL

  • Understand T-SQL programming elements

    Implement loops and conditions in T-SQL queries

15

  • MODULE 15: ADD ERROR HANDLING TO QUERIES

  • Understand SQL Server error handling

    Implement structured exception handling

16

  • MODULE 16: USE TRANSACTIONS IN QUERIES

  • Understand database transactions

    Implement transactions in T-SQL

Audience

This course is intended for novice database developers, database administrators, Business Intelligence developers, report creators, and application developers who have an understanding of relational database concepts and have basic Windows navigation skills.

Language

English

Prerequisites

Students in this course should have the following knowledge: • Basic understanding of relational databases. • Basic Windows knowledge.

$3,275

Length: 5.0 days (40 hours)

Level:

Not Your Location? Change

Course Schedule:

Schedule select
13
Nov
Monday
9:00 AM ET -
5:00 PM ET
Available
Schedule select
22
Jan
Monday
9:00 AM ET -
5:00 PM ET
Available
Schedule select
11
Mar
Monday
8:00 AM PT -
4:00 PM PT
Available
Schedule select
24
Jun
Monday
9:00 AM ET -
5:00 PM ET
Available
Loading...