How This Course Is Organised

The Weblearning Oracle and SQL Server Database Development course combines Oracle SQL&PL/SQL and SQL Server Database design and T-SQL development foundation courses into a single solution for students wanting to start a highly rewarding career in Database administration and development.

Oracle and SQL Server are relational database management systems whose primary function is to store and retrieve data. As a database developer you will be working closely with database administrators and business analysts to capture business requirements and implement them using Oracle SQL and Microsoft T-SQL Queries and Procedural programming languages; ensuring performance, reliability and security.

You will also be filling the databases with new and updated information, backing up information and ensuring that databases have ample storage space.

Supported versions: Oracle 12/19c, SQL Server 2017, 2019 and SQL Server 2022


Audience:
Business Analysts/End Users
Database Developers
Database  Administrators
Business Intelligence Developer
Reports Developer
Support Engineer
Project Manager


Course Objectives:

Understand Relational Database Concepts
Understand Oracle and SQL Server Data Tools
Writing SELECT queries
Sorting and filtering data
Using SQL aggregate functions
Using SET operators
Writing joins to query multiple tables
Using built-in functions
Using Subqueries
Inserting, updating, deleting and merging rows
Transaction control
Creating and using views
Using table expressions
Enhanced Grouping and Pivoting data
Using Windowing ranking, offset and ranking functions
Creating and using Stored Procedures
Controlling Program Flow
Using Dynamic SQL
Implementing Error Handling
Improving Query Performance


Courses Topics:

Database Design
Introduction
Data vs. Information
History of the Database
Major Transformations in Computing

Microsoft Topics:
Overview of SQL Server
Working with Management Studio

Introduction to T-SQL Querying
Overview of SQL Server types of statements
Commands and Statement Elements
Understanding Sets
Understanding Predicate Logic
Understanding the Logical Order of Operations in SELECT statements
Basic SELECT Statements

Advanced SELECT Clauses
Using DISTINCT, Aliases and Scalar Functions
Using CASE and MERGE statements
Query Multiple Tables using JOIN statements
Filtering and sorting data
NULL values

SQL Server Data Types and Functions
Introduce data types
Character data types
Date and Time data type
Built in Functions

Grouping and Aggregating Data
Aggregate Functions
GROUP BY and HAVING Clauses

Using Subqueries
Writing Self-Contained Subqueries
Writing Correlated Subqueries
Using the EXISTS Predicate with Subqueries

Working with table Functions
Using Views
Using Inline Table-Valued Functions
Using Derived Tables
Using Common Table Expressions

SET Operators Grouping and Windows Functions
SET Operators
Writing Queries with the UNION Operator
Using EXCEPT and INTERSECT
Using APPLY
Window Functions
Grouping Sets(PIVOT, UNPIVOT, CUBE, ROLLUP).

Modifying Data in SQL Server
DML Statements
INSERT, UPDATE, and DELETE statements
Working with defaults, constraints and triggers
Using the OUTPUT clause

Programming with T-SQL
Introducing T-SQL
Control of Flow Options
Implementing error checking
Understanding and implementing transactions

Retrieving SQL Metadata and Improving SQL Performance
Querying system catalogs and DMV's
Creating and executing stored procedures
Improving SQL Server Query performance
Monitoring SQL Server Performance

Oracle Topics:
Overview of Database
Working with Oracle SQL Developer

Anatomy of a SQL Statement
SELECT and WHERE
Columns, Characters, and Rows
Limit Rows Selected

Comparison Operators
WHERE, ORDER BY
Logical Comparisons and Precedence Rules
Sorting Rows

Introduction to Functions
Single Row Functions Part I
Case and Character Manipulation
Number Functions
Date Functions

Single Row Functions Part II
Conversion Functions
NULL Functions
Conditional Expressions

JOINs
Cross Joins and Natural Joins
Join Clauses
Inner versus Outer Joins
Self-Joins and Hierarchical Queries
Microsoft Equijoin and Cartesian Product
Microsoft Non-equijoins and Outer Joins

Group Functions
Group Functions
Microsoft Non-equijoins and Outer Joins
Using Group By and Having Clauses
Using Rollup and Cube Operations, and Grouping Sets
Using Set Operators

Subqueries
Fundamentals of Subqueries
Single-Row Subqueries
Multiple-Row Subqueries
Correlated Subqueries

Ensuring Quality Queries Part I
Ensuring Quality Query Results

Data Manipulation Language - DML
INSERT Statements
Updating Column Values and Deleting Rows
DEFAULT Values, MERGE, and Multi-Table Inserts

Data Definition Language - DDL
Creating Tables
Using Data Types
Modifying a Table

Constraints
Intro to Constraints; NOT NULL and UNIQUE Constraints
PRIMARY KEY, FOREIGN KEY, and CHECK Constraints
Managing Constraints

Views
Creating Views
DML Operations and Views
Managing Views

Sequences and Synonyms
Working With Sequences
Indexes and Synonyms

Privileges and Regular Expressions
Controlling User Access
Creating and Revoking Object Privileges
Regular Expressions

Transaction Control Language - TCL
Database Transactions

Ensuring Quality Queries Part II
Ensuring Quality Query Results - Advanced Techniques

Introduction to PL/SQL Programming
PL/SQL Blocks       
Creating an Anonymous Block      
Using Triggers         
Creating a DML Trigger     
Using Procedures and Functions  
Creating a Procedure         
Creating a Function
Handling Exceptions
Creating  Exceptions          
PL/SQL packages  
Creating a Package

     

NOTE: THIS COURSE IS ALSO AVAILABLE IN SELF-STUDY E-LEARNING, TRAINING GUIDE OR DISTANCE LEARNING FORMATS. FEEL FREE TO ASK IF YOU PREFER DISTANCE, E-LEARNING OR TRAINING GUIDE LEARNING  FORMATS. PRICES DIFFER

What is Computer Based Training - CBT?

Computer Based Training -  CBT's are usually interactive with Software Simulations (behaves like you're using the real thing on the PC, therefore you don't even need the real software installed on your machine) and sometimes BUT NOT ALWAYS with voice. This Publisher's CBT Titles do NOT have voice..

Generally, CBT's tend to be more expensive. Our CBT Titles are NOT based on Flash. They are based on the latest technology which uses the: See It, Do It, and Try It learning paradigm. A very powerful new way of learning.

Today's competitive business environment and frequent software updates by software vendors demands rapid skill acquisition. WebLearning Publishing helps you keep up-to-date by delivering quality Interactive Self-Study in CBT format. These factors give CBT's a clear advantage over conventional, inconvenient and expensive Classroom and Hard-copy print training methods.

CBT's not only offers the advantage in terms of convenience and lower cost, but it also facilitates improved subject-matter comprehension.

FACT:

·        Material retention with CBT’S are 45% better than with conventional training methods

·        Your learning curve with CBT is 25%-55% less than conventional methods

·        The cost of CBT training is over 65% less than Classroom training

·        CBT’S are self pacing, interactive and simulate the actual software being studied