Introduction

• Describe the features of Oracle Database 12c
• Describe the salient features of Oracle Cloud 12c
• Explain the theoretical and physical aspects of a relational database
• Describe Oracle servers implementation of RDBMS and object relational database management system (ORDBMS)

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.