Language: English (en-US) 

Awarding Body: Oracle

Duration: 80 Hrs

Versions: 10g, 11g and 12c, 19c

Exams: 1Z0-071: Oracle Database SQL

Exam 1Z0-149: Oracle Database Program with PL/SQL

Module 1: SQL Workshop

Course Description

Oracle Database: Introduction tSQL training helps you write subqueries, combine multiple queries into single query using SET operators and report aggregated data using group functions. Learn this and more through hands-on exercises.

This course will help you understand the advanced features of SQL. Learning these features will help you query and manipulate data within the database, use the dictionary views to retrieve metadata and create reports about their schema objects. Some of the date-time functions available in the Oracle Database are also covered.

Pre Requisite

None

Course Objectives

• Identify the major structural components of the Oracle Database 12c
• Create reports of aggregated data
• Write SELECT statements that include queries
• Retrieve row  and column data from tables
• Run data manipulation statements (DML) in Oracle Database 12c
• Create tables to store data
• Utilize views to display data
• Control database access to specific objects
• Manage schema objects
• Display  data from multiple tables using the ANSI SQL 99 JOIN syntax
• Manage objects with data dictionary views
• Write multiple-column sub-queries
• Employ  SQL functions to retrieve customized data
• Use scalar and correlated sub-queries
• Create reports of sorted and restricted data

Course Content

• Inroduction
• Retrieving Data using the SQL SELECT Statement
• Restricting and Sorting Data
• Using Single-Row Functions tCustomize Output
• Using Conversion Functions and Conditional Expressions
• Reporting Aggregated Data Using the Group Functions
• Displaying Data from Multiple Tables Using Joins
• Using Subqueries tSolve Queries
• Using the SET Operators
• Managing Tables using DML statements
• Introduction tData Definition Language
• Introduction tData Dictionary Views
• Creating Sequences, Synonyms, Indexes
• Creating Views
• Managing Schema Objects
• Retrieving Data by Using Subqueries
• Manipulating Data by Using Subqueries
• Controlling User Access
• Manipulating Data
• Managing Data in Different Time Zones

Module 2: Oracle Database: PLSQL Workshop

Course Description

This course provides detailed information on the architecture of an Oracle Database instance and database, enabling you to manage your database resources effectively. You learn how  to create database storage structures appropriate for the business applications supported by your database. In addition, you learn how  tcreate users and administer database security to meet your business requirements. This course provides basic information on backup and recovery techniques. To provide an acceptable response time to users and manage resources effectively, you learn how  to monitor your database and manage performance.

Pre Requisite

• SQL Workshop

Course Objectives

• Use conditional compilation to customize the functionality in a PL/SQL application without removing any source code
• Design PL/SQL packages to group related constructs
• Create overloaded package subprograms for more flexibility
• Design PL/SQL anonymous blocks that execute efficiently
• Use the Oracle supplied PL/SQL packages to generate screen output, file output and mail output
• Write dynamic SQL for more coding flexibility
• Describe the features and syntax of PL/SQL
• Create and debug stored procedures and functions
• Use PL/SQL programming constructs and conditionally control code flow (loops, control structures, and explicit cursors)
• Manage dependencies between PL/SQL subprograms
• Handle runtime errors
• Create triggers to solve business challenges

Course Content

• Introduction
• Introduction tPL/SQL
• Declare PL/SQL Identifiers
• Write Executable Statements
• Interaction with the Oracle Server
• Control Structures
• Composite Data Types
• Explicit Cursors
• Exception Handling
• Stored Procedures
• Stored Functions and Debugging Subprograms
• Packages
• Deploying Packages
• Implement Oracle-Supplied Packages in Application Development
• Dynamic SQL
• Design Considerations for PL/SQL Code
• Triggers
• Creating Compound, DDL, and Event Database Triggers
• PL/SQL Compiler
• Manage Dependencies