SQL Server 2005: Introduction for Beginners (4 day)

Scheduled Dates (sort by: location | date)

Course Overview

SQL Server 2005 offers a robust architecture for enterprise data management, developer productivity and business intelligence. The ability to employ and capitalise on this powerful feature set is essential for an organisation in the current competitive market.

This course provides the knowledge and practical skills needed to exploit SQL Server 2005. You gain a solid foundation on which to build essential SQL Server development and administrative skills.

Audience

This course is valuable to 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

Managing SQL Server Security and Administration

Administering security

• Creating SQL Server logins
• Implementing a security plan
• Defining database roles

Deploying the Maintenance Plan Wizard

• Checking database consistency
• Verifying index pointers
• 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
• Changing packages with SSIS Designer

Analysis Services

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

SQL Server 2005 Reporting Services

• Authoring and deploying reports
• Extracting data from Analysis Services
• Viewing reports from a Web site

Providing Application Solutions with SQL Server

• Exploiting XML B2B capabilities
• Investigating .NET Common Language Runtime (CLR) assemblies

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