SQL Server 2005: Introduction for Beginners (4 day)

Course Overview

SQL Server 2005 offers a robust architecture for enterprise data management, developer productivity and business intelligence. This course provides the knowledge and practical skills needed to maximise the benefits of SQL Server 2005. You gain a solid foundation on which to build essential SQL Server development and administrative skills, including creating databases, generating Transact-SQL statements and implementing security.

Audience

Those who are new to SQL Server, as well as those evaluating SQL Server 2005. Familiarity with relational databases is helpful.

Skills Gained

• Build databases, queries and reports for SQL Server 2005
• Create and manage databases with SQL Server Management Studio
• Generate Transact-SQL statements for data retrieval and modification
• Implement security and back up databases to protect critical data
• Configure merge replication and handle data conflicts
• Explore business intelligence solutions using Integration, Analysis and Reporting Services

Course Outline

Introducing SQL Server 2005

Overview of database fundamentals

• Client/Server
• RDBMS

SQL Server 2005 editions

• Express
• Standard
• Enterprise
• Workgroup
• Mobile
• Developer

SQL Server components and tools

• SQL Server Management Studio
• Business Intelligence Development Studio
• SQL Server Agent
• Database engine

Assembling a Database

Building a database

• Choosing file names and locations
• Determining file size and growth parameters
• Utilising schemas to partition tables

Designing tables

• Defining columns and attributes
• Selecting appropriate datatypes
• Setting the primary keys and indexes
• Modifying or removing columns

Applying declarative constraints

• Enforcing check constraints
• Protecting relationships with foreign keys
• Validating the existing data

Developing Database Applications

Retrieving data with Transact-SQL

• Writing SQL queries with Management Studio
• Sorting the final result sets
• Joining columns from different tables
• Aggregating data by groups
• Refining result sets with subqueries

Altering data with SQL commands

• INSERT
• UPDATE
• DELETE

Storing query definitions

• Generating views with Management Studio
• Simplifying data access with views

Server-side and client-side programming

• Constructing a stored procedure
• Executing stored procedures
• Exploring functions and triggers
• Integrating SQL Server with Office

Providing Application Solutions with SQL Server

• Upsizing from Microsoft Access
• Displaying data on the Web with ASP.NET
• Pulling data into Microsoft Excel

Managing SQL Server Security and Administration

Administering security

• Allowing logins access to databases
• Creating SQL Server logins
• Implementing a security plan
• Defining database roles
• Granting permissions on tables to roles

Deploying the Maintenance Plan Wizard

• Checking database consistency
• Backing up the database and log
• Reporting on maintenance plan success

Automating administration

• Defining operators
• Generating and scheduling jobs
• Notifying operators of job outcome

Replicating Data

• Designing replication strategies
• Configuring the distribution server
• Publishing data with merge replication
• Pulling or pushing subscriptions
• Resolving conflicts using priorities

Leveraging Business Intelligence

SQL Server Integration Services (SSIS)

• Importing data into SQL Server
• Exporting data to text or CSV format

Analysis Services

• Storing multidimensional data
• Pivoting cubes in Microsoft Excel
• Detecting patterns with data mining

SQL Server 2005 Reporting Services

• Authoring and deploying reports
• Viewing reports from a Web site

SQL Server 2005: Introduction for Beginners (4 day) / SQ05-IN4