Oracle SQL & PL/SQL Fundamentals
- Course Code OFUN
- Duration 5 days
Course Delivery
Additional Payment Options
-
GTC 34 inc. VAT
GTC, Global Knowledge Training Credit, please contact Global Knowledge for more details
Jump to:
Course Delivery
This course is available in the following formats:
-
Company Event
Event at company
-
Public Classroom
Traditional Classroom Learning
-
Virtual Learning
Learning that is virtual
Request this course in a different delivery format.
Course Overview
TopOracle SQL & PL/SQL Fundamentals Course Overview
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.
Course Schedule
Top-
- Delivery Format: Virtual Learning
- Date: 08-12 April, 2024
- Location: Virtual
-
- Delivery Format: Virtual Learning
- Date: 13-17 May, 2024
- Location: Virtual
-
- Delivery Format: Virtual Learning
- Date: 10-14 June, 2024
- Location: Virtual
-
- Delivery Format: Virtual Learning
- Date: 08-12 July, 2024
- Location: Virtual
-
- Delivery Format: Virtual Learning
- Date: 05-09 August, 2024
- Location: Virtual
-
- Delivery Format: Virtual Learning
- Date: 02-06 September, 2024
- Location: Virtual
Target Audience
TopWho 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 Objectives
TopCourse Objectives
To 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.
Course Content
TopOracle SQL & PL/SQL Fundamentals Training Course
Course Contents - DAY 1
Course Introduction
- Administration and Course Materials
- Course Structure and Agenda
- Delegate and Trainer Introductions
Session 1: RELATIONAL DATABASE CONCEPTS
- What is an Oracle Database
- Relational Database Structures
- Tables,Rows and Columns
- Indexes,Primary Keys and Foreign Keys
- Data Types
- The Data Dictionary
Session 2: USING SQL*PLUS
- What is SQL*Plus
- Getting Started
- Entering and Executing SQL Statements
- Editing SQL Statements
- Creating,Editing and Executing SQL Files
Session 3: USING SQL DEVELOPER
- What is Oracle SQL Developer
- Starting SQL Developer
- Configure a Connection
- Navigation Tabs
- SQL Worksheet
Session 4: RETRIEVING DATA WITH THE SELECT STATEMENT
- 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
Session 5: AGGREGATE FUNCTIONS
- Overview of Built In Aggregate Functions
- The GROUP BY Clause
- The HAVING Clause
Oracle SQL & PL/SQL Fundamentals Training Course
Course Contents - DAY 2
Session 6: JOINING TABLES
- Overview of Table Joins
- Inner Joins
- Table Aliases
- Outer Joins
- Self Joins
- ANSI Standard Joins
Session 7: NUMERIC,CHARACTER AND DATE FUNCTIONS
- Function Types
- Using the Table dual to try out Functions
- Numeric Functions
- Character Functions
- String Concatenation
- Date Arithmetic and Date Functions
Session 8: CONVERSION AND MISCELLANEOUS FUNCTIONS
- Conversion Functions
- The NVL and NVL2 Functions
- The DECODE Function
- CASE Expressions
- The COALESCE and NULLIF Functions
Session 9: SQL*PLUS PARAMETERS
- Command Line Substitution Parameters
- The Accept Command
- The Define and Undefine Commands
Session 10: USING SUBQUERIES
- 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
Oracle SQL & PL/SQL Fundamentals Training Course
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
Session 12: MANAGING TABLES
- Creating Tables
- Specifying Constraints
- Altering Tables,Columns and Constraints
- Dropping Tables,Columns and Constraints
- Copying Tables
Session 13: MANAGING INDEXES AND VIEWS
- Creating Indexes
- Dropping Indexes
- Listing Indexes
- Creating and Using Views
- Dropping Views
- Listing Views
Session 14: MANAGING SEQUENCES AND SYNONYMS
- Create a Sequence
- View Sequence Details
- Create a Synonym
- List Synonyms
Oracle SQL & PL/SQL Fundamentals Training Course
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
Session 16: PROGRAM LOGIC
- 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
Session 17: USING CURSORS
- 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
Oracle SQL & PL/SQL Fundamentals Training Course
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
Session 19: PL/SQL RECORDS AND INDEX-BY TABLES
- 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
Course Prerequisites
TopRequirements
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.
Follow on Courses
TopFurther Learning
- Oracle PL/SQL Stored Program Units
- Oracle Database 19c Administration
- STA_OFUN
- Oracle SQL & PL/SQL Fundamentals
- Database Management & Development
- STA_OFUN | Oracle SQL & PL/SQL Fundamentals | Training Course | Oracle.
- Oracle