Oracle Database: SQL and PL/SQL Fundamentals NEW

12  Download (0)

Full text

(1)

Oracle University | Contact Us: 001-855-844-3881 & 001-800-514-06-97

Oracle Database: SQL and PL/SQL Fundamentals NEW

Duration: 5 Days

What you will learn

This Oracle Database: SQL and PL/SQL Fundamentals training delivers the fundamentals of SQL and PL/SQL along with the benefits of the programming languages using Oracle Database technology. You'll explore the concepts of relational databases.

Learn To:

Write queries against single and multiple tables, manipulate data in tables and create database objects.

Use single row functions to customize output.

Invoke conversion functions and conditional expressions.

Use group functions to report aggregated data.

Create PL/SQL blocks of application code that can be shared by multiple forms, reports and data management applications.

Develop anonymous PL/SQL blocks, stored procedures and functions.

Declare identifiers and trap exceptions.

Use DML statements to manage data.

Use DDL statements to manage database objects.

Declare PL/SQL Variables.

Conditionally control code flow (loops, control structures).

Describe stored procedures and functions.

Retrieve row and column data from tables.

Benefits to You

Ensure fast, reliable, secure and easy to manage performance. Optimize database workloads, lower IT costs and deliver a higher quality of service by enabling consolidation onto database clouds.

Hands-On Practices

Demonstrations and hands-on practice reinforce the fundamental concepts that you’ll learn in this course. By enrolling in this course, you’ll begin using Oracle SQL Developer to develop these program units. SQL*Plus and JDeveloper are available as optional tools.

Course Bundle

Note: This course is a combination of Oracle Database: SQL Workshop I and Oracle Database: PL/SQL Fundamentals courses.

Audience

Application Developers

(2)

Forms Developer Functional Implementer PL/SQL Developer Portal Developer Reports Developer Technical Consultant

Related Training Required Prerequisites

Familiarity with data processing concepts and techniques

Familiarity with programming concepts

Course Objectives

Run data manipulation statements (DML) to update data in the Oracle Database.

Design PL/SQL anonymous block that execute efficiently.

Describe the features and syntax of PL/SQL.

Handle runtime errors.

Describe stored procedures and functions.

Use PL/SQL programming constructs and conditionally control code flow (loops, control structures, and explicit cursors).

Use cursors to process rows.

Identify the major structural components of the Oracle Database 11g.

Retrieve row and column data from tables with the SELECT statement.

Create reports of sorted and restricted data.

Employ SQL functions to generate and retrieve customized data.

Display data from multiple tables using the ANSI SQL 99 JOIN syntax.

Create reports of aggregated data.

Run data definition language (DDL) statements to create and manage schema objects.

Course Topics Introduction

Overview of Oracle Database 12c and related products

(3)

Overview of relational database management concepts and terminologies Introduction to SQL and its development environments

The HR schema and the tables used in this course Oracle Database documentation and additional resources

Retrieve Data using the SQL SELECT Statement List the capabilities of SQL SELECT statements

Generate a report of data from the output of a basic SELECT statement Use arithmetic expressions and NULL values in the SELECT statement Invoke Column aliases

Concatenation operator, literal character strings, alternative quote operator, and the DISTINCT keyword Display the table structure using the DESCRIBE command

Restricted and Sorted Data

Write queries with a WHERE clause to limit the output retrieved Describe the comparison operators and logical operators

Describe the rules of precedence for comparison and logical operators Usage of character string literals in the WHERE clause

Write queries with an ORDER BY clause

Sort the output in descending and ascending order Substitution Variables

Usage of Single-Row Functions to Customize Output

List the differences between single row and multiple row functions Manipulate strings using character functions

Manipulate numbers with the ROUND, TRUNC, and MOD functions Perform arithmetic with date data

Manipulate dates with the DATE functions

Conversion Functions and Conditional Expressions Describe implicit and explicit data type conversion

Describe the TO_CHAR, TO_NUMBER, and TO_DATE conversion functions Nesting multiple functions

Apply the NVL, NULLIF, and COALESCE functions to data Usage of conditional IF THEN ELSE logic in a SELECT statement

Aggregated Data Using the Group Functions

Usage of the aggregation functions in SELECT statements to produce meaningful reports Describe the AVG, SUM, MIN, and MAX function

How to handle Null Values in a group function?

Divide the data in groups by using the GROUP BY clause Exclude groups of date by using the HAVING clause

Display Data From Multiple Tables

Write SELECT statements to access data from more than one table Join Tables Using SQL:1999 Syntax

View data that does not meet a join condition by using outer joins Join a table to itself by using a self join

Create Cross Joins

Usage of Subqueries to Solve Queries Use a Subquery to Solve a Problem

(4)

Single-Row Subqueries

Group Functions in a Subquery Multiple-Row Subqueries

Use the ANY and ALL Operator in Multiple-Row Subqueries Use the EXISTS Operator

SET Operators

Describe the SET operators

Use a SET operator to combine multiple queries into a single query Describe the UNION, UNION ALL, INTERSECT, and MINUS Operators Use the ORDER BY Clause in Set Operations

Data Manipulation Add New Rows to a Table Change the Data in a Table

Use the DELETE and TRUNCATE Statements

How to save and discard changes with the COMMIT and ROLLBACK statements Implement Read Consistency

Describe the FOR UPDATE Clause

DDL Statements to Create and Manage Tables Categorize Database Objects

Create Tables

Describe the data types Understand Constraints

Create a table using a subquery How to alter a table?

How to drop a table?

Other Schema Objects

Create, modify, and retrieve data from a view

Perform Data manipulation language (DML) operations on a view How to drop a view?

Create, use, and modify a sequence Create and drop indexes

Create and drop synonyms

Introduction to PL/SQL PL/SQL Overview

List the benefits of PL/SQL Subprograms Overview of the Types of PL/SQL blocks Create a Simple Anonymous Block Generate the Output from a PL/SQL Block

PL/SQL Identifiers

List the different Types of Identifiers in a PL/SQL subprogram Usage of the Declarative Section to Define Identifiers

Use of variables to store data Scalar Data Types

%TYPE Attribute Bind Variables

Sequences in PL/SQL Expressions

(5)

Write Executable Statements

Basic PL/SQL Block Syntax Guidelines How to comment code?

SQL Functions in PL/SQL Data Type Conversion Nested Blocks Operators in PL/SQL

Interaction with the Oracle Server

SELECT Statements in PL/SQL to Retrieve data Data Manipulation in the Server Using PL/SQL The SQL Cursor concept

Learn to use SQL Cursor Attributes to Obtain Feedback on DML How to save and discard transactions?

Control Structures

Conditional processing Using IF Statements Conditional processing Using CASE Statements Simple Loop Statement

While Loop Statement For Loop Statement The Continue Statement

Usage of Composite Data Types PL/SQL Records

The %ROWTYPE Attribute

Insert and Update with PL/SQL Records Associative Arrays (INDEX BY Tables) INDEX BY Table Methods

INDEX BY Table of Records

Explicit Cursors

Understand Explicit Cursors Declare the Cursor

How to open the Cursor?

Fetching data from the Cursor How to close the Cursor?

Cursor FOR loop

Explicit Cursor Attributes

FOR UPDATE Clause and WHERE CURRENT Clause

Exception Handling What are Exceptions?

Handle Exceptions with PL/SQL Trap Predefined Oracle Server Errors Trap Non-Predefined Oracle Server Errors Trap User-Defined Exceptions

Propagate Exceptions

RAISE_APPLICATION_ERROR Procedure

Stored Procedures and Functions

What are Stored Procedures and Functions?

(6)

Differentiate between anonymous blocks and subprograms Create a Simple Procedure

Create a Simple Procedure with IN parameter Create a Simple Function

Execute a Simple Procedure Execute a Simple Function

(7)

Oracle University | Contact Us: 52-800-514-06-97

Oracle Database 12c: Admin, Install and Upgrade Accelerated

Duration: 5 Days

What you will learn

This Oracle Database 12c: Admin, Install and Upgrade Accelerated course covers 7 days worth of content in only 5 days. It consists of the Oracle Database 12c: Administration Workshop Ed 1 course (5 days) and the Oracle Database 12c: Install and Upgrade Workshop Ed 1 course (2 days). This course will will teach you about the Oracle Database architecture and you will discover how to effectively manage an Oracle Database instance. You will also be provided key information on how to install and administer Oracle Database 12c.

Learn To:

Install Oracle Grid Infrastructure for a Standalone Server.

Use Oracle Restart to manage components.

Upgrade an existing Oracle Database to Oracle Database 12c.

Create and manage an Oracle Database Instance.

Create and manage Storage Structures.

Configure the Oracle Network Environment.

Create and manage users.

Monitor the database and manage performance.

Learn basic information on backup and recovery techniques.

Benefits To You

Discover how you can effectively install and manage an Oracle Database instance. Create a container database and provision pluggable databases to support your business needs. Benefit from learning how to configure the Oracle Network Environment to perform database maintenance.

Audience

Data Warehouse Administrator Database Administrators Database Designers Support Engineer Technical Administrator

Related Training Required Prerequisites

Working knowledge of SQL and use of PL/SQL packages

(8)

Suggested Prerequisites

Basic knowledge of Linux Operating System

Course Objectives

Use Oracle Restart to manage components

Upgrade database to Oracle Database 12c

Install Oracle Grid Infrastructure for a Standalone Server

Create a container database

Create an Oracle Database

Install Oracle Database 12c software

Monitor the Database

Manage Database Performance

Implement Database Auditing

Configure the Database Instance Such That Resources Are Appropriately Allocated Among Sessions and Tasks

Schedule Jobs to Run Inside or Outside of the Database

Configure Oracle Net Services

Configure your Database For Backup and Recovery Operations

Describe Oracle Database Architecture

Manage the Oracle Database Instance

Manage Oracle Database Storage structures

Course Topics

Introduction Course Objectives Course Schedule

Overview of Oracle Database 12c Overview of Oracle Cloud

Overview of the HR Schema

Exploring the Oracle Database Architecture Oracle Database 12c Introduction

Oracle Database Architecture: Overview

(9)

Oracle Database Instance Configurations Connecting to the Oracle Database Instance Oracle Database memory Structures (overview) Process Architecture (description)

Process Structures

Process Startup Sequence

Oracle Software Installation Basics Planning Your Installation

Configuring Oracle Linux with Oracle RDBMS Pre-Install RPM Operating System Groups and Users

Environment Variables

Configuring the Oracle Software Owner Environment Using Oracle Universal Installer (OUI)

Installation Option: Silent Mode

Installing Oracle Database Software System Requirements for Oracle Database Creating Operating System Groups and Users Types of Installations

Creating an Oracle Database by Using DBCA Planning the Database Storage Structure Types of Databases (based on workload) Choosing the Appropriate Character Set Understanding How Character Sets are Used Setting the NLS_LANG Initialization Parameter Using the Database Configuration Assistant (DBCA)

Oracle Database Management Tools

Introducing Oracle Database Management Tools Using SQL*Plus

Using SQL Developer

Logging in to Oracle Enterprise Manager Database Express Using the Enterprise Manager Database Express Home Page Understanding the Enterprise Manager Cloud Control Framework Using Enterprise Manager Cloud Control

Managing the Database Instance Initialization Parameter Files

Starting an Oracle Database Instance Shutting Down an Oracle Database Instance Viewing Log Files

Using Trace Files

Using the Dynamic Performance Views Data Dictionary

Configuring the Oracle Network Environment Oracle Net Services Overview

Oracle Net Listener Overview

Establishing Oracle Network Connections Connecting to a Server

(10)

Tools for Configuring and Managing the Oracle Network Using the Listener Control Utility

Using Oracle Net Configuration Assistant Using Oracle Net Manager

Administering User Security Database User Accounts (overview) Predefined Administrative Accounts Creating a User

Authentication

Unlocking a User Account and Resetting the Password Privileges

Roles Profiles

Managing Database Storage Structures Understanding Storage of Data

Database Block contents Exploring the Storage Structure Creating a New Tablespace

Overview of Tablespaces Created by Default Managing Tablespaces

Viewing Tablespace Information Using Oracle Managed Files

Managing Space

Space Management Overview Block Space Management Row Chaining and Migration

Free Space Management Within Segments Types of Segments

Allocating Extents Allocating Space

Creating Tables Without Segments

Managing Undo Data Undo Data Overview

Transactions and Undo Data Storing Undo Information

Comparing Undo Data and Redo Data Managing Undo

Configuring Undo Retention Guaranteeing Undo Retention

Changing an Undo tablespace to a Fixed Size

Managing Data Concurrency Overview of Locks

Locking Mechanism Data Concurrency DML Locks

Enqueue Mechanism Lock Conflicts

(11)

Implementing Oracle Database Auditing Separation of Responsibilities

Database Security

Monitoring for Compliance Standard Database Auditing Unified Audit Data Trail

Separation for Duties for Audit Administration (AUDIT_ADMIN and AUDIT_VIEWER roles) Configuring the Audit Trail

Specifying Audit Options

Backup and Recovery Concepts Categories of Failures

Flashback Technology

Understanding Instance Recovery Phases of Instance Recovery Tuning Instance Recovery Using the MTTR Advisor

Comparing Complete and Incomplete Recovery Oracle Data Protection Solutions

Backup and Recovery Configuration Configuring for Recoverability

Configuring the Fast Recovery Area Multiplexing the Control File

Online Redo Log File

Multiplexing the Online Redo Log File Archived Redo Log Files

Archiver Process

Archived Redo Log File Naming and Destinations

Performing Database Backups Backup Solutions Overview Oracle Secure Backup (overview) User Managed Backup (overview)

Backup Terminology and Types of Backups Using Recovery Manager (RMAN)

Configuring Backup Settings Oracle-Suggested Backup

Backing Up the Control File to a Trace File

Performing Database Recovery Data Recovery Advisor

Loss of a Control File Loss of a Redo Log File

Loss of a Datafile in NOARCHIVELOG Mode

Loss of a Noncritical Datafile in ARCHIVELOG Mode Loss of a System-Critical Datafile in ARCHIVELOG Mode

Moving Data

Moving Data: General Architecture Oracle Data Pump

SQL*Loader

(12)

External Tables

Performing Database Maintenance Database Maintenance (overview) Viewing the Alert History

Terminology

Automatic Workload Repository (AWR) Statistic Levels

Automatic Database Diagnostic Monitor (ADDM) Advisory Framework

Enterprise Manager and Advisors

Managing Performance Performance Monitoring Tuning Activities

Performance Planning Instance Tuning

Performance Tuning Methodology Performance Tuning Data

Monitoring Performance Managing Memory

Managing Performance: SQL Tuning SQL Tuning

Oracle Optimizer SQL Plan Directives Adaptive Execution Plans SQL Advisors

Automatic SQL Tuning results

Implement Automatic Tuning Recommendations SQL Tuning Advisor

Figure

Updating...

References

Related subjects :