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