+91 9008553112
info@parkus.in

Review of Basic SQL statements

  • Select
  • Using Arithmetic operators
  • Describing Oracle tables
  • Define Null Values
  • Using alias
  • Concatenation Operator
  • Using Literal Character strings and Q operator
  • Restricting duplicate rows
  • Describe

Restricting and sorting rows

  • Where clause
    • Comparison conditions
    • Is null, Like
    • Escape operator
    • Logical operators
    • Rules for precedence
    • Sorting
    • Substitution variable
    • Double ambrasand
    • Define keyword
    • Set verify on

FUNCTIONS

  • Single row functions
  • Multiple row functions
  • Character functions
  • Case Manipulation function
  • Character Manipulation function
  • Number Functions
  • Date Functions
  • Conversion Functions
  • RR and YY difference
  • NVL Functions
  • COALESCE function
  • Case Expression and Decode Function

Joining Oracle tables

  • Equi-join
  • Non Equi join
  • Left Outer join
  • Right Outer join
  • Full Outer Join
  • Self Join
  • Cross Join
  • Natural Join

Using Sub Queries

  • Single row sub query
  • Multiple row sub query
  • Group functions in sub query
  • Having clause in sub query
  • IN,ANY,ALL operators in sub query

Aggregation in SQL

  • Count(*)
  • Sum
  • Avg
  • Min and max
  • STDDEV
  • VARIANCE
  • Using the group by clause
  • Using Having clause

 

Using SET operators

  • UNION
  • UNION ALL
  • INTERSECT
  • MINUS

 Data Manipulation Language

  • Insert
  • Update
  • Delete
  • Commit
  • Rollback

Data Definition Language

  • Create
  • Alter
  • Rename
  • DROP
  • Truncate

Creating Other Schema Objects

  • View
  • Sequence
  • Index
  • Synonym

PL/SQL Section

Introduction to PL/SQL

  • PL/SQL architecture
  • What is PL/SQL
  • Benefits of PL/SQL
  • Basic PL/SQL Block Structure
  • PL/SQL Block types
  • Creating and Executing Anonymous block

PL/SQL Variables

  • Identify valid and invalid identifiers
  • List the uses of variables
  • Declare and initialize variables
  • List and describe various data types
  • Identify the benefits of using the %TYPE attribute
  • Declare, use, and print bind variables

Writing Executable Statements

  • Identify lexical units in a PL/SQL block
  • Use built-in SQL functions in PL/SQL
  • Describe when implicit conversions take place and when explicit conversions have to be dealt with
  • Write nested blocks and qualify variables with labels
  • Write readable code with appropriate indentations

Interacting with Oracle Server

  • Determine which SQL statements can be directly included in a PL/SQL executable block
  • Manipulate data with DML statements in PL/SQL
  • Use transaction control statements in PL/SQL
  • Use the INTO clause to hold the values returned by a SQL statement
  • Differentiate between implicit cursors and explicit cursors
  • Use SQL cursor attributes
  • Merging rows
  • Types of cursor

Writing Control Structures

  • Identify the uses and types of control structures
  • Construct an IF statement
  • Use CASE statements and CASE expressions
  • Construct and identify different loop statements
  • Use guidelines when using conditional control structures

Working with Composite Data Types

  • Create user-defined PL/SQL records
  • Create a record with the %ROWTYPE attribute
  • Create an INDEX BY table
  • Create an INDEX BY table of records
  • Describe the differences among records, tables, and tables of records

Using Explicit Cursor

  • Distinguish between implicit and explicit cursors
  • Discuss the reasons for using explicit cursors
  • Declare and control explicit cursors
  • Use simple loops and cursor FOR loops to fetch data
  • Declare and use cursors with parameters
  • Lock rows with the FOR UPDATE clause
  • Reference the current row with the WHERE CURRENT OF clause

Handling Exceptions

  • Define PL/SQL exceptions
  • Recognize unhandled exceptions
  • List and use different types of PL/SQL exception handlers
  • Trap unanticipated errors
  • Describe the effect of exception propagation in nested blocks
  • Customize PL/SQL exception messages

Stored procedures and functions

  • Basics of stored procedures
  • Basics of functions
  • Defining stored procedures & functions
  • Function and stored procedures prototypes
  • Passing arguments to functions and stored procedures
  • Recompiling functions and stored procedures
  • Differentiate Function and Stored Procedure