Database Series Course:

Querying MS SQL Server 2000 With Transact-SQL (MS2071)

The goal of this course is to provide students with the technical skills required to write basic Transact-SQL queries for Microsoft SQL Server™ 2000.

Target Audience

This course is intended for SQL Server database administrators, implementers, system engineers, and developers who are responsible for writing queries.

 

Prerequisites

Before attending this course, students must have:

  • Experience using a Microsoft Windows® operating system.
  • An understanding of basic relational database concepts, including:
    • Logical and physical database design.
    • Data integrity concepts.
    • Relationships between tables and columns (primary key and foreign key, one-to-one, one-to-many, and many-to-many).
    • How data is stored in tables (rows and columns).
  • Familiarity with the role of the database administrator

Examinations

This course will help the student prepare for the following Microsoft Certified Professional exam:

Topics

Module 1: Introduction to Transact-SQL
  • The Transact-SQL Programming Language
  • Types of Transact-SQL Statements
  • Transact-SQL Syntax Elements
  • Using SQL Server Books Online
Module 2: Using Transact-SQL Querying Tools
  • SQL Query Analyzer
  • Using the Object Browser Tool in SQL Query Analyzer
  • Using the osql Utility
  • Executing Transact-SQL Statements
  • Creating and Executing Transact-SQL Scripts
Module 3: Retrieving Data
  • Retrieving Data by Using the SELECT Statement
  • Filtering Data
  • Formatting Result Sets
  • How Queries Are Processed
  • Performance Considerations
  • Retrieving Data and Manipulating Result Sets
Module 4: Grouping and Summarizing Data
  • Listing the TOP n Values
  • Using Aggregate Functions
  • GROUP BY Fundamentals
  • Generating Aggregate Values Within Result Sets
  • Using the COMPUTE and COMPUTE BY Clauses
  • Grouping and Summarizing Data
Module 5: Joining Multiple Tables
  • Using Aliases for Table Names
  • Combining Data from Multiple Tables
  • Combining Multiple Result Sets
  • Querying Multiple Tables
Module 6: Working with Subqueries
  • Introduction to Subqueries
  • Using a Subquery as a Derived Table
  • Using a Subquery as an Expression
  • Using a Subquery to Correlate Data
  • Using the EXISTS and NOT EXISTS Clauses
  • Working with Subqueries
Module 7: Modifying Data
  • Using Transactions
  • Inserting Data
  • Deleting Data
  • Updating Data
  • Performance Considerations
  • Modifying Data
Module 8: Querying Full-Text Indexes
  • Introduction to Microsoft Search Service
  • Microsoft Search Service Components
  • Getting Information About Full-Text Indexes
  • Writing Full-Text Queries
  • Querying Full-Text Indexes
Module 9: Introduction to Programming Objects
  • Displaying the Text of a Programming Object
  • Introduction to Views
  • Advantages of Views
  • Creating Views
  • Introduction to Stored Procedures
  • Introduction to Triggers
  • Introduction to User-defined Functions
  • Working with Views