Oracle 9I Sql Online Training

[itonlinetraining]

[center][b][email="[email protected]"][email protected][/email][/b][/center]
[center][b]Contact No: 8861201149[/b][/center]


[center][size=5][color=#FF0000][b]Oracle9i SQL[/b][/color][/size][/center]

WORKSHOP Details:

Agenda: Oracle 9i SQL
Modus-Operandi: 3 days

[indent=1][b]Day 1:[/b][/indent]

[b]1. Writing Basic SQL Select Statements [/b]
[indent=1.575]· List the capabilities of SQL SELECT statements[/indent]
[indent=1.575]· Execute a basic SELECT statement[/indent]
[indent=1.575]· Differentiate between SQL statements and iSQL*Plus commands[/indent]

[b]2. Restricting and Sorting Data [/b][list]
[*]Limit the rows retrieved by a query
[*]Sort the rows retrieved by a query
[/list]

[b]3. Single-Row Functions [/b][list]
[*]Describe various types of functions available in SQL
[*]Use character, number, and date functions in SELECT statements
[*]Use conversion functions
[/list]

[b]4. Displaying Data from Multiple Tables [/b][list]
[*]Write SELECT statements to access data from more than one table using equality and nonequality joins
[*]View data that generally does not meet a join condition by using outer joins
[*]Join a table to itself using a self-join
[/list]
[center][b]Day 2:[/b][/center]
[b]5. Aggregating Data using Group Functions [/b][list]
[*]Identify the available group functions
[*]Use group functions
[*]Group data using the GROUP BY clause
[*]Include or exclude grouped rows by using the HAVING clause
[/list]

[b]6. Subqueries [/b][list]
[*]Describe the types of problems that subqueries can solve
[*]Define subqueries
[*]List the types of subqueries
[*]Write single-row and multiple-row subqueries
[/list]


[b]7. Producing Readable Output with iSQL*Plus [/b][list]
[*]Produce queries that require a substitution variable
[*]Produce more readable output
[*]Create and execute script files
[*]
[/list]
[b]8. Manipulating Data [/b][list]
[*]Describe each DML statement
[*]Insert rows into a table
[*]Update rows in a table
[*]Delete rows from a table
[*]Merge rows in a table
[*]Control transactions
[/list]

[center][b]Day 3:[/b][/center]
[b]9. Creating and Managing Tables [/b][list]
[*]Describe the main database objects
[*]Create tables
[*]Describe the datatypes that can be used when specifying column definition
[/list]
· Alter table definitions
· Drop, rename and truncate tables

[b]10. Including Constraints [/b][list]
[*]Describe constraints
[*]Create and maintain constraints
[/list]

[b]11. Creating Views [/b][list]
[*]Describe a view
[*]Create, alter the definition, and drop a view
[*]Retrieve data through a view
[*]Insert, update and delete data through a view
[/list]

[b]12. Creating Other Database Objects [/b][list]
[*]Create, maintain and use sequences
[*]Create and maintain indexes
[*]Create private and public synonyms
[/list]

[b]13. Controlling User Access[/b][list]
[*]Controlling User Access
[*]Creating Users
[*]Granting System Privileges
[/list]



[indent=1][b][u]Program with PL/SQL[/u][/b][/indent]

WORKSHOP Details:
Agenda: Oracle 9i PL/SQL
Modus-Operandi: 5 days
[center][b]Day 1:[/b][/center]

[b]1. Overview of PL/SQL Programs [/b][list]
[*]Describe a PL/SQL program construct
[*]List the components of a PL/SQL block
[*]List the benefits of subprograms
[*]Describe how a stored procedure/function is invoked
[/list]

[b]2. Writing Executable Statements[/b][list]
[*]Describe the significance of the executable section
[*]Use identifiers correctly
[*]Write statements in the executable section
[*]Describe the rules of nested blocks
[*]Execute and test a PL/SQL block
[*]Use coding conventions
[/list]

[b]3. Interacting with the Oracle Server[/b][list]
[*]Write a successful SELECT statement in PL/SQL
[*]Write DML statements in PL/SQL
[*]Control transactions in PL/SQL
[*]Determine the outcome of SQL data manipulation language (DML) statements
[/list]

[b]4.[/b][b] Writing Control Structures[/b][list]
[*]Identify the uses and types of control structures
[*]Construct an IF statement
[*]Use CASE expressions
[*]Construct and identify different loop statements
[*]Use logic tables
[*]Control block flow using nested loops and labels
[/list]

[center][b]Day 2:[/b][/center]
[b]5.[/b] [b]Working with Composite Data Types[/b][list]
[*]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 difference between records, tables, and tables of records
[/list]

[b]6.[/b][b] Writing Explicit Cursors[/b][list]
[*]Distinguish between an implicit and an explicit cursor
[*]Discuss when and why to use an explicit cursor
[*]Use a PL/SQL record variable
[*]Write a cursor FOR loop
[/list]

[b]7.[/b] [b]Advanced Explicit Cursor Concepts[/b][list]
[*]Write a cursor that uses parameters
[*]Determine when a FOR UPDATE clause in a cursor is required
[*]Determine when to use the WHERE CURRENT OF clause
[*]Write a cursor that uses a sub query
[/list]

[b]8. Handling Exceptions[/b][list]
[*]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
[/list]

[center][b]Day 3:[/b][/center]
[b]9. Creating Procedures[/b]
[b]• [/b]Define what a stored procedure is
• List the development steps for creating a procedure
• Create a procedure
• Describe the difference between formal and actual parameters
• List the types of parameter modes
• List the methods for calling a procedure with parameters
• Describe the DEFAULT option for parameters
• Create a procedure with parameters
• Invoke a procedure that has parameters
• Define a subprogram in the declarative section of a procedure
• Describe how exceptions are propagated
• Remove a procedure

[b]10. Creating Functions[/b][list]
[*]Define what a stored function is
[*]Create a function
[*]List how a function can be invoked
[*]List the advantages of user-defined functions in SQL statements
[*]List where user-defined functions can be called from within an SQL statement
[*]Describe the restrictions on calling functions from SQL statements
[*]Remove a function
[*]Describe the differences between procedures and functions
[/list]

[b]11. Managing Subprograms[/b][list]
[*]Contrast system privileges with object privileges
[*]Grant privileges
[*]Contrast invokers rights with definers rights
[*]Identify views in the data dictionary to manage stored objects
[/list]

[b]12. Creating Packages [/b]
• Use DESCRIBE command to describe packages and list their possible components
• Identify a package specification and body
• Create packages: Create related variables, cursors, constants, exceptions, procedures, and functions
• Designate a package construct as either public or private
• Invoke a package construct
• Use a bodiless package
• Drop Packages
• Identify benefits of Packages

[center][b]Day 4:[/b][/center]
[b]13. More Package Concepts [/b]
• Write packages that use the overloading feature
• Use Forward Referencing
• Describe errors with mutually referential subprograms
• Initialize variables with a one-time-only procedure
• Identify persistent states in package variables and cursors
• Identify restrictions on using Packaged functions in SQL statements
• Invoke packaged functions from SQL
• Use PL/SQL tables and records in Packages

[b]14. Oracle Supplied Packages [/b]
• Describe the benefits of Execute Immediate over DBMS_SQL for Native Dynamic SQL
• Identify the flow of execution
• Use EXECUTE IMMEDIATE
• Describe the use and application of some Oracle server-supplied packages: DBMS_SQL, DBMS_OUTPUT, UTL_FILE

[b]15. Manipulating Large Objects[/b]
• Compare and contrast LONG and large object (LOB) data types
• Describe LOB datatypes and how they are used
• Differentiate between internal and external LOBs
• Identify and Manage Bfiles
• Migrate from LONG To LOB
• Use the DBMS_LOB PL/SQL package
• Create LOB columns and populate them
• Perform SQL operations on LOBS: Update LOBs with SQL, Select from LOBS, Delete LOBS
• Describe the use of temporary LOBs


[center][b]Day 5:[/b][/center]

[b]16. Creating Database Triggers [/b]
• Describe the different types of triggers
• Describe database triggers and their uses
• List guidelines for designing triggers
• Create a DML trigger
• List the DML trigger components
• Describe the trigger firing sequence options
• Use conditional predicates in a DML trigger
• Create a row level trigger
• Create a statement level trigger
• Use the OLD and NEW qualifiers in a database trigger
• Create an INSTEAD OF trigger
• Describe the difference between stored procedures and triggers
• Describe the trigger execution model
• Alter a trigger status
• Remove a trigger

[b]17. More Trigger Concepts [/b]
• Define what a database trigger is
• Describe events that cause database triggers to fire
• Create a trigger for a DDL statement
• Create a trigger for a system event
• Describe the functionality of the CALL statement
• Describe the cause of a mutating table
• List what triggers can be implemented for
• List the privileges associated with triggers
• View trigger information in the dictionary views

[b]18 Managing Dependencies [/b]
• Track procedural dependencies
• Describe dependent objects and referenced objects
• View dependency information in the dictionary views
• Describe how the UTLDTREE script is used
• Describe how the IDEPTREE and DEPTREE procedures are used
• Describe a remote dependency
• List how remote dependencies are governed
• Describe when a remote dependency is unsuccessfully recompiled
• Describe when a remote dependency is successfully recompiled
• List how to minimize dependency failures.

[adiva]

SQL PLSQL Online Training ($199), Contact [email protected] for demo session.

[adiva]

Oracle 11g, SQL PLSQL Online Training ($199), Contact [email protected] for demo session.

[itonlinetraining]

[email protected]