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
鄂公网安备 42010302001612号 