Oracle Database: SQL and PL/SQL Fundamentals NEW

1. Introduction

• Oracle Database 12c and related products overview
  
• Relational database management concepts and terminologies overview
  
• SQL and its development environments
  
• Oracle Database documentation and additional resources
  
  

• Oracle Database Exadata Express Cloud Service
  
• Access Cloud Database using SQL Workshop
  
• Connect to Exadata Express using Database Clients
  
  

• Explore capabilities of SQL SELECT statements
  
• Using arithmetic expressions and NULL values in the SELECT statement
  
• Generating a report of data from the output of a basic SELECT statement
  
• Invoking Column aliases
  
• Concatenation operator, alternative quote operator, literal character strings, and the DISTINCT keyword
  
• Displaying the table structure using the DESCRIBE command
  
  

• Writing queries with a WHERE clause to limit the output retrieved
  
• Describing the comparison operators and logical operators
  
• Utility of character string literals in the WHERE clause
  
• Writing queries with an ORDER BY clause
  
• Sorting the output in descending and ascending order
  
  

• Listing the differences between single row and multiple row functions
  
• Manipulating strings using character functions
  
• Deploying numbers with the ROUND, TRUNC, and MOD functions
  
• Manipulating dates with the DATE functions
  
  

• Utility of the aggregation functions in SELECT statements to produce meaningful reports
  
• Describing the AVG, SUM, MIN, and MAX function
  
• Techniques to handle Null Values in a group function
  
• Dividing the data in groups by using the GROUP BY clause
  
  

• Writing SELECT statements to access data from more than one table
  
• Viewing data that does not meet a join condition by using outer joins
  
• Joining Tables Using SQL:1999 Syntax
  
• Joining a table to itself by using a self-join
  
• Creating Cross Joins
  
  

• Use a Subquery to Solve a Problem
  
• Single-Row Subqueries
  
• Multiple-Row Subqueries
  
• Using the ANY and ALL Operator in Multiple-Row Subqueries
  
• Using the EXISTS Operator
  
  

• SET operators Overview
  
• Deploy a SET operator to combine multiple queries into a single query
  
• Describing the UNION, UNION ALL, INTERSECT, and MINUS Operators
  
• Using the ORDER BY Clause in Set Operations
  
  

• Adding New Rows to a Table
  
• Changing the Data in a Table
  
• Using the DELETE and TRUNCATE Statements
  
• Save and discard changes with the COMMIT and ROLLBACK statements
  
• Implementing Read Consistency
  
• Describing the FOR UPDATE Clause
  
  

• Creating Tables
  
• Describing the data types
  
• Understanding Constraints
  
• Creating a table using a subquery
  
• Altering a table
  
• Dropping a table
  
  

• Creating, modifying, and retrieving data from a view
  
• Perform Data manipulation language (DML) operations on a view
  
• Creating, using, and modifying a sequence
  
• Creating and dropping indexes
  
• Creating and dropping synonyms
  
  

• PL/SQL Overview
  
• Listing the benefits of PL/SQL Subprograms
  
• Types of PL/SQL blocks
  
• Creating a Simple Anonymous Block
  
• Generating the Output from a PL/SQL Block
  
  

• List the different Types of Identifiers in a PL/SQL subprogram
  
• Utility of the Declarative Section to Define Identifiers
  
• Deploying variables to store data
  
• Scalar Data Types
  
• Bind Variables
  
• %TYPE Attribute
  
• Sequences in PL/SQL Expressions
  
  

• PL/SQL Block Syntax Guidelines
  
• SQL Functions in PL/SQL
  
• Operators in PL/SQL
  
• Nested Blocks
  
• Data Type Conversion
  
  

• SELECT Statements in PL/SQL to Retrieve data
  
• The SQL Cursor concept
  
• Data Manipulation in the Server Using PL/SQL
  
• Using SQL Cursor Attributes to Obtain Feedback on DML
  
  

17. Control Structures

• Conditional processing Using IF Statements & CASE Statements
  
• For Loop Statement
  
• While Loop Statement
  
• The Continue Statement
  
• Simple Loop Statement
  
  

18. Composite Data Types

• PL/SQL Records
  
• Insert and Update with PL/SQL Records
  
• The %ROWTYPE Attribute
  
• INDEX BY Table Methods & Table of Records
  
  

• Understanding Explicit Cursors
  
• Declaring the Cursor
  
• Opening & Closing the Cursor
  
• Fetching data from the Cursor
  
• Cursor FOR loop
  
• FOR UPDATE Clause and WHERE CURRENT Clause
  
  

• Exceptions & Handling Exceptions with PL/SQL
  
• Trapping Predefined Oracle Server Errors
  
• Trapping Non-Predefined Oracle Server Errors
  
• Trapping User-Defined Exceptions
  
• Propagating Exceptions
  
  

• Describing Stored Procedures and Functions
  
• Differentiating between anonymous blocks and subprograms
  
• Creating & Executing a Simple Function
  
• Creating a Simple Procedure
  
• Creating a Simple Procedure with IN parameter