A Foundation in SQL for Oracle (3 days)
OSQ-73: Fixed price Oracle SQL training delivered on your site at any UK location.
COURSE OUTLINE |
pricing | more Oracle SQL training |
This is a course for Oracle database developers or users who need to manipulate data by interacting with Oracle relational databases using Structured Query Language (SQL) and who need to progress their SQL skills beyond the basics. It is aimed at those who need to extract data from, and insert data into, existing Oracle databases to an in-depth level and who also need to create and modify Oracle databases and tables. The course is highly practical in nature and the focus throughout is on coding Oracle-based SQL by hand. On completion, a comprehensive set of course notes, examples, tutor and attendee scripts are provided on a free USB pen drive to take away.
Suggested Prerequisites
No prior SQL, Oracle or relational database experience is assumed.
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 Editing 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
Inserting, Updating and Deleting Data
- Inserting Single Rows
- Inserting Multiple Rows
- Inserting Rows by Column Position
- Inserting Rows by Column Name
- Dealing with Auto-Incrementing Values
- Dealing with Nulls when Inserting
- Inserting Data from one Table into Another
- Updating Data
- Deleting Data
- Modifying Data through a View
Inserting, Updating and Deleting in a Transaction Environment
- Transactions Explained
- Why Use Transactions?
- Protecting Yourself with Transactions
- How to Setup a Transaction Environment
- Checking Your Work
- Undoing your Changes with ROLLBACK
- Committing the Transaction
Creating and Modifying Tables
- Using CREATE TABLE
- Specifying Primary and Foreign Keys
- Using DEFAULT values
- Constraining Input
- Using Temporary Tables
- Creating a New Table From an Existing Table
- Altering and Dropping Tables
"Liked the concept of different people being able to go at their own rate."
Systems Engineer
Schlumberger