DATABASE

  1. Home
  2. Database

Explore your knowledge

DATABASE

DB2 DBA Topics


Part 1:- RELATIONAL DATA MODEL

  • Data and Databases
  • Database Management Systems (DBMS)
  • Information Model & Data Model
  • Relational Data Model & Relational Databases
  • Normalization

Part 2:- DB2 FUNDAMENTALS AND IBM DATA STUDIO TOOL

  • Product Overview
  • Editions & Features
  • Licensing
  • Fundamentals
  • Architecture
  • Users
  • Instances
  • DB2 Client
  • Storage
  • Table spaces
  • Buffer pools
  • Security
  • SQL and XQuery
  • IBM Data Studio
  • DSAC

Part 3:- INSTALLATION AND CONFIGURATION (WORKING WITH WINDOWS OS)

Installation
  • Configuration
  • Database creation
  • Instance creation
  • Table space Creation
  • Working with Buffer pool

    • Part 4:- WORKING WITH DATABASES AND DATABASE OBJECT IN DB2

  • Servers, Instances, and Databases
  • Managing Instances
  • Creating a DB2 Database
  • Cataloging
  • Database (Data) Objects

Part 5:- INTRODUCTION TO SQL

SQL
  • Data Definition Language
  • Data Manipulation Language
  • JOINs
  • Data Control Language
  • Transaction Control Statements

Part 6:- DATA CONCURRENCY

  • Database Transactions
  • Concurrency
  • Concurrency Issues
  • Concurrency Control
  • Isolation Levels
  • Locking
  • Specifying Isolation Levels

Part 7:- DB2 SECURITY

  • Authentication
  • Trusted Context
  • Authorization
  • Authorities
  • Privileges
  • Label-Based Access Control (LBAC)
  • ROLES

Part 8:- DB2 BACKUP AND RECOVERY

  • Backup
  • Recovery

Part 9:- DB2 PURE XML

  • Overview of XML
  • PureXML in DB2
  • XML Data Movement in DB2
  • XQuery and SQL/XML
  • XML Indexes in DB2
  • Application Development

Part 10:- DB2 PROGRAMMING FUNDAMENTALS

  • Application Development Environment
  • Embedded SQL
  • Static SQL
  • Dynamic SQL
  • Routines
  • Types
  • Benefits
  • Usage
  • Tools for Developing Routines
  • Triggers

MS Exchange Server 2010 & 2013


Exchange Server 2010

Deploying Microsoft Exchange Server 2010

  • Overview of Exchange Server 2010 Requirements
  • Installing Exchange Server 2010 Server Roles
  • Installing Exchange Server 2010

Configuring Mailbox Server

  • Overview of Exchange Server 2010 Administrative Tools
  • Configuring Mailbox Server Roles
  • Configuring Public Folders

Managing Client Access

  • Configuring the Client Access Server Role
  • Configuring Client Access Service for Outlook Clients
  • Configuring for Outlook Web App
  • Configuring Outlook Anywhere

Managing Message Transport

  • Overview of Message Transport
  • Configuring Message Transport
  • Configuring Internet Message Transport
  • Troubleshooting Message Transport
  • Troubleshooting Internet Message Delivery

Implementing Messaging Security

  • Deploying Edge Transport Servers
  • Deploying an Antivirus Solution
  • Configuring an Anti-Spam Solution
  • Configuring Secure SMTP Messaging

Implementing Backup and Recovery

  • Planning Backup and Recovery
  • Backing Up Exchange Server 2010
  • Restoring Exchange Server 2010

Exchange Server 2013

Module 1:

Overview of Exchange Server 2013 Unified Messaging

This module explains the basic concept of Unified Messaging in Exchange Server 2013.

Lessons
  • Lesson 1: Telephony technologies overview
  • Lesson 2: Unified Messaging in Exchange Server 2013
  • Lesson 3: Unified Messaging components
Lab : Unified Messaging Overview
  • Exercise 1: Identifying Unified Messaging components

After completing this module, students will be able to explain the basic concept of Unified Messaging in Exchange Server 2013.

Module 2:

Designing and implementing Exchange Server 2013 Unified Messaging

This module explains how to design and implement Exchange Server 2013 Unified Messaging.

Lessons
  • Lesson 1: Designing a Unified Messaging deployment
  • Lesson 2: Deploying and configuring Unified Messaging components
  • Lesson 3: Integrating Exchange Server 2013 Unified Messaging with Lync
Lab : Designing and Implementing Exchange Server 2013 Unified Messaging
  • Exercise 1: Designing the Unified Messaging implementation
  • Exercise 2: Configuring Unified Messaging features
  • Exercise 3: Configuring Unified Messaging integration with Lync Server 2013

After completing this module, students will be able to design and implement Exchange Server 2013 Unified Messaging.

Module 3:

Designing and implementing site resiliency

This module explains how to design and implement site resiliency for Exchange Server 2013.

Lessons
  • Lesson 1: Site resiliency in Exchange Server 2013
  • Lesson 2: Planning a site resilient implementation
  • Lesson 3: Implementing site resiliency
Lab : Designing and implementing site resiliency
  • Exercise 1: Designing a site resilient solution
  • Exercise 2: Implementing site resiliency
  • Exercise 3: Validating site resiliency

After completing this module, students will be able to design and implement site resiliency for Exchange Server 2013.

Module 4:

Planning virtualization for Exchange Server 2013

This module explains how to plan a virtualization strategy for Exchange Server 2013 roles.

Lessons
  • Lesson 1: Hyper-V 3.0 overview
  • Lesson 2: Virtualizing Exchange Server 2013 server roles
Lab : Planning virtualization of Exchange Server roles
  • Exercise 1: Designing an Exchange Server 2013 deployment for a large organization
  • Exercise 2: Designing an Exchange Server 2013 deployment for a medium organization
  • Exercise 3: Designing an Exchange Server 2013 deployment for a medium complex organization

After completing this module, students will be able to plan a virtualization strategy for Exchange Server 2013 roles.

Module 5:

Designing and implementing message transport security

This module explains how to design and implement message transport security.

Lessons
  • Lesson 1: Overview of policy and compliance requirements
  • Lesson 2: Designing and implementing transport compliance
  • Lesson 3: Designing and implementing AD RMS integration with Exchange Server 2013
Lab : Designing and implementing message transport security
  • Exercise 1: Planning a message transport implementation
  • Exercise 2: Implementing message transport security
  • Exercise 3: Implementing AD RMS and Exchange Server integration

After completing this module, students will be able to design and implement message transport security.

Module 6:

Designing and implementing message retention

This module explains how to design and implement message retention in Exchange Server 2013.

Lessons
  • Lesson 1: Message records management and archiving overview
  • Lesson 2: Designing in-place archiving
  • Lesson 3: Designing and implementing message retention
Lab : Designing and implementing message retention
  • Exercise 1: Designing message retention and archiving
  • Exercise 2: Implementing message retention and archiving

After completing this module, students will be able to design and implement message retention in Exchange Server 2013

Module 7:

Designing and implementing messaging compliance

This module explains how to design and implement messaging compliance.

Lessons
  • Lesson 1: Designing and implementing data loss prevention
  • Lesson 2: Designing and implementing an in-place hold
  • Lesson 3: Designing and implementing in-place e-discovery
Lab : Designing and implementing messaging compliance
  • Exercise 1: Designing messaging compliance
  • Exercise 2: Implementing data loss prevention
  • Exercise 3: Implementing in-place e-discovery
  • Exercise 4: Comparing messaging policy and compliance options

After completing this module, students will be able to design and implement messaging compliance.

Module 8:

Designing and implementing administrative security and auditing

This module explains how to design and implement administrative security in an Exchange Server 2013 environment.

Lessons
  • Lesson 1: Designing and implementing role based access control
  • Lesson 2: Designing and implementing split permissions
  • Lesson 3: Planning and implementing audit logging
    • Lab : Designing and implementing administrative security and auditing
  • Exercise 1: Designing a solution to fulfill requirements
  • Exercise 2: Implementing role based access control
  • Exercise 3: Implementing mailbox access and administrative logging
  • Exercise 4: Implementing and testing split permissions

    • After completing this module, students will be able to design and implement administrative security in an Exchange Server 2013 environment.

    Module 9:

    Managing Exchange Server 2013 with Exchange Management Shell

    This module explains how to use Windows PowerShell 3.0 to manage Exchange Server 2013.

    Lessons
  • Lesson 1: Overview of Windows PowerShell 3.0
  • Lesson 2: Using Exchange Management Shell to manage Exchange Server recipients
  • Lesson 3: Managing Exchange Server 2013 with Exchange Management Shell
    • Lab : Managing Exchange Server 2013 with Exchange Management Shell
  • Exercise 1: Exploring Exchange Management Shell
  • Exercise 2: Using Exchange Management Shell to manage recipients

    • After completing this module, students will be able to use Windows PowerShell 3.0 to manage Exchange Server 2013.

    Module 10:

    Designing and implementing integration with Exchange Online

    This module explains how to design and implement integration with Exchange Online.

    Lessons
  • Lesson 1: Planning for Exchange Online
  • Lesson 2: Planning and implementing the migration to Exchange Online
  • Lesson 3: Planning coexistence with Exchange Online
    • Lab : Designing integration with Exchange Online
  • Exercise 1: Designing integration with Exchange Online
  • Exercise 2: Implementing integration with Exchange Online

    • After completing this module, students will be able to design and implement integration with Exchange Online.

    Module 11:

    Designing and implementing messaging coexistence

    This module explains how to design and implement messaging coexistence.

    Lessons
  • Lesson 1: Designing and implementing federation
  • Lesson 2: Designing coexistence between Exchange organizations
  • Lesson 3: Designing and implementing cross-forest mailbox moves
  • Exercise 1: Implementing Message Routing Coexistence
  • Exercise 2: Migrating user mailboxes
  • Exercise 3: Configuring administrative coexistence

    • After completing this module, students will be able to design and implement messaging coexistence.

    Module 12:

    Designing and implementing Exchange Server migrations and upgrades

    This module explains how to design and implement Exchange Server migrations from non-Exchange messaging systems, and upgrades from previous Exchange Server versions.

    Lessons
  • Lesson 1: Designing migration from non-Exchange email systems
  • Lesson 2: Planning the upgrade from previous Exchange versions
  • Lesson 3: Implementing the migration from previous Exchange versions
    • Lab : Designing and implementing Exchange Server migrations
  • Exercise 1: Planning the migration strategy
  • Exercise 2: Deploying Exchange Server 2013 in an Exchange Server 2007 organization
  • Exercise 3: Migrating all messaging components to Exchange Server 2013
  • Exercise 4: Removing the Exchange Server 2007 server

    • After completing this module, students will be able to design and implement Exchange Server migrations from non-Exchange messaging systems, and upgrades from previous Exchange Server versions.

    SQL DBA

    1:  Starting with SQL Server

    •  Responsibilities of Database Admin

    •  Types of DBAs

    •  History of SQL Server

    •  Editions of SQL Server

    •  Tools of SQL Server

    •  Differences between Standard and Enterprise editions

    •  Instances types in SQL Server

    •   Default Instance
    •   Named Instance

    •  SQL Server Services

    •   Instance aware services
    •   Instance unaware services
    2:  Installing SQL Server

    •  Pre-requisites

    •  Installation

    •  Post Installation verification Session

    3:  Functioning of Databases

    •  Database

    •  Types of Database and Brief explanation

    •   System Databases
    •   User Database

    •  Database Architecture

    •   Pages
    •   Extents
    •   File groups
    •   Database

    •  Transaction Architecture

    •  Creating Database

    •  Modifying Database

    •   Adding Files
    •   Moving and renaming of Database files
    • Database modes
    • Real time Scenario
    4:  Security

    •  Authentication Types in SQL Server

    •  Types of Login

    •   Windows Login
    •   SQL Login

    •  Creating Users and Logins

    •   Server roles
    •   Password policy
    •   Understanding Database and Server Roles
    •   Permissions
    •   Working on access level issues
    •   Orphan users Finding and Fixing
    •   Important Queries
    5:  Database Backups and Restoration

    •  Database Backups

    •   Why we need backups

    •  Types of Backup

    •   Full Backup
    •   Differential Backup
    •   Transaction Log Backup

    •  Copy-only, Mirrored, Split and Tail log Backups

    •  Differences between backups

    •  Backup Strategy

    •  Understanding how the data moving from Log to Data File, CHECKPOINT

    •  Monitoring the space usage of Log File and fixing

    •  Checking the backup files VALID or CORRUPTED

    •  Backup storage tables

    •  Important Queries

    •  Performing Restorations

    •   Types of Restoration
    •   Attach and Detach
    •   Shrinking files
    6:  Recovery Models of Database

    •  Types of Recovery Models

    •   Full
    •   Bulk Logged
    •   Simple

    •  Differences between Recovery Models

    •  Setting Recovery Models according to the Scenarios with examples

    7:  Automation of SQL SERVER

    •  Working with SQL SERVER Agent

    •   Creating Jobs
    •   Managing Jobs and Resolving issues
    •   Monitoring Jobs
    •   Monitoring Tables
      • Log Shipping
      • Mirroring
      • Replication
      • Clustering

    HIGH AVAILABILTY CONCEPTS

    1:  Log Shipping

    •  Log Shipping

    •   Working with Log Shipping
    •   Terminology
    •   Pre-requisites to implement Log Shipping
    •   Different modes of Log Shipping
    •   Configuring Log Shipping
    •   Monitoring
    •   Manually Performing Failover
    •   Log Shipping Tables

      • •  Discussing Real Time Scenarios

    2:  Database Mirroring

    •  Working with Database Mirroring

    •   Terminology
    •   Pre-requisites to implement Mirroring
    •   Pre check mirroring ports ENABLED or DISABLED
    •   Architecture
    •   Operating Modes
    •   Differences between Modes
    •   Port numbers involved in Mirroring
    •   Requirements for Mirroring
    •   Configuring Mirroring
    •   Mirroring failover
    •   Issues on Mirroring
    3:  Replication

    •  Working with Replication

    •   Terminology
    •   Pre-requisites to implement to Replication
    •   Types of Replications
    •   DISTRIBUTOR Database
    •   Agents involved in Replication
    •   Configuring Replication
    •   Resolving Issues
    4:  Clustering
    • Introduction about clustering
    • Clustering Architecture
    • Importance of IP’s involved in Clustering
    • SAN importance
    • Drive dependency
    • Nodes in Clustering
    • Important commands
    5:  Always On
    • What is availability group
    • Advantages of HADRON(Always on)

    DIFFERENCES BETWEEN ABOVE HIGH AVAILABILTY CONCEPTS and ADVANCED CONCEPTS

    1:  Monitoring Tools
    • System Monitor
    • SQL Server Profiler
    • SQL Server and Windows Error Logs
    2:  Database Performance Tanning
    • Indexes
    • Types of indexes
    • Fragmentation in Indexes
    • Types of Fragmentations
    • Database Tuning Advisor
    • Dead Locks
    • Blockings
    • Terminating Processes
    3:  Managing the SQL Server and Databases
    • Starting SQL Server in Single user mode
    • Changing the Default path of Database files
    • Modifying the Logical and Physical names of database files
    High Lights: 
    • Discussions on Real time scenarios
    • Replying to Questioner mails on subject doubts

    * Windows and SQL cluster configuration

    * SQL2012 new feature (Always On) Configuration

    MS SQL Developer

    Part 1

    Chapter 1: SQL SERVER OVERVIEW

    Understanding Client Server Architecture

    Introducing the Tools

    Selecting an Edition

    Creating Sample Databases

    Chapter 2: USING MANAGEMENT STUDIO

    Arranging the Workspace

    Registering Servers

    Explaining Items in the Object Explorer

    Explaining the Database Properties Dialog

    Explaining the Columns Properties Dialog

    Continuing with Object Explorer Items

    Using the Query Editor

    Chapter 3: DESIGNING RELATIONAL DATABASES

    Understanding Normalization

    Discovering Patterns

    Avoiding Over Complexity

    Managing Optional Data

    Creating Physical Keys

    Using the Database Diagrammer Tool

    Final Comments

    Part 2: Selecting Data

    Chapter 1: WORKING WITH SINGLE TABLE QUERIES

    Understanding Query Flow

    Filtering Data with the Where Clause

    Columns, Expressions & Scalar Functions

    Using Case Expressions

    Working with Nulls & Coalesce

    Selecting Environment Functions

    Working with Dates & Times

    Parsing Strings

    Converting Data

    Sorting Data

    Topping Off the Query with Predicates

    Chapter 2: MERGING DATA WITH JOINS & UNIONS

    Working with Inner Joins

    Using Left Outer Joins

    Multiplying Data with Cross Joins

    Referencing Self-Joins

    Working with Complex Joins

    Finding the Set Difference

    Stacking Data with Unions

    Chapter 3: WORKING WITH SUBQUERIES

    Using Subqueries as Expressions

    Including Subqueries as Lists

    Adding Subqueries as Derived Tables

    Working with Correlated Subqueries

    Solving for X with Relations Division

    Chapter 4: AGGREGATING DATA

    >

    Using the New Windowing Functions

    Using Aggregate Functions

    Grouping a Data Set

    Nesting Aggregations

    Filtering Groups & Adding Grand Totals

    Generating Cross Tab Tables (Pivot)

    Calculating Cumulative Sums

    Chapter 5: USING FULL-TEXT SEARCH

    Creating a Full-Text Index

    Searching for Words

    Using a Fuzzy Search to Search for Phrases

    Chapter 6: SELECTING DATA THROUGH VIEW

    Creating Views

    Preventing Disappearing Rows

    Protecting from Schema Changes

    Nesting Views

    Chapter 7: DISTRIBUTED QUERIES

    Linking to External Data Sources

    Executing Distributed Queries

    Looking at Where the Work Takes Place

    Using Pass Through Queries

    Controlling Distributed Transactions

    Chapter 8: MODIFYING DATA

    Inserting Data

    Updating Data

    Deleting Data

    Using the Output Clause

    Final Comments & Credits

    Part 3: Developing Solutions

    Chapter 1: T-SQL & STORED PROCEDURES

    Introducing T-SQL

    Working with Variables

    Controlling Procedural Flow

    Examining SQL Server with Code

    Using Temp Tables

    Developing Dynamic SQL

    Introducing Stored Procedures

    Passing Parameters

    Returning Parameters

    Working with a Cursor

    Handling Errors

    Explaining the Raise Error

    Controlling Transactions

    Chapter 2: TRIGGERS & AUDIT TRAILS

    Introducing After Triggers

    Using "Instead of" Trigger

    Disabling Triggers

    Using the Update ( ) Function

    Selecting Transaction Data

    Nesting & Recursion

    Enforcing Complex RI

    Auditing Data Changes

    Chapter 3: USER-DEFINED FUNCTIONS

    Creating Scalar Functions

    Working with Inline Table-Valued Functions

    Using Multi-Statement Table-Valued UDFs

    Navigating a Hierarchy

    Having Fun with Apply

    Exploring Functions in Nordic

    Chapter 4: INDEXING FOR PERFORMANCE

    Introducing Indexes

    Creating Indexes

    Examinging Query Execution Plans, Pt. 1

    Examinging Query Execution Plans, Pt. 2

    Chapter 5: TRANSACTIONS, LOCKING & BLOCKING

    Introducing ACID

    Understanding Transactional Faults

    Stepping through a Transaction

    Exploring Locks

    Working with Locks

    Viewing Deadlocks

    Using Snapshot Isolation

    Chapter 6: INTRODUCTION TO OPTIMIZATION THEORY

    Deconstructing the Database System

    Walking through Optimization Theory

    Optimization Theory Continued

    Final Comments & Credits

    Performance Tuning Overview

    The Performance-Tuning Process

    Performance vs. Price

    Performance Baseline

    Where to Focus Efforts

    SQL Server Performance Killers

    System Performance Analysis

    Performance Monitor Tool

    Dynamic Management Views

    Hardware Resource Bottlenecks

    Memory Bottleneck Analysis

    Memory Bottleneck Resolutions

    Disk Bottleneck Analysis

    Disk Bottleneck Resolutions

    Processor Bottleneck Analysis

    Processor Bottleneck Resolutions

    Network Bottleneck Analysis

    Network Bottleneck Resolutions

    SQL Server Overall Performance

    Creating a Baseline

    System Behavior Analysis Against Baseline

    SQL Query Performance Analysis

    Extended Events Wizard

    Extended Events Automation

    Extended Events Recommendations

    Other Query Performance Metrics Methods

    Costly Queries

    Execution Plans

    Index Analysis

    What Is an Index?

    Index Design Recommendations

    Clustered Indexes

    Nonclustered Indexes

    Clustered vs. Nonclustered Indexes

    Advanced Indexing Techniques

    ColumnStore Indexes

    Special Index Types

    Additional Characteristics of Indexes

    Database Engine Tuning Advisor

    Database Engine Tuning Advisor Mechanisms

    Database Engine Tuning Advisor Examples

    Database Engine Tuning Advisor Limitations

    Bookmark Lookup Analysis

    Purpose of Bookmark Lookups

    Drawbacks of Bookmark Lookups

    Analyzing the Cause of a Bookmark Lookup

    Resolving Bookmark Lookups

    Statistics Analysis

    The Role of Statistics in Query Optimization

    Statistics on an Indexed Column

    Statistics on a Nonindexed Column

    Analyzing Statistics

    Statistics Maintenance

    Analyzing the Effectiveness of Statistics for a Query

    Recommendations

    Fragmentation Analysis

    Causes of Fragmentation

    Fragmentation Overhead

    Analyzing the Amount of Fragmentation

    Fragmentation Resolutions

    Significance of the Fill Factor

    Automatic Maintenance

    Execution Plan Cache Analysis

    Execution Plan Generation

    Execution Plan Caching

    Components of the Execution Plan

    Aging of the Execution Plan

    Analyzing the Execution Plan Cache

    Execution Plan Reuse

    Query Plan Hash and Query Hash

    Execution Plan Cache Recommendations

    Query Recompilation

    Benefits and Drawbacks of Recompilation

    Identifying the Statement Causing Recompilation

    Analyzing Causes of Recompilation

    Avoiding Recompilations

    Query Design Analysis

    Query Design Recommendations

    Operating on Small Result Sets

    Using Indexes Effectively

    Avoiding Optimizer Hints

    Using Domain and Referential Integrity

    Avoiding Resource-Intensive Queries

    Reducing the Number of Network Round-Trips

    Reducing the Transaction Cost

    Blocking Analysis

    Blocking Fundamentals

    Understanding Blocking

    Locks

    Isolation Levels

    Effect of Indexes on Locking

    Capturing Blocking Information

    Blocking Resolutions

    Recommendations to Reduce Blocking

    Automation to Detect and Collect Blocking Information

    Deadlock Analysis

    Deadlock Fundamentals

    Using Error Handling to Catch a Deadlock

    Deadlock Analysis

    Avoiding Deadlocks

    Cursor Cost Analysis

    Cursor Fundamentals

    Cursor Cost Comparison

    Default Result Set

    Analyzing SQL Server Overhead with Cursors

    Cursor Recommendations

    Database Performance Testing

    Performance Testing Overview

    Capturing Data with the Server Side Trace

    Distributed Replay for Database Testing

    Summary and SQL Server Optimization Checklist

    Conclusion

    1.  DBMS Concepts

    •  What is Database?

    •  What is Database System?

    •  E. F. CODD’s Rules

    •  RDBMS

    •  ORDBMS

    •  Difference Between DBMS, RDBMS & ORDBMS

    2.  SQL

    •  What is SQL?

    •  DDL Commands

    •  Create, Alter, Drop

    •  Truncate, Rename

    •  DML Command – Insert

    SQL SELECT statements

    a.  Order by Clause

    b.  Distinct Clause

    c.  Where Clause

    d.  Operators

    e.  Comparison Conditions

    Constraints

    a.  Primary Key Constraint

    b.  Unique Constraint

    c.  Not Null Constraint

    d.  Foreign Key Constraint

    e.  Check Constraint

    f.  Cascade Constraints

    Joins

    a.  What is a Join?

    b.  Cartesian Join

    c.  Equi Join

    d.  Non – Equi Join

    e.  Self Join

    f.  Outer Join

    g.  Left Outer Join

    h.  Right Outer Join

    i.  Full Outer Join

    j.  Pseudo Columns

    Functions

    a.  Numeric Functions

    b.  Character Functions

    c.  Date Functions

    d.  Conversion Functions

    e.  General Functions

    f.  Aggregating Data Using Group Functions

    Sub Queries

    a.  Simple Sub Queries

    b.  Multiple Row Sub Queries

    Update

    a.  Data Updating

    b.  Updating data using Sub Queries

    Delete

    a.  Deleting Data

    b.  Deleting data using Sub Queries

    c.  Deleting data using join

    Transaction Control Language (TCL)

    a.  Commit

    b.  Roll back

    c.  Save point

    Database Objects

    a.  View

    b.  Synonym

    c.  Sequence

    d.  Index

    e.  Privileges

    View

    a.  Simple View

    b.  Complex View

    c.  In Line View

    Synonym

    a.  Private Synonym

    b.  Public Synonym

    Privileges

    a.  Grant

    b.  Revoke

    3.  PL/SQL

    •  Block Structure of PL/SQL

    •  Data Types, Variables & Constants

    Flow Control Statements

    a.  Simple IF

    b.  IF-THEN-ELSE Statements

    c.  IF-THEN-ELSIF Statements

    Iterative Statements

    a.  Simple Loop

    b.  While Loop

    c.  For Loop

    Attribute Data Types

    a.  %type

    b.  %Rowtype

    Cursor Management

    a.  What is Cursor?

    b.  Implicit Cursor

    c.  Explicit Cursor

    d.  Cursor with parameters

    e.  Cursor with For Loop

    Exceptions

    a.  What is an Exception?

    b.  Predefined Exception

    c.  User defined Exception

    Sub Programs

    a.  Named PL/SQL Block

    b.  Stored Procedures

    c.  Types of Parameters

    d.  Stored Function

    e.  Difference between Function and Procedure

    Package

    a.  What is package?

    b.  Importance of package

    Triggers

    a.  What is a Trigger?

    b.  Row Level Trigger

    c.  Table Level Trigger

    d.  Instead of Trigger

    e.  DML Triggers

    f.  System Triggers

    T-SQL Topics

    Relational Database Basics

    o  Brief History of SQL

    o  Relational Databases [may be compressed due to attendee experience]

    •   Tables
    •   Rows
    •   Columns
    •   Relationships
    •   Datatypes
    •   Primary Keys
    •   Foreign Keys
    •   Relational Database Management System

    o  SQL Server Objects

  •   Valid Object References

    • o  SQL Statements

    •   Database Manipulation Language (DML)
    •   Database Definition Language (DDL)
    •   Database Control Language (DCL)

    Simple SELECTs with SQL Server

    o  Introduction to the Northwind Database

    o  Some Basics

    •   Comments
    •   Whitespace and Semi-colons
    •   Case Sensitivity

    o  SELECTing All Columns in All Rows

    o  Exploring the Tables

    o  SELECTing Specific Columns

    o  Sorting Records

    •   Sorting By a Single Column
    •   Sorting By Multiple Columns
    •   Sorting By Column Position
    •   Ascending and Descending Sorts

        • o  The WHERE Clause and Operator Symbols

        •   Checking for Equality
        •   Checking for Inequality
        •   Checking for Greater or Less Than
        •   Checking for NULL
        •   WHERE and ORDER BY

        o  The WHERE Clause and Operator Words

        •   The BETWEEN Operator
        •   The IN Operator
        •   The LIKE Operator
        •   The NOT Operator

        o  Checking Multiple Conditions

        •   AND
        •   oR
        •   order of Evaluation

        o  Writing SELECTs with Multiple Conditions

        Advanced SELECTs with SQL Server

        o  Calculated Fields

        •   Concatenation
        •   Mathematical Calculations
        •   Aliases

        o  Aggregate Functions and Grouping

        •   Aggregate Functions
        •   Grouping Data

        o  Built-in Data Manipulation Functions

        •   Common Math Functions
        •   Common String Functions
        •   Common Date Functions

        Subqueries, Joins, and Unions with SQL Server

        o  Subqueries

        o  Joins

        •   Table Aliases
        •   Multi-table Joins

        o  outer Joins

        o  Unions

        •   UNION ALL
        •   UNION Rules

        Inserting, Updating and Deleting Records with SQL Server

        o  UPDATE

        o  DELETE

        Creating and Modifying Tables with SQL Server

        o  Creating Tables

        o  Datatypes

        o  Altering Tables

        •   Adding a Column
        •   Dropping a Column

        o  Renaming Tables

        o  Dropping Tables

        Views and Stored Procedures in SQL Server

        o  Creating Views

        o  Dropping Views

        o  Creating Stored Procedures

        •   Designing a stored procedure
        •   Specifying input parameters
        •   Returning a recordset
        •   Returning output parameters

        o  Testing stored procedures

        Conclusion