Location
Location

Uppal Hyderabad

Location
Phone

+91 70934 77157

ORACLE (SQL , PL/SQL)



Course Content:

SQL

1. Data Sub languages

  • 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.

    2. FUNCTIONS IN SQL

  • Single - Row functions ( Character , Number , Date , Conversion , Miscellaneous Functions)
  • Group Functions (MIN , MAX,SUM , AVG,COUNT)
  • DECODE() Function, CASE... WHEN statement
  • 3. CLAUSES IN SQL

  • FROM CLAUSE, WHERE CLAUSE,GROUP BY CLAUSE,HAVING , ORDER BY CLAUSE
  • 4. 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 Subqueries

    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,B-Tree index.

    7. Custom Types, Objects (Header & Body)

  • Abstract Datatypes,Nested Tables,Object Views,Varrays
  • 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 indexedcollections, 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 Datatype. 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 Dependecies, 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)