igreen softwarE TRAINING INSTITUTE

Oracle SQL/PLSQL Coures contents:

1. Basic DATABASE Concept and SQL

• Basic history of database concept: DBMS, RDBMS, ORDBMS

• Advantage of ORACLE database and version information

• Interface tools usage: sqlplus, isqlplus, sqldeveloper, Toad

• SQL Language overview : DQL, DML, DDL, DCL, TCL

• What is the usage of ANSI standard.

• SELECT Command - Column Alias Rules, String data,

• Concatenations with various data

• Null Value handling with number and characters,

• Arithmetic Operator

• Concatenation Operator,

• Eliminating Duplicate Rows

2. Restricting and Sorting Data

• WHERE Clause - Character Strings and Dates, number

• General Comparison Conditions = > >= < <= <>

• Other Comparison BETWEEN , IN , LIKE , NULL

• Logical Conditions AND OR NOT

• ORDER BY Clause, Sorting by Column Alias , Column Position, Multiple Columns

3. Single-Row Functions

• Character Functions: UPPER, LOWER, INITCAP, LENGTH, SUBSTR, INSTR, LPAD, RPAD, CONCAT, LTRIM, RTRIM, TRIM, REPLACE, TRANSLATE, REVERSE

• Number Functions: ROUND, TRUNC, MOD, POWER, CEIL , FLOOR, ABS

• Dates Functions: SYSDATE, MONTHS_BETWEEN, NEXT_DAY, LAST_DAY, ADD_MONTHS, ROUND, TRUNC, Arithmetic on Date

• Conversion Functions: Implicit Data-Type Conversion & Explicit Data-Type Conversion, TO_CHAR ,TO_NUMBER ,TO_DATE

• General Functions: NVL , NVL2 , NULLIF, COALESCE

• CASE Expression, DECODE

• Nested function with real-time usage

4. JOINS

• EQUI JOIN / SIMPLE JOIN / NORMAL JOIN

• ANSI JOIN, LEFT OUTER, RIGHT OUTER, FULL OUTER

• NATURAL JOIN, NATURAL OUTER JOINS

• INNER JOIN, JOIN ... USING clause, JOIN ... ON clause,

• CROSS JOIN, NON-EQUI JOIN, SELF JOIN

• ORACLE STANDARD OUTER JOINS.

• Multi table Joins, Complex Joins – How to simplified complex joins.

5. Multi-row Functions

• Group Functions Rules, SUM, MIN, MAX, COUNT, AVG

• Creating Groups of Data: GROUP BY Clause

• Filtering Group Results: The HAVING Clause

6. Sub-queries

• Single-Row Subqueries- Rules, Operators : = > >= < <= <>

• Null Values in a Subquery

• Multi-Row Subqueries- Rules, Operators : IN, ANY , ALL

7. Reporting data using interface commands

• pagesize, linesize , column heading , column format , colsep

• tTitle , bTitle , break on column, spool , CSV file generation, Text file generation

8. Data Manipulation Language DML and Transaction Control Language TCL

• DML : INSERT, UPDATE, DELETE, MERGE

• TCL : COMMIT, ROLLBACK, SAVEPOINT

9. Data Definition Language - DDL

• DDL : CREATE, ALTER, RENAME, DROP, TRUNCATE

• DEFAULT OPTION.

• Constrain table copy

10. Constraints

• NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK

• Column Level Constraint, Table Level Constraint – Naming constraints and usage

• Adding a Constraint, Dropping a Constraint,

• Disabling Constraints, Enabling Constraints

• Validating Constraints

11. Views

• Simple Views and Complex Views – Create, Drop, Source Code

• Rules for Performing DML Operations on a View

• WITH CHECK OPTION , WITH READ ONLY

• Inline Views

• Materialized View – Create, Refresh, Drop - Usage

12. Other Database Objects

• Sequence- NEXTVAL and CURRVAL

• Index - When to Create an Index, When Not to Create an Index.

• Synonyms

13. DCL COMMANDS

• Creating Users

• Granting / Revoking Privileges

• Creating and Granting Privileges to a Role

14. Dictionary Tables

• Tables, Views, Synonyms, Index, Sequence, Constrains, Source and other Dictionary

15. SET Operators

• UNION ,

• UNION ALL ,

• INTERSECT ,

• MINUS

16. Advanced Date-time Functions

• TIME ZONES ,

• SYSDATE, SYSTIMESTAMP,

• CURRENT_DATE , CURRENT_TIMESTAMP

• SESSIONTIMEZONE ,

• Storing time zone data in Table

• EXTRACT ,

• TO_YMINTERVAL

17. Advanced GROUP BY Clause

• Group by with ROLLUP,

• Group by with CUBE,

• GROUPING SETS

18. Advanced Subqueries

• Pairwise Comparison Subquery ,

• Nonpairwise Comparison Subquery

• Correlated Subqueries,

• Correlated UPDATE,

• Correlated DELETE

• EXISTS , NOT EXISTS Operator

19. Hierarchical Retrieval

• Walking the Tree: From the Bottom Up , From the Top Down

• LEVEL Pseudo column,

• Connect by prior,

20. Multi-table Insert

• Unconditional INSERT ALL

• Conditional INSERT ALL

• Conditional FIRST INSERT

21. DATA LOADER –

• SQLLDR – Loading CSV file / Flat file into ORACLE table.

22. Analytic Functions

• WM_CONCAT, LAG, LEAD, RANK, DENSE_RANK

• Query_by partition_clause with sum, min, max, avg, count,

• order_by_clause with sum, min, max, avg, count,

• Psudo column : Rownum, Rowid, - Elimination duplicate data

• Connect by rownum , Connect by Level – Generating random numbers, random dates,

• Quote Operator syntax and usage

23. Backup

• Export / Import SCHEMA

24. General discussion

• What is migration?

• Migration Estimating, Planning, Preparation – Simple Scenario / Complex Scenario.

Our Trainer work as an Oracle Consultant & Instructor, He has over 3 years of Oracle Implementation experience and recognized expert in Oracle PLSQL training.

My database knowledge will help you, as I will give you and expose you to practical situations in real time scenarios. This will help you understand the concepts with ease and confidence.This training program will make a novice into an expert consultant. No pre-requisite is required for understanding this Oracle.

My training tend towards real time requirement, as well as from exam point of view. It is not a power point slide show program. I will cover all the topics related to both SQL & PL/SQL, which will help you to take online oracle exams. The coverage will be indepth and the participants will become ready to handle production server, by the time they complete this training program.

Now I have added new topics apart from the standard oracle curriculum in my training topics - SQL and PL/SQL both courses based on real time requirements and demand of Program code optimization.

Program code optimization is to make some aspect of it work more efficiently or use fewer resources, to execute more rapidly, or is capable of operating with less memory.

Oracle Training Instituite Benefits:

• My batch sizes are generally very small size 5-7 members OR 1 to 1 also, so students receive direct, hands-on training from us.

• 100% practical training only. It is not a slide show training program / theory class program. At the end of this class, definitely you will refer your colleagues / friends / relatives for my training.

• Conducting regularly online- training for students flexible time slots.

• Installation of Softwares in your desktop / laptop will be done.

• Will be provided self evaluation testing software, exam simulator, dumps & books with this training.

• Continuous support is provided for any of your on-site problems. assistance will be provided and you will be recommended to some of IT development firms.

• Training duration is 2 months and we are giving practical training upto job placement.

• Hardware for the required training should be provided by you, like Hall, Projector, and Furniture.

• I will be bringing my laptop for training purpose, which should be allowed in to your premises.

• Your role will be working on the back end part by writing queries. This might turn out useful when application converse with the database directly. Later you would be working as a DBA who creates tables, users, permissions and roles.

• You would be having scope to deal with the performance tuning part since mostly several applications have concerns with the PL/SQL stored procedures.

• Oracle is the most deployed database in the IT industry. Being an RDBMS it plays a substantial role in several fields in banking, finance etc. India is the IT hub and it provides good opportunities of OraclePL/SQL. The roles that a student trained in Oracle PL/SQL can take up are:

• Oracle Developer

• Applications Developer

• PL/SQL administrator

• SQL Developer