Oracle SQl Plsql Training

Oracle 11g SQL and PL/SQL Course Contents

ORACLE 11g 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 11g 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)

 Visit  www.futurepointtech.com for course curriculum     

Drop a mail [email protected]  we will get in touch with u 

http://www.futurepointtech.com/oracle-11g-sql-pl-sql-training.html

Visit  www.futurepointtech.com for course curriculum     

Drop a mail [email protected]  we will get in touch with u

http://www.futurepointtech.com/oracle-11g-sql-pl-sql-training.html