Microsoft SQL Server 2000 Querying with Transact-SQL Part 2: Working with Queries

This course will teach students how to work with queries. The course will also introduce views stored procedures triggers and user-defined functions.

This is primarily online training This is an online eLearning or CBT training program Contact Serebra Learning Corporation for more information

Duration: 8 hours Training Presented in: English

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.

Objective

• 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 singlevalue subquery.
• Identify the functions of views.
• Identify the considerations for defining views.
• Create a view by using an SQL Query Analyzer template.

Topics Include

Unit 1: Joining Multiple Tables

• 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

• 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/

• 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.

Duration

8

Minimum Requirements

The CDROM version of this course requires:

• At least a 486DX 33Mhz CPU.
• Microsoft Windows 3.1 or higher and a Microsoft compatible mouse.
• At least 8MB RAM.
• At least VGA graphics capability with a minimum 512K video RAM (1MB video RAM recommended).
• At least a double speed CDROM drive.
• An MPC compliant sound card with attached speakers or headphones is recommended (Currently only the CDROM version supports audio).

• At least a 486DX 33Mhz CPU.
• Microsoft Windows 3.1 or higher and a Microsoft compatible mouse.
• At least 8MB RAM and 22MB available hard disk space or file server space.
• At least VGA graphics capability with a minimum 512K video RAM (1MB video RAM recommended).

Media

Web Based Training

• Microsoft SQL Server 2000 Querying with Transact-SQL Part 1: Manipulating Data