SQL/PL Course Content

Introduction to Oracle SQL

What is RDBMS, Oracle versions, Architecture of Oracle Database Server, Installation of Oracle 12c

Hands-on Exercise – Install Oracle 12c

Using DDL Statements to Create and Manage Tables

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

Retrieving Data Using the SQL SELECT Statement

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

Restricting and Sorting Data

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

General functions

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

Using Single-Row Functions to Customize Output

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

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

OLAP Functions

The various OLAP functions, cube, model clause, roll up and grouping functions

Hands-on Exercise – Working with OLAP commands – Cube, Roll Up, etc.

Using Conversion Functions and Conditional Expressions

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

Displaying Data from Multiple Tables

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

Using Subqueries to Solve Queries

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

Using the Set Operators

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

Manipulating Data using SQL

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

Database Transactions

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

Creating Other Schema Objects

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

Writing Cursor and Conditional Statement

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

Introduction to Explicit Cursor

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 concepts of explicit 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

Exception Handling

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

Writing Subprogram, Procedure and passing parameters

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

Creating PL/SQL Package

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

Advance Package Concepts and functions

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

Introduction and writing Triggers

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

Compound 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

Working with Dynamic SQL

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

Advance level- Scripting

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

FAQ’S

What if I miss one (or) more class?

No need to worry about the classes you missed. We will definitely guide you by having optional classes or by having classes with other batches with the same topic you missed previous classes.

Who is my instructor?

IT professionals who have strong knowledge in technical know how to convey things with the real-time example. Even a layman could understand the concepts which given by our experts.

What are the modes of training offered for this course?

We offer this course in “Live Instructor-Led Online Training” mode. Through this way you won’t mess anything in your real-life schedule. You will be shared with live meeting access while your session starts.

What are the system requirements to work?

Minimum 2GB RAM and i3 processor is required

Can I attend a demo session?

You can get a sample class recording to ensure you are in right place. We ensure you will be getting complete worth of your money by assigning a best instructor in that technology.

How about group discounts (or) corporate training for our team?

We are absolutely loved to talk in-person about group training (or) corporate training. So, please get in touch with our team through “Quick Enquiry”, “Live Chat” or “Request Call-back” channels.

Where do Our Online learners and Trainer’s come from

We are providing online training, One-to-One training with the help of experts. Our learners and trainers are frequently coming from different countries like USA, India, UK, Australia, New Zealand, Canada and UAE. To specify in cities London, Bangalore, California, New York, Pune, Mumbai, Chennai, New Delhi, San Francisco, New Jersey, Texas, Florida, Kolkata, Gurgaon, Berlin and Hyderabad among many.

I have more queries?

If you want to know More Details about Online Training Please Contact us. Or you can share your quires through info@monstercourses.com. Estimated turnaround time will be 24 hours for mails.

Contact us