Oracle SQL PlSQL Training at FuturePoint Training
By
FuturePoint

 

Oracle 12c SQL and PL/SQL Course Contents

ORACLE 12c SQL

 

DBMS Concepts

•       What is Database?

•       What is Database System?

•       RDBMS

•       ORDBMS

•       Difference Between DBMS, RDBMS & ORDBMS

 

SQL

•       What is SQL?

•       Using SQL*PLUS, Using SQL Developer

•       SELECT statements

Order by Clause

Distinct Clause

Operators

Comparison Conditions

Where Clause

      

      SINGLE-ROW FUNCTIONS

•       Describe various types of functions that are available in SQL

•       Use character, number, and date functions in SELECT statements

•       Describe the use of conversion functions

•       Identify the available group functions

 

       GROUP FUNCTIONS

•       Describe the use of group functions

•       Group data by using the GROUP BY clause

•       Include or exclude grouped rows by using the HAVING clause

 

      JOINS - Displaying Data from Multiple Tables

•       Access data from more than one table using equijoins and nonequijoins

•       Join a table to itself by using a self-join

•       View data that generally does not meet a join condition by using outer joins

•       Cartesian product from two or more tables

 

SUBQUERIES

•       What is a subqueries

•       Types of problems that subqueries can solve

•       Single-row and multiple-row subqueries

•       Solve problems with correlated subqueries

 

DML Statements

•       Describe each data manipulation language (DML) statement

•       Insert rows into a table

•       Update rows in a table

•       Delete rows from a table

•       Control transactions

 

DDL Statements

•       Database objects

•       Table structure and the data types for columns

•       Create a table

•       Creating constraints at the time of table creation

•       Managing Database Objects with Data Dictionary Views

 

Other Database Objects

•       Simple and complex views

•       Create, maintain, and use sequences

•       Create and maintain indexes

•       Create private and public synonyms

 

        Regular Expression Functions

•       REGEXP_LIKE

•       REGEXP_REPLACE

•       REGEXP_INSTR

•       REGEXP_SUBSTR

•       REGEXP_COUNT

 

ORACLE 12c PLSQL

 

Introduction to PL/SQL

•       Describe the need for PL/SQL

•       Benefits of PL/SQL

•       Different types of PL/SQL blocks

 

Declaring PL/SQL Variables

•       Uses of variables

•       Declare and initialize variables

•       Describe various PLSQL data types

•       Benefits of using the %TYPE attribute

•       Bind variables

 

Executable Statements in a PLSQL block

•       Lexical units in a PL/SQL block

•       Built-in SQL functions in PL/SQL

•       Nested blocks and variables with labels

•       SQL statements can be directly included in a PL/SQL executable block

•       DML statements in PL/SQL transaction control statements in PL/SQL

•       INTO clause to hold the values returned by a SQL statement

•       Iimplicit cursors and explicit cursors and SQL cursor attributes

 

Control Structures

•       An IF statement

•       CASE statements and CASE expressions

•       Loop statements

 

Composite Data Types

•       User-defined PL/SQL records

•       The %ROWTYPE attribute

•       An INDEX BY table and an INDEX BY table of records

•       Describe the differences among records, tables, and tables of records

 

Explicit Cursors

•       Describe the reasons for using explicit cursors

•       Declare and control explicit cursors

•       Use simple loops and cursor FOR loops to fetch data

•       Use of cursors with parameters

•       FOR UPDATE clause and the WHERE CURRENT clause

 

Handling Exceptions

•       PL/SQL exceptions and recognize unhandled exceptions

•       Different types of PL/SQL exception handlers

•       The effect of exception propagation in nested blocks

•       Customize PL/SQL exception messages

 

Stored Procedures and Functions

•       Difference between anonymous block and subprograms

•       Create a simple procedure and invoke it from an anonymous block

•       Create a simple procedure that accepts a parameter

•       Create a simple function

•       Create a simple function that accepts a parameter

•       Differentiate between procedures and functions

 

Packages

•       Describe packages and their components

•       Create a package to group together related variables, cursors, constants, exceptions, procedures, and functions

•       Describe a package construct as either public or private

•       Describe the use of a bodiless package

•       Overload package procedures and functions

•       Use forward declarations

 

Dynamic SQL

•       Describe the execution flow of SQL statements

•       Dynamically build and execute SQL statements using Native Dynamic SQL EXECUTE IMMEDIATE statement

               

Oracle-Supplied Packages

•       The DBMS_OUTPUT package

 

Large Objects

•       Compare  LONG and LOB data types

•       Create and maintain LOB data types

•       Internal and external LOBs

 

Triggers

•       Describe the different types of triggers and their uses

•       Create DML triggers - statement and row triggers

•       Understand trigger-firing sequence

•       Creating database triggers on DDL statements 

 

 

 

 

 

Courses  Offerings 
      

• Android
• AIX Administration
• Business Analyst
• CCNA, CCNP Security, CCIE
• Citrix  XenApp
• Cognos 10 BI & Tm1
• Crystal Reports
• Data Stage
• DB2 DBA
• Dot Net
• DAC
• Google Web Tool Kit
• Hadoop
• IBM Lotus Notes (Development)
• IBM Lotus Notes Domino Server Administration
• IBM Message Broker
• IBM MQ Series
• IBM Tivoli Access Manager
• IBM Web Sphere Application Server Administration (WAS)
• IBM WAS Portal server Development
• IBM Websphere Transformation extender (WTX 8.2)
• Informatica
• I Phone
• Java/J2EE         
• J Query 
• MCITP
• MicroSoft .NET Technologies (VB.NET, C#, ASP.NET)
• Microstrategy
• MicroSoft Business Intelligence
• MicroSoft Dynamics CRM
• OBIEE 11 g
• Oracle APPS –  HRMS
• Oracle APPS –  SCM
• Oracle APPS –  Financial
• Oracle APPS – Technical
• Oracle BI Apps  
• Oracle BI Publisher
• Oracle DBA  11g
• Oracle RAC
• Oracle Fusion SOA 
• Oracle SQL , PL SQL
• People Soft
• PHP
• Perl Scripting
• SAP  SD  ,  BO  , FICO , BI / BW , APO , BPC, BASIS
• Sales Force CRM
• SharePoint Server 2010
• Shell Scripting
• Siebel CRM , EAI, E-Scripting
• SQL Server DBA
• Springs and Hibernate
• Storage Area Network  ( SAN)
• Tera Data
• Testing Tools  - QTP, QC, Load Runner, Selenium, ISTQB
• TIBCO BW, BE, TIBCO I Process
• Tivoli Access Manager & Identity Manager
• Unix Administration
• VN Ware
• WCF, WPF, LINQ,  AJAX, SILVER LIGHT
• Webmethods
• Webservices   , SOAP ( JAVA)