PL SQL Interview questions

1.Can we have a commit statement inside a trigger? if no why cant we?
2.How to sum negative and positive numbers from a single column ?
3.select strings containing % in a column.
4.Why is self-join required ?
5.differentiate between Where and Having ?
6.Find the Nth largest value in a column
8.Select alternate records from a table
9.select distinct records from a table,based on one column
10.delete duplicate rows in a table
11.What is a sequence?
12.How to access a sequence ?
13.What is a SYNONYM? in which cases do we generally use synonyms?
14.Advantages of synonym?
15.Will the synonym work when the parent table name is changed or table is dropped and created again??

16.difference between PK and Unique Key

17.differentiate between char and varchar

18.procedures are give better performance ??
    do we need to compile procedures ?

19.diff between ,
  select count(*) from table
  select count(column1) from table

20.Can we use commit inside the trigger? If not then how can we save the transaction made by the trigger?

21. What is the difference between "IS" and "AS" while creating procedure. Ex:- create procedure IS or AS

22. what is the difference between stored procedures and stored functions in ORACLE.How are they invoked.

23. How can we call stored procedures inside store procedures?

24. What is stored procedure?How to invoke them ?can we use them in dml statement ??

25. Explain about the process which takes place to execute a Stored routine?

26.Where the procedures are stored in database?

28. Explain about the difficulties faced by the database developer in implementing pre compiled statements?

29. how many types of stored procedure?

30. What is the difference between stored procedures and stored functions in ORACLE

31. Explain the benefits of running stored procedure on a database engine?

32. How one calls DDL statement using stored procedures in Oracle? Why can't we write ddl statement directly into the PL/SQL block ?

CREATE OR REPLACE PROCEDURE test IS
BEGIN
    truncate table table_name; // error
END test;
/
But,

CREATE OR REPLACE PROCEDURE test IS
BEGIN
    execute immediate 'truncate table table_name'; // works fine
END test;
/

Data definition language (DDL) statements such as CREATE, DROP, GRANT, and REVOKE can be executed within PL/SQL program units.

When using EXECUTE IMMEDIATE, remember that any DDL operations you execute will implicitly COMMIT the current transaction.

33. Are triggers fired during DDL statements?Say you have a "on delete" trigger. What will happen when you fire a delete?

Trigger will be fired and trigger body will be executed
34. What are external procedures ? Why and when they are used?
35. 
State the different extensions for stored procedures?
Does storing of data in stored procedures increase the access time? Explain?
What is cursors?
What are the uses of stored procedure?

Explain about recursive stored procedures?

PL/SQL allows sub procedures or functions to be called recursively. The tutorial example below shows you how to calculate factorial values with a recursive sub function:

CREATE OR REPLACE PROCEDURE FACTORIAL_TEST AS              
  FUNCTION FACTORIAL(N NUMBER)                             
    RETURN NUMBER AS                                       
  BEGIN            
    IF N <= 1 THEN 
      RETURN 1;    
    ELSE           
      RETURN N*FACTORIAL(N-1);                             
    END IF;        
  END;             
BEGIN              
  DBMS_OUTPUT.PUT_LINE('3! = ' ||    TO_CHAR(FACTORIAL(3)));
  END;

/  


What Is the Scope of a Local Variable?
The scope of a variable can be described with these rules:
A variable is valid within the procedure or function where it is defined.
A variable is also valid inside a sub procedure or function defined.
If a variable name is collided with another variable in a sub procedure or function, this variable becomes not visible in that sub procedure or function.

CREATE OR REPLACE PROCEDURE PARENT AS
   X CHAR(10) := 'FYI';
   Y NUMBER := 999999.00;
   PROCEDURE CHILD AS
     Y CHAR(10) := 'CENTER';
     Z NUMBER := -1;
   BEGIN
     DBMS_OUTPUT.PUT_LINE('X = ' || X); -- X from PARENT
     DBMS_OUTPUT.PUT_LINE('Y = ' || Y); -- Y from CHILD
     DBMS_OUTPUT.PUT_LINE('Z = ' || TO_CHAR(Z));
   END;
 BEGIN
   DBMS_OUTPUT.PUT_LINE('X = ' || X); -- X from PARENT
   DBMS_OUTPUT.PUT_LINE('Y = ' || TO_CHAR(Y));
   -- DBMS_OUTPUT.PUT_LINE('Z = ' || TO_CHAR(Z));
   CHILD;
 END;

 /


what is the Difference between View and Stored Procedure?
Explain about the implementation of stored procedures?