• +27 (0)21 418 4874


2KO International offers Oracle courses in South Africa. 2KO’s Oracle courses in Cape Town are delivered by full time instructor-led training; as well as internet-based online learning, aimed at International Oracle Certification. 2KO delivers cutting-edge IT products in the consulting and business services space, as well as offering top-rated training for various IT vendors. Our internationally recognised courses can be offered either as hands-on full time classes, or as online learning, enabling 2KO students the opportunity to study courses online world-wide, to ensure students have access to the best training possible.

Duration
5 Days full time

Overview
This course offers students an introduction to Oracle Database 11g database technology. In this class, students learn the concepts of relational databases and the powerful SQL programming language. This course provides the essential SQL skills that allow developers to write queries against single and multiple tables, manipulate data in tables, create database objects, and query meta data. Demonstrations and hands-on practices reinforce the fundamental concepts.

Target Audience
Application Developers
Business Intelligence Developer
Database Administrators
Database Designers
End Users
Forms Developer
PL/SQL Developer
Portal Developer

Pre-requisites
Familiarity with Data Processing Concepts and Techniques
Ability to use a graphical user interface (GUI)

Writing SQL SELECT statements
List the Oracle Database 11g Main Features
An Overview of: components, internet platform, app server and developer suite
Describe Relational and Object Relational Database Designs
Review the System Development Life Cycle
Define the term Data Models
Describe different means of Sorting Data
Show how Multiple Tables can be related
Describe how SQL Communicates to the Database

Writing SQL SELECT statements
Define projection, selection, and join terminology
Review the basic SQL SELECT statement syntax
Select all columns using a wildcard notation from a table
State simple rules and guidelines for writing SQL statements
Write a query containing the arithmetic operators
Use aliases to customize column headings
Create a character expression with the concatenation operator
Use the quote operator q to control text strings containing the quote character

Using the iSQL*Plus Environment
SQL statements versus iSQL*Plus commands
Explaining the iSQL*Plus interface
Interacting with script files
Setting iSQL*Plus Preferences

Restricting Data
Limit Rows Using a Selection
Use the WHERE Clause
List the main Comparison Conditions
Use the LIKE Condition to Compare Literal Values
List the Logical Conditions AND, OR, NOT
Use Multiple Conditions in the WHERE clause
Describe the Rules of Precedence

Sorting Data
Sort Rows with the ORDER BY Clause
Use the && Substitution Variable
Use the iSQL*Plus DEFINE Command
Use the VERIFY Command

SQL Functions
Differentiate between Single Row and Multiple Row SQL Functions
Categorize the Character Functions into Case Manipulation and Character Manipulation types
Explain the Numeric Functions ROUND, TRUNC, and MOD
List the Rules for Applying the Arithmetic Operators on Dates
Use the Arithmetic Operators with Dates in the SELECT Clause
Explain the DATE functions MONTHS_BETWEEN, ADD_MONTHS, NEXT_DAY, LAST_DAY, ROUND, and TRUNC
Explain Implicit and Explicit conversion
Nest Functions to Perform Multiple Tasks in One Statement

Conditional Expressions
Use the CASE Expression
Explain the DECODE Expression

Aggregating Data Using Group Functions
Categorize the Types of Group Functions
Use the AVG, SUM, MAX, MIN, and COUNT Functions in a Query
Utilize the DISTINCT Keyword with the Group Functions
Describe how Nulls are handled with the Group Functions
Create Groups of Data with the GROUP BY Clause
Group Data by more than one column
Avoid Illegal Queries with the Group Functions
Exclude Groups of Data with the HAVING Clause

Displaying Data from Multiple Tables
Identify Types of Joins
Retrieve Records with Natural Joins
Use Table Aliases to write shorter code and explicitly identify columns from multiple tables
Create a Join with the USING clause to identify specific columns between tables
Use the ON clause to specify arbitrary conditions or specify columns to Join
Create a Three-way join with the ON clause to retrieve information from 3 tables
List the Types of Outer Joins LEFT, RIGHT, and FULL
Generating a Cartesian Product

Using Subqueries
Use a sub query to solve a problem
Identify where sub queries can be placed in a SELECT statement
Describe the types of sub queries (single row, multiple row)
Show the single row sub query operators
Use the group functions in a sub query
Identify illegal statements with sub queries
Show the multiple row sub query operators
Explain how null values are handled in sub queries

Using the Set Operators
Use the UNION operator to return all rows from multiple tables and eliminate any duplicate rows
Use the UNION ALL operator to return all rows from multiple table (with duplicates)
Describe the INTERSECT operator
Use the INTERSECT operator
Explain the MINUS operator
Use the MINUS operator
List the SET operator guidelines
Order results when using the UNION operator

Inserting and Updating Data
Write INSERT statements to add rows to a table
Insert Special Values
Copy Rows from Another Table
Update Rows in a Table

Deleting Data
Use DELETE statements to remove rows from a table
Delete Rows Based on Another Table
Describe the TRUNCATE Statement

Database Transactions
Save and Discard Changes to a Table through Transaction Processing (COMMIT, ROLLBACK, and SAVEPOINT)
Show how Read Consistency works

Using DDL Statements
List the main database objects
Identify the Naming Rules
Display the basic Syntax for Creating a Table
Show the DEFAULT option
List the Data Types that are available for Columns

Managing Tables
Explain the different types of constraints
Show resulting exceptions when constraints are violated with DML statements
Create a table with a sub query
Describe the ALTER TABLE functionality
Remove a table with the DROP statement

Creating Other Schema Objects
Categorize simple and complex views and compare them
Create a view
Retrieve data from a view
Explain a read-only view
List the rules for performing DML on complex views
Create a sequence
List the basic rules for when to create and not create an index
Create a synonym

Managing Objects with Data Dictionary Views
Describe the structure of each of the dictionary views
List the purpose of each of the dictionary views
Write queries that retrieve information from the dictionary views on the schema objects
Use the COMMENT command to document objects

Appendices
Oracle Join Syntax
Using SQL*Plus
Using SQL Developer
Additional Practices