SQL Server training 7class - SQL Server 7 course outline

Pre-requisites

Experience using the Microsoft Windows NTŪ Server network operating system. It is recommended delegates have one year's experience with relational databases, three to six months of SQL Server experience together with an understanding of basic ANSI SQL statements.

This course provides delegates with technical skills required to implement a database solution with the Microsoft SQL Server relational database management system. The course provides extensive hands-on practical experience covering all the main topics including creating databases, creating indexes, performing advanced queries and summarising data.

• 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 summarise data
• Manage locking options and transactions to ensure data concurrency and recoverability
• Create views of data
• Design & create stored procedures
• Design and create triggers
• Work with distributed data

Course Content

SQL Server Overview
What is Microsoft SQL Server? | SQL Server architecture | SQL Server security | SQL Server databases | Working with SQL Server

Overview of Transact - SQL
Microsoft SQL Server programming tools | Transact-SQL programming language | Elements of Transact-SQL | Ways to execute Transact-SQL statements | How queries are processed

Creating Databases
Introduction to databases | Working with databases | Modifying databases | Creating file groups | Library database | Creating data types | Creating tables | Generating scripts

Implementing Data Integrity
Types of data integrity | Enforcing data integrity | Using constraints | Using defaults and rules | Deciding which enforcement method to use

Planning and Creating Indexes
Introduction to indexes | Index architecture | Creating indexes | CREATE INDEX options

Querying Multiple Tables
Combining data from multiple tables | Combining multiple result sets | Creating a table from a result set

Advanced Query Techniques
Introduction to subqueries | Nested subqueries | Correlated subqueries | Using the EXISTS and NOT EXISTS keywords | Modifying data

Summarizing Data
Using aggregate functions | GROUP BY fundamentals | Generating aggregate values within result sets | Using the COMPUTE and COMPUTE BY clauses | Listing the top n values

Managing Transactions and Locks
Introduction to transactions and lock | Managing transactions | SQL Server locking | Managing locks

Working with Distributed Data
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

Implementing Views
What is a view? | Advantages of views | Defining views | Modifying data through views

Implementing Stored Procedures
Introduction to stored procedures | Creating, executing and modifying stored procedures | Using parameters in stored procedures | Executing extended stored procedures | Handling error messages

Implementing Triggers
Introduction to triggers | Defining triggers | Examples of triggers

Advanced Text Queries
Microsoft Search Service | Microsoft English Query