Course: MS2071 Querying Microsoft SQL Server 2000 with Transact-SQL

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.

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

Module 1: Introduction to Transact-SQL

The following topics are covered in this module:

• The Transact-SQL Programming Language
 
• Types of Transact-SQL Statements
 
• Transact-SQL Syntax Elements
 
• Using SQL Server Books Online
 

After completing this module, you will be able to:

• Differentiate between Transact-SQL and ANSI-SQL.
 
• Describe the basic types of Transact-SQL.
 
• Describe the syntax elements of Transact-SQL.
 

Module 2: Using Transact-SQL Querying Tools

The following topics are covered in this module:

• 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
 

After completing this module, you will be able to:

• Describe the basic functions of SQL Query Analyzer.
 
• Describe how to use the Object Browser tool in SQL Query Analyzer.
 
• Describe how to use the templates in SQL Query Analyzer.
 
• Describe how to use the osql command-line utility.
 
• Execute Transact-SQL statements in various ways.
 

Module 3: Retrieving Data

The following topics are covered in this module:

• Retrieving Data by Using the SELECT Statement
 
• Filtering Data
 
• Formatting Result Sets
 
• How Queries Are Processed
 
• Performance Considerations
 
• Retrieving Data and Manipulating Result Sets
 

After completing this module, you will be able to:

• Retrieve data from tables by using the SELECT statement.
 
• Filter data by using different search conditions to use with the WHERE clause.
 
• Format result sets.
 
• Describe how queries are processed.
 
• Describe performance considerations that affect retrieving data.
 

Module 4: Grouping and Summarizing Data

The following topics are covered in this module:

• 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
 

After completing this module, you will be able to:

• Use the TOP n keyword to retrieve a list of the specified top values in a table.
 
• Generate a single summary value by using aggregate functions.
 
• Organize summary data for a column by using aggregate functions with the GROUP BY and HAVING clauses.
 
• Generate summary data for a table by using aggregate functions with the GROUP BY clause and the ROLLUP or CUBE operator.
 
• Generate control-break reports by using the COMPUTE and COMPUTE BY clauses.
 

Module 5: Joining Multiple Tables

The following topics are covered in this module:

• Using Aliases for Table Names
 
• Combining Data from Multiple Tables
 
• Combining Multiple Result Sets
 
• Querying Multiple Tables
 

After completing this module, you will be able to:

• Use aliases for table names.
 
• Combine data from two or more tables by using joins.
 
• Combine multiple result sets into one result set by using the UNION operator.
 

Module 6: Working with Subqueries

The following topics are covered in this module:

• 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
 

After completing this module, you will be able to:

• Describe when and how to use a subquery.
 
• Use subqueries to break down and perform complex queries.
 

Module 7: Modifying Data

The following topics are covered in this module:

• Using Transactions
 
• Inserting Data
 
• Deleting Data
 
• Updating Data
 
• Performance Considerations
 
• Modifying Data
 

After completing this module, you will be able to:

• Describe how transactions work.
 
• Write INSERT, DELETE, and UPDATE statements to modify data in tables.
 
• Describe performance considerations related to modifying data.
 

Module 8: Querying Full-Text Indexes

The following topics are covered in this module:

• Introduction to Microsoft Search Service
 
• Microsoft Search Service Components
 
• Getting Information About Full-Text Indexes
 
• Writing Full-Text Queries
 
• Querying Full-Text Indexes
 

After completing this module, you will be able to:

• Describe Microsoft Search service function and components.
 
• Write full-text queries.
 
• Get information about full-text indexes.
 

Module 9: Introduction to Programming Objects

The following topics are covered in this module:

• 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
 

After completing this module, you will be able to:

• Display the text of a programming object.
 
• Describe the concepts of views.
 
• List the advantages of views.
 
• Describe stored procedures.
 
• Describe triggers.
 
• Describe user defined functions.