click here
back to Class schedule
2071-Querying Microsoft SQL Server
2000 with Transact-SQL—Two days—Instructor-led
Introduction
This course provides students with the technical skills
required to write basic Transact-SQL queries for Microsoft®
SQL Server™ 2000.
At Course Completion
At the end of 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.
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).
- For
students who do not meet these prerequisites, the following course provides
students with the necessary knowledge and skills: Course 1609, Designing
Data Services and Data Models
- 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).
For students who do not meet these prerequisites,
the following course provides students with the necessary knowledge and skills:
- Knowledge of basic Transact-SQL syntax (SELECT, INSERT,
UPDATE, and DELETE statements).
For students who do not meet these prerequisites,
the following course provides students with the necessary knowledge and skills:
- Familiarity with the role of the database administrator.
Microsoft Certified Professional Exams
This course will help the student prepare for the following
Microsoft Certified Professional exam:
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
At the end of 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
At the end of 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
At the end of 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
At the end of 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
At the end of 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
At the end of 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
At the end of 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
At the end of this module, you will be able to:
- Describe Microsoft Search service function and
components.
- Get information about full-text indexes.
- Write full-text queries.
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
At the end of 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.