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

This two-day instructor-led course provides students with the knowledge and skills to query data using Microsoft SQL Server.

Course Outline

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.

Audience Profile
This course is intended for Information Workers who need to use SQL Server data for client applications such as reporting.

At Course Completion
After completing this course, students will be able to:

• Understand the purpose and benefit of relational data storage.
• Retrieve data using SELECT statements.
• Summarize data using GROUP BY and aggregate functions.
• Modify data using INSERT, UPDATE, and DELETE statements.
• Incorporate SQL Server data into Microsoft Office 2007 Applications.

Before attending this course, students must have:

• Basic knowledge of Windows operating Systems.
• Understand the concepts of storing data on a network.
• Basic understanding of Microsoft Office Applications.

Course Outline

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

Additional Details

Pre-Requisites

Certificates offered

MS Certification

Cancellation Policy

Cancellations less than 2 business weeks before the expected delivery date are eligible for a 50% refund, or a credit voucher will be provided for regularly scheduled courses (choice being that of the registrant). Credit Vouchers are transferable within the same company. Please send your cancellation notice to info@itplanit.com.

Map & Reviews

Itplanit Services Corp.
[ View Provider's Profile ]

Reviews

 

This course has not yet been rated by one of our members.

If you have taken a course through this vendor please log into your account and leave feedback for this vendor. You will be helping ensure our members get directed to the best training facilities.

Here are some reviews of the training vendor.

   

A very interesting course. Claude Gerin, our instructor, was excellent in making this a very interesting and learning experience. THANKS!

Reviewed by Oleg T. 2012