This course is intended for system engineers and developers who are responsible for implementing Microsoft SQL Server and writing Transact-SQL code.

This course provides students with the technical skills required to implement a database solution with the Microsoft® SQL Server client/server database management system, based on a case-study design. Lab exercises allow hands-on implementation of the case-study design.

At Course Completion, students will be able to create databases, user-defined data types, and tables. Write Transact-SQL® statements to query data, manipulate data, and program the server; identify issues to consider when creating indexes. Create views, triggers, and stored procedures; enforce data integrity by creating and implementing constraints, defaults, and rules describe how to use cursors to perform row operations. Determine how to distribute data; and determine which external components should be incorporated into a server environment.

DETAILED COURSE DESCRIPTION

Day 1

• Implementing a Database

What is Microsoft SQL Server version 7.0? SQL Server administration tools; Exploration of SQL Enterprise Manager.
• Defining Data Storage

Defining system catalog; System databases; System tables; Database components; System stored procedures; System configuration, Allocating storage.

• Creating and Managing Databases

Creating a database; Creating tables; Managing data with a retrieval and information database (RAID).
• Retrieving Data

SELECT statement; Choosing columns; Manipulating character data; Choosing rows; Sorting results.

• Retrieving Data-Advanced Topics

Generating summary data; Correlating data; Performing subqueries; Generate summary data with aggregate functions and the GROUP BY and HAVING clauses; Generate online analytical processing reports using the GROUP BY WITH; CUBE and GROUP BY WITH ROLLUP clauses; Correlate data with natural joins, outer joins, and self joins. Write subqueries; Combine results sets with the UNION operator.
• Modifying Data

Inserting rows; Updating row data; Deleting rows; Add new rows with the INSERT statement; Update and remove rows based on information from other tables; Use data in other tables to determine which rows to delete or update; Use data in other tables as the values for updating.
• Implementing Indexes

Implementing indexes; Types and characteristics of indexes; Performance considerations; Determine when indexes are useful; Create indexes using the CREATE INDEX statement; Describe the difference between a clustered and nonclustered index, and explain when a clustered index would be advantageous; Create unique and composite indexes; Describe performance considerations when using indexes; Define and describe the terms UPDATE STATISTICS, FILLFACTOR, and PAD_INDEX; Create clustered indexes using SORTED_DATA and SORTED_DATA_REORG.

• Designing Data Integrity

What is data integrity? Using the IDENTITY property to generate values; Creating and implementing defaults and rules; Using constraints to enforce data integrity; When to use data integrity components; Define how SQL Server ensures data integrity; Implement the IDENTITY property; Define the purpose and function of defaults and rules; Create, bind, unbind, and drop defaults and rules; Define the purpose and function of constraints; Identify the appropriate uses for the different types of constraints; Implement constraints using the ALTER TABLE statement.
• Implementing Views, Triggers

Views: Creating views; Modifying data through views; View considerations;
Triggers: Creating triggers; Enforcing data integrity through triggers.

• Stored procedures: Creating stored procedures; Execution plan;System stored procedures.
• Programmability

Batches and scripts; Transaction management; Distributed transactions; Control-of-flow language; EXECUTE statement; Cursors.
• Distributed Data Overview (Advanced Topics if we have time)

The need for distributed data; Implementing distributed data; Two-phase commit; Remote stored procedures; Open Data Services library; Bulk Copy program; Replication; SQL Server Web Assistant; Extensibility of SQL Server 7.0; SQL Server extensions; SQL Executive Manager; Extended stored procedures; Integrating the messaging API; Using OLE with SQL Server; Client-side and server-side APIs; Open Database Connectivity (ODBC); DB-LibraryTM.