16 Corporate Woods Boulevard | Albany, New York 12211 | Tel: 518.437.1600

#50464A: Understanding and Querying Relational Data for Information Workers Using Microsoft SQL Server

Available Dates: Request Dates
Class Length: 2 day
Cost: $998

Class Outline:

Description:
This two-day instructor-led course provides students with the knowledge and skills to query data using Microsoft SQL Server. The course will introduce the concepts of relation databases and how to retrieve, modify, and use SQL Server data. This course is intended for Information Workers who need to use SQL Server data for client applications such as reporting.

Table of Contents:
Module 1: Understanding Relational Databases
This module explains how use relational databases. It explains entities and attributes, and describes relationships.
Lessons
SQL Server and Transact-SQL
Relational Database Concepts
Accessing Relational Data
Lab : Understanding Relational Databases
Identifying Entities, Attributes, and Relationships
After completing this module, students will be able to:
Identify relational database concepts
Explain the first 3 normal forms
Use tools to access SQL Server Data

Module 2: Retrieving Simple Data
This module explains how query SQL Server data using SELECT statements. It also discusses how to format results sets.
Lessons
Simple SELECT Statements
Restricting Rows
Formatting Output
Lab : Writing Simple Queries
Writing SELECT Statements.
Using the WHERE Clause
Formatting Results
After completing this module, students will be able to:
Write basic SELECT statements
Restrict results with the WHERE clause
Use column aliases
Sort results with the ORDER BY clause

Module 3: Complex Data Retrieval
This module explains how to create results sets using joins.
Lessons
Using INNER JOINS
Using OUTER JOINS
Other Methods of Combining Data
Lab : Using Joins
Inner Joins
Outer Joins
Other Types of Joins
After completing this module, students will be able to:
Combine related data using INNER and OUTER JOINS
Use CROSS JOINS and self-joins
Use the UNION operator

Module 4: Summarizing Data
This module explains how summarizes data using GROUP BY. It also discusses aggregate functions and the PIVOT and UNPIVOT operator.
Lessons
Using Aggregates
Summarizing Results Sets
Lab : Summarizing Data
Using simple aggregate functions
Using GROUP BY
Using COMPUTE BY
After completing this module, students will be able to:
Understand how to summarize data
Use aggregate functions
Use GROUP BY
Use COMPUTE BY

Module 5: Modifying Data
This module explains how to modify data using INSERT, UPDATE, and DELETE.
Lessons
Inserting Data
Deleting Data
Updating Data
Lab : Data Modification
Using the INSERT Statement
Using the DELETE Statement
Using the UPDATE Statement
After completing this module, students will be able to:
How data modification works
How to modify data with INSERT, UPDATE, and DELETE

Module 6: Using Database Objects
This module explains how to use views, stored procedures, and functions. It also describes their purpose and how to handle parameters and return values.
Lessons
Introduction to Database Objects
Using Views
Calling Stored Procedures
Using Functions
Lab : Using Database Objects
Using Views
Using Stored Procedures
Using Functions
After completing this module, students will be able to:
Create and use views
Call stored procedures
Pass parameters into stored procedures and use output parameters
Return data from functions

Module 7: Advanced Queries
This module explains how to use conditional logic, transactions, and error handling.
Lessons
Using Conditional Logic
Handling Errors
Using Transactions
Lab : Creating Advanced Queries
Use Conditional Logic
Create Transactions
After completing this module, students will be able to:
Use CASE and IF logic
Use TRY/CATCH logic to handle errors
Use transaction to maintain data integrity

Module 8: Accessing Data with Microsoft Office 2007
This module explains how to work with SQL Server Data in Excel. Word, and Access.
Lessons
Using Data with Microsoft Excel 2007
Using Data with Microsoft Access 2007
Using Data with Microsoft Word 2007 (Optional)
Lab : Accessing Data with Microsoft Office
Import SQL Server Data into Excel 2007
Link to SQL Server Tables from Access 2007
Do a Mail Merge Using Word 2007 (Optional)
After completing this module, students will be able to:
Import data into Microsoft Excel 2007
Use Microsoft Access 2007 to create reports from SQL Server data
Perform a Mail Merge from Microsoft Word 2007 using SQL Server data