Oracle - SQL & PL/SQL Course

Oracle

Course Details:

Length: 5 days

Price: $2,910/person (USD)

Bundle & Save: View Bundle

Group Price: Request Quote

Training Reviews

Course Features:

Live Instructor Teaching

Certificate of Completion

Digital Badge: Yes

Courseware: Print

Free 6 Month Online Retake

Hands-On Learning: Yes

Software Lab Included: Yes

Delivery Methods:

 Live Online

Individuals & Groups
@ Your Location


  Onsite for Teams

Groups & Teams
@ Your Organization

This is an instructor-led course. It is taught live online by an instructor for individuals or at organizations for groups.
For a private team training, we can teach onsite at your office or private live online.

 

Course Overview

Unlock the Power of Oracle Databases

This comprehensive course equips you with the in-demand skills to master Oracle SQL and PL/SQL, transforming you into a proficient database professional. Whether you're a beginner or looking to enhance your existing SQL knowledge, this program provides a structured learning path to:

  • Build a Strong Foundation: Learn core SQL concepts, including data retrieval, manipulation, and advanced querying techniques.
  • Develop Robust Database Solutions: Design and implement efficient database structures, manage data security, and optimize database performance.
  • Master PL/SQL Programming: Leverage the power of PL/SQL to create stored procedures, functions, and triggers for automating tasks and improving data integrity.
  • Gain In-Demand Skills: Acquire highly sought-after skills by industry leaders, making you a valuable asset in today's competitive job market.
  • Prepare for Career Advancement: Boost your resume and increase your earning potential with a deep understanding of Oracle databases.
This course covers:
  • Core SQL Fundamentals: Data retrieval, data manipulation, joins, subqueries, aggregate functions, and more.
  • Database Design and Implementation: Database normalization, creating tables, indexes, and views.
  • Data Security and Management: User management, role-based access control, and database security best practices.
  • PL/SQL Programming: Stored procedures, functions, triggers, exception handling, and transaction management.
  • Cloud Database Integration: Learn to work with Oracle databases in the cloud.

Invest in your future with this comprehensive Oracle SQL & PL/SQL training. Enroll today and unlock your potential!

 


Course Notes

Register Early: Registration Deadline is 2 Weeks Prior to Class Start.

Important Course Information!
Course Taught with Courseware: Oracle 21c for Windows () Courseware.
Course Taught with Software: Oracle 21c for Windows () Software.
Versions that Can Attend: Oracle 23c, 21c, 19c, 18c & 12c for Windows () or Macintosh - iOS () Versions.
Prerequisites & Follow-Ons
Knowledge Prerequisites:

This course is open to all skill levels, from beginners with no coding experience to experienced programmers. Familiarity with basic computer use, typing, and keyboard navigation is assumed. While no prior database or programming knowledge is required, some concepts may be easier to grasp with such experience.


Suggested Follow-Ons:
Objectives
Target Audience
Exams & Certifications

Delivery Methods

For Individuals

Learn From an Instructor Live Online

Certstaffix Training public classes are taught by live instructors to you in the comfort of your home or work. Attend our live online, instructor-led classes from the convenience of your location and avoid travel.

How It Works

  • A live instructor teaching you online via Zoom
  • Hands-on learning with the software or skill you are being taught
  • Interaction with all students in the class
  • Any needed software for the class is provided in online lab
  • Easy Q&A. Classes are kept small to have time for student questions

Have more than 10 students needing this course? Contact Us for bulk pricing.

For Groups/Teams

Bundle & Save

Save up to $515 with our Oracle Bundle.

 


Course Topics

Oracle - SQL & PL/SQL


Section 1: AN INTRODUCTION TO SQL

Chapter 1: An introduction to relational databases and SQL
  • An introduction to client/server systems
    • The hardware components of a client/server system
    • The software components of a client/server system
    • Other client/server architectures
  • An introduction to the relational database model
    • How a database table is organized
    • How tables are related
    • How columns in a table are defined
  • An introduction to SQL and SQL-based systems
    • A brief history of SQL
    • A comparison of four relational databases
  • SQL statements and comments
    • An introduction to SQL statements
    • Typical SQL statements
    • SQL coding guidelines
    • How to code comments
Chapter 2: How to use SQL Developer
  • How to use SQL Developer to work with a database
    • How to create a database connection
    • How to navigate through the database objects
    • How to view the column definitions and data for a table
  • How to use SQL Developer to run SQL statements
    • How to enter and run a SQL statement
    • How to handle syntax errors
    • How to open and save SQL scripts
    • How to code and run SQL scripts
  • How to use the Oracle Database documentation

Section 2: THE ESSENTIAL SQL SKILLS

Chapter 3: How to retrieve data from a single table
  • An introduction to the SELECT statement
    • How to interpret syntax in this book
    • The basic syntax of the SELECT statement
    • SELECT statement examples
  • How to code the SELECT clause
    • How to code column specifications
    • How to name the columns in a result set
    • How to code string expressions
    • How to code arithmetic expressions
    • How to use scalar functions
    • How to use DISTINCT to eliminate duplicate rows
  • How to code the WHERE clause
    • How to use the comparison operators
    • How to use the AND, OR, and NOT logical operators
    • How to use the IN phrase
    • How to use the BETWEEN phrase
    • How to use the LIKE operator
    • How to use the REGEXP_LIKE function
    • How to use the IS NULL condition
  • How to code the ORDER BY clause
    • How to sort by a column name
    • How to sort by an alias, an expression, or a column number
  • Two more skills
    • How to use the row limiting clause
    • How to test expressions
Chapter 4: How to retrieve data from two or more tables
  • How to work with inner joins
    • How to code an inner join
    • When and how to use table aliases
    • How to use compound join conditions
    • How to use a self-join
    • How to join more than two tables
    • How to use the implicit inner join syntax
  • How to work with outer joins
    • How to code an outer join
    • Outer join examples
    • Outer joins that join more than two tables
    • How to use the implicit outer join syntax
  • Other skills for working with joins
    • How to combine inner and outer joins
    • How to join tables with the USING keyword
    • How to join tables with the NATURAL keyword
    • How to use cross joins
  • How to work with unions
    • The syntax of a union
    • Unions that combine data from different tables
    • Unions that combine data from the same table
    • How to use the MINUS and INTERSECT operators
Chapter 5: How to code summary queries
  • How to work with aggregate functions
    • How to code aggregate functions
    • Queries that use aggregate functions
  • How to group and summarize data
    • How to code the GROUP BY and HAVING clauses
    • Queries that use the GROUP BY and HAVING clauses
    • How the HAVING clause compares to the WHERE clause
    • How to code complex search conditions
  • How to summarize data using the ROLLUP CUBE clause
    • How to use ROLLUP
    • How to use CUBE
  • How to code analytic functions
    • How analytic functions work
    • How to code frames
    • Two more examples of frames
    • How to use named windows
    • How to use the ranking functions
Chapter 6: How to code subqueries
  • An introduction to subqueries
    • How to use subqueries
    • How subqueries compare to joins
  • How to code subqueries in search conditions
    • How to use subqueries with the IN operator
    • How to compare a subquery with an expression
    • How to use the ALL keyword
    • How to use the ANY and SOME keywords
    • How to code correlated subqueries
    • How to use the EXISTS operator
  • Other ways to use subqueries
    • How to code subqueries in the FROM clause
    • How to code subqueries in the SELECT clause
  • Guidelines for working with complex queries
    • A complex query that uses subqueries
    • A procedure for building complex queries
  • How to use subquery factoring
    • How to use the WITH clause
    • How to code a recursive query
    • How to use the hierarchical query clause
Chapter 7: How to insert, update, and delete data
  • How to create test tables
    • How to re-create the tables for this book
    • How to create a table from a SELECT statement
  • How to insert new rows
    • How to insert a single row
    • How to insert default values and null values
    • How to use a subquery to insert multiple rows
  • How to update existing rows
    • How to update rows
    • How to use a subquery in an UPDATE statement
  • How to delete existing rows
    • How to delete rows
    • How to use a subquery in a DELETE statement
  • How to commit and roll back changes
Chapter 8: How to work with data types and functions
  • Data type overview
  • How to work with character data
    • The character data types
    • How to use character functions
    • How to parse a string
  • How to work with numeric data
    • The numeric data types
    • Common number format elements
    • How to use numeric functions
    • How to search for floating-point numbers
  • How to work with temporal data
    • The temporal data types
    • Common datetime format elements
    • How to use datetime functions
    • How to perform a date search
    • Common timestamp and interval formats
    • How to use timestamp functions
    • How to use interval functions
    • How to use the EXTRACT function
  • How to convert data from one type to another
    • How to convert characters, numbers, and dates
    • How to sort strings in numerical sequence
    • How to perform a time search
    • How to convert characters to and from their numeric codes
  • Other functions you should know about
    • How to use the CASE expression
    • How to use the COALESCE, NVL, and NVL2 functions
    • How to use the GROUPING function
Section 3: DATABASE DESIGN AND IMPLEMENTATION

Chapter 9: How to design a database
  • How to design a data structure
    • The basic steps for designing a data structure
    • How to identify the data elements
    • How to subdivide the data elements
    • How to identify the tables and assign columns
    • How to identify the primary and foreign keys
    • How to enforce the relationships between tables
    • How normalization works
    • How to identify the columns to be indexed
  • How to normalize a data structure
    • The seven normal forms
    • How to apply the first normal form
    • How to apply the second normal form
    • How to apply the third normal form
    • When and how to denormalize a data structure
Chapter 10: How to create a database
  • How to work with container databases
    • An introduction to CDBs and PDBs
    • How to create and drop a pluggable database
  • How to work with tables
    • How to create a table
    • How to code a primary key constraint
    • How to code a foreign key constraint
    • How to code a check constraint
    • How to alter the columns of a table
    • How to alter the constraints of a table
    • How to rename, truncate, and drop a table
  • How to work with indexes
    • How to create an index
    • How to drop an index
  • How to work with sequences
    • How to create a sequence
    • How to use a sequence
    • How to alter a sequence
    • How to drop a sequence
  • How to automatically generate ID values
  • A script that’s used to create a schema
    • An introduction to scripts
    • How the DDL statements work
  • How to use SQL Developer
    • How to work with tables, indexes, and sequences
    • How to display an EER diagram for a table
Chapter 11: How to create views
  • An introduction to views
    • How views work
    • Benefits of using views
  • How to work with views
    • How to create a view
    • How to create an updatable view
    • How to create a read-only view
    • How to use WITH CHECK OPTION
    • How to insert or delete rows through a view
    • How to alter or drop a view
  • How to use SQL Developer with views
Chapter 12: How to manage database security
  • How to work with users and roles
    • How to create a user
    • How to create an admin user
    • How to alter and drop a user
    • How to create and drop a role
  • How to work with privileges and synonyms
    • System privileges and object privileges
    • How to grant privileges
    • How to revoke privileges
    • How to work with synonyms
    • A script that creates roles and users
    • How to view the privileges for users and roles
  • How to use SQL Developer
    • How to create an admin connection for a PDB
    • How to work with users
    • How to grant and revoke roles
    • How to grant and revoke system privileges
Chapter 13: How to host a database in the cloud
  • How to get started with Oracle Cloud
    • The Oracle Cloud portal
    • How to create a database in the cloud
    • How to create a user for a schema
    • How to create the tables for a schema
    • How to view the database objects for a schema
  • How to use SQL Developer with a cloud database
    • How to connect to a cloud database
    • How to run SQL against a cloud database
  • More skills for working with a cloud database
    • How to restore and delete a cloud database
    • How to restart a cloud database

Section 4: THE ESSENTIAL PL/SQL SKILLS

Chapter 14: How to write PL/SQL code
  • An introduction to PL/SQL
    • An anonymous PL/SQL block in a script
    • Statements for working with PL/SQL and scripts
  • How to code the basic PL/SQL statements
    • How to print data to an output window
    • How to declare and use variables
    • How to code IF statements
    • How to code CASE statements
    • How to code loops
    • How to use a cursor
  • How to work with composite variables
    • How to use collections
    • How to use records
  • How to work with exceptions and errors
    • How to handle exceptions
    • Predefined exceptions
    • How to drop database objects without displaying errors
Chapter 15: How to manage transactions and locking
  • How to work with transactions
    • How to commit and roll back transactions
    • How to work with save points
  • How to work with concurrency and locking
    • How concurrency and locking are related
    • How to set the transaction isolation level
    • Best practices for concurrency
Chapter 16: How to create stored procedures and functions
  • How to code stored procedures
    • How to create a stored procedure
    • How to call a stored procedure
    • How to code input and output parameters
    • How to code optional parameters
    • How to raise a predefined exception
    • How to raise a user-defined exception
    • A stored procedure that inserts a row
    • How to drop a stored procedure
  • How to code functions
    • How to create and call a function
    • A function that uses multiple RETURN statements
    • How to drop a function
  • How to work with packages
    • How to create a package
    • How to drop a package
  • How to use SQL Developer
    • How to view and drop procedures, functions, and packages
    • How to edit and compile procedures and functions
    • How to grant and revoke privileges
    • How to debug procedures and functions
Chapter 17: How to create triggers
  • How to work with triggers
    • How to create a trigger for a table
    • A trigger that enforces data consistency
    • How to use conditional predicates
    • How to create a trigger for a view
    • How to create a system trigger
    • How to enable, disable, rename, or drop a trigger
  • Other skills for working with triggers
    • How to create a compound trigger
    • A trigger that causes the mutating-table error
    • How to solve the mutating-table problem
  • How to use SQL Developer
    • How to view, enable, disable, rename, or drop a trigger
    • How to edit a trigger

APPENDICES

Appendix A: How to set up Windows for this book
  • How to install Oracle Database XE
  • How to install Oracle SQL Developer
  • How to download the files for this book
  • How to connect as the sysdba user
  • How to create the schemas for this book
  • How to import connections for the schemas
  • How to make sure your system is set up correctly
  • How to stop and start the database service
Appendix B: How to set up macOS for this book
  • How to download the files for this book
  • How to install Oracle SQL Developer
  • How to set up the database for this book
  • How to create the connections for this book
  • How to make sure your system is set up correctly

 


Course FAQs

This course is taught by a live instructor and is available in two class formats:

  1. Live Online for Individuals
  2. Onsite/Online for Private Groups

 



 


Related Oracle Information:

Public instructor-led Oracle course prices start at $2,910 per student. Group training discounts are available.

Self-Paced Oracle eLearning courses cost $825 at the starting point per student. Group purchase discounts are available.







Registration:

Have a Group?
Request Private Training

5/19/2025 10:00:00 AM
Online Class

Registration Deadline - 05/04/2025

 

7/14/2025 10:00:00 AM
Online Class

Registration Deadline - 06/29/2025

 

9/8/2025 10:00:00 AM
Online Class

Registration Deadline - 08/24/2025

 

10/27/2025 10:00:00 AM
Online Class

Registration Deadline - 10/12/2025

 

12/8/2025 10:00:00 AM
Online Class

Registration Deadline - 11/23/2025

Start your training today!