微软培训与认证系列教材833B：Implementing a Database on Microsoft SQL Server 7.0（英文影印版）

About This Curse

Introduction

Introductions

Course Materials

Prerequisites

Course Outline

Course Outline

Course Outline

Appendices

Microsoft Certified Professional Program

Facilities

Module 1 : SQL Server Overview

What Is SQL Server

SQL Server Architecture

SQL Server Security

SQL Server Databases

Working with SQL Server

Lab II: SQL Server Overview

Review.

Module 2: Overview of Transact-SQL

SQL Server Programming Tools.

The Transact-SQL Programming Language

Elements of Transact-SQL

Ways to Execute Transact-SQL Statements

How Queries Are Processed

Recommended Practices

Lab 2. l : Overview of Transact-SQL

Review

Module 3: Creating Databases

How Data Is Stored

Creating Databases

Modifying Databases

Creating Filegroups

Lab 3. l : Creating Databases

The Library Database

Creating Data Types

Creating Tables

Generating Scripts

Recommended Practices

Lab 3.2: Creating Database Objects

Review

Module 4:Implementing Data Integrity

Types of Data Integrity

Enforcing Data Integrity

Using Constraints

Using Defaults and Rules

Deciding Which Enforcement Method to Use

Recommended Practices

Lab 4. l : Implementing Data Integrity

Review

Module 5:Planning and Creating Indexes

Introduction to Indexes

Index Architecture

Creating Indexes

CREATE INDEX Options

Lab 5. l : Creating Indexes

Maintaining Indexes

Performance Considerations

Recommended Practices

Lab 5.2: Maintaining Indexes

Review

Module 6: Querying Multiple Tables

Combining Data from Multiple Tables

Combining Multiple Result Sets

Creating a Table from a Result Set

Recommended Practices

Lab 6. l : Querying Multiple Tables

Review

Module 7: Advanced Query Techniques

Introduction to Subqueries

Nested Subqueries

Correlated Subqueries

Using the EXISTS and NOT EXTSTS Keywords

Modifying Data

Recommended Practices

Lab 7. l : Advanced Query Techniques

Review

Module 8: Summarizing Date

Using Aggregate Functions.

GROUP BY Fundamentals

Generating Aggregate Values Within Result Sets

Using the COMPUTE and COMPUTE BY Clauses

Listing the TOP n Values

Recommended Practices

Lab 8. l : Summarizing Data

Review

Module 9: Managing Transactions and Locks

Introduction to Transactions and Locks

Managing Transactions

Managing Locks

Deadlocks.

Recommended Practices

Lab 9. 1 : Managing Transactions and Locks

Review

Module 10: Working with Distributed Data

Introduction to Distributed Queries

Executing an Ad Hoc Query on a Remote Data Source

Setting Up a Linked Server Environment

Executing a Query on a Linked Server

Executing a Stored Procedure on a Linked Server

Modifying Data on a Linked Server

Distributing Data.

Recommended Practices

Lab 10.l : Working with Distributed Data

Review .

Module 11 : Implementing Views

What Is a View

Advantages of Views

Defining Views

Modifying Data Through Views

Performance Considerations

Recommended Practices

Lab 1l.1: Implementing Views

Review

Module 12: Implementing Stored Procedures

Introduction to Stored Procedures

Creating, Executing, and Modifying Stored Procedures

Lab 12.1I : Creating Stored Procedures

Using Parameters in Stored Procedures

Executing Extended Stored Procedures

Handling Error Messages

Performance Considerations

Recommended Practices

Lab 12.2: Creating Stored Procedures with Parameters

Review

Module 13: Implementing Triggers

Introduction to Triggers

Defining Triggers

Examples of Triggers

Recommended Practices

Lab 13. l : Creating Triggers

Review

Module 14: Advanced Text Queries

Microsoft Search Service

Lab 14.l: Microso8 Search Service

Microsoft English Query

Lab 14.2: Microsoft English Query

Review

Appendix A: Library Database Case Study

Introduction

Overview of Library Operations

Daily Library Functions.

Library Database Design

Appendix B: Database Schemas

Appendix C: Performing Basic Queries

Retrieving Data

Formatting Result Sets

Lab C. 1 : Retrieving Data and Manipulating Result Sets

Modifying Data

Performance Considerations

Recommended Practices

Lab C.2: Modifying Data

Review

Lab C.1 : Retrieving Data and ManipulatingResultSets

Objective

Before You Begin

Exercise l Retrieving Data

Exercise 2 Manipulating Result Sets

Lab C.2: Modifying Data

Objective

Before You Begin

Exercise l Using the INSERT Statement

Exercise 2 Using the INSEB.T Statement with the DEFAULT Keyword

Exercise 3 Using the INSERT Statement with the DEFAULT

VALUES Keyword

Exercise 4 Using the DELETB Statement

Exercise 5 Using the UPDATE Statement

Appendix D: Using Cursors

What is a Cursor

Methods of Calling Cursors

Transact-SQL Cursors

Demonstration: Using Transact-SQL Cursors

Recommended Practices

Review

Trainer Lab Manual

Lab 1.1: SOL Server Overview

Objectives

Exercise l Using SQL Server Books Online

Exercise 2 Creating a Database Diagram

Lab 2.1 : Overview of Transact-SQL

Objectives

Before Yon Begin

Exercise l Writing Basic SELECT Statements

Exercise 2 Modifying a Script File.

Exercise 3 Using System Functions.

Lab 3.1 : Creating Databases

Objective .

Before You Begin

Exercise l Creating the library Database

Exercise 2 Managing the Growth of the library Log File

Exercise 3 Setting an Option to Clear the Transaction Log

Lab 3.2:Creating Database Objects

Objectives

Before You Begin

Exercise l Creating User-Defined Data Types

Exercise 2 Creating Tables in the library Database

Exercise 3 Adding and Dropping Columns

Exercise 4 Generating SQL Scripts

Exercise 5 Loading the library Database with Sample Data

Lab 4.1 : Implementing Data Integrity

Objectives

Before You Begin

Exercise I Defining DEFAULT Constraints

Exercise 2 Defining CHECK Constraints

Exercise 3 Defining PRIMARY KEY Constraints

Exercise 4 De8ning FOREIGN KEY Constraints

If Time Permits Creating Defaults and Rules

Lab 5.1 : Creating Indeses

Objective

Before You Begin

Exercise l Creating Indexes

Lab 5.2: Maintaining Indexes '

Objectives

Before You Begin

Exercise I Using the FILLFACTOR and DBCC SHOWCONTIG

Options

Exercise 2 Gathering Query Performance Information

Lab 6.1 : Querying Multiple Tables

Objectives

Before You Begin

Exercise. I Joining Tables.

Exercise 2 Using the UNION Operator to Combine Result Sets

Exercise 3 Creating Temporary Tables

Lab 7.1 : Advanced Query Techniques

Objectives

Before You Begin

Exercise l Using Subqueries

Exercise 2 Modifying Tables Based on Data in Other Tables

Lab 8.1: Summarizing Data

Objectives

Before You Begin

Exercise l Using the GROUP BY and IIAVDI6 Clauses

Exercise 2 Using the ROLLUP and CUBE Operators

Exercise 3 Using the COMPUTE and COMPUTEBY Clauses

If Time Permits Using the TOP n Keyword

Lab 9.1 : Managing Transeactions and Locks

Objectives

Before You Begin

Exercise l Creating and Executing a Transaction

Exercise 2 Rolling Back a Transaction

Exercise 3 Viewing Locking Information

Exercise 4 Setting Locking Options.

Lab 10.1 : Working with Distributed Data

Objectives

Before You Begin

Exercise l Setting Up Linked Servers

Exercise 2 Querying Remote Data

If Time Permits Managing Distributed Transactions

Lab 11.1: Implementing Views

Objectives

Before You Begin

Exercise l Creating and Testing Views

Exercise 2 Creating a View of a View

Exercise 3 Encrypting a View De8nition

Exercise 4 Modifying Data Through Views

Exercise 5 Locating View Definitions

Exercise 6 Testing Effects of Ownership Chains

If Time Permits Creating a View with SQL Server Enterprise Manager

Lab 12.1 : Creating Stored Procedures

Objectives

Before You Begin

Exercise l Writing and Executing a Stored Procedure

Exercise 2 Locating Stored Procedure Information

Lab 12.2: Creating Stored Procedures with Parameters

Objectives

Before You Begin

Exercise l Using the Create Stored Procedure Wizard

Exercise 2 Creating Stored Procedures from Scripts

Exercise 3 Altering Stored Procedures

Exercise 4 Customizing Error Messages

Exercise 5 Using Return Codes

If Time Permits Executing Extended Stored Procedures

If Time Permits Monitoring the Procedure Cache

Lab 13.1 : Creating Triggers

Objectives

Before You Begin

Exercise l Creating Triggers

Exercise 2 Creating a Trigger for Updating Derived Data

Exercise 3 Creating a Trigger That Maintains a Complex Business Rule

Exercise 4 Creating a Trigger for the reservation Table

Exercise 5 Testing Nested Triggers.

Lab 14.1 : Microsoft Search Service

Objectives

Before You Begin

Exercise l Setting Up Microsoft Search Service

Exercise 2 Writing Full-Text Queries

If Time Permits Setting Up Microsoft Search Service by Using

System Stored Procedures

Lab 14.2: Microsoft English Query

Objectives

Before You Begin

For More Information

Exercise I Installing English Query.

Exercise 2 Using English Query.

Exercise 3 Creating an English Query Application

If Time Permits Review the English Query Tutorial

Classroom Setup Guide

Classroom Requirements

Classroom Configuration

Setup Instructions