Computer Training

SQL Server 2000

Course Length: 5 Days

Course Prerequisites

  • Required: You should already be familiar with the use of a database program, such as Microsoft Access, and have a solid understanding of the Windows operating system.

Course Description

  • This course teaches you how to use the Transact-SQL language to query and program Microsoft SQL Server 2000 in a Windows 2000 Server environment. This course will also assist you in designing, creating, and managing databases, stored procedures and queries, implementing data integrity techniques and managing transactions and locks with Microsoft SQL Server 2000 Enterprise Edition.

Detailed List of Topics

Exploring Transact-SQL

  • Overview of SQL Server and Transact-SQL
  • Programming in Transact-SQL

Querying Data

  • Using the SELECT Statement
  • Formatting the Results Set

Changing Data Through Queries

  • Inserting/Deleting/Updating Data

Summarizing Data

  • Working with Aggregate Functions
  • Grouping Data

Joining Tables

  • Querying Multiple Tables
  • Implementing Advanced Table Joins

Implementing Subqueries

  • Designing Subqueries

Working with Full-text Indexes

  • Implementing the Microsoft Search Service
  • Querying Full-text Indexes

Programming Transact-SQL

  • Creating and Managing Views
  • Triggers,User-defined Functions

An Overview of SQL Server

  • Exploring the Components of SQL Server
  • Exploring Features of Microsoft SQL Server
  • Exploring the SQL Server Services
  • Identifying Administrative Utilities
  • Using SQL Server Enterprise Manager
  • Using SQL Query Analyzer
  • SQL Server Database Structure
  • Discussing the Components of SQL Server
  • Identify Default Databases / System Tables
  • Creating/Working with a Database Diagram
  • Understanding the SQL Server Architecture
  • Exploring the Application Architecture
  • Identifying SQL Server Management Tasks
  • Determining SQL Management Tasks
  • Configuring Server’s Authentication Mode
  • Creating a SQL Login Account
  • Working With Server and Database Roles

Exploring Transact-SQL

  • Working with Transact-SQL
  • Using SQL Query Analyzer
  • Using Osql
  • Transact-SQL Statements
  • Creating a Simple Table
  • Inserting Data
  • Selecting Data
  • Assigning Permissions to Public DB Role
  • Programming in Transact-SQL
  • Using Variables
  • Saving a Script File
  • Choosing an XML Format

Designing and Implementing Databases

  • Identifying Database Design Issues
  • Designing Databases
  • Identify Space Requirements for Databases
  • Optimizing Database Performance
  • Creating Databases
  • Setting Database Options
  • Use Stored Procedures to View Information
  • Managing Databases
  • Configuring to Auto Shrink a DatabaseFile
  • Deleting a Database

Creating and Managing Tables

  • Design and Create Tables
  • Normalizing Table Designs
  • Maintaining Tables
  • Using Enterprise Mgr to Generate a Script

Designing & Implementing Data Integrity

  • Understanding Data Integrity
  • Implementing Constraints
  • Adding a Primary Key Constraint
  • Adding a Foreign Key Constraint
  • Adding a Default Constraint
  • Adding a Check Constraint
  • Disabling Constraint Checking

Implementing Indexes

  • Designing Indexing
  • Understanding Index Architecture
  • Implementing Indexes
  • Create Clustered Index on the Movie Table
  • Viewing the Information in Sysindexes
  • Maintaining Indexes
  • Rebuilding an Index
  • Recreate PrimaryKey Index
  • Generating a Workload File in SQL Profiler
  • Observing Index Statistics

Joining Tables

  • Querying Multiple Tables
  • Working with Cross Joins
  • Implementing Advanced Table Joins
  • Joining Multiple Tables
  • Working with Self Joins
  • Combining SELECT Statements
  • Creating New Table with SELECT INTO

Designing Advanced Queries

  • Designing Subqueries
  • Designing Single Value Subqueries
  • Changing Data Through Queries
  • Inserting Data Based on a Query
  • Deleting Rows Based on a Query

Designing Views

  • Creating and Managing Views
  • Creating a View
  • Displaying View Information
  • Dropping a View
  • Creating an Indexed View
  • Creating a Partitioned View

Creating Stored Procedures

  • Designing Stored Procedures
  • Research Stored Procedures Online
  • Running Extended Stored Procedures
  • Understanding Processes
  • Creating Stored Procedures
  • Executing a Stored Procedure
  • Using Parameters in Stored Procedures
  • Create With an Input Parameter
  • With Output Parameters
  • Managing Stored Procedures
  • Recompiling a Stored Procedure

Using Functions

  • Working with Aggregate Functions
  • Aggregate Functions to Summarize Data
  • Designing GROUP BY Queries
  • Using TOP in a Query
  • Designing/Creating User-defined Functions
  • Dropping a User-defined Function

back to top