ORACLE SQL, PL/SQL DEVELOPER

Oracle SQL PL/SQL Training Course

Table of Contents:

PART I:

1. Introduction to Oracle SQL.
2. Installation of Oracle Database and SQL Developer.
3. Role of SQL in RDBMS.

PART II: Structured Query Language – SQL

4. Basic SQL construct
5. Column naming conventions
6. Importance of NULL
7. Data Types
8. Literals
9. Format Models
10. Database Objects
11. Database Object Names and Qualifiers
12. Arithmetic Operators
13. Concatenation Operator
14. Set Operators

PART III: Functions and Expressions

15. Dual table
16. Single-Row Functions

• Numeric Functions
• Character Functions
• Datetime Functions
• Conversion Functions
• Large Object Functions

17. Aggregate Functions
18. Analytic Functions
19. Model Functions
20. OLAP Functions
21. Simple Expressions
22. Compound Expressions
23. CASE Expressions
24. Column Expressions
25. Datetime Expressions
26. Function Expressions

PART IV: SQL Queries and Subqueries

27. Creating Simple Queries
28. Applying conditions

• Comparison Conditions
• Logical Conditions
• Multiset Conditions
• Null Conditions
• BETWEEN Condition
• EXISTS Condition
• IN Condition
• Compound Conditions

29. Sorting Query Results
30. Joins
31. Using Subqueries
32. Unnesting of Nested Subqueries
33. Selecting from the DUAL Table

PART V: DDL, DML, DCL and TCL

34. INSERT statement
35. UPDATE statement
36. DELETE statement
37. Definition of Transaction
38. COMMIT & ROLLBACK statements
39. Using the SAVEPOINT statement
40. MERGE statement
41. Creating tables
42. Modifying tables, adding constraints to a table, renaming a table and its columns.
43. Dropping columns (set unused command) and dropping tables.
44. Creating and replacing views
45. Creating and dropping public synonyms
46. Creating modifying and dropping sequences
47. Grant and Revoke of object privileges between two Oracle database users
48. Need for Indexes
49. Types of Indexes
50. Composite Indexes
51. Function based Indexes
52. Analyzing and rebuilding Indexes
53. Materialized Views

PART VI: Procedural Language/ Structured

Query Language – PLSQL

1. PL/SQL Language Fundamentals

• The anonymous PLSQL block
• Variables
• Data types
• Bind variables
• PLSQL operators
• Nested blocks
• Implicit data conversion
• SELECT … INTO statement
• DBMS_OUTPUT.PUT_LINE

2. PL/SQL Data Types

3. PL/SQL Control Statements

• Conditional selection statements
• Loop statements
• Sequential control statements

4. Composite data types

• Collections
• Records

5. PL/SQL Static SQL

• Cursors
• Query Result Set Processing
• Cursor Variables
• CURSOR Expressions
• Autonomous Transactions

6. PL/SQL Dynamic SQL

• When You Need Dynamic SQL
• Native Dynamic SQL
• DBMS_SQL Package
• SQL Injection

7. PL/SQL Subprograms

• Reasons to Use Subprograms
• Subprogram Invocations
• Subprogram Parts
• Forward Declaration
• Subprogram Parameters
• Overloaded Subprograms
• Subprogram Side Effects
• PL/SQL Function Result Cache
• Procedure
• Functions

8. PL/SQL Triggers

• Overview of Triggers
• Reasons to Use Triggers
• DML Triggers
• System Triggers
• Subprograms Invoked by Triggers
• Trigger Compilation, Invalidation, and Recompilation
• Exception Handling in Triggers
• Trigger Design Guidelines
• Trigger Restrictions
• Order in Which Triggers Fire
• Trigger Enabling and Disabling
• Trigger Changing and Debugging

9. PL/SQL Packages

• What is a Package?
• Reasons to Use Packages
• Package Specification
• Package Body
• Package Instantiation and Initialization
• Package State
• Package Writing Guidelines
• Package Example

10. PL/SQL Error Handling

• Compile-Time Warnings
• Overview of Exception Handling
• Internally Defined Exceptions
• Predefined Exceptions
• User-Defined Exceptions
• Redeclared Predefined Exceptions
• Raising Exceptions Explicitly
• Exception Propagation
• Unhandl