Microsoft SQL Server 2000: Querying with Transact-SQL Curriculum CBT Training CDs

Rating:
Summary: Microsoft SQL Server 2000: Querying with Transact-SQL
Review: Course Overview
This curriculum will cover manipulating data with Transact-SQL, how to work with queries, introduce views, understand stored procedures, triggers, and user-defined functions.

# This curriculum consists of two courses: Course 73470 Microsoft SQL Server 2000 Querying with Transact-SQL Part 1: Manipulating Data
# Course 73471 Microsoft SQL Server 2000 Querying with Transact-SQL Part 2: Working with Queries

Learn To
See individual course descriptions for specific course objectives and scope.

Audience
Candidates for this course operate in medium to very large computing environments that use SQL Server 2000, Windows 2000 Active Directory, and Internet Information Server (IIS). They have a minimum of one year's experience administering SQL Server environments. The general categories include: system administrators, database administrators, application developers, system analysts, and software engineers. Students are expected to have an understanding of basic relational database concepts and should be familiar with the role of the database administrator.

Deployment Options
e-Learning

Accreditation
NASBA credits: 4 CPE Credits Per Course
CEU credits: 1.40 CEUs

Language Options
US English

Total Learning Time
12 to 16 hours



Microsoft SQL Server 2000 Querying with Transact-SQL Part 1: Manipulating Data
Course Overview
This course presents students with both theoretical and practical instruction in the subject of manipulating data with Transact-SQL.

Learn To
# Identify the basic types of statements used in Transact-Structured Query Language (SQL).
# Identify the functions of Data Definition Language (DDL) statements.
# Identify the functions of Data Control Language (DCL) statements.
# Identify the guidelines for selecting columns within a table.
# Identify the guidelines for using the WHERE clause to retrieve rows in a table.
# Retrieve specific columns from a table.
# Identify the characteristics of aggregate functions.
# Identify the considerations for using the GROUP BY clause.
# Identify the guidelines for using the GROUP BY clause with the HAVING clause.


Audience
Candidates for this course operate in medium to very large computing environments that use SQL Server 2000, Windows 2000 Active Directory, and Internet Information Server (IIS). They have a minimum of one year's experience administering SQL Server environments. The general categories include: system administrators, database administrators, application developers, system analysts, and software engineers. Students are expected to have an understanding of basic relational database concepts and should be familiar with the role of the database administrator.

Deployment Options
e-Learning

Accreditation
NASBA credits: 4 CPE Credits
CEU credits: 0.70 CEUs

Language Options
US English

Total Learning Time
6 to 8 hours

Objectives
Unit 1: Introduction to Transact-SQL 2 - 3 hours
# Identify the basic types of statements used in Transact-Structured Query Language (SQL).
# Identify the functions of Data Definition Language (DDL) statements.
# Identify the functions of Data Control Language (DCL) statements.
# Identify the considerations for using Data Manipulation Language (DML) statements.
# Identify the rules for naming SQL Server object identifiers.
# Identify Transact-SQL base data types.
# Identify the functions of SQL operators.
# Identify the characteristics of expressions.
# Identify the functions of language elements that control the flow of logic within an SQL statement.
# Identify the features of the SQL Query Analyzer tool.
# Identify the functions of the Object Browser tool within SQL Query Analyzer.
# Identify the functions of templates in SQL Query Analyzer.
# Identify the uses of the object-oriented Structured Query Language (osql) utility.
# Identify the characteristics of dynamically constructed SQL statements.
# Identify the characteristics of batch usage.
# Write basic SELECT statements.
# Identify the features of script files.


Unit 2: Data Management 2 - 3 hours
# Identify the guidelines for selecting columns within a table.
# Identify the guidelines for using the WHERE clause to retrieve rows in a table.
# Retrieve specific columns from a table.
# Select rows by using a comparison operator.
# Identify the features of logical operators.


Unit 3: Data Grouping 2 hours
# Identify the characteristics of aggregate functions.
# Identify the considerations for using the GROUP BY clause.
# Identify the guidelines for using the GROUP BY clause with the HAVING clause.
# Create a GROUP BY clause with a HAVING clause.
# Identify the guidelines for using the GROUP BY clause with the ROLLUP operator.
# Identify the guidelines for using the GROUP BY clause with the CUBE operator.
# Identify features of the GROUPING function.
# Identify the considerations for using the COMPUTE clause.
# Identify the considerations for using the COMPUTE BY clause.




Microsoft SQL Server 2000 Querying with Transact-SQL Part 2: Working with Queries
Course Overview
This course will teach students how to work with queries. The course will also introduce views, stored procedures, triggers, and user-defined functions.

Learn To
# Identify advantages of using aliases for table names.
# Identify the guidelines for using joins.
# Identify the considerations for using inner joins.
# Identify considerations for using subqueries.
# Create a derived table by using a subquery.
# Create a single-value subquery.
# Identify the functions of views.
# Identify the considerations for defining views.
# Create a view by using an SQL Query Analyzer template.


Audience
Candidates for this course operate in medium to very large computing environments that use SQL Server 2000, Windows 2000 Active Directory, and Internet Information Server (IIS). They have a minimum of one year's experience administering SQL Server environments. The general categories include: system administrators, database administrators, application developers, system analysts, and software engineers. Students are expected to have an understanding of basic relational database concepts and should be familiar with the role of the database administrator.

Deployment Options
e-Learning

Accreditation
NASBA credits: 4 CPE Credits
CEU credits: 0.70 CEUs

Language Options
US English

Total Learning Time
6 to 8 hours

Objectives
Unit 1: Joining Multiple Tables 2 - 3 hours
# Identify advantages of using aliases for table names.
# Identify the guidelines for using joins.
# Identify the considerations for using inner joins.
# Identify the functions of the types of outer joins.
# Identify the guidelines for joining more than two tables.
# Identify the guidelines for using self-joins.
# Identify the guidelines for combining multiple queries into a single result set with the UNION operator.
# Use the UNION operator to combine result sets.


Unit 2: Subqueries and Full-Text Indexes 2 - 3 hours
# Identify considerations for using subqueries.
# Create a derived table by using a subquery.
# Create a single-value subquery.
# Create a multiple-value subquery.
# Identify the steps involved in the evaluation of a correlated subquery.
# Identify the syntax of a correlated subquery to mimic a JOIN clause.
# Identify the syntax of a correlated subquery to mimic a HAVING clause.
# Identify the features of the EXISTS and NOT EXISTS operators.
# Correlate data by using a subquery.
# Identify the characteristics of Microsoft Search service.
# Identify the components of Microsoft Search service.
# Identify the procedures and functions used to obtain information about full-text search.
# Retrieve information about full-text indexes.
# Identify the syntax elements used to write full-text queries.
# Identify the uses of the CONTAINS predicate.
# Identify the characteristics of the FREETEXT predicate.
# Identify the characteristics of the CONTAINSTABLE and FREETEXTTABLE functions.
# Write full-text queries.


Unit 3: Introduction to Programming Objects/ 2 hours
# Identify the functions of views.
# Identify the considerations for defining views.
# Create a view by using an SQL Query Analyzer template.
# Alter a view by using the Object Browser in SQL Query Analyzer.
# Identify the characteristics of stored procedures.
# Identify the advantages of stored procedures.
# Identify the features of sp_helptext for displaying the text of a programming object.
# Identify the characteristics of user-defined functions.
# Identify the characteristics of the CREATE FUNCTION statement.