Courses Home

ORACLE SQL TRAINING IN CHENNAI


Oracle SQL Coures contents (Includes ANSI SQL / ISO SQL)


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.

Testimonials

I did my oracle training in greens technology, Chennai. Now I got placed in Virtusa. I really thank Dinesh for his involvement in my training. His way of teaching is really awesome. I never saw such quality teaching and placements were so fast. Thanks a lot Greens Technologys for your support. I wish dinesh sir all the best for his new IT development venture. Chandra,
Adyar

Greens Technology,
No.15 First Street,Padmanabha nagar,
Adyar,
Chennai-20.

Phone(M): +91 98404 96320
Phone(L): 044 43511623

eMail: contact@greenstech.in