Introduction

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.

Audience

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

At Course Completion

After completing this 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).
• Familiarity with the role of the database administrator

Course Outline

• Introduction to Transact-SQL
  - The Transact-SQL Programming Language
  - Types of Transact-SQL Statements
  - Transact-SQL Syntax Elements
  - Using SQL Server Books Online
• 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
• 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
• 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
• Joining Multiple Tables
  - Using Aliases for Table Names
  - Combining Data from Multiple Tables
  - Combining Multiple Result Sets
  - Querying Multiple Tables
• 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
• Modifying Data
  - Using Transactions
  - Inserting Data
  - Deleting Data
  - Updating Data
  - Performance Considerations
  - Modifying Data
• 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
• 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

Microsoft Certified Professional Exams

Exam 70-229: Designing and Implementing Databases with Microsoft SQL Server 2000 Enterprise Edition

Course Materials

The student kit includes a comprehensive workbook and other necessary materials for this class.

Related courses:

• M2072 Administering a Microsoft® SQL Server™ 2000 Database
• M2073 Programming a Microsoft SQL Server 2000 Database
• M2074 Designing and Implementing OLAP Solutions Using Microsoft SQL Server 2000

Microsoft Software Assurance

What is Software Assurance?

Software Assurance (SA) provides an easy way for Microsoft's Volume Licensing customers to stay current with the latest and most innovative Microsoft products. Under the SA program, customers acquire the right to install any new release of products covered in the agreement during the term of their coverage. Companies can acquire tomorrow's technology at today's prices and have the ability to spread payments annually instead of paying up front. Visit www.microsoft.com/licensing to learn more about Microsoft's licensing programs.

What is the SA Enhancement Offering?

The SA Enhancement Offering is designed to increase customer satisfaction by giving customers more value from Software Assurance and a better Microsoft product experience. The Enhancement Offering contains tools, training, and support as well as additional licensing benefits which meet different customer needs across product segments and across customer segments. The many benefits include:

• Training vouchers redeemable
• Employee Purchase Program
• Home Use Program
• Problem Resolution Support
• TechNet Subscription
• Technology & Tools

What is the Software Assurance (SA) Training Voucher Program?

Qualifying organizations that purchase Software Assurance receive training credits from Microsoft. Each training credit is worth the equivalent of one day of training at Networking Technologies™. Customers can redeem these credits for SA Training Vouchers for select Microsoft courses from «Networking Technologies»™.

How to Get Started?

Contact the individual in your organization responsible for software purchases to verify that you purchased volume licensing with Software Assurance. We can help you activate your benefits and administer the training vouchers. To register for a training contact Education Center «Networking Technologies»™ directly via e-mail edu@tex.kiev.ua