Oracle Database Design and Development

Duration: 4 Days

How can I attend my course?

On-line from
your chosen location

At our dedicated
training facility

On-site at
your premises

Course Content

Basic Oracle Database Concepts

Database and relational database concepts
• Introduction to Entity Relationship Diagrams
• Defining a Relational Database         
• What is Structured Query Language (SQL)?
• How information is stored in an Oracle Database (fields, rows, columns)
• Concept of ‘Schema’ in Oracle Database

Using the PL/SQL Developer GUI Tool

Introducing the SELECT Statement

• Capabilities of SELECT Statement
• Basic SELECT statements
• SELECT all columns
• Column Aliases
• Retrieving Distinct Values
• Introduction to SQL Operators

Restricting and Sorting Data

• WHERE clause
• Comparison operators and logical operators
• Rules of precedence for comparison and logical operators
• Character string literals
• Date queries
• ORDER BY clause
• SQL Row Limiting Clause
• Substitution Variables

Single-Row Functions – Part 1

• Single row and multiple row functions
• Using functions in the SELECT and WHERE clauses
• Manipulate strings with character functions
• Manipulate numbers with the numeric functions
• Manipulate dates with the date functions

Single-Row Functions – Part 2

• Describe implicit and explicit data type conversion
• Conversion functions
• Nesting functions
• Using CASE expressions

Display Data from Multiple Tables Using Joins

• Overview of joins
• Inner Joins
• Natural Joins
• USING clause
• ON clause
• Outer Joins
• Non-equi Joins
• Cross Joins
• Join a table to itself by using a self-join
• Joining multiple tables

Aggregation of Data using Group Functions

• Aggregation functions
• Other Group Functions
• GROUP BY clause
• HAVING clause

Using Sub-queries

• Overview of sub-queries
• Define sub-queries
• Common types of sub-queries
• Single-row sub-queries
• Multiple-row sub-queries
• Multiple-column sub-queries
• In-line Views
• Correlated Sub-queries
• Use the EXISTS and NOT EXISTS operators
• Scalar sub queries
• WITH clause

The SET Operators

• Describe the SET operators – UNION, UNION ALL, INTERSECT, MINUS
• Use a SET operator to combine multiple queries into a single query
• Control the order of rows returned

Introducing Analytical Functions

• What are Analytic Functions?
• General Syntax of the Analytic Clause
• ORDER BY Clause
• Combining the PARTITION BY and ORDER BY Example
• Examples of Analytical Functions

DML Statements

• Describe each DML statement
• Insert a single row into a table
• Insert a single row with NULLs into a table
• Insert a single row using a script file
• Inserting multiple rows
• Change rows in a table by using the UPDATE statement
• Change rows in a table by using a sub-query
• Removing rows from a table
• Removing rows from a table using a sub-query
• Transaction Control – COMMIT, ROLLBACK, SAVEPOINT

Manipulating Large Datasets

• Describe the features of multi-table INSERTs
• Unconditional INSERTs
• Pivoting INSERTs
• Conditional ALL INSERTs
• Conditional FIRST INSERTs
• Using the MERGE statement
• Using the MERGE extensions
• Track the changes to data over a period of time

Use of DDL Statements to Create and Manage Tables – Part 1

• Overview of DDL Statements and Schemas
• Overview of Oracle Tables
• Understanding Constraints
• NOT NULL Constraint
• UNIQUE Constraint
• PRIMARY KEY Constraint
• FOREIGN KEY Constraint
• CHECK Constraint
• A complete example of Constraints

Use of DDL Statements to Create and Manage Tables – Part 2

• Create External Tables
• Create Temporary Tables
• ALTER table
• TRUNCATE table
• DROP table
• Invisible columns
• Virtual Columns
• Identity Columns

Other Schema Objects

• Creating and using Views and materialized views
• Creating and using Sequences
• Creating and using Synonyms
• Introduction to Indexes and their use
• Writing efficient code

Explaining the data dictionary

• What type of object metadata is stored?
• Find table information
• Report on column information

Users, roles and privileges

• Creating Users
• Distinguish between system and object privileges
• Grant system privileges
• Grant object privileges on tables
• Grant privileges on tables
• Revoking Privileges
• View privileges in the data dictionary
• Distinguish between Privileges and Roles
• Using ROLEs

You will receive a full set of course notes
and all supporting materials for your course.

Hard Copy Delivered to your premises or Downloaded to a chosen device.

To book this course please call 
+44 (0) 1444 410296 or email