SQL,PLSQL

Day 1 Day 2

Introduction to SQL constructs

  • Review of Basic SQL statements
  • Select
    Project
    Join
    Describing Oracle tables
    Exercise – investigate the EMP database

    • Restricting row returns

    Where clause
    Exercise – write a sample query in the emp database
    Complex Boolean logic in SQL
    ROWID restrictions
    Exercise – write a sample query in the emp database

    Using SQL*Plus

    • Creating basic reports
    Exercise – format a simple query in the emp database
    • Using the set commands
    Adjusting line output
    Setting pagesize and linesize
    Echo
    Feedback
    Verify
    termout
    Exercise – set the SQL*plus environment
    • Column wrapping
    Exercise – add column statements
    • Creating breaks and summaries
    Exercise – add breaks and summaries
    • Adding prompts to queries
    Exercise – parameterize a SQL*Plus script

    Joining Oracle tables


    • Equi-join
    Exercise – write an equi-join
    • Outer join
    Exercise – write an outer join
    • Hiding joins by creating views
    Exercise – create a view of a join
    • Using IN, NOT IN, EXISTS and NOT EXISTS
    • Subqueries
    Exercise – write a subquery
    • Correlated subquery
    • Non-correlated subqueries

    Advanced SQL operators


    • Between operator
    • IN and NOT In operators
    • Sub-queries
    • EXISTS clause
    • Using wildcards in queries (LIKE operator)

    Aggregation in SQL


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

    SQL access methods


    • Review of Basic joining methods
    • Merge join
    • Hash Join
    • Nested Loop join
    • Advanced SQL operators
    • Between operator
    DAY 3 DAY 4

    SQL Tuning


    • Introduction to rule-based optimization
    • Introduction to cost-based optimization
    • Collecting table and index statistics
    • Using column histograms
    • Changing the default optimizer modes
    • Using TKPROF
    • Using SQL*Trace
    • SQL reusability within the library cache
    • Table high-water mark
    • Table striping and table partitions
    • Using EXPLAIN PLAN
    • Interpreting EXPLAIN PLAN Output
    • Using indexes to improve performance
    • Identifying full-table scans
    • Re-writing SQL queries
    • Using hints to improve SQL performance
    • Using parallel query to improve performance
    • Tuning sub-queries

    PL/SQL Section


    1:Basics of PL/SQL
    • PL/SQL architecture
    • PL/SQL and SQL*Plus
    • PL/SQL Basics
    Variables
    Constants
    Datatypes
    Error messages – user_errors and show errors
    • PL/SQL wrapper utility

    2:PL/SQL structures


    • Simple blocks
    • Control structures
    • PL/SQL records
    • Recognizing the Basic PL/SQL Block and Its Sections
    • Describing the Significance of Variables in PL/SQL
    • Distinguishing Between PL/SQL and Non-PL/SQL Variables
    • Declaring Variables and Constants
    • Executing a PL/SQL Block

    Error checking – exception handling


    • Defining exceptions
    • Using the when others clause
    • Ensuring complete error checking
    • Passing error messages to calling routine

    4. Boolean logic in PL/SQL


    • Identifying the Uses and Types of Control Structures
    • Constructing an IF Statement
    • Constructing and Identifying Different Loop Statements
    • Controlling Block Flow Using Nested Loops and Labels
    • Using Logic Tables
    • If-then-else structure
    • Testing for numbers characters and Booleans

    Cursors in PL/SQL


    • Cursor basics
    • Using a cursor for a multi-row SQL query

    6. Iteration in PL/SQL


    • For loop
    • While loop
    DAY 5

    7. PL/SQL tables


    • Defining PL/SQL tables
    • Reasons to use PL/SQL tables
    • Populating a PL/SQL table
    • Retrieving from a PL/SQL table

    8. Dynamic SQL in PL/SQL


    • Introduction to the dbms_sql package
    • Creating a dynamic SQL statement

    9. Nested blocks in PL/SQL


    • Creating nested blocks
    • Understanding scope in nested blocks
    >

    10. Triggers in PL/SQL


    • Triggers and database events
    • Defining a trigger
    • Timing a trigger
    • Enabling and disabling a trigger

    11. Stored procedures, functions and packages


    • Basics of stored procedures
    • Basics of functions
    • Basics of packages
    • Defining stored procedures & functions
    • Function and stored procedures prototypes
    • Passing arguments to functions and stored procedures
    • Recompiling functions and stored procedures
    • Pinning packages in the SGA with dbms_shared_pool.keep
    • Package forward declaration
    • Package dependency
    • Package overloading
    • Listing package information

    Bulking in PL/SQL


    • Bulk queries
    • Bulk DML (forall statement)
    • Using cursor attributes
    • Analyzing impact of bilk operations