Oracle Database For Beginners

Topics covered:

• How to use SELECT command to retrieve data from tables

• How to uses ORDER BY and WHERE clauses

Topics covered:

• How to use DML commands like UPDATE, DELETE and INSERT.

• Understand what is a transaction and how to manage it.

Topics covered:

• Number functions

• Date data type

• Date arithmetic

• Date functions

Topics covered:

• String functions

Topics covered:

• Automatic type conversion

• Conversion functions

• Miscellaneous functions

Topics covered:

• How to create a table using create table command

• Column and table constraints

• Getting information about user tables

Topics covered:

• Adding a new column to existing table

• Modifying existing column attributes

• Dropping a constraint or a column

• Dropping table using drop table command

Topics covered:

• Group functions

• Group by clause to group rows

• Having clause to filter groups

Topics covered:

• How to join tables using NATURAL JOIN

• Joining USING clause

• Joining using ON clause

• Joining more than two tables

• Self join

• Outer join

Topics covered:

• What is subquery and how to use it

• Multiple and nested subqueries

• Using subqueries in DML commands

• Correlated subqueries

• Correlated subqueries in DML commands

Topics covered:

• What is a view and its advantages

• How to create a view and use it

• Modifying base table through View

• Using WITH CHECK OPTION

• What is Materialized view and its advantages

Topics covered:

• What is Index and its importance

• How to create an index

• When Oracle does not use index

• How to create a sequence and use it

Topics covered:

• Difference between System privileges and Object privileges

• Different types of object privileges

• Using GRANT and REVOKE commands

• What is a ROLE and its usage

Topics covered:

• What is a regular expression

• Meta characters used in regular expressions

• Functions related to regular expression like REGEXP_LIKE

  
  

Topics covered:

• What is PL/SQL

• Structure of PL/SQL blocks

• How to use INTO options with SELECT command in PL/SQL

Topics covered:

• How to use IF statements and its variants

• Using CASE expression and CASE statement

Topics covered:

• How to use LOOP and ENDLOOP

• WHILE Loop

• FOR loop

• EXIT and Continue statements

Topics covered:

• What is an exception and how to handle it

• SQLCODE and SQLERRM functions

• Nested blocks

• User-defined exception

• Exception propagation

• Re-raising exceptions

Topics covered:

• What is explicit cursor and how to use it

• Cursor for loop

• Attributes of explicit cursor

• Using input arguments with cursor

• Use of implicit cursor

  
  

Topics covered:

• What is a stored procedure and its advantages

• Creating and calling stored procedures

• Creating and calling stored functions

• How to get errors related to program unit's creation

• How to access source code of program units

• EXECUTE privilege

Topics covered:

• Parameter modes - in, out, in out

• Parameter notations - position, named and mixed

• Invoker rights

• What is a package and how to create its specification and body

• Calling members of package

• Oracle supplied packages

Topics covered:

• What is a trigger and its usages

• Execution flow of triggers

• How to create row-level trigger to implement business rules

• Using  correlation names NEW and OLD

• How to create statement-level triggers

  
  

Topics covered:

• How to use instead-of triggers

• Compound trigger and its usage

• How to enable and disable triggers

Topics covered:

• What is index-by table and how to use it

• What is dynamic sql and its usage

• Executing non-queries using dynamic sql

• Executing non-query using parameters with dynamic sql

• Executing single row query with dynamic sql

• Executing multi-row query with cursor in dynamic sql

Topics covered:

• How to use correlated subquery to get top-n rows like top 5 highly paid employees 

• How to use FETCH and OFFSET clauses introduced in Oracle Database 12c

• How to use multi-column correlated subquery