Credo Systemz offers Oracle training in Chennai to enhance the skills in achieving the Oracle certification. According to the reviews over the internet, Credo Systemz named as the Best Oracle training institute in Chennai.
Chapter 1: 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)
Chapter 2: 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
>
Chapter 3: 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
Chapter 4: 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
Chapter 5: 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
Chapter 6: Multi-row Functions
- Group Functions Rules, SUM, MIN, MAX, COUNT, AVG
- Creating Groups of Data: GROUP BY Clause
- Filtering Group Results: The HAVING Clause
Chapter 7: Sub-queries
- Single-Row Subqueries- Rules, Operators : = > >= < <= <>
- Null Values in a Subquery
- Multi-Row Subqueries- Rules, Operators : IN, ANY , ALL
Chapter 8: Reporting data using interface commands
- Pagesize, linesize , column heading , column format , colsep
- tTitle , bTitle , break on column, spool , CSV file generation, Text file generation
Chapter 9: Data Manipulation Language DML and Transaction Control Language TCL
- DML : INSERT, UPDATE, DELETE, MERGE
- TCL : COMMIT, ROLLBACK, SAVEPOINT
Chapter 10: Data Definition Language – DDL
- DDL : CREATE, ALTER, RENAME, DROP, TRUNCATE
- DEFAULT OPTION
- Constrain table copy
Chapter 11: 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
Chapter 12: 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
Chapter 13: Other Database Objects
- Sequence- NEXTVAL and CURRVAL
- Index – When to Create an Index, When Not to Create an Index.
- Synonyms
Chapter 14: DCL COMMANDS
- Creating Users
- Granting / Revoking Privileges
- Creating and Granting Privileges to a Role
Chapter 15: Dictionary Tables
- Tables, Views, Synonyms, Index, Sequence, Constrains, Source and other Dictionary
Chapter 16: SET Operators
- UNION
- UNION ALL
- INTERSECT
- MINUS
Chapter 17: Advanced Date-time Functions
- TIME ZONES
- SYSDATE, SYSTIMESTAMP
- CURRENT_DATE , CURRENT_TIMESTAMP
- SESSIONTIMEZONE
- Storing time zone data in Table
- EXTRACT
- TO_YMINTERVAL
Chapter 18: Advanced GROUP BY Clause
- Group by with ROLLUP
- Group by with CUBE
- GROUPING SETS
Chapter 19: Advanced Subqueries
- Pairwise Comparison Subquery
- Nonpairwise Comparison Subquery
- Correlated Subqueries
- Correlated UPDATE
- Correlated DELETE
- EXISTS , NOT EXISTS Operator
Chapter 20: Hierarchical Retrieval
- Walking the Tree: From the Bottom Up , From the Top Down
- LEVEL Pseudo column
- Connect by prior
Chapter 21: Multi-table Insert
- Unconditional INSERT ALL
- Conditional INSERT ALL
- Conditional FIRST INSERT
Chapter 23: Analytic Functions
- SQLLDR Loading CSV file / Flat file into ORACLE table.
Chapter 24: Backup
Chapter 25: General discussion
- What is migration?
- Migration Estimating, Planning, Preparation Simple Scenario / Complex Scenario.
Chapter 1: Grouping and Aggregating Data Using SQL
- Generating Reports by Grouping Related Data
- Review of Group Functions
- Reviewing GROUP BY and HAVING Clause
- Using the ROLLUP and CUBE Operators
- Using the GROUPING Function
- Working with GROUPING SET Operators and Composite Columns
- Using Concatenated Groupings with Example
Chapter 2: Hierarchical Retrieval
- Using Hierarchical Queries
- Sample Data from the EMPLOYEES Table
- Natural Tree Structure
- Hierarchical Queries: Syntax
- Walking the Tree: Specifying the Starting Point
- Walking the Tree: Specifying the Direction of the Query
- Using the WITH Clause
- Hierarchical Query Example: Using the CONNECT BY Clause
Chapter 3: Working with Regular Expressions
- Introducing Regular Expressions
- Using the Regular Expressions Functions and Conditions in SQL and PL/SQL
- Introducing Metacharacters
- Using Metacharacters with Regular Expressions
- Regular Expressions Functions and Conditions: Syntax
- Performing a Basic Search Using the REGEXP_LIKE Condition
- Finding Patterns Using the REGEXP_INSTR Function
- Extracting Substrings Using the REGEXP_SUBSTR Function
Chapter 4: Analyzing and Reporting Data Using SQL
- Overview of SQL for Analysis and Reporting Functions
- Using Analytic Functions
- Using the Ranking Functions
- Using Reporting Functions
Chapter 5: Performing Pivoting and Unpivoting Operations
- Performing Pivoting Operations
- Using the PIVOT and UNPIVOT Clauses
- Pivoting on the QUARTER Column: Conceptual Example
- Performing Unpivoting Operations
- Using the UNPIVOT Clause Columns in an UNPIVOT Operation
- Creating a New Pivot Table: Example
Chapter 6: Pattern Matching using SQL
- Row Pattern Navigation Operations
- Handling Empty Matches or Unmatched Rows
- Excluding Portions of the Pattern from the Output
- Expressing All Permutations
- Rules and Restrictions in Pattern Matching
- Examples of Pattern Matching
Chapter 7: Modeling Data Using SQL
- Using the MODEL clause
- Demonstrating Cell and Range References
- Using the CV Function
- Using FOR Construct with IN List Operator, incremental values and Subqueries
- Using Analytic Functions in the SQL MODEL Clause
- Distinguishing Missing Cells from NULLs
- Using the UPDATE, UPSERT and UPSERT ALL Options
- Reference Models
Chapter 1: Exploring the Oracle Database Architecture
- Describe the major architectural components of Oracle Database server
- Explain memory structures
- Describe background processes
- Correlate logical and physical storage structures
Chapter 2: Introduction to SQL Tuning
- Describe what attributes of a SQL statement can make it perform poorly
- Describe the Oracle tools that can be used to tune SQL
- Explain the tuning tasks
Chapter 3: Introduction to the Optimizer
- Describe the execution steps of a SQL statement
- Explain the need for an optimizer
- Explain the various phases of optimization
- Control the behavior of the optimizer
Chapter 4: Interpreting Execution Plans
- Gather execution plans
- Display execution plans, display xplan
- Interpret execution plans
Chapter 5: Application Tracing
- Configure the SQL Trace facility to collect session statistics
- Use the trcsess utility to consolidate SQL trace files
- Format trace files using the tkprof utility
- Interpret the output of the tkprof command
Chapter 6: Optimizer Operations
- Describe the SQL operations for tables and indexes
- Describe the possible access paths for tables and indexes
Chapter 7: Optimizer: Join Operations
- Describe the SQL operations for joins
- Describe the possible access paths for joins
Chapter 8: Other Optimizer Operations
- Describe Clusters, In-List, Sorts, Filters and Set Operations
- Use Result Cache operations
Chapter 9: Case Study: Star Transformation
- Define a star schema, a star query plan without transformation and a star query plan after transformation
Chapter 10: Optimizer Statistics
- Gather optimizer statistics
- Gather system statistics
- Set statistic preferences
- Use dynamic sampling
- Manipulate optimizer statistics
Chapter 11: Using Bind Variables
- Explain the benefits of using bind variables
- Use bind peeking
- Use adaptive cursor sharing
Chapter 12: SQL Tuning Advisor
- Describe statement profiling
- Use SQL Tuning Advisor
Chapter 13: Using SQL Access Advisor
Chapter 14: Automating SQL Tuning
Chapter 15: SQL Plan Management
- Manage SQL performance through changes
- Set up SQL Plan Management
- Set up various SQL Plan Management scenarios
Chapter 16: Using Optimizer Hints
- Use hints when appropriate
- Specify hints for Optimizer mode, Query transformation, Access path, Join orders, Join methods and Views
Chapter 17: Parallel Processing Concepts
- Explain what parallel processing is and why is it useful
Chapter 18: Basics of Parallel Execution
- Describe operations that can be parallelized
- Explain parallel execution theory
- Understand impact of initiali zation parameter on parallel execution
Chapter 19: Manual DOP Management
- Understand an explain plan of a parallel query
- Understand an explain plan of parallel DML and DDL
Chapter 20: Simplified Auto DOP
- Understand the new parameters of Auto DOP
- Explain when to use Auto DOP
- Use Auto DOP
Chapter 21: Statement Queuing
- Explain statement queuing, concurrency and DBRM
Chapter 22: In-Memory Parallel execution
- Use in-memory parallel execution
Chapter 23: Partitioning Concepts
- Explain the available partitioning strategies
- Explain partition pruning
- Implement partition enhancements in star query optimization
Chapter 24: Materialized Views
- Use summaries to improve performance
- Differentiate materialized view types
