Objectives:
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.  top

Microsoft Certified Professional Exams
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

Microsoft SQL Server Overview
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.

top
Transact-SQL Overview
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.
top
Creating Databases
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.

top
Implementing Data Integrity
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.

top
Planning and Creating Indexes
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.
top
Performing Advanced Queries
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.    top

Summarizing Data
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.
top
Managing Transactions and Locks
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.          top
Compare distributed transactions and replication.

Implementing Views
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.
top
Implementing Stored Procedures
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.    top

Implementing Triggers
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.
top

Particulars:
Cost: £1550( € 2480) excluding VAT
Platform:  Microsoft NT 4 
Numbers: Maximum of 6 people on each course at F1’s training facilities in London, Bath and Manchester