Loading...

Course Description

The course will cover ANSI/ISO standard SQL, with examples in SQL Server, Oracle, DB2 luw and z/os, and MySQL, with emphasis on SQL Server and Oracle.  Labs can be done in any of these database environments.

Related Courses

Other Courses

Who Should Attend

Application developers who have taken SQL Basics or the equivalent work, and have several weeks of experience using SQL.

Prerequisites

Completion of SQL Basics or the equivalent work and several weeks of experience using SQL.

Attend and You Will Learn / Expected Outcome

Expected Outcomes

  • Understand basic relational database design principles, and how tables and other objects are created and maintained
  • Use a wide variety of tools and techniques to improve performance of complex queries on large databases
  • Follow best practices to improve security of data
  • Create and manage complex views, and handle updating of views, the Check Option, materialized views, and virtual columns
  • Create and manage indexes, including knowing when to create, and when not to create, indexes, and the use of partitioning, clustering, filtered, full-text, and other types of indexes
  • Create stored procedures, including the use of required and optional parameters, variables, conditional statements, looping, cursors, exception handling, transactions, and debugging
  • Create user defined functions, including the use of parameters, and all language features; create scalar and table functions, and understand the differences between table functions, stored procedures, and view
  • Create triggers, including table, database and server triggers, before, instead of, and after triggers, handle various events, use the "transition" data involved, and deal with transaction issues
 

Attend And You Will Learn

Introduction

  • Tools for diagnosing and improving performance
  • Best practices for improving security

View Issues

  • Overview/review of creating views
  • Updatability of views 
  • The Check option 
  • Materialized views 
  • Virtual columns

View issues

  •  Overview/review of creating views
  • Updatability of views
  • The Check option
  • Materialized views
  • Virtual columns

 Index issues

  • Overview/review of indexes
  • Indexing for performance
  • Special index type
  •  Partitioning
  • Clustering
  • Index-organized tables
  • Filtered
  • Full-text

Stored Procedures

  • Overview/review of Microsoft's Transact-SQL, Oracle's PL/SQL, and other procedure languages 
  • Parameters: input, output, optional parameters with default values 
  • Variables, conditional statements, looping 
  • Returning a result set 
  • Exception handling 
  • Transaction handling 
  • Debugging 
  • Overloading

User-Defined Functions

  • Scalar functions: parameters, default values 
  • Table functions and comparison to views

Triggers

  • Triggers compared to stored procedures or user-defined functions
  • Triggers compared to column constraints or application code
  • Uses of triggers
  • Defining a trigger
  • Specifying a table-level, database-level, or server-level trigger
  • Before, Instead Of, and After triggers
  • Specifying the table (and possibly column(s)) controlled by the trigger
  • Specifying the event(s) that fire the trigger
  • Handling the old (Deleted) and new (Inserted) data values
  • Handling commit/rollback issues in triggers
Loading...

Enroll Now - Select a section to enroll in

Section Title
SQL Level 3
Type
Online
Days
Th, F
Time
9:00AM to 4:30PM
Dates
Nov 05, 2020 to Nov 06, 2020
Schedule and Location
Contact Hours
12.0
Location
  • Off Campus
Delivery Options
Course Fee(s)
Registration Fee non-credit $899.00