Oracle Course Syllabus

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

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

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

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.

Multi-row Functions

• Group Functions Rules, SUM, MIN, MAX, COUNT, AVG
• Creating Groups of Data: GROUP BY Clause
• Filtering Group Results: The HAVING Clause

Sub-queries

• Single-Row Subqueries- Rules, Operators : = > >= < <= <>
• Null Values in a Subquery
• Multi-Row Subqueries- Rules, Operators : IN, ANY , ALL

Reporting data using interface commands

• pagesize, linesize , column heading , column format , colsep
• tTitle , bTitle , break on column, spool , CSV file generation, Text file generation

DML and TCL

• DML : INSERT, UPDATE, DELETE, MERGE
• TCL : COMMIT, ROLLBACK, SAVEPOINT

Data Definition Language – DDL

• DDL : CREATE, ALTER, RENAME, DROP, TRUNCATE
• DEFAULT OPTION.
• Constrain table copy

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

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

Other Database Objects

• Sequence- NEXTVAL and CURRVAL
• Index – When to Create an Index, When Not to Create an Index.
• Synonyms

DCL COMMANDS

• Creating Users
• Granting / Revoking Privileges
• Creating and Granting Privileges to a Role

Dictionary Tables

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

SET Operators

• UNION ,
• UNION ALL ,
• INTERSECT ,
• MINUS

Advanced Date-time Functions

• Group by with ROLLUP,
• Group by with CUBE,
• GROUPING SETS

Advanced GROUP BY Clause

• Group by with ROLLUP,
• Group by with CUBE,
• GROUPING SETS

Hierarchical Retrieval

• Walking the Tree: From the Bottom Up , From the Top Down
• LEVEL Pseudo column,
• Connect by prior,

Multi-table Insert

• Unconditional INSERT ALL
• Conditional INSERT ALL
• Conditional FIRST INSERT

DATA LOADER

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

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

Backup

• <ul> <li> Export / Import SCHEMA </li> </ul>

General discussion

• What is migration?
• Migration Estimating, Planning, Preparation Simple Scenario / Complex Scenario.