Course 50439-B: Working with Data using SQL Server 2008 R2 for Non-Database Administrators

Microsoft

Course 50439-B: Working with Data using SQL Server 2008 R2 for Non-Database Administrators

Share This Learning Path

This instructor-led course provides students with the necessary knowledge to work with the data in SQL Server 2008R2. They will learn how to use the built in tools included with SQL Serverto construct queries, do tuning,analyze a query and it's performance along with how to modify existing data, perform joins, create indexes and work with XML.The new features are discussed and their impact is explained. See At Course Completion.

Audience Profile

This course is intended for Power Users and IT Professionals that are called on to work with SQL Server in the retrieving, or modifying of data and need a good foundation to perform those functions. This would include those users that develop reports and need to create Data Sets that work efficiently.

Prerequisites

Course outline

Module 1: The Toolsets

Module Overview

In this module you will become familiar with the range of tools available to you in SQL Server. You may already be familiar with some or all of the tools and then again some of them may be either new to you or not applicable in your current job assignment. Some of the tools such as BCP have a long history and do not change much over product iterations while others such as SQL Server Integration Services have matured and changed substantially while still others like SQL Server Analysis Services are growing in importance along with changing.

Lessons

SQL Server Management Studio
SQLCMD Utility
Business Intelligence Development Studio (BIDS)
SQL Profiler
Bulk Copy
Adventure Works Sample Database
Data Definition Language

Lab Sessions

The Toolsets

Lab Lessons

Set Options in SSMS
Create a Solution and Project
Explore the Query Editor
Examine Graphical Execution Plans
Use Object Explorer
Use SQLCMD Utility
Create a Basic SSIS Package Using the Wizard
Run a SQL Profiler Trace
Use BCP

After completing this module, students will be able to:

Explain the purpose of the toolsets in SQL Server.
Utilize a subset of the key features.
Customize options.
Understand where the tool is utilized in SQL Server.

Module 2: Data Types and New T-SQL Language Features

Module Overview

In this module students start by looking at SQL Server data types then move into the new data types that are available and wrap up with a look at some of the major features that have either been added to the language or have been enhanced.

Lessons

SQL Server Data Types
New Data Types
Hierarchyid Data Type
Spatial Data
Date and Time
FileStream Storage
Grouping Sets

Lab Sessions

Data Types and New T-SQL Language Features

Lab Lessons

Use Hierarchyid
FILESTREAM Storage
Use Merge Statement

After completing this module, students will be able to:

Knowledge on Data Types and New T-SQL Language Features.

Module 3: Single Table Queries

Module Overview

In this module the students will be looking at the basics of how to query a single table.

Lessons

Logical Query Processing
Query Clauses (Listed in Logical Processing Order)
FROM
WHERE
GROUP BY
HAVING
SELECT
ORDER BY
CASE
NULLS

Lab Sessions

Single Table Queries

Lab Lessons

Basic Query with FROM and SELECT
Add WHERE
ADD GROUP BY
ADD HAVING
Modify SELECT
ADD ORDER BY

After completing this module, students will be able to:

Knowledge on Single Table Queries.

Module 4: Joining Multiple Tables

Module Overview

In this module the students will go into some detail about combining our stored data so that we can retrieve answers to questions where the data that satisfies those answers is outputted to the person requesting it.

Lessons

Normalization
Old Style vs. New Style
Cross Join
Inner Join
Outer Join
Self Join
Joining Multiple Tables

Lab Sessions

Joining Multiple Tables

Lab Lessons

Use SSMS and create a new query saving it the name of your choice and saving into your data structure
Inner Join
Outer Join
Self Join
Multiple Tables

After completing this module, students will be able to:

Knowledge on Joining Multiple Tables.

Module 5: Subqueries, Common Table Expressions and Ranking Functions

Module Overview

This module describe Subqueries, Common Table Expressions and Ranking Functions.

Lessons

Subqueries
Self-Contained Subqueries
Correlated Subqueries
EXISTS
Views
Derived Tables
Common Table Expressions
Analytic Ranking Functions

Lab Sessions

Subqueries, Common Table Expressions and Ranking Functions

Lab Lessons

Subqueries
Views
EXISTS
Derived Tables
Common Table Expressions

After completing this module, students will be able to:

Knowledge on Subqueries, Common Table Expressions and Ranking Functions.

Module 6: Indexing

Module Overview

This module describe Indexing

Lessons

Understanding Execution Plans
Table and Index Structures
Syntax
Clustered Index
Non Clustered Index on Heap
Non Clustered Index on Clustered Index

Lab Sessions

Indexing

Lab Lessons

sys.dm_db_index_physical_stats
Set Statistics IO
WHERE Clause
Narrow Indexes
Column Uniqueness
Column Ordering

After completing this module, students will be able to:

Knowledge on Indexing.

Module 7: Query Tuning

Module Overview

This module describe Query Tuning

Lessons

System Performance Analysis
Query Analysis
Database Engine Tuning Advisor
Bookmark Lookup
Statistics Analysis
Query Design

Lab Sessions

Query Tuning

Lab Lessons

Performance Monitor and DMVs
Use a Dynamic Management View
SQL Profiler
Database Engine Tuning Advisor
Query Design

After completing this module, students will be able to:

Knowledge on Query Tuning.

Module 8: Data Modification, Transactions and Error Processing

Module Overview

This module describe Data Modification, Transactions and Error Processing

Lessons

Modifying Data INSERT
Modifying Data UPDATE
Modifying Data DELETE
Transactions
Error Processing
Debugging in SQL Server 2008

Lab Sessions

Data Modification, Transactions and Error Processing

Lab Lessons

Inserting Values
Use Bulk Copy
Modify Data
DELETE Data
Transactions
Try-Catch

After completing this module, students will be able to:

Knowledge on Data Modification, Transactions and Error Processing.

Module 9: Stored Procedures and Triggers

Module Overview

In this module the students will discuss Stored Procedures and their kissing cousins triggers. Stored Procedures or Sprocs as they are known with our penchant for shortening the name of anything are an excellent methodology for encapsulating logic into a reusable format.

Lessons

Stored Procedures
Triggers

Lab Sessions

Stored Procedures and Triggers

Lab Lessons

Basic Stored procedure
Stored procedure with Parameters (In)
Stored procedure with Parameters (In and Out)
Triggers
INSTEAD OF Trigger

After completing this module, students will be able to:

Knowledge on Stored Procedures and Triggers.

Module 10: XML

Module Overview

In this module the students will examine the increasing role that XML plays in SQL Server 2008.

Lessons

XML Introduction
XML in SQL Server
XML Data Type
XML Indexes

Lab Sessions

XML

Lab Lessons

FOR XML Clause
Create Table with an XML Column
Register a Schema
Query() Method
Value() Method
Exist() Method
Nodes() Method
Modify() Method

After completing this module, students will be able to:

Knowledge on XML.

Book Your Seat

Find Learning Paths

Course AZ-800T00-A: Administering Windows Server Hybrid Core Infrastructure

Join our community of certified professionals

About Us

ICTC is the leader in technical certification courses and exams. Our labs consist of a latest tech PCs and our instructors are certified from each vendor

Policy for the Prevention and Combating of Violence and Harassment in the Workplace.

Learn

View all the provided certifications and there relevant courses. Book online for a certification exam.

Contact Us

International Computer Training Center