Implementing a Database in Microsoft SQL Server 7.0
Class #833Table of Contents
Introduction
At Course Completion
Microsoft Certified Professional Exams
Prerequisites
Course Materials and Software
Course Outline
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.
Return to top
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.
Return to top
This course will help the student prepare for the following Microsoft Certified
Professional exam(s):
- Exam 70-029, Implementing a Database Design on Microsoft SQL Server 7.0
Return to top
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
The course materials, lectures, and lab exercises are in English. To benefit fully
from our instruction, students need an understanding of the English language and
completion of the prerequisites.
Return to top
The course workbook and lab book are yours to keep.
You will be provided with the following software for use in the classroom:
Return to top
Day 1
Module 1: Microsoft SQL Server Overview
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.
Module 2: Transact-SQL Overview
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.
Day 2
Module 3: Creating Databases
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.
Module 4: Implementing Data Integrity
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.
Module 5: Planning and Creating Indexes
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 nonclustered indexes with unique or composite characteristics.
- Use the CREATE INDEX options to expedite index creation and improve index performance.
- Apply the appropriate fillfactor value to accommodate the future growth of tables.
- Use various tools and verification features to maintain indexes and enhance their
optimal performance.
Day 3
Module 6: Performing Advanced Queries
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.
Module 7: Summarizing Data
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.
Day 4
Module 8: Managing Transactions and Locks
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.
Module 9: Implementing Views
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.
Day 5
Module 10: Implementing Stored Procedures
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.
Module 11: Implementing Triggers
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.
Return to top
|
