What is RDBMS, Oracle versions, Architecture of Oracle Database Server, Installation of Oracle 12c
Hands-on Exercise – Install Oracle 12c
Categorize the main database objects, Review the table structure, List the data types that are available for columns, Create a simple table, Explain how constraints are created at the time of table creation, Describe how schema objects work
Hands-on Exercise – Create a database table Person with two column (Name, Age) with constraint on age not greater than 100, Insert records using insert query
List the capabilities of SQL SELECT statements, Execute a basic SELECT statement
Hands-on Exercise – Use a basic select statement to retrieve all the records in the Person table
Limit the rows that are retrieved by a query, Sort the rows that are retrieved by a query, Ampersand substitution to restrict and sort output at runtime
Hands-on Exercise – Write a select query to retrieve records where age is more than 60 yrs, Write a select query to sort the records by name, Write a select query to sort the records by age in descending order, Use ampersand substitution to restrict and sort output at runtime
The general functions in SQL, working with any data type and handling Null values, using COALESCE() and Null function, Constructing and executing SQL query that applies the NUL, NUL1, NUL2 and COALESCE()
Hands-on Exercise – Use NULL function to deal with null values in data
Describe various types of functions (character, number, date, string etc.) available in SQL
Hands-on Exercise – Create a table with columns of type char, number and date, Use character, number, and date functions in SELECT statements
Large object functions – BFILENAME, EMPTY_BLOB, EMPTY_CLOB, Aggregate or Group functions – COUNT, COUNT(*),MIN,MAX,SUM,AVG,etc…,Group BY Clause, HAVING Clause
Hands-on Exercise – Count records based on a condition, Use Count(*) to know the count of all records, Find Max, Min, Sum, Avg
The various OLAP functions, cube, model clause, roll up and grouping functions
Hands-on Exercise – Working with OLAP commands – Cube, Roll Up, etc.
Describe various types of conversion functions that are available in SQL, Conditional expressions in a SELECT statement
Hands-on Exercise – Group data by using the GROUP BY clause, Include or exclude grouped rows by using the HAVING clause
Joins, Inner Join, Outer Join, Left Join, Right Join, Equijoins and Non-equijoins
Hands-on Exercise – Write SELECT statements to access data from more than one table using equijoins and non equijoins, Join a table to itself by using a self-join, View data that generally does not meet a join condition by using outer joins, Generate a Cartesian product of all rows from two or more tables
Define subqueries, Describe the types of problems that the subqueries can solve, List the types of subqueries
Hands-on Exercise – Write single-row and multiple-row subqueries
Describe set operators, UNION [ALL], INTERSECT, MINUS Operators
Hands-on Exercise – Use a set operator to combine multiple queries into a single query Control the order of rows returned
Describe data manipulation language (DML) statement, Insert, Update, Delete Statements, Control transactions
Hands-on Exercise – Insert rows into a table, Update rows in a table, Delete rows from a table
What is a database transaction, Properties of a transaction (Atomic, Consistent, Isolated, Durable – ACID), Avoiding error/fault in manipulating database records using transaction
Hands-on Exercise – Begin a transaction, Execute queries to update or insert or delete records, If no error, commit the transaction Else roll back the transaction and end it
Views – simple and complex, Sequences, Index, Synonym
Hands-on Exercise – Create simple and complex views, Retrieve data from views, Create, maintain, and use sequences, Create and maintain indexes, Create private and public synonyms
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.
Hands-on Exercise – Use Boolean condition on a select query, Use logic table, Create 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
Hands-on Exercise – Write an explicit cursor, Use cursor function, Fetch data cursor
Advance Explicit cursor, Cursor with parameters, For update Clause, Where current of Clause, Cursor with sub queries
Hands-on Exercise – Use cursor with subquery, Use Where Current clause to retrieve data
Handling Exception, Handling Exception with PL/SQLPredefined Exceptions, User Defined Exceptions, Non-Predefined Error, Function for trapping Exception, Trapping user-defined Exception, Raise Application Error Procedure
Hands-on Exercise – Use Predefined exception, Write user defined exception, Generate and handle exception, Use a function for trapping an exception
Overview of subprograms, PL/SQL Subprograms, What is Procedure, Syntax for creating Procedure, Creating Procedure with parameter, Example of Passing parameters, Referencing a public variable from a standalone procedure, Declaring Subprogram
Hands-on Exercise – Create a parameterized procedure, Pass parameters in a procedure call, Access a public variable from a standalone procedure
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, Guide lines for deploying packages
Hands-on Exercise – Create a package, Deploy the created package
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
Hands-on Exercise – Use supplied package, Use native dynamic SQL, Use DBMS-SQL package, Use DBMS-DDL package, Submit a job
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
Hands-on Exercise – Create a DML statement trigger, Use old and new qualifiers, Manage a trigger using the alter & drop SQL statement, Test the created 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
Hands-on Exercise – View a trigger’s information, Use a compound trigger structure for views, Use a compound trigger to resolve the mutating table error, Create triggers on system events, Use LOGON and LOGOFF triggers, Call statements in 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
Hands-on Exercise – Use the executive immediate statement, Use native dynamic SQL to compile PL/SQL code, Create DBMS-SQL with a DML statement, Create a DBMS-SQL package subprograms
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
Hands-on Exercise – Query direct object dependencies, Display direct and indirect dependencies, Set the remote dependencies mode parameter, Recompile PL/SQL program unit, Edit a procedure and recompile it
Project 1 : Performing DML operations on database
Industry : General
Problem Statement : How to manipulate data with 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 2 : Streamlining banking data with PL/SQL
Industry : Banking and finance
Problem Statement : Get information on customer accounts spread across geographies and the history of transactions done.
Description : In this project you will create a model to capture details of bank, its different branches and store customer information based on geography. With this model you should be able to identify a customer having multiple accounts in different geographies as well as what transactions have been done in the past at all the branches.
Project 3 : Creating Detailed Patient Report
Industry : Healthcare
Problem Statement : How to analyze the condition of various patients based on disparate data sets
Description : In this PL SQL project you will be working on creating tables and columns based on various entities and insert the sample data. Write SQL queries to extract the data that can be used for analysis purpose. Based on that you shall create a detailed report about the patient, medications taken, any adverse conditions and so on.
Project 4 : Working on university data set
Industry : Education
Problem Statement : How to improve the teaching process of a university
Description : In this PL SQL project you have the Student and Staff data in tables. You will create the Course table and insert data into it. Then you will join the three tables and analyze the data. This includes coming up with all the courses with corresponding students and the teachers associated. Then finally you will create a detailed report on the teaching structure and ways to improve it.
Intellipaat provides industry-leading PL/SQL training. This comprehensive PL/SQL training will help you master the creation, implementation and management of very large database applications. You will learn PL/SQL programming, procedural language, creating subprograms and understand section and syntax query and Data Manipulation Language. The entire training is aimed towards clearing the Oracle PL/SQL Developer Certified Associate exam.
You will be working on real-time projects and step-by-step assignments that have high relevance in the corporate world, and the course curriculum is designed by industry experts. Upon the 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 the latest version at no extra fee. Hence, it is clearly a one-time investment.
This course is designed for clearing the Oracle PL/SQL Developer Certified Associate exam and learning PL/SQL as per the needs of the industry. The entire course content is in line with the certification program and helps you clear the certification exam with ease and get the best jobs in 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 scenarios, thus helping you fast-track your career effortlessly.
At the end of this training program, there will be a quiz that perfectly reflects the type of questions asked in the certification exam and helps you score better marks.
Intellipaat Course Completion Certificate will be awarded upon the completion of the project work (after the expert review) and upon scoring at least 60% marks in the quiz. Intellipaat certification is well recognized in top MNCs like Ericsson, Cisco, Cognizant, Sony, Wipro, Standard Chartered, TCS, Genpact, Tata Communication, etc.