Oracle SQL and PL/SQL

Skip to Available Dates


Course Details

Course Outline

1 - SQL Module 1
Database essentials: tables with their relationships, Primary Key, Foreign Key, normalisationSelecting data using SELECT (DISTINCT) and operators (||, +, -, *, /, %), from a table using FROMSorting data using ORDER BYFiltering data using WHERE (AND, OR) and comparison operators (=, <, >, <=, >=, <>, IS, NOT, LIKE (%, _), BETWEEN, IN, NOT IN ... AND ... IS NOT NULL)Aggregating and grouping data using aggregate functions (COUNT, SUM, AVG, MAX, MIN) and GROUP BY Filtering aggregated data and groups using HAVING
2 - SQL Module 2
  • Calculating data using functions for numbers, texts, dates, NULL and data type conversion
  • Joining tables using INNER JOINS, OUTER JOINS (LEFT, RIGHT, FULL), CROSS JOINS, self joins and the pre-ANSI 1992 Oracle way (+)
  • Further processing of query results using Subqueries and Correlated subqueries
  • Combining Query results using Set-operators: UNION (ALL), MINUS, INTERSECT
  • 3 - SQL Module 3
  • Performing advanced analytics using Analytical Functions:
  • Adding ranking to your query result using RANK, DENSE_RANK, ROW_NUMBER
  • Navigating through your query result using LAG, LEAD, FIRST_VALUE, LAST_VALUE
  • Calculating running totals and rolling averages using aggregate functions and the OVER clause
  • Pivoting result sets using PIVOT
  • Using variables in queries
  • Storing your queries in the database for later use: Views and Materialized Views
  • 4 - SQL Module 4
  • Creating, changing and deleting tables and other database objects using CREATE, ALTER, DROP
  • Copy a table using CREATE TABLE AS … query
  • Inserting, updating and deleting rows into/in/from database tables using INSERT, UPDATE, DELETE
  • Clearing a database table using TRUNCATE TABLE
  • Transaction Management
  • 5 - PL/SQL Module 5
  • PL/SQL introduction
  • Anonymous Blocks: DECLARE, BEGIN, EXCEPTION, END
  • Variables and Anchors
  • Program Structures: IF, CASE, LOOP
  • Cursors: implicit, explicit
  • 6 - PL/SQL Module 6
  • Stored Programs: Procedures, Functions
  • Packages: Package Specification, Package Body, Overloading, Describe, Scope
  • Exceptions
  • Nested Blocks
  • Triggers
  • 7 - PL/SQL Module 7
  • Records
  • Collections: Associative Arrays, Varrays, Nested Tables
  • Table Functions
  • Creating scripts based on table data
  • Actual course outline may vary depending on offering center. Contact your sales representative for more information.

    Who is it For?

    Target Audience

    Oracle SQL and PL/SQL

    Course Length : 7 Days

    There are currently no scheduled dates for this course. Please contact us for more information.

    Need Help Picking the Right Course? Give us a call! 01 8898700