This course provides students with the technical skills required to implement a database solution with the Microsoft SQL Server relational database management system. Course 833 is a major revision of Course 750: Implementing a Database Design Using Microsoft SQL Server 6.5. The course content is being revised to reflect significant changes in the product and to be more task-oriented.

At Course Completion

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

• Describe the elements of SQL Server 7.0 and the environments in which it can operate.
• Describe and configure the data storage architecture of SQL Server.
• Describe the elements of the Transact-SQL language.
• Create and manage files, file groups, databases, tables, and transaction logs.
• Enforce data integrity using constraints, defaults, and rules.
• Plan for appropriate use, create, and maintain indexes.
• Manage locking options and transactions to ensure data concurrency and recoverability.
• Write queries that retrieve and modify data using joins and subqueries.
• Create views of data.
• Design and create stored procedures.
• Design and create triggers.

Prerequisites

This course requires that students meet the following prerequisites:

• Experience using the Microsoft Windows NT Server operating system
• One year of experience with relational databases
• Three to six months SQL Server experience
• Understanding of basic ANSI SQL statements

Topics:

What is SQL Server?
SQL Server architecture
SQL Server components
Working with SQL Server

Lab:

Using SQL Server books online

Skills:

Students will be able to:

• Describe Microsoft SQL Server 7.0 and its supporting operating systems.
• Describe SQL Server architecture.
• Describe SQL Server components.
• Identify SQL Server design options, as well as implementation and administration activities.

Topics:

SQL Server programming tools
The Transact-SQL programming language
Elements of Transact-SQL
Ways to execute Transact-SQL statements

Lab:

Transact-SQL overview

Skills:

Students will be able to:

• Describe the basic elements of Transact-SQL.
• List data definition statements.
• List data manipulation statements.
• List data control statements.
• Describe the ways to execute Transact-SQL statements.

Topics:

Introduction to databases
Working with databases
Modifying databases
Creating filegroups
Creating data types
Creating tables
Generating scripts

Lab:

Creating databases

Skills:

Students will be able to:

• Evaluate database considerations.
• Create and configure a database.
• Manage a database and transaction log.
• Create and drop user-defined data types.
• Create and drop user tables.
• Generate a script.

Topics:

Introduction to data integrity
Using constraints
Using defaults and rules

Lab:

Implementing data integrity

Skills:

Students will be able to:

• Describe the three types of data integrity.
• Determine which features to use to enforce data integrity.
• Define and use DEFAULT and CHECK constraints.
• Define PRIMARY KEY, UNIQUE, and FOREIGN KEY constraints.
• Describe and use defaults and rules.

Topics:

Implementing indexes
Creating and dropping indexes
Types of indexes
Characteristics of indexes
Indexing guidelines
CREATE INDEX options
Maintaining indexes

Lab:

Creating indexes

Skills:

Students will be able to:

• Determine when indexes are useful and decide the types of indexes to create.
• Create clustered and non-clustered indexes with unique or composite characteristics.
• Use the CREATE INDEX options to expedite index creation and improve index performance.
• Apply the appropriate fill factor value to accommodate the future growth of tables.
• Use various tools and verification features to maintain indexes and enhance their optimal performance.

Topics:

Using joins to combine data from multiple tables
Manipulating a result set
Using subqueries
Modifying data with joins or subqueries
Querying a remote SQL Server

Lab:

Performing advanced queries

Skills:

Students will be able to:

• Combine data from two or more tables using joins.
• Combine multiple result sets into one result set using the UNION operator and the SELECT INTO statement.
• Use subqueries to break down and perform complex queries.
• Use joins and subqueries as selection or data modification criteria within a statement.
• Set up a distributed environment that accesses data stored in a remote SQL Server.

Topics:

Using aggregate functions
Generating a summary value for a column
Generating aggregate values within result sets
Listing the top n values

Lab:

Summarizing data

Skills:

Students will be able to:

• Generate a single summary value using aggregate functions.
• Organize summary data for a column using aggregate functions with the GROUP BY, HAVING, COMPUTE, and COMPUTE BY clauses.
• Generate summary data for a table using aggregate functions with the GROUP BY clause and the ROLLUP or CUBE operators.
• Use the TOP n keyword to retrieve a list of the specified top values in a table.

Topics:

Introduction to transactions and locks
Managing transactions
Managing locks
Managing distributed transactions

Lab:

Managing transactions and locks

Skills:

Students will be able to:

• Describe transaction processing.
• Execute, cancel, or roll back a transaction.
• Identify resource items that can be locked.
• Identify the types of locks.
• Set locking options and display locking information.
• Initiate a distributed transaction.
• Compare distributed transactions and replication.

Topics:

Views: an alternate way to look at data
Advantages of views
Creating views
Altering views
Modifying data through views

Lab:

Implementing views

Skills:

Students will be able to:

• Explain the purpose and benefits of using views.
• Create a view using the CREATE VIEW statement.
• Drop a view from a database.
• Locate view definition information.
• Alter a view.
• Update a source table using a view.

Topics:

Introduction to stored procedures
Creating, executing, and modifying stored procedures
Using parameters in stored procedures
Handling error messages

Lab:

Implementing stored procedures

Skills:

Students will be able to:

• Describe how a stored procedure is processed.
• Create, execute, modify, and drop a stored procedure.
• Execute a stored procedure on a remote server.
• Use parameters with stored procedures.
• Recompile a stored procedure.
• Create custom error messages.

Topics:

Introduction to triggers
Creating, dropping, and altering triggers
Examples of triggers

Lab:

Creating a trigger

Skills:

Students will be able to:

• Define a trigger.
• Explain the advantages of using a trigger.
• Describe some considerations when using a trigger.
• Create a trigger.
• Drop a trigger.
• Alter a trigger.