Search Courses
Dates for this class:
No dates are available for this class.

Course Details    [choose new course]     [printable version]
Understanding and Querying Relational Data for Information Workers Using Microsoft SQL Server (MOC50464)
Course Number: MOC50464
Category: Networks & Operating Systems
Duration: 2.00 days
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.
Target Audience

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

Prerequisites
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.
Objectives
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. 
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