2071: Querying Microsoft SQL Server 2000 with Transact-SQL
SQL Server 2000 Training Course for Sheffield, Leeds, Manchester & Yorkshire
Course Information
| Course Code | MS2071 (MOC2071) (M2071) |
| Fee | £635 (Exclusive of VAT) |
| Duration | Two Days |
| Delivery Format | Instructor-led |
| Feb | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec |
| - | - | - | 12 | - | - | TBA | - | - | TBA | - |
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.
Completion
- 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
- 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
- The course materials, lectures, and lab exercises are in English. To benefit fully from our instruction, students need an understanding of the English language and completion of the prerequisites
Examinations
- Exam 70-229, Designing and Implementing Databases with Microsoft SQL Server 2000 Enterprise Edition
Materials
The student kit includes a comprehensive workbook and other necessary materials for this class. The following software is provided in the student kit:
- Microsoft SQL Server 2000, Enterprise Edition Evaluation Copy
Course Outline
Module 1: Introduction to Transact-SQLThe 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
- Differentiate between Transact-SQL and ANSI-SQL
- Describe the basic types of Transact-SQL
- Describe the syntax elements of Transact-SQL
- 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
- 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
- Retrieving Data by Using the SELECT Statement
- Filtering Data
- Formatting Result Sets
- How Queries Are Processed
- Performance Considerations
- Retrieving Data and Manipulating Result Sets
- 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
- 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
- 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
- Using Aliases for Table Names
- Combining Data from Multiple Tables
- Combining Multiple Result Sets
- Querying Multiple Tables
- 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
- 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
- Describe when and how to use a subquery
- Use subqueries to break down and perform complex queries
- Using Transactions
- Inserting Data
- Deleting Data
- Updating Data
- Performance Considerations
- Modifying Data
- Describe how transactions work
- Write INSERT, DELETE, and UPDATE statements to modify data in tables
- Describe performance considerations related to modifying data
- Introduction to Microsoft Search Service
- Microsoft Search Service Components
- Getting Information About Full-Text Indexes
- Writing Full-Text Queries
- Querying Full-Text Indexes
- Describe Microsoft Search service function and components
- Write full-text queries
- Get information about full-text indexes
- 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
- 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