Course description
The Oracle SQL & PL/SQL Fundamentals course is designed to give delegates practical experience in writing SQL statements and scripts using Oracle SQL. The basic SELECT statement, the use of SQL functions, SQL*Plus parameters and the basic table and view handling statements are introduced.
The course also provides practical experience in using Oracle's PL/SQL programming language to implement conditional execution, loop control, cursor handling and exception handling.
Exercises and examples are used throughout the course to give practical hands-on experience with the techniques covered.
Who will the Course Benefit?Anyone who needs to use and understand Oracle SQL to query and update an Oracle database; and who also needs an introduction to the PL/SQL programming language.
Course ObjectivesTo provide the skills needed to query and update data held in an Oracle Relational Database.
To provide an introduction to Oracle PL/SQL as a preparation for other Oracle products such as Forms and Reports.
Upcoming start dates
Suitability - Who should attend?
There are no formal pre-requisites for the Oracle SQL & PL/SQL Fundamentals course, although an understanding of databases and exposure to information technology in general would be useful. This knowledge can be gained by attendance on the Relational Databases & Data Modelling Overview course.
Training Course Content
Oracle SQL & PL/SQL Fundamentals Training Course
Course Contents - DAY 1
Course Introduction- Administration and Course Materials
- Course Structure and Agenda
- Delegate and Trainer Introductions
- What is an Oracle Database
- Relational Database Structures
- Tables, Rows and Columns
- Indexes, Primary Keys and Foreign Keys
- Data Types
- The Data Dictionary
- What is SQL*Plus
- Getting Started
- Entering and Executing SQL Statements
- Editing SQL Statements
- Creating, Editing and Executing SQL Files
- What is Oracle SQL Developer
- Starting SQL Developer
- Configure a Connection
- Navigation Tabs
- SQL Worksheet
- The SELECT Statement
- The SELECT and FROM Clauses
- Conditions and the WHERE Clause
- Other Conditional Operators
- Logical Operators
- The ORDER BY Clause
- Column Aliases
- Arithmetic Expressions
- Precedence of Operators
- Overview of Built In Aggregate Functions
- The GROUP BY Clause
- The HAVING Clause
Course Contents - DAY 2
Session 6: JOINING TABLES- Overview of Table Joins
- Inner Joins
- Table Aliases
- Outer Joins
- Self Joins
- ANSI Standard Joins
- Function Types
- Using the Table dual to try out Functions
- Numeric Functions
- Character Functions
- String Concatenation
- Date Arithmetic and Date Functions
- Conversion Functions
- The NVL and NVL2 Functions
- The DECODE Function
- CASE Expressions
- The COALESCE and NULLIF Functions
- Command Line Substitution Parameters
- The Accept Command
- The Define and Undefine Commands
- Overview of Subqueries
- Use a Subquery as an Alternative to Join
- Handle Multiple Records in Subqueries
- Subquery in a Having Clause
- Anti-Join
- In-Line Views
- Top-N Queries
- Complex Subqueries
- Multi Column Subqueries
- Correlated Subqueries
- Subquery Rules
- Combining Unrelated Aggregates
- Using the ANY, ALL and SOME Operators
Course Contents - DAY 3
Session 11: MANAGING DATA- Inserting Rows
- Updating Rows
- Deleting Rows
- Verifying Updates
- Transaction Control
- Commit and Rollback
- Savepoints
- Commits and Constraints
- Amending Data in SQL Developer
- Creating Tables
- Specifying Constraints
- Altering Tables, Columns and Constraints
- Dropping Tables, Columns and Constraints
- Copying Tables
- Creating Indexes
- Dropping Indexes
- Listing Indexes
- Creating and Using Views
- Dropping Views
- Listing Views
- Create a Sequence
- View Sequence Details
- Create a Synonym
- List Synonyms
Course Contents - DAY 4
Session 15: PL/SQL FUNDAMENTALS- What is PL/SQL?
- Basic Elements
- Variables and Constants
- Data Types
- Initialising Variables and Assigning Values
- Using SQL Statements in Code
- Generating Output to SQL or SQL Developer
- IF THEN ELSIF ELSE Statements
- CASE Statements
- The Basic Loop Construct
- WHILE and FOR Loops
- Nested and Labelled Loops
- The GOTO Statement
- The CONTINUE Statement
- What is a Cursor?
- Implicit and Explicit Cursors
- Cursor Operations
- Declaring, Opening and Closing Cursors
- Fetching Rows
- Status Checking
- Using Cursors FOR UPDATE
- The Cursor FOR Loop
- Parameterised Cursors
Course Contents - DAY 5
Session 18: EXCEPTIONS AND NESTED BLOCKS- The EXCEPTION Section
- Types of Exception
- Handling Named System-Raised Exceptions
- Handling Un-named System-Raised Exceptions
- User-Declared Exceptions and Application Errors
- WHEN OTHERS THEN NULL
- Nested and Labelled Blocks
- Propagation of Exceptions
- Scope of Variables and Cursors
- Scope of Goto Statements
- Declaring Record Types
- Handling PL/SQL Records
- Nested Records
- Declaring PL/SQL Index-By Tables or Associative Arrays
- PL/SQL Table Built-in Functions
- Manipulating PL/SQL Tables or Associative Arrays
Customer Outreach Award
We believe that it should be easy for you to find and compare training courses.
Our Customer Outreach Award is presented to trusted providers who are excellent at responding to enquiries, making your search quicker, more efficient and easier, too.
Reviews
Average rating 4.9
Training was very well delivered. Facilitator has an excellent understanding of the course. He made the training easy to understand and grasp. My experience with Tarn was fan...
Training was very impactful. Facilitator is very knowledgeable on the product. He thought the concepts very well. His teaching method is also fun.
Continuing Studies
Further Learning
- Oracle PL/SQL Stored Program Units
- Oracle Database 19c Administration
Request info
Why StayAhead. From a single person on a scheduled course to large scale training projects StayAhead Training have the expertise and experience to help. Established since 1992, StayAhead Training is recognised as one of the leading independent IT Training specialists...
I've enjoyed my time on the course. My lecturer was able to answer all the questions I asked.