Course Overview
Course Content
- List the features of Oracle Database 11g
- Discuss the basic design, theoretical, and physical aspects of a relational database
- Categorize the different types of SQL statements
- Describe the data set used by the course
- Log on to the database using SQL Developer environment
- Save queries to files and use script files in SQL Developer
- Hands-On
- List the capabilities of SQL SELECT statements
- Generate a report of data from the output of a basic SELECT statement
- Select All Columns
- Select Specific Columns
- Use Column Heading Defaults
- Use Arithmetic Operators
- Understand Operator Precedence
- Learn the DESCRIBE command to display the table structure
- Hands-On
- Write queries that contain a WHERE clause to limit the output retrieved
- List the comparison operators and logical operators that are used in a WHERE clause
- Describe the rules of precedence for comparison and logical operators
- Use character string literals in the WHERE clause
- Write queries that contain an ORDER BY clause to sort the output of a SELECT statement
- Sort output in descending and ascending order
- Hands-On
- Explain the data dictionary
- Use the Dictionary Views
- USER_OBJECTS and ALL_OBJECTS Views
- Table and Column Information
- Query the dictionary views for constraint information
- Query the dictionary views for view, sequence, index and synonym information
- Add a comment to a table
- Query the dictionary views for comment information
- Hands-On
- Use Subqueries to Manipulate Data
- Retrieve Data Using a Subquery as Source
- Insert Using a Subquery as a Target
- Usage of the WITH CHECK OPTION Keyword on DML Statements
- List the types of Multitable INSERT Statements
- Use Multitable INSERT Statements
- Merge rows in a table
- Track Changes in Data over a period of time
- Hands-On
- Use the Regular Expressions Functions and Conditions in SQL
- Use Meta Characters with Regular Expressions
- Perform a Basic Search using the REGEXP_LIKE function
- Find patterns using the REGEXP_INSTR function
- Extract Substrings using the REGEXP_SUBSTR function
- Replace Patterns Using the REGEXP_REPLACE function
- Usage of Sub-Expressions with Regular Expression Support
- Implement the REGEXP_COUNT function
- Hands-On
- Create a Modularized and Layered Subprogram Design
- Modularize Development With PL/SQL Blocks
- Describe the PL/SQL Execution Environment
- Identity the benefits of Using PL/SQL Subprograms
- List the differences Between Anonymous Blocks and Subprograms
- Create, Call, and Remove Stored Procedures Using the CREATE Command and SQL Developer
- Implement Procedures Parameters and Parameters Modes
- View Procedures Information Using the Data Dictionary Views and SQL Developer
- Hands-On
- Create, Call, and Remove a Stored Function Using the CREATE Command and SQL Developer
- Identity the advantages of Using Stored Functions in SQL Statements
- List the steps to create a stored function
- Implement User-Defined Functions in SQL Statements
- Identity the restrictions when calling Functions from SQL statements
- Control Side Effects when calling Functions from SQL Expressions
- View Functions Information
- Hands-On
- Overloading Subprograms in PL/SQL
- Use the STANDARD Package
- Use Forward Declarations to Solve Illegal Procedure Reference
- Implement Package Functions in SQL and Restrictions
- Persistent State of Packages
- Persistent State of a Package Cursor
- Control Side Effects of PL/SQL Subprograms
- Invoke PL/SQL Tables of Records in Packages
- Hands-On
- Standardize Constants and Exceptions
- Understand Local Subprograms
- Write Autonomous Transactions
- Implement the NOCOPY Compiler Hint
- Invoke the PARALLEL_ENABLE Hint
- The Cross-Session PL/SQL Function Result Cache
- The DETERMINISTIC Clause with Functions
- Usage of Bulk Binding to Improve Performance
- Hands-On
- Describe Triggers
- Identify the Trigger Event Types and Body
- Business Application Scenarios for Implementing Triggers
- Create DML Triggers Using the CREATE TRIGGER Statement and SQL Developer
- Identify the Trigger Event Types, Body, and Firing (Timing)
- Statement Level Triggers Versus Row Level Triggers
- Create Instead of and Disabled Triggers
- How to Manage, Test, and Remove Triggers?
- Hands-On
- What are Compound Triggers?
- Identify the Timing-Point Sections of a Table Compound Trigger
- Compound Trigger Structure for Tables and Views
- Implement a Compound Trigger to Resolve the Mutating Table Error
- Compare Database Triggers to Stored Procedures
- Create Triggers on DDL Statements
- Create Database-Event and System-Event Triggers
- System Privileges Required to Manage Triggers
- Hands-On
- What is the PL/SQL Compiler?
- Describe the Initialization Parameters for PL/SQL Compilation
- List the New PL/SQL Compile Time Warnings
- Overview of PL/SQL Compile Time Warnings for Subprograms
- List the benefits of Compiler Warnings
- List the PL/SQL Compile Time Warning Messages Categories
- Setting the Warning Messages Levels: Using SQL Developer, PLSQL_WARNINGS Initialization Parameter, and the DBMS_WARNING Package Subprograms
- View Compiler Warnings: Using SQL Developer, SQL*Plus, or the Data Dictionary Views
- Hands-On
- What Is Conditional Compilation?
- Implement Selection Directives
- Invoke Predefined and User-Defined Inquiry Directives
- The PLSQL_CCFLAGS Parameter and the Inquiry Directive
- Conditional Compilation Error Directives to Raise User-Defined Errors
- The DBMS_DB_VERSION Package
- Write DBMS_PREPROCESSOR Procedures to Print or Retrieve Source Text
- Obfuscation and Wrapping PL/SQL Code
- Hands-On
- Overview of Schema Object Dependencies
- Query Direct Object Dependencies using the USER_DEPENDENCIES View
- Query an Object’s Status
- Invalidation of Dependent Objects
- Display the Direct and Indirect Dependencies
- Fine-Grained Dependency Management in Oracle Database 11g
- Understand Remote Dependencies
- Recompile a PL/SQL Program Unit
- Hands-On