Database Querying with Oracle SQL

An Overview of Oracle Relational Databases

• The Role of the Oracle Database Server
• Using a Client to interact with the Oracle Server
• Some Available Clients for Oracle
• Databases, Tables, Rows and Columns
• Primary Keys and Foreign Keys Explained
• Introducing Data Types: CHAR, VARCHAR, NUMBER and DATE

Introducing SQL for Oracle

• Creating and Edit SQL
• About Statements, Batches and Scripts
• Executing and Parsing SQL Scripts
• SQL Syntax and The Rules of SQL
• About Keywords, Identifiers. Operators, Whitespace and Case
• About the Semi Colon
• SQL Conventions and Good Practice

Retrieving Data with Oracle SQL: First Steps

• Introducing Queries: The SELECT Statement
• The Clauses of the SELECT Statement
• About Optional Clauses and Mandatory Clauses
• Using FROM to Specify the Source Table(s)
• Retrieving Entire Tables
• Retrieving Specific Columns
• The Importance of Clause Order
• How to Build Successful Queries
• Types of Output: About the Result Set
• Using ORDER BY to Sort the Output

Some Oracle Specific SQL

• Renaming Columns: Oracle and Column Aliases
• Performing Calculations: Oracle Operators
• Using Numeric and String Operators to Create Derived Output
• About Pseudocolumns and Dummy Tables
• Using the ROWNUM Pseudocolumn to Limit the Result Set
• The DUAL Dummy Table
• Establishing the Date with the SYSDATE Pseudocolumn

Using WHERE to Filter Results

• Working with Comparison Operators (=, >= etc)
• Numeric and String Based Filtering
• Filtering Based on Calculations
• Eliminating Duplicate Results with DISTINCT
• Working with Execution Order
• Column Aliases: Where You Can and Cannot Use Them
• Extending Filters with AND and OR
• Solving AND/OR Difficulties with Brackets
• Excluding Results with NOT: Some Tips
• Range Filtering using BETWEEN and IN
• NULL and its Implications Explained
• Catering for NULL
• Matching Patterns with LIKE

Getting Results From Multiple Tables

• Qualifying Column Names
• Joins Explained
• The Different Types of Joins
• Creating an Inner Join: WHERE Syntax
• Creating an Inner Join: INNER JOIN Syntax
• Table Aliases: The Need
• Working with Self Joins
• Outer Joins: An Example
• How to Simplify Joins: An Approach

Using Standard Oracle Functions

• How to Use Standard Oracle Functions to Modify Results
• How to Find the Right Function
• Mathematical, String and Conversion Functions
• TO_CHAR(), TO_DATE() and other Functions for working with Dates
• Using TO_CHAR() to Format Numbers to Two Decimal Places
• NVL(), NVL2() and COALESCE(): Replacing NULL with a Specific Value
• Using Standard Oracle Functions in WHERE
• Using CASE to Specify Output Conditions

Grouping, Summarizing and Ranking Results

• The difference Between Tabular and Scalar Results
• Using Aggregate Functions (MAX(), SUM(), AVG(), COUNT() etc)
• The Way Aggregate Functions Work
• Where to Use and Where Not to Use Aggregate Functions
• Using GROUP BY to Group Results
• The Need for HAVING: Filtering the Result Table
• Ranking Results with RANK(), PERCENT_RANK() and DENSE_RANK()

Working with Subqueries

• Subqueries Explained
• Where you can Use Subqueries
• How to Successfully Construct Subqueries
• Subqueries for Filtering
• Subqueries to Create Derived Columns

Working with Views

• Views Explained
• Advantages of Views
• How to use Views to Simplify your Work
• Creating Views
• Dropping Views

Oracle SQL and Regular Expressions

• An Introduction to Regular Expressions
• The benefits of Regular Expressions
• Regular Expression Syntax
• Replacing Values with REGEXP_REPLACE
• Taking Advantage of REGEXP_SUBSTR and REGEXP_INSTR
• Using REGEXP_LIKE to Select Data

On Site Requirements

Remember, we provide all equipment and software required to deliver a course at your premises. Aside from this, we need a suitably quiet and equipped room with enough work space for each attendee and a whiteboard or flipchart. Most courses involve the use of a PC projector and we bring our own. But either a projector screen, or usually just a clear wall, would be very helpful.

Other Courses to Consider

• OSQ-72: Introduction to SQL for Oracle
  Introduction to SQL for those who need to extract and update Oracle-based data and objects.
• OSQ-73: A Foundation in SQL for Oracle
  A 3-day foundation in SQL for those who need to both query and modify Oracle data and create Oracle databases and tables.

on site training courses available in:  

• London
• , Birmingham
• , Edinburgh
• , Manchester
• , Scotland
• , Glasgow
• , Nottingham
• , Midlands
• , Bristol
• , Wales
• , Cardiff
• , Dublin
• , Belfast
• , Leeds
• , Liverpool
• , Sheffield
• , Reading
• , Oxford
• , Cambridge
• , Southampton
• , Newcastle
• , Durham
• , Warrington.

and across the UK and Ireland