MS2071 - Querying Microsoft SQL Server 2000 with Transact-SQL
| Categories |
MCDBA Training, MCSE Training, MCSD Training, MCAD Training, Microsoft Certification Training, SQL Training, Database Training, Microsoft SQL Server Training |
| Duration |
2 days (14 hours) |
| Pre-requisites |
An understanding of basic relational database concepts and an understanding of database administration, i.e.: DMD - Database Modeling and Design, or equivalent knowledge and skills and experience using a Microsoft Windows®
operating system.
|
|
|
|
| Courseware |
MS2071-MS (300 pages) |
| Price |
$790 |
| Notes |
This course covers topics students need to know to prepare for the following Microsoft exam:
Exam 70-229: Designing and Implementing Databases with Microsoft SQL Server 2000 Enterprise Edition (MCSD, MCSE, MCSE 2003 and MCAD elective; MCDBA core)
|
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. It is intended for SQL Server database administrators, implementers, system engineers, and developers who are responsible for writing queries.
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 and describe how to create
programming objects.
The following software is provided in the student kit: Microsoft SQL Server 2000, Enterprise Edition
Evaluation Copy
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