Courses

• What is RDBMS?
• Oracle versions
• The architecture of Oracle Database Server
• Installation of Oracle 12c

Labs:

• 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

Labs:

• Create a database table “Person” with two columns (Name, Age),  with a constraint on age not greater than 100; Insert records using an insert query

• List the capabilities of SQL SELECT statements
• Execute a basic SELECT statement

Labs:

• 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

Labs:

• 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()

Labs:

• Use the NULL function to deal with null values in data

• Describe various types of functions (character, number, date, string etc.) available in SQL

Labs:

• 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

Labs:

• 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

Labs:

• 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

Labs:

• 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

Labs:

• 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

Labs:

• Write single-row and multiple- row subqueries

• Describe set operators, UNION [ALL], INTERSECT, MINUS Operators

Labs:

• 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

Labs:

• 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

Labs:

• Begin a transaction; Execute queries to update or insert or delete records; If no error, commit the transaction Else to roll back the transaction and end it

• Views – simple and complex, Sequences, Index, Synonym

Labs:

• Create simple and complex views; Retrieve data from views; Create, maintain, and use sequences, Create and maintain indexes, Create

• 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

Labs:

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

Labs:

• Write an explicit cursor; Use cursor function; Fetch data cursor

• Advance Explicit cursor
• A cursor with parameters, For update Clause
• Where current of Clause, Cursor with sub queries

Labs:

• Use cursor with a 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

Labs:

• Use Predefined exception; Write user-defined exception; Generate and handle the exception; Use a function for trapping an exception

• Overview of subprograms, PL/SQL Subprograms
• What is Procedure?
• The syntax for creating Procedure
• Creating Procedure with a parameter, Example of Passing parameters
• Referencing a public variable from a standalone procedure
• Declaring Subprogram

Labs:

• Create a parameterized procedure, Pass parameters in a procedure call

• 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

Labs:

• 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 a 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

Labs:

• 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

Labs:

• 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

Labs:

• 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

Labs:

• 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

Labs:

• 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

• Performing DML operations on the database
• Streamlining banking data with PL/SQL
• Telecom Company wants to learn more about customers
• An airline company wants to optimize routes & seats