This is a Combo Training Course that provides you full proficiency in working with Oracle Database. This includes understanding the PL/SQL, subprogram, section and syntax query, DML and advanced DML with scripting. Study Oracle DBA Architecture, database structures, security schema objects and more.
Introduction to database fundamentals, relational database management system, the various RDBMS products and the architecture of DBMS.
The basics of RDBMS, the various versions of Oracle, Editions of Oracle database, different Oracle products, tools for Oracle Application Development, the Oracle Server and architecture of Oracle database server.
The hardware and software requirements for installation of Oracle 12c on Windows 7/WindowsX.
The various Query languages used in Oracle, role of SQL in RDBMS, the concepts of SQL, various features of SQL, writing SQL statements, the various SQL statements and SQL tools.
The basic constructs of SQL and basic building blocks, the various data types, creation of tables, the naming conventions for column and table, data loading into tables, the significance of NULL, data retrieving from the tables.
The various format models, number format models, date format models and the format model modifiers.
The concept of literals, the explicit numeric string, Boolean or character value not represented by identifier, numeric, text, interval and numeric literals.
Comparing database schema and user, finding out how objects are stored in the database, getting to know table, the various kinds of database objects, non-objects, database object names rules.
The various SQL operators, DQL/DRL select command, relational and SQL operators used in where clause, IS NULL, IS NOT NULL, BETWEEN, NOT BETWEEN, IN, NOT IN, LIKE, NOT LIKE
Finding out the appropriate applications of single row functions in query statements, classification of functions as single row or multi-row functions, differentiating between the single row and multi-row function, comparing the results reached by the two functions.
The different conversion functions, implicit and explicit conversion functions, To_char, To_Number, To_Date Conversion Functions
The join clause in SQL for combining rows form tow or more tables, most common joins including inner, outer, cross, natural, non equi and self-join.
The various sub queries, multiple sub queries and correlated sub queries.
The concept of analytic function, the syntax of analytic functions, breaking down the result set into partitions or groups, specifying the order of the records in the partition, Row _Number, Rank and Dense_Rank.
The general functions in SQL, working with any data type and handling Null values, using COALESCE() and Null function, the deployment of general functions for dealing with Null values in data, constructing and executing SQL query that applies the NUL, NUL1, NUL2 and COALESCE() single row functions.
SQL functions: Large object functions – BFILENAME, EMPTY_BLOB, EMPTY_CLOB, Aggregrate or Group functions – COUNT, COUNT(*),MIN,MAX,SUM,AVG,etc…,Group BY Clause, HAVING Clause
Set operators: UNION, UNION ALL, INTERSECT, MINUS, Order of execution, Order by clause.
The concept of Pseudo Column, Row ID, Row Num, various case expression, simple case expression, searched case expression and Decode functions.
The various OLAP functions, cube, model clause, roll up and grouping functions.
SQL Cursor, SQL Cursor Attributes., Controlling PL/SQL flow of executions, IF Statement, Simple IF Statement, IF-THAN-ELSE Statement Execution Flow, IF-THAN-ELSE Statement, IF-THAN-ELSIF Statement, Logic Tables, Boolean Conditions., Iterative controlling loop statement, Nested Loops and Labels.
Writing Explicit cursors, About cursors, Explicit cursor functions, Controlling explicit cursor, Opening the cursor, Fetching Data cursor, Cursor and records, Cursor for loop using sub queries
Advance Explicit cursor, Cursor with parameters, For update Clause, Where current of Clause, Cursor with sub queries, Handling Exception, Handling Exception with PL/SQL, Predefined Exception
Predefined Exceptions, User Defined Exceptions, Non-Predefined Error, Function for trapping Exception, Trapping user-defined Exception, Raise Application Error Procedure
Overview of subprograms, PL/SQL Subprograms, What is Procedure, Syntax for creating Procedure, Creating Procedure with parameter, Example of Passing parameters, Declaring Subprogram
Procedures – Developing procedures, removing procedures, Benefits of subprograms, Procedural Parameter Modes, Actual vs. Formal Parameters, Methods for passing parameters, Function – Creation, overview, syntax, Restrictions on calling from SQL, Advantages of user defined functions in SQL expressions, Comparing procedures and functions, Benefits of stored procedures and functions, Detecting compilation errors
PL/SQL records, Using Pl/SQL Table method and example, Creating PL/SQL Table, Packages – Objective, overview, component, developing, removing, advantages, Creating the package specification/example, Declaring Public construct, Public and private construct, Invoking package construct, Referencing a public variable from a standalone procedure, Guide lines for deploying packages
Overloading, Using forward declaration, One time only procedure, Package functions, User define package function, Persistent state of package function, Persistent state of package variable, Controlling the persistent state of package cursor, Purity end, Using supplied package, Using native dynamic SQL, Execution flow, Using DBMS-SQL package, Using DBMS-DDL package, Submitting jobs, Interacting with operating system links
Triggers – Definition, objective and its event type, Application & database triggers, Business application scenarios for implementing triggers, Define DML triggers, Define Non – DML triggers, Triggers event type & body, Creating DML triggers using the create triggers statement, Define statement level triggers v/s low level triggers, Triggers firing sequence: single row manipulation, Creating a DML statement triggers, Using old and new qualifiers, Old and new qualifiers, Instead of triggers, Managing triggers using the alter & drop SQL statement, Testing triggers
Testing triggers, Viewing trigger information, Describe user triggers, What is a compound trigger and working with it, Compound trigger structure for tables, Timing-point sections of a table compound trigger, Compound trigger structure for views, Trigger restrictions on mutating tables, Compound trigger restrictions, Using a compound trigger to resolve the mutating table error, Creating triggers on system events, LOGON and LOGOFF triggers example, Call statements in triggers, Benefits of database-event triggers, System privileges required to manage triggers
Dynamic SQL-objectives, Describe execution flow of SQL statements, Dynamic SQL with a DDL statement-example, Working with dynamic SQL, Native Dynamic SQL(NDS), Using the executive immediate statement-example, Using native dynamic SQL to compile PL/SQL code, Using DBMS-SQL with a DML statement, Using the DBMS-SQL package subprograms, Parameterized DML statement
Managing Dependencies, Objectives, overview of schema object dependencies, Direct local dependencies, Querying direct object dependencies, Displaying direct and indirect dependencies, Fine-Grained dependency management, Changes to synonym dependencies, Maintaining valid PL/SQL program units and views, Object re-validation, Concepts of remote dependencies, Setting the remote dependencies mode parameter, Recompiling PL/SQL program unit, Packages and Dependencies, Successful and unsuccessful recompilation, Recompiling procedures
What is the Oracle database? How it functions? Installation of Oracle database.
The Architecture of Oracle server, the key components – Oracle Instance and Oracle Database, the various file types – data files, control files, redo log files.
The basics of Oracle Storage structures, logical storage units like tablespaces, data blocks, extents and segments, database Schemas, Schema Objects, Operating System blocks, table statement and way to check “create table”.
The Oracle Instance consists of background processes and memory structures, learn about the Oracle process and memory architecture, shared pool, redo log buffer, and buffer cache.
The Oracle background process errors can be monitored using the Trace Files and Alert Logs, learn how this can be deployed for Oracle Administration and support.
The methodology of starting a database instance, initializing parameter files, preparing for startup, serving user requests, understanding of the Server process and user process.
Learn about the powerful Oracle database security features, data privacy, regulatory compliance, “create user” process, altering and dropping users, generating profiles and limiting resources, auditing, activity monitoring and blocking.
Understanding of Database Schema objects, the various types available like views, tables, clusters, indexes, sequence, database links, packages and procedures, learning how data is stored in database tables, creating of temporary tables and external tables.
Mastering the database Schema objects, learning Materialized View, deploying user-generated schema object for generating sequence, the balanced search tree index structure for placing and locating files, learn about data concurrency and data consistency in multi-user databases, the concepts of Locking and Deadlocks.
Study the client/server Network Environment, configuring the network, connecting to the database, the Oracle Network Environment and database link.
Understand how Oracle database ecosystem backup and recovery is deployed, the database testing, database standby, Media recovery options, backup in offline mode.
Learn the significance of Oracle Recovery Manager, how it deploys performance, management and restoration of entire databases, learn about SQL Loader, Oracle Data Pump for import and export and support for External Table.
The Oracle Data Dictionary contains information related to database privileges, objects, users, and roles, learn Dynamic Performance Table for identifying instance-level problems, get complete knowledge to work with Oracle DBA.
Introduction to database tuning, ensuring higher throughput, lowering response time of time-critical applications, optimizing the storage space, SQL scripts, deploying Index Selectivity, Index Statistics and Table Statistics for tuning the database, understanding Oracle Chained Rows and Locks.
Deep diving into Oracle Database Tuning, shared pool tuning, improving performance using Data Dictionary, tuning of Data Dictionary, deploying PL/SQL code, code reuse, database buffer.
Project – DML
Topics – In this project you will learn about the Data Manipulation Language (DML), understand all about the DML triggers which are a special type of stored procedures that are automatically executed when DML operation is performed. You will learn about the various DML operations like INSERT, UPDATE, or DELETE which when fired on a Table or View can activate a DML Trigger. This project involves working with database and application Triggers, deploying the Triggers for various business applications and testing of the DML Triggers.
Project – Database Tuning
– This project is involved with working on the Oracle Database for deploying the database performance tuning which includes optimizing and homogenizing the database performance. It is associated with query tuning but also includes DBMS selection, and configuration of the database environment. Some of the tasks that you will perform are–
This Intellipaat PL/SQL, DBA combo training provides you proficiency in working with Oracle PL/SQL, Oracle DBA, master DML, advanced DML, learn about subprograms, database structures, security schema objects and more. This entire course content is in line with the requirements of clearing the Oracle PL/SQL Developer Certified Associate Exam and the Oracle Database Administrator Certified Associate Exam.
You will be working on real time Oracle PL/SQL & DBA projects that have high relevance in the corporate world, step-by-step assignments and curriculum designed by industry experts. Upon completion of the training course you can apply for some of the best jobs in top MNCs around the world at top salaries. Intellipaat offers lifetime access to videos, course materials, 24/7 Support, and course material upgrading to latest version at no extra fees. Hence it is clearly a one-time investment.
Intellipaat basically offers the self-paced training and online instructor-led training. Apart from that we also provide corporate training for enterprises. All our trainers come with over 12 years of industry experience in relevant technologies and also they are subject matter experts working as consultants. You can check about the quality of our trainers in the sample videos provided.
If you have any queries you can contact our 24/7 dedicated support to raise a ticket. We provide you email support and solution to your queries. If the query is not resolved by email we can arrange for a one-on-one session with our trainers. The best part is that you can contact Intellipaat even after completion of training to get support and assistance. There is also no limit on the number of queries you can raise when it comes to doubt clearance and query resolution.
The Intellipaat self-paced training is for people who want to learn at their own leisurely pace. As part of this program we provide you with one-on-one sessions, doubt clearance over email, 24/7 Live Support, lifetime LMS and upgrade to the latest version at no extra cost. The prices of self-paced training can be 75% lesser than online training. While studying should you face any unexpected challenges then we shall arrange a Virtual LIVE session with the trainer.
We provide you with the opportunity to work on real world projects wherein you can apply your knowledge and skills that you acquired through our training. We have multiple projects that thoroughly test your skills and knowledge of various aspect and components making you perfectly industry-ready. These projects could be in exciting and challenging fields like banking, insurance, retail, social networking, ecommerce, marketing, sales, high technology and so on. The Intellipaat projects are equivalent to six months of relevant experience in the corporate world.
Yes, Intellipaat does provide you with placement assistance. We have tie-ups with 80+ organizations including Ericsson, Cisco, Cognizant, TCS, among others that are looking for skilled & quality professionals and we would be happy to assist you with the process of preparing yourself for the interview and the job.
Yes, if you would want to upgrade from the self-paced training to instructor-led training then you can easily do so by paying the difference of the fees amount and joining the next batch of classes which shall be separately notified to you.
Upon successful completion of training you have to take a set of quizzes, complete the projects and upon review and on scoring over 60% marks in the qualifying quiz the official Intellipaat verified certificate is awarded.The Intellipaat Certification is a seal of approval and is highly recognized in 80+ corporations around the world including many in the Fortune 500 list of companies.
This course is designed to clear the following certification exams:
Oracle PL/SQL Developer Certified Associate Oracle Database Administrator Certified Associate The entire training course content is in line with respective certification program and helps you clear the requisite certification exam with ease and get the best jobs in the top MNCs.
As part of this training you will be working on real time projects and assignments that have immense implications in the real world industry scenario thus helping you fast track your career effortlessly.
At the end of this training program there will be quizzes that perfectly reflect the type of questions asked in the respective certification exams and helps you score better marks in certification exam.
Intellipaat Course Completion certificate will be awarded on the completion of Project work (on expert review)and upon scoring of at least 60% marks in the quiz. Intellipaat certification is well recognized in top 80+ MNCs like Ericsson, Cisco, Cognizant, Sony, Mu Sigma, Saint-Gobain, Standard Chartered, TCS, Genpact, Hexaware, etc.
You will get Lifetime access to high quality interactive tutorials along with life time access to complete Course Material .There will be 24/7 access to video tutorials with email support. If you stuck in any unexpected problem we will provide online interactive sessions with trainer for issue resolving.
We provide 24X7 support by email for issues or doubts clearance for Self-paced training.
In online Instructor led training, trainer will be available to help you out with your queries regarding the course. If required, the support team can also provide you live support by accessing your machine remotely. This ensures that all your doubts and problems faced during labs and project work are clarified round the clock.
"PMI®", "PMP®" and "PMI-ACP®" are registered marks of the Project Management Institute, Inc.
The Open Group®, TOGAF® are trademarks of The Open Group.
The Swirl logoTM is a trade mark of AXELOS Limited.
ITIL® is a registered trade mark of AXELOS Limited.
PRINCE2® is a Registered Trade Mark of AXELOS Limited.
Certified ScrumMaster® (CSM) and Certified Scrum Trainer® (CST) are registered trademarks of SCRUM ALLIANCE®
Professional Scrum Master is a registered trademark of Scrum.org