 |
 |
|
|
|
 Technical Training - Querying
Microsoft SQL Server 2000 with Transact SQL
Course
No: M2071 Course Length: 2 Days |
|
Who
Should Attend: This
course is intended for SQL Server database administrators,
implementers, system engineers, and developers who are responsible
for writing queries. 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. |
Pre-Requisites: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
|
| Topics
Covered:
- Describe
the uses of and ways to execute the Transact-SQL language.
- Use querying
tools.
- Write SELECT
queries to retrieve data.
- Group and
summarise 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
|
|
|
|
| Introduction
to Transact-SQL
- 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.
Using
Transact-SQL Querying Tools
- SQL Query Analyser
- Using the Object
Browser Tool in SQL Query Analyser
- Using the osql
Utility
- Executing Transact-SQL
Statements
- Creating and Executing
Transact-SQL Scripts
- Describe the basic
functions of SQL Query Analyser.
- Describe how to
use the Object Browser tool in SQL Query Analyser.
- Describe how to
use the templates in SQL Query Analyser.
- Describe how to
use the osql command-line utility.
- Execute Transact-SQL
statements in various ways.
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
- 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.
Grouping
and Summarising 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 Summarising
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.
- Organise 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.
Joining
Multiple Tables
- 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.
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
- Describe when and
how to use a subquery.
- Use subqueries
to break down and perform complex queries.
Modifying
Data
- 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.
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
- Describe Microsoft
Search service function and components.
- Write full-text
queries.
- Get information
about 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
- 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
|
|
|
 |
|
Desktop Courses