PL/SQL Question Example For Assignmnet

       1. Create a PL/SQL block that determines the top employees with respect to salaries.

a.       Accept a number n as user input with a SQL PLUS substitution parameter.

b.      In a loop get the last names and salaries of the top n people with respect to salary in the EMP table.

c.       Store the names and salaries in the TOP_DOGS table.

d.      Assume that no two employees have the same salary.

e.       Test a variety of special cases. Such as n=0 or where n is greater than the number of employees in the EMP table. Empty the TOP_DOGS table after each test.

Please enter the number of top money makers: 5 NAME                 SALARY ---------------------------------- KING                        5000 FORD                       3000 SCOTT                     3000 JONES                      2975 BLAKE                     2850

    2. Create a PL/SQL block to retrieve the name of each department from the DEPT table and print each department name to the screen, incorporating a PL/SQL table.

a.  Declare a PL/SQL table, MY_DEPT_TABLE, to temporarily store the name of the departments.

b. Using a loop retrieve the name of all departments currently in the DEPT table and store them in the PL/SQL table. Each department number is a multiple of 10.

c.  Using another loop, retrieve the department names from the PL/SQL table and print them to the screen, using DBMS_OUTPUT.PUT_LINE
 3.

a.       How can you create, alter and drop constraints (only foreign key and not null) and views. Also explain some object and system level privileges.

b.      What is the Difference between Pre-defined Exceptions and User Defined Exceptions? How can you handle all these exceptions provide some examples?
4.  Differentiate the Following Terms with some suitable examples:                                                                 

  i.      Object Libraries;                           Visual Attributes

ii.      Query Built-ins;                            Transactional Built-ins

iii.      Form Menus;                                 Parameters

iv.      Tab Canvases;                               Stack Canvases

v.      Message Handling Triggers;         Transactional Triggers

      5.     

a.       Briefly explain different Non Input Items and their properties. How can you add Functionality using navigational, query time and interface event triggers. Also provide some examples to change different properties of these item types programmatically using built-in functions.

b.      Define the type, scope and code of item level triggers and built-ins with some suitable examples.

    6. With Reference to your Oracle Project how can you implement the security of your system creating different roles and privileges etc? What type of dynamic functionality you will add up to integrate different modules of your system using developer forms 6i. Also explain the steps you followed to design user friendly interface of your project.