Oracle SQL / PLSQL

Duration : 30 Hrs

SQL  short for Structured Query Language) is a special-purpose programming language designed for managing data held in a relational database management systems (RDBMS).

Originally based upon relational algebra and tuple relational calculus, SQL consists of a data definition language and a data manipulation language. The scope of SQL includes data insert, query, update and delete, schema creation and modification, and data access control. Although SQL is often described as, and to a great extent is, a declarative language (4GL), it also includes procedural elements.

SQL was one of the first commercial languages for Edgar F. Codd’s relational model, as described in his influential 1970 paper “A Relational Model of Data for Large Shared Data Banks”.Despite not adhering to the relational model as described by Codd, it became the most widely used database language.

Procedural Language/Structured Query Language (PL/SQL) is Oracle Corporation’s procedural extension language for SQL and the Oracle relational database. PL/SQL’s general syntax resembles that of Ada or Pascal.

PL/SQL is one of three key programming languages embedded in the Oracle Database, along with SQL itself and Java.

PL/SQL is available in Oracle Database (since version 7), TimesTen in-memory database (since version 11.2.1), and IBM DB2 (since version 9.7). PL/SQL supports variables, conditions, loops and exceptions. Arrays are also supported, though in a somewhat unusual way, involving the use of PL/SQL collections.

Implementations from version 8 of Oracle Database onwards have included features associated with object-orientation.

Once the program units have been stored into the database, they become available for execution at a later time.

DBMS Concepts
  • What is Database?
  • What is Database System?
  • Difference Between DBMS, RDBMS & ORDBMS
  • What is SQL?
  • Using SQL*PLUS, Using SQL Developer
  • SELECT statements
Order by Clause Distinct Clause Operators Comparison Conditions Where Clause
  • 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
  • Describe the use of group functions
  • Group data by using the GROUP BY clause
  • Include or exclude grouped rows by using the HAVING clause
  • GROUP BY with ROLLUP and CUBE Operators
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
  • 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
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
  • 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
  • Compare Native Dynamic SQL with the DBMS_SQL package approach
Oracle-Supplied Packages
  • The DBMS_OUTPUT package
  • The UTL_FILE to direct output to operating system files
  • The HTP package to generate a simple Web page
  • The main features of UTL_MAIL
Large Objects
  • Compare LONG and LOB data types
  • Create and maintain LOB data types
  • Internal and external LOBs
  • 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
Other Courses Offered
  • 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
  • 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
  • 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
  • Webmethods
  • Webservices , SOAP ( JAVA)


No reviews

Post Review

Review Title
Review Content
View All Categories

Quick Enquiry

Technology Portfolio