Course 833: Five days; Instructor-led
Introduction
This course provides students with the technical skills required to implement a database
solution with Microsoft SQL Server™ client/server database management system
version 7.0.
At Course Completion
At the end of the course, students will be able to:
- Describe the elements of Microsoft SQL Server 7.0 and the environments in which it can operate.
- Describe the elements of the Transact-SQL language.
- Describe and configure the data storage architecture of SQL Server.
- 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.
- Write queries that retrieve and modify data using joins and subqueries.
- Write queries that summarize data.
- Manage locking options and transactions to ensure data concurrency and recoverability.
- Create views of data.
- Design and create stored procedures.
- Design and create triggers.
- Work with distributed data.
Microsoft Certified Professional Exams
This course will help the student prepare for the following Microsoft Certified Professional exam:
Prerequisites
- Experience using the Microsoft Windows NT® Server network operating system
- One year of experience with relational databases
- Three to six months of 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.
Course Materials
The course workbook and lab book are yours to keep.
You will be provided with the following software for use in the classroom:
- Microsoft Windows NT Server version 4.0 or later
- Microsoft SQL Server version 7.0
Course Outline
Day 1
Module 1: SQL Server Overview
Topics:
What is Microsoft SQL Server?
SQL Server architecture
SQL Server security
SQL Server databases
Working with SQL Server
Lab:
SQL Server overview
Skills:
Students will be able to:
- Describe SQL Server 7.0 and its supporting operating system platforms.
- Describe SQL Server architecture.
- Describe SQL Server security.
- Describe SQL Server databases.
- Identify SQL Server application design options, as well as implementation and administration activities.
Module 2: Overview of Transact-SQL
Topics:
Microsoft SQL Server programming tools
Transact-SQL programming language
Elements of Transact-SQL
Ways to execute Transact-SQL statements
How queries are processed
Lab:
Overview of Transact-SQL
Skills:
Students will be able to:
- Describe the primary SQL Server programming tools.
- Describe the basic elements of Transact-SQL.
- Describe the ways to execute Transact-SQL statements.
- Describe how queries are processed.
Module 3: Creating Databases
Topics:
Introduction to databases
Working with databases
Modifying databases
Creating file groups
Library database
Creating data types
Creating tables
Generating scripts
Labs:
Creating databases
Creating database objects
Skills:
Students will be able to:
- Evaluate database storage considerations.
- Create and configure a database.
- Manage a database and transaction log.
- Create a file group.
- Create and drop user-defined data types.
- Create and drop user tables.
- Generate a script.
Day 2
Module 4: Implementing Data Integrity
Topics:
Types of data integrity
Enforcing data integrity
Using constraints
Using defaults and rules
Deciding which enforcement method to use
Lab:
Implementing data integrity
Skills:
Students will be able to:
- Describe the types of data integrity.
- Define and use DEFAULT and CHECK constraints.
- Define PRIMARY KEY, UNIQUE, and FOREIGN KEY constraints.
- Describe and use defaults and rules.
- Determine which data integrity methods to use.
Module 5: Planning and Creating Indexes
Topics:
Introduction to indexes
Index architecture
Creating indexes
CREATE INDEX options
Labs:
Creating indexes
Maintaining indexes
Skills:
Students will be able to:
- Determine when indexes are useful and decide on 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: Querying Multiple Tables
Topics:
Combining data from multiple tables
Combining multiple result sets
Creating a table from a result set
Lab:
Querying multiple tables
Skills:
Students will be able to:
- Combine data from two or more tables by using joins.
- Combine multiple result sets into one result set by using the UNION operator.
- Create tables by using the SELECT INTO statement.
Module 7: Advanced Query Techniques
Topics:
Introduction to subqueries
Nested subqueries
Correlated subqueries
Using the EXISTS and NOT EXISTS keywords
Modifying data
Lab:
Advanced query techniques
Skills:
Students will be able to:
- Use subqueries to break down and perform complex queries.
- Modify data using the INSERT… SELECT, DELETE, and UPDATE statements.
Day 4
Module 8: Summarizing Data
Topics:
Using aggregate functions
GROUP BY fundamentals
Generating aggregate values within result sets
Using the COMPUTE and COMPUTE BY clauses
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 and HAVING clauses.
- Generate summary data for a table using aggregate functions with the GROUP BY clause and the ROLLUP or CUBE operators.
- Generate control-break reports using the COMPUTE and COMPUTE BY clauses.
- Use the TOP n keyword to retrieve a list of the specified top values in a table.
Module 9: Managing Transactions and Locks
Topics:
Introduction to transactions and locks
Managing transactions
SQL Server locking
Managing locks
Lab:
Managing transactions and locks
Skills:
Students will be able to:
- Describe transaction processing.
- Execute, cancel, or roll back a transaction.
- Identify locking concurrency issues.
- Identify resource items that can be locked and the types of locks.
- Describe lock compatibility.
- Set locking options and display locking information.
Module 10: Working with Distributed Data
Topics:
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
Lab:
Working with distributed data
Skills:
Students will be able to:
- Write ad hoc queries that access data that is stored in a remote SQL Server or in an OLE DB data source.
- Set up a linked server environment to access data that is stored in a remote SQL Server or in an OLE DB data source.
- Write queries that access data from a linked server.
- Execute stored procedures on a remote server.
- Use distributed transactions to modify distributed data.
Module 11: Implementing Views
Topics:
What is a view?
Advantages of views
Defining views
Modifying data through views
Lab:
Implementing views
Skills:
Students will be able to:
- Define a view with the CREATE VIEW statement.
- Alter a view definition.
- Drop a view from a database.
- Describe the characteristics and implications of a broken ownership chain.
- Locate view definition information.
- Update a source table using a view.
Day 5
Module 12: Implementing Stored Procedures
Topics:
Introduction to stored procedures
Creating, executing, and modifying stored procedures
Using parameters in stored procedures
Executing extended stored procedures
Handling error messages
Labs:
Creating stored procedures
Creating stored procedures with parameters
Skills:
Students will be able to:
- Describe how a stored procedure is processed.
- Create, execute, modify, and drop a stored procedure.
- Create stored procedures that accept parameters.
- Execute extended stored procedures.
- Create custom error messages.
Module 13: Implementing Triggers
Topics:
Introduction to triggers
Defining triggers
Examples of triggers
Lab:
Creating triggers
Skills:
Students will be able to:
- Create a trigger.
- Drop a trigger.
- Alter a trigger.
Module 14: Advanced Text Queries
Topics:
Microsoft Search Service
Microsoft English Query
Labs:
Microsoft Search Service
English Query
Skills:
Students will be able to:
- Set up Microsoft Search Service.
- Create full-text indexes.
- Write full-text queries.
- Install Microsoft English Query.
- Identify query terms.
- Execute English queries.
|
