Virtual and Classroom Training - All our courses are available virtually. We have also started a safe return to classroom training. Click here to learn more.

banner image

A Foundation in SQL for Oracle

3 Day Course
course icon

Course Information

Overview

This is an introductory to intermediate level Oracle SQL training course for those who need to manipulate data by interacting with Oracle relational database servers using Structured Query Language (SQL) and who need to progress their SQL skills beyond the basics. It teaches core Oracle SQL and is aimed at those who need to extract data from, and insert data into, existing databases to an in-depth level. The course is highly practical in nature and the focus throughout is on coding SQL by hand. On completion, a comprehensive set of course notes, examples, tutor and attendee scripts are made available online for each attendee.

Prerequisites

No prior SQL, Oracle or relational database experience is assumed.

Course Outline

1. An Overview of Oracle Relational Databases
  • The Role of the Oracle Database Server
  • Using a Client to interact with the Oracle Server
  • SQL: Its Role and Purpose
  • Using an Integrated development Environment (IDE)
  • Databases, Schemas, Tables, Rows and Columns
2. Introducing SQL for Oracle
  • Creating and Editing SQL
  • About Statements, Batches and Scripts
  • Executing and Parsing SQL Scripts
  • Using Comments
  • SQL Syntax and The Rules of SQL
  • About Keywords, Identifiers. Operators, Whitespace and Case
  • About the Semi Colon
  • SQL Conventions and Good Practice
  • Working to Minimise, Trap and Find Errors
3. 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
4. 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
  • Ways of Limiting the Output
5. 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
6. Joining: Getting Results from Multiple Tables
  • Qualifying Column Names
  • Joins Explained
  • The Different Types of Joins
  • Mastering the Inner Join: WHERE Syntax
  • Mastering the Inner Join: INNER JOIN Syntax
  • Table Aliases: Why We Need Them
  • Joining Two Tables
  • Joining Multiple tables
  • How to Simplify Joins: An Approach
7. Getting Confident with SQL Joins
  • The Importance of the Database Diagram
  • What if there is No Database Diagram?
  • Primary Keys and Foreign Keys Explained
  • Locating Primary Keys and Foreign Keys
  • Do we Need Primary Keys and Foreign Keys?
  • Approaching Joins the Right Way
  • Avoiding Common Join Errors
8. 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
9. Working with Dates
  • Understanding How Dates are Stored
  • Introducing Date Functions
  • Converting Text Dates To Date Format
  • Establishing Today's Date
  • Extracting Parts of a Date (Day, Month, Year)
  • Displaying Dates in Specific Date Formats
  • Filtering with Dates
10. Grouping and Summarizing 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
11. Working with Subqueries
  • Using Subqueries
  • Replacing Scalar Values with a Subquery
  • Using Subqueries in IN and NOT IN
  • Using Subqueries to create tables 'On the Fly'
  • Ensuring Subqueries Work Every Time
12. More SQL Joins
  • Using Self Joins
  • Outer Joins: Full, Left and Right
13. 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

Private Courses

We can arrange your own private SQL course.

Tailored

Have us build a custom private course tailored to your needs.

Cost Effective

If you are looking to training a group of people private courses can be very cost effective.

Post Course Support

Unlimited post course email support on the course topics.

What Our Clients Think

Really enjoyed this course. It delivered exactly what I wanted out it.

Malkit Singh - Regatta Ltd

Very informative and was at a good pace. Lots of extra useful tips too.

Harry Finch - Lancashire Insurance

This course was great, the trainer was very knowledgeable and has improved my knowledge considerably. The facilities are fantastic. Thank You!

Kurtis Fleming - HM Revenue & Customs

Really good course. lots of hands on practice and clear explanations.

fiona dawe - Covetrus Global Software Services

Very informative course, very useful tips on how to work with SQL Management Studio. A lot of SQL quirks explained, a lot of good examples.

Magdalena Piekarska - Veolia

Course is well structured and instructor delivered the content, and more, perfectly. No question left unanswered, no theory unexplained.

Aidan Hamill - Intact Software

Related Courses

Public Course Dates Available!

This comprehensive SQL course will teach you the all the basics on extracting data from an SQL database as well as adding data, removing data and creating new databases.

Introductory SQL training for those who need to extract data from and update data in PostgreSQL databases.

Introductory SQL training for those who need to extract data from and update data in MySQL databases.

Introductory SQL training for those who need to extract data from, and update data in, Oracle databases

Introductory T-SQL training for developers or users who need to both extract and modify data stored in SQL Server databases.

Students will learn what SQL is and how it can help them query a database to answer business questions.

SQL training for those who need to both query and modify PostgreSQL data and create PostgreSQL databases and tables.

SQL training for MySQL developers or users who need to both query and modify MySQL data and create MySQL databases and tables.

Introductory to intermediate level Oracle SQL training for developers or users who need to query and modify data stored in Oracle database servers.

Introductory to intermediate level SQL Server-specific Transact-SQL (T-SQL) training for developers or users who need to query and modify data stored in SQL Server.

Students will learn advanced queries and how to add, update, and delete data, tables, views, and indexes.

Introduction to SQL training for developers or users who need only to extract (not modify) data from PostgreSQL databases.

Introduction to SQL training for developers or users who need only to extract (not modify) data from MySQL databases.

Introduction to SQL training for developers or users who need only to extract (not modify) data from Oracle databases.

Introductory T-SQL training for developers or users who only need to extract (not modify) data stored in SQL Server databases.

Practical skills booster Oracle SQL training for existing Oracle SQL developers or users who wish to take their existing Oracle SQL skills to the next level.

Practical skills booster Transact SQL (T-SQL) training for existing SQL Server T-SQL developers or users who wish to take their existing T-SQL skills to the next level.

PL/SQL training to develop solutions with Oracle's programming objects and procedural extensions.

Procedural programming techniques for SQL Server developers. Conditional Processing, Stored Procedures, User defined Functions, DML Triggers and Error Handling techniques are covered in a highly practical and modular environment.

Practical skills booster ANSI SQL training for existing SQL developers or users who are currently working with data stored in database servers like SQL Server, Oracle, IBM DB2, PostgreSQL and MySQL and who wish to take their existing SQL skills to the next level.

Introductory to intermediate level ANSI SQL training for developers or users who need to query and modify data stored in database servers like SQL Server, Oracle, IBM DB2, PostgreSQL and MySQL.

Introductory ANSI SQL training for developers or users who only need to extract (not modify) data stored in database servers like: SQL Server, Oracle and MySQL.

Practical Introductory ANSI SQL training for developers or users who need to query and modify data stored in database servers like SQL Server, Oracle and MySQL.