Course Description :

Gain hands-on experience with the newest features of Adaptive Server Enterprise (ASE),

and learn to create database objects expertly using ANSI-standard SQL and Transact-SQL

extensions. Study key features and functions of ASE including automatic recovery of transactions,

writing Stored Procedures and triggers, and creating and using cursors. Course material also

covers important concepts from Introduction to SQL (EDB100), new functions, increased number

of arguments in IN statements, increased limits on object identifiers, and new datatypes.

Course Objectives :

Upon completion of this course, you will be able to:

Use the basic components of Adaptive Server Enterprise to connect to a server database

Create tables, populate the tables with data, and efficiently query data from those tables

Employ data integrity methods to ensure the validity of the data

Write code in Transact-SQL

Use and manage transactions effectively

Write stored procedures and triggers to help in database maintenance and upkeep

Course Topics:

Module 00: Course Introduction

Module 01: Introducing Relational Database Concepts and Transact-SQL

Relational Databases

ANSI SQL and Transact-SQL

Module 02: Introducing Client/Server Architecture

Adaptive Server

isql

Interactive SQL

Database System Procedures

Module 03: Querying Data in Tables

Writing Queries 3-5

Reformatting the Result Set

Modifying the Result Set

Writing Qualified Queries

Module 04: Analyzing Data using Aggregate Functions

Aggregates

Grouping Clauses

Compute Clauses

Module 05: Querying Multiple Tables (Joins and Subqueries)

Joins

Subqueries

Module 06: Creating Tables

Datatypes

Column Properties and create table Syntax

default Clauses

Table Management

Module 07: Creating Temporary Tables and Querying System Tables

Temporary Tables

Creating copy tables

System Tables

Module 08: Modifying Data in Tables

Insert and Update Statements

Delete and Truncate Statements

The Case Statement

Transaction Log and DML

Module 09: Using Views and Indexes to Affect Query Performance

Views

Indexes

Module 10: Using Constraints to Enforce Data Integrity

Overview of Data Integrity

Check Constraints

Primary Key and Unique Constraints

References Constraints

Constraint Management

Module 11: Employing Reusable Database Objects (Defaults and Rules)

Defaults

Rules

User-Defined Datatypes

Data Integrity Methods

Module 12: Manipulating Data using Transact-SQL Functions

Function Basics

Datatype Conversion

Function Categories

Module 13: Programming with Variables

Batches and Comments

Local Variables

Global Variables

Dynamic SQL

Module 14: Programming with Control-of-Flow Statements

if Statements

while Loops

Message Statements

Module 15: Using Transactions to Maintain Database Consistency

Defining Transactions

Transaction Statements

Transaction Modes

Error Processing

Module 16: Transaction Management: Isolating Data with Locks

Locking Basics

Locking Schemes

Isolation levels

Module 17: Using Cursors to Process Data

Cursor Statements

Cursors and Data Modification

Cursors and Transactions

Module 18: Writing Basic Stored Procedures

Benefits of Stored Procedures

Stored Procedure Syntax

Input Parameters

Return Parameters

Module 19: Refining Stored Procedures

Making Code Readable

Verifying Input Values

Tagging Transaction Modes

Capturing an Error Status

Avoiding Unintentional Rollbacks

Preventing Nesting-Level Errors

Recompiling Procedures

Module 20: Using Triggers to Enforce Referential Integrity

Defining Triggers

Special Trigger Tables

Triggers and Transactions

Trigger Production Practices

Comparing Referential Integrity Methods