ORACLE (SQL, PL/SQL)

Course Content:

ORACLE (SQL, PL/SQL)

DDL (Create , Alter, Truncate, Drop)

DML (Insert,update,delete)

DRL (Select)

DCL (Grant, Revoke)

TCL (Commit,Rollback,Savepoint) Using these, We able to creating tables, Changing table contents, Inserting rows, Modifying rows, Deleting rows, Describe the TRUNCATE Statement, Save and Discard Changes to a Table through Transaction Processing, (COMMIT, ROLLBACK, andSAVEPOINT),Show the DEFAULT option, List the Data Types that are available for Columns.

FUNCTIONS IN SQL

Single - Row functions (Character, Number, Date, Conversion, Miscellaneous Functions)

Group Functions (MIN, MAX, SUM, AVG, COUNT)

DECODE () Function, CASE... WHEN statement

CLAUSES IN SQL

FROM CLAUSE, WHERE CLAUSE, GROUP BY CLAUSE, HAVING, ORDER BY CLAUSE

DATABASE SECURITY

Explain the different types of constraints, Show resulting exceptions when constraints are violated with DML statements, Create a table with a sub query, Describe the ALTER TABLE functionality, Remove a table with the DROP Statement, Creating Temporary Tables, External Tables, Describe the structure of each of the dictionary views, List the purpose of each of the dictionary views, Write queries that retrieve information from the dictionary views on the schema objects, Use the COMMENT command to document objects.

5. JOINS AND SUBQUERIES

Displaying Data from Multiple Tables, Joins concept, Identify Types of Joins Retrieve Records with Natural Joins, Use Table Aliases to write shorter code and explicitly identify columns from multiple tables, Create a Join with the USING clause to identify specific columns, List the Types of Outer Joins LEFT, RIGHT, and FULL Generating a Cartesian Product, Concepts of Views, restrictions on views and types of access on view, Materialized views. Single-Row, Multi-Row, Multi-Column Subqueries, Subqueries in FROM clause, NULL Values in Sub queries Subqueries in a WHERE Clause, Subqueries in the FROM Clause – In-Line Views, Subqueries In the SELECT Clause – Scalar Subqueries, Correlated Subqueries.

6. DATABASE OBJECTS (SYNONYM, SEQUENCE, VIEW, INDEX)

Categorize simple and complex views and compare them, Create a view, Materialized Views ,Retrieve data from a view, Explain a read-only view, List the rules for performing DML on complex views, Create a sequence, List the basic rules for when to create and not create an index, Create a synonym, Bitmap index-Tree index.

7. Custom Types, Objects (Header & Body)

Abstract Data types, Nested Tables, Object Views, arrays

8. CONTROLLING USER ACCESS, BULK DATA HANDLING

Users, Privileges, Roles, Authentication Methods, SQL*Loader Basics, Import and Export concepts

9. ENHANCEMENTS IN ORACLE 10g/11g/12c

10. NORMALIZATION & PROJECT MODULE

PLSQL

1. INTRODUCTION TO PL/SQL

Explain the need for PL/SQL, Explain the benefits of PL/SQL, Identify the different types of PL/SQL blocks, Use iSQL*Plus as a development environment for PL/SQL, Output messages in PL/SQL, Identify valid and invalid identifiers, List the uses of variables, Declare and initialize variables, List and describe various data types, Identify the benefits of using the %TYPE attribute, Declare, use, and print bind variables, Identify lexical units in a PL/SQL block, Use built-in SQL functions in PL/SQL, Describe when implicit conversions take place and when explicit conversions have to be dealt with, Write nested blocks and qualify variables with labels, Write readable code with appropriate indentations.

2. WORKING WITH CONTROL STRUCTURES, PLSQL ATTRIBUTES (%TYPE,%ROWTYPE)

3. EXCEPTION HANDLING

4. CURSORS

Explicit, Implicit, Cursor..for loop, Ref Cursor

5. STORED PROCEDURES, FUNCTIONS, PACKAGES (Package Specification and Package Body)

6. DATABASE TRIGGERS

7. PLSQL COLLECTIONS

Describe and use nested tables, Describe and use varrays, Describe and use associative arrays, Describe and use string indexed collections, Describe and use nested collections, Write PL/SQL programs that use collections, Describe the common collection exceptions and how to code for them, Compare associative arrays to Collections

8. MANIPULATING LARGE OBJECTS, MANAGING DEPENDANCIES

LOB, Contracting LONG and LOB Data type. Internal LOB, Managing Internal LOB, What are B files, Securing B file. A new database object directory. Guidelines to create a directory. Managing B files, Preparing to use B files, Preparing B FILENAME function, Loading B FILE, DBMS_LOB Pachage, DBMS_LOB.READ,DBMS_LOB.WRITE,Adding LOB column to a table, Populating and updating LOB using SQL, Selecting CLOB value, Removing LOB, Temporary LOB, Understanding dependencies, Local dependencies, Scenario of Local Dependencies, Using User dependencies, Understanding Remote Dependencies, Packages and Dependencies, Unsuccessful recompilation, Successful Compilation, Recompilation of procedures

9. PL/SQL BUIT-IN PACKAGES

DBMS_ALERT, DBMS_DDL, DBMS_DEBUG, DBMS_LOCK, DBMS_MONITOR, UTL_FILE,.....etc

10. PERFORMANE AND TUNING

Explain Plan, TKPROF, Oracle Trace Facility

Discuss with DBMS_PROFILER for runtime statistics.

11. PIPELINED FUNCTIONS

12. DATA DICTIONARIES - BASIC DBA CONCEPTS

Here we will discuss about Database structure (Physical structures, Logical structures) About Data dictionaries like USER_ , ALL_ , DBA_
we will cover system privileges , object privileges SGA (Memory structures and Oracle processes)