ORACLE (SQL, PL/SQL)
FUNCTIONS IN SQL
CLAUSES IN SQL
FROM CLAUSE, WHERE CLAUSE, GROUP BY CLAUSE, HAVING, ORDER BY CLAUSE
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
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
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)