COURSE OBJECTIVE:
Course 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.
TARGET AUDIENCE:
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 PREREQUISITES:
Requirements 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.
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 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
FOLLOW ON COURSES:
Further Learning Oracle PL/SQL Stored Program Units Oracle Database 19c Administration