Oracle Database is an object-relational database management system (ORDBMS) developed and marketed by Oracle Corporation. Oracle Database is commonly referred. In the early 70’s Dr. EFT Codd, a computer scientist, invented the relational model for database management. The relational model deals with many issues caused by the flat file model or the FMS (File Management System). According to his model, instead of combining everything in a single structure, data is organized in entities and attributes to make it more effecient. Oracle has the SQL (Structured Query Language) and also support PL/SQL (Procedural Language using SQL).
This course is meant for everyone who would like to visualize how information gets stored and delivered to end users and a solid understanding of the Oracle products used in professional job roles.
- Application Developer
- Database Developer
- Support Analyst
Introduction to DBMS
• DBMS, Data, Information, Database
Data models – FMS/ HDBMS/
NDBMS/ RDBMS/ ORDBMS
• E-R Diagram, Normalization, Codd Rules
• RDBMS Packages • Versions
SQL (Structured Query Language)
• SQL and SQL*Plus Commands
• Types of commands in SQL
• How to login into SQL
• HR user, locking & unlocking user
Retrieving data
• Select statement usage
• Alias names – columns & expressions
• Applying filters using operators
• Operators – Relational/ Logical / Special
• Distinct clause, Order by clause
• Sorting NULL values
Working with tables
• Datatypes in Oracle
• Naming conventions; Rules – tables/columns
• Creating Tables in different methods
DML operations
• Insert data in different methods
• Update / Delete data
DDL commands
• Alter / Truncate / Rename / Drop table
• Recyclebin concept • Flashback / Purge
Table Constraints
• Data Integrity and importance
• Not Null/Unique/Primary Key/ Check
• References/Foreign Key
• Add/enable/disable/drop constraints
• Composite primary key/unique key
• About ‘User_constraints’ table
TCL commands (Transaction Handling)
• About login session • Redo log files
• Commit / Rollback / Savepoint
• Working with multiple savepoints
DCL Commands
• User management, SYSTEM user
• Create user, Grant basic privileges
• Change password, lock/unlock user
• Grant/Revoke a user as DBA
• Data sharing between users
• Grant and Revoke commands
• Accessing another user data
• Dead lock situations
About ‘User_tab_privs_made’, ‘User_tab_privs_recd’
Database functions
• Group / Row / Numeric / String
• Conversion functions
• Dates & date formats
• Date functions, Misc. functions
• Pseudo columns
Data grouping
• Group by / Having clause
• ‘where’ versus ‘having’ clause
• Using expressions in group by
Set operators
• Union/ Intersect/ Minus/ Union All
Subqueries
• Simple / Multiple row subquery
• Nested / Co-related subquery
Joins tables
• Simple join (equi and non-equi)
• Self/ inner / Outer join (Left/Right/Full)
• Cartesian/Cross join
• ANSI Standard of joins
Oracle Database objects
• Synonyms • Views
• Sequences • Indexes
PLSQL
PL/SQL architecture
• PL/SQL engine
• Structure of a PL/SQL program
• Different section of a program
• Datatypes in PL/SQL
• Operators, I/O operations
• Write expressions, simple program
• Extracting data from tables
Control statements
• If-Then-Else – Simple, Nested
• Compound conditions (and/or/not)
• Case – End Case
• Loops – For/ While/ Simple/ ForALL
Exception Handling
• Introduction • Types of Exceptions
• Runtime errors vs Exceptions
• System / User defined exceptions
• Using multiple exceptions
• Raise_Application_Error()
Cursor management
• Cursor types – Implicit / Explicit
• Cursor attributes
• Declaring, opening, closing
• Fetching, checking End of cursor
• Parameterized cursor
• For loops in cursor processing
• DML operations using cursors
• Cursor using joins
Sub programs
Stored Procedures
• Creating a simple procedure
• Compiling, verifying errors
• Executing procedure from SQL prompt
• Calling procedure in another PL/SQL program
• Procedure including DML operations
• Procedures with parameters
• Types of parameters (IN, OUT & IN OUT)
• Cursors in a procedure
User defined Functions
• Writing a user’s function
• About ‘Return’ statement
• Compiling, verifying errors
• Executing from SQL prompt
• Functions with parameters (IN, OUT & IN OUT)
Packages
• Create package specification / body
• Calling the elements of a package
• Adv of package over procedure/function
• Implementing Polymorphism
• Function overloading
• Procedure overloading
Database Triggers
• Types of triggers
• Creating before/after triggers
• :OLD, :NEW reference for data references
• DML operations using triggers
• ‘Instead of’ triggers • Triggers Vs constraints
• Transaction auditing using triggers
No comments:
Post a Comment