Course 2071

Querying Microsoft SQL Server 2000 with Transact-SQL

Course Outline

Duration: 2 Days

Description: 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.

Objectives:
After completing the course, students will be able to:

•	Describe the uses of and ways to execute the Transact-SQL language.
•	Use querying tools.
•	Write SELECT queries to retrieve data.
•	Group and summarize data by using Transact-SQL.
•	Join data from multiple tables.
•	Write queries that retrieve and modify data by using subqueries.
•	Modify data in tables.
•	Query text fields with full-text search.
•	Describe how to create programming objects.

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.