Audience
This course is perfect for those who wish to gain a comprehensive understanding of how the Oracle Structured Query Language (SQL) is used for data retrieval and reporting.
This course is suitable for all recent versions of Oracle including 12c, 18c, and 19c.
Prerequisites
Delegates should be computer literate with a good knowledge of the standard editor available on their target hardware platform.
Duration
3 days. Hands on.
NOTE: This course may be combined with any of the following specialisations: Oracle SQL Tuning, Oracle SQL Extended or Oracle SQL Analytics and run over a total of four days, please contact us for more information.
Course Objectives
This course introduces delegates to the concepts of relational databases and provides an in-depth look at SQL in order to retrieve and manipulate data from an Oracle database.
This course provides the perfect prerequisite knowledge to embark on training for Oracle PL/SQL Programming, developer components such as Application Express or administrative roles such as Oracle Database Administration.
On completion of the course, delegates will be able to:
- Utilise the SQL language to retrieve data from an Oracle database
- Use the SQL Developer tool
- Edit, store and recall queries, format results, and create standard queries and reports
Course Content
Introduction to RDBMS and Oracle
Relational database theory
Overview of oracle
ISO (incorporating ANSI and BSI) standards
Oracle architecture
Oracle background processes
SQL Developer
Basic SQL
Filtering by rows and columns
Filtering for distinct or unique values
Use of the Between, Like & In operators
Handling Null values
Filtering with a compound Where clause
Sorting results
Perform a calculation
Use of column alias
Deriving values
Data types
Substitution variables
Joins
Inner joins (Pre and Post SQL99)
Outer joins (Pre and Post SQL99)
Combining inner and outer joins
Functions
Conversion functions
Date functions
Character functions
Numeric functions
Aggregate Summary functions
Group By clause
Having.clause
Sub-Queries
Nested sub-queries
Multi-column sub-queries
Correlated sub-queries
Exists clause
Inline views
Case Statements
Case statement examples
Decode vs. Case
Set Operators
Union
Intersect
Minus
Introduction to Data Management
Insert records
Insert via a Select clause
Multi-table inserts
Update records
Delete records
Transactions
Locking
Use of Oracle Data Dictionary
Introduction To Analytic Functions
Cube, rollup & grouping
Partitioned outer join
Analytic Summary Functions
Hierarchical queries
Introduction To Basic SQL Tuning
Overview Of Explain Plan Facility
Overview of Indexes