Print the Course OutlineEmail this Register for this courseFind more courses
 

Querying Microsoft SQL Server 2000 with Transact-SQL
Course 2071  

Introduction

This course provides students with the technical skills required to write Transact-SQL queries for Microsoft SQL Server 2000.


At Course Completion

At the end of the course, students will be able to:

  • Describe the uses of the Transact-SQL language.
  • Write SELECT queries to retrieve data.
  • Modify data by using Transact-SQL.
  • Write complex queries by using functions and control-of-flow language in batches and scripts.
  • Write queries that summarize data.
  • Join data from multiple tables.
  • Write queries that retrieve and modify data by using subqueries.
  • Query text fields with full-text search.
  • Write queries to solve business problems.


Microsoft Certified Professional Exams

This course will help the student prepare for the following Microsoft Certified Professional exam:
 

  • To be determined


Prerequisites

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

    For students who do not meet this prerequisite, the following course provides students with the necessary knowledge and skills:

  • Familiarity with the role of the database administrator.

Course Outline

Module 1: Introduction to Transact-SQL

Topics

The Transact-SQL Programming Language
Types of Transact-SQL Statements
Data Manipulation Language Statements
Transact-SQL Syntax Elements

Lab

Overview of Transact-SQL

Skills

Students will be able to:

  • Differentiate between Transact-SQL and ANSI-SQL.
  • Describe the basic types of Transact-SQL.
  • Describe the syntax elements of Transact-SQL.

Module 2: Using Transact-SQL Programming Tools

Topics

SQL Server Programming Tools
Ways to Execute Transact-SQL Scripts

Lab

Viewing the Northwind Database

Skills

Students will be able to:

  • Describe the primary SQL Server programming tools.
  • Execute Transact-SQL scripts in various ways.

Module 3: Retrieving Data

Topics

Retrieving Data
Filtering Data
Formatting Result Sets
How Queries Are Processed
Performance Considerations

Lab

Retrieving Data and Manipulating Result Sets

Skills

Students will be able to:

  • Retrieve data from tables by using the SELECT statement.
  • Filter data by writing the different types of search conditions to use with the WHERE clause.
  • Write queries that sort values and eliminate duplicates in result sets, and format result sets to improve readability.
  • Describe how queries are processed.
  • Describe performance considerations that affect retrieving data.

Module 4: Grouping Data

Topics

Listing the TOP n Values
Using Aggregate Functions
GROUP BY Fundamentals
Generating Aggregate Values Within Result Sets
Using the COMPUTE and COMPUTE BY Clauses

Lab

Grouping Data

Skills

Students will be able to:

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

Module 5: Joining Multiple Tables

Topics

Using Aliases for Table Names
Combining Data from Multiple Tables
Combining Multiple Result Sets

Lab

Querying Multiple Tables

Skills

Students will be able to:

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

Module 6: Working with Subqueries

Topics

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

Lab

Writing Nested and Correlated Subqueries

Skills

Students will be able to:

  • Describe when and how to use a subquery.
  • Use subqueries to break down and perform complex queries.

Module 7: Modifying Data

Topics

Using Transactions
Inserting Data
Deleting Data
Updating Data
Performance Considerations

Lab

Modifying Data

Skills

Students will be able to:

  • Describe how transactions work.
  • Write INSERT, DELETE, and UPDATE statements to modify data in tables.
  • Describe performance considerations related to modifying data.

Module 8: Querying Full-Text Indexes

Topics

Microsoft Search Service
Microsoft Search Service Components
Getting Information About Full-Text Indexes
Writing Full-Text Queries

Lab

Querying Full-Text Indexes

Skills

Students will be able to:

  • Set up Microsoft Search service.
  • Create full-text indexes.
  • Write full-text queries.

Module 9: Introduction to Programming Objects

Topics

What Is a View
Advantages of Views
Defining Views
Introduction to Stored Procedures
Introduction to Triggers
Introduction to User-Defined Functions

Labs

Working with Views
Working with Stored Procedures

Skills

Students will be able to:

  • Explain the purpose and benefits of using views.
  • Create a view by using the CREATE VIEW statement.
  • Describe how a stored procedure is processed.
  • Describe triggers.
  • Describe user-defined functions.

Module 10: Writing Queries to Solve Business Problems

Topics

Leveraging Transact-SQL Script
Surveying Data
Working with Data Sets
Processing Conditional Data

Lab

Writing Queries to Solve Business Problems

Skills

Students will be able to:

  • Leverage and reuse Transact-SQL script.
  • Survey data.
  • Work with data sets.
  • Process conditional data.

For more information or to give feedback, send e-mail to info@OneApex.com or call 949-597-2100