Oracle
Syllabus
Environment
- Oracle 11 g Express
Introduction To SQL
- Introduction Database
- Understanding DBMS vs RDBMS
- Difference between 10g vs 11g vs 12c
- Installation of 12c
- About SQL*Plus and use of developer tool
- Datatypes in Oracle
- Operators in Oracle
- Understanding Schema design and objects
Data Retrieval Techniques
- How to use select statement in different ways to retrieve records?
- Working with Column alias
- Working with Table alias
- Data filtering and sorting with in single table
- Clauses and its types in oracle
- Where clause
- Having clause
- From clause
- Group by clause
- Order by clause
- Using clause
- Constraint clause
- For update clause
Working With DDL Commands
- Table creation using CREATE statement
- Creating table from another table
- Dropping a table using DROP command
- Altering the column of a table
- Modifying the column datatype in a table
- Renaming the column of a table
- Renaming an entire table
- Using truncate command
- Difference between Delete and Truncate command
Working With DML Commands
- How to copy data from one table to another table?
- How to copy the structure alone from a table?
- Different types of inserting row to an existing table
- Updating any value of with in a record using UPDATE command
- Deleting a particular record from a table
- Using merge & insert all command [Implementation of Project 1 ]
Integrity Constraints
- How to declare column level constraints?
- How to declare row level constraints ?
- How to add constraints to an existing table?
- Types of integrity constraints
- Not null
- Unique key
- Primary key
- Referential integrity
- Check integrity
- How to enable and disable constraints?
- How to get information about constraints?
Built In Functions
- Understanding Single row functions
- How to use single row functions using dummy table?
- Types of single row functions
- String functions
- Date functions
- Mathematical functions
- Conversion functions
- Special functions
- Analytical functions
- Working with multi row functions [Implementation of Project 2]
Data Aggregation
- Working with aggregate function
- Count()
- Sum()
- Max()
- Min()
- Avg()
- Working with group by clause
- Working with having clause
- Difference between WHERE and HAVING clause
Importance Of JOIN
- Understanding joins and its uses
- Types of joins
- Equi join
- Non – equi join
- Self join
- Outer join
- Left & Right outer join
- Full outer join
- Cross join [Implementation of Project 3]
Set Operators And Pseudo Columns:
- How to use set operators in a single table content?
- Working with set operator types
- UNION
- UNION ALL
- INTERSECT
- MINUS
- Working with pseudo columns using the following
- ROWID
- ROWNUM
Sub Queries
- Importance of sub queries
- Using different types of sub queries
- Single row sub queries
- Multi row sub queries
- Nested queries
- Multi column sub queries
- Correlated sub queries
- Using inline views and scalar queries [ Implementation of Project 4]
Database Transaction And Security
- Working with data query language using TCL
- Working with data control language commands
- Use of commit and rollback
- Use of savepoint and set transaction
- How to give system privileges to an user?
- How to invoke and revoke object privileges?
- How to create users and roles?
Design Of Schema Objects
- Creating and working with Views
- Working with Synonyms
- Creating Index and clusters
- Working with in materialized view
- Understanding sequences and its types [Implementation of Project 5]
PL/SQL (Procedural Language Extension to SQL)
Introduction To PL/SQL
- Informal introduction to PL/SQL
- Advantages of PL/SQL
- Datatypes in PL/SQL
- Program structure of PL/SQL
- Embedding SQL statements
- Using conditional statements and loops
Creating And Using Cursors
- What is cursor?
- How to create cursor?
- Using cursors in PL/SQL
- How to create explicit cursor?
- Creation of for loop cursor
- What are cursor parameters?
- How to use for update clause?
- What is ref cursors?
- How to use implicit cursors?
Understanding Exception Handling
- What is an Exception?
- Describing Exception types
- Handling system defined exceptions
- Handling user defined exceptions?
- Sql code vs Sql errm
- Pragma exception_init
Creation Of Stored Procedures
- Creating procedures in Pl/SQL
- Working with procedure parameters
- IN parameter
- OUT parameter
- INOUT parameter
- How to create procedures with cursors
- How procedures return records?
- What is Pragma autonomous transaction?
Triggers In PL/SQL
- How to create triggers?
- Benefits of trigger
- How to trigger a trigger?
- Using DML trigger & DDL trigger
- How to audit database using triggers?
- What are database level trigger?
Collections In PL/SQL
- What is collection?
- How to use arrays?
- Using nested tables
- How to use index by value?
- Listing types of collection methods.
- General overview and discussion about DBA Concepts
Nashik Road Branch
- 2nd Floor, Jaideep Sadan , Near Patil Plaza , Canda Corner , College Road , Nashik.