|
Oracle 11g/12c Essentials: SQL, SQL*Plus and PL/SQL
Course: OR11E
Duration: 5 Days
Level: I
Course Summary
You will receive an introduction to Oracle's relational database, and a hands-on introduction in how to add, update, extract and organize information from the database. You will cover how to format reports and export data using ODBC. You will work with Oracle's Structured Query Language (SQL), SQL*Plus, and (Procedural Language for SQL) PL/SQL. You will cover debugging and error reporting procedures and how to define, execute and store procedures for reuse in the future.
This course can also be presented with content specific to Oracle 10g.
« Hide The Details
Topics Covered In This Course
Database Management System Overview
- What is a Database?
- Types of DBMSs
Introduction to Entity Relationship Modeling
- What is a Data Model?
- Why Use an Entity Relationship Model?
Fundamentals of Relational Database Management Systems
- What is an RDBMS?
- Relationships between Tables
SQL Basics and SQL*Plus
- Understanding the Difference Between SQL, SQL*Plus, and PL/SQL
- Entering and Executing Commands
Using SQL Developer
- Understanding the SQL Developer Environment and Commands
The WHERE and ORDER BY Clause
Using the Data Dictionary
Single Row Functions
- Functions
- Date and Numeric Format Models
- Data Conversion
- Control Statements
Joining Data from Multiple Tables in Queries
- The JOIN Condition / The Cartesian Product
- Outer JOINs
- SET Operators
- ANSI SQL99 Join Syntax
Group Functions and the GROUP BY Clause
- Distinct Operator in Group Functions
Using Subqueries and CASE Clauses
- Single-Row, Multi-Row, Multi-Column Subqueries
- SELECT, FROM, WHERE, HAVING and ORDER BY Subqueries
Analytic Functions
- Single Row and Aggregating Analytic Functions
SQL*Plus Reporting
- SQL*Plus Report Writing Commands
- Control Breaks
- Computing Aggregate Amounts
Data Manipulation Language (DML) Commands
- INSERT, UPDATE, DELETE
- MERGE Command
- Use of Subqueries
- Transaction Control
- Locking
Getting Data In And Out Of Oracle
- SQL*Loader Basics
- Importing Into Excel
- Direct Database Queries Using ODBC
PL/SQL Basics
- PL/SQL anonymous block structure and Datatypes
- Variable declarations
- Object naming rules
- Executable Statements
- Repetition control
- CASE Expressions and Statements
- Basic Coding Standards
Working With Database Data
- Referencing other variables or a database column?s datatypes
- Implicit cursor attributes
- Transaction control commands
- Selecting data into PL/SQL variables
- Declaring explicit cursors
Exception Handling
- Handling predefined and user-defined exceptions
- Preventing unhandled exceptions
- Controlling exception processing
Stored PL/SQL Objects
Introduction to procedures and functions stored in the database
- Creating stored PL/SQL objects
- Creating procedures, functions, packages, package bodies
- Introduction to database triggers
- Viewing and recompiling stored code
What You Can Expect
On successfully completing this course, participants can expect to be able to:
- Extract and organize information from the database
- Format reports using SQL*Plus commands
- Insert, update & delete information in database tables
- Work with the PL/SQL Environment and Program Structure
- Use Error Report Functions, Debugging Procedures
- Use Logical Comparisons
- Define and Execute Procedures, and Functions
- Test Procedures and Functions
- Create Package Specifications and Package Bodies
- Create DML Triggers
- Store PL/SQL Program Units
Who Should Take This Course
Anyone who needs to extract and organize information from an Oracle database, including formatting reports, adding, updating, and deleting data, and exporting data to ODBC files. I.e. Application designers, developers, database administrators, and End Users and those seeking the OCA certification.
Recommended Prerequisites
An understanding of relational database design concepts.
Training Style
Short lecture sessions (60%) along with hands-on lab sessions (40%).
« Hide The Details
Related Courses
| Code |
Course Title |
Duration |
Level |
|
OR18NEW |
Oracle12c -18c New Features For Administrators (Five Days) |
5 Days |
I |
Details |
ORDSCD |
ORACLE 10g/11g DISCOVERER DESKTOP For End Users |
2 Days |
I |
Details |
OR18PLS3 |
Oracle 18c Release 2 PL/SQL (Three Days) |
3 Days |
I |
Details |
OR18DBA1 |
Oracle 18c DBA I |
5 Days |
I |
Details |
OR11E2 |
Oracle 11g/12c Express Introduction to SQL, SQL*Plus, and PL/SQL |
3 Days |
I |
Details |
OR11F |
Oracle 11g and 12c Foundations: SQL and SQL*Plus |
5 Days |
I |
Details |
ORDSCP |
ORACLE 10g / 11g BI DISCOVERER PLUS for End Users |
2 Days |
I |
Details |
OR11D |
Oracle 11g/12c Developer: PL/SQL Complete |
5 Days |
I |
Details |
ORAPEX |
Oracle APEX Version 5.0.4 |
5 Days |
II |
Details |
OR11SQL2 |
Transitioning to Oracle 11g/12c SQL |
3 Days |
II |
Details |
OR11D2 |
Oracle 11g/12c Developer: Intermediate PL/SQL |
3 Days |
II |
Details |
Every student attending a Verhoef Training class will receive
a certificate good for $100 toward their next public class taken
within a year.
You can also buy "Verhoef Vouchers" to get a discounted rate for a
single student in any of our public or web-based classes.
Contact your account manager or our sales office for details.
|
|
Notify me the next time this course is confirmed!
|
