Oracle Questions

1.                  From an Employee table, how will you display the record which has a maximum salary?

Ans:- select * from emp where salary = (select max(salary) from emp)

2.                  What is the difference between the Primary and Foreign key?

 Ans:- 1)Primary Key  is unique and not null column .Foreign key is define relation b/w two        tables it is primary key In child tables.

           2)we can have only one Pk for a table but we canhave multiple FK.

           3)By default clustered index will be created for PK where as FK doesn't.       

3.                  How will you delete a particular row from a Table?  (Using where condition)

4.                  How will you select unique values from a list of records?(distinct)

5.                  What is meant by Join? What are the different types of Joins available? Explain.

            A JOIN is used to match/equate different fields from 2 or more tables using primary/foreign keys. Output is based on type of Join and what is to be queries i.e. common data between 2 tables, unique data, total data, or mutually exclusive data.

 Simle join ,inner /equi/natural join,outer join(left,right),self join

  

6.                  overloading of stored procedure is possible in oracle? 

             No Only Package procedure/Functions can be Overloaded.

7.                  how to create table with in the procedure or function?(Dynamic Sql i.e execute immediate)

create or replace procedure Mypro()
is
v_sql varchar2(100);
begin

v_sql := 'create table mytab(mynum number(10),myname varchar(100))';
execute immediate v_sql ;

end;
8.               what is overloading procedure or overloading function ?
Ans:-  

It is the idea that the functionality of a PL/SQL stored procedure of function can be changed based on the input datatype.

For a simple example of overloading, you can write a PL/SQL function that does one thing when a numeric argument is passed to the procedure and another thing when a character string is passed as an argument.

PL/SQL lets you overload packaged (but not standalone) functions: You can use the same name for different functions if their formal parameters differ in number, order, or datatype family.

However, a RESTRICT_REFERENCES pragma can apply to only one function declaration. Therefore, a pragma that references the name of overloaded functions always applies to the nearest preceding function declaration.

In this example, the pragma applies to the second declaration of valid:

CREATE PACKAGE Tests AS 
    FUNCTION Valid (x NUMBER) RETURN CHAR; 
    FUNCTION Valid (x DATE) RETURN CHAR; 
    PRAGMA RESTRICT_REFERENCES (valid, WNDS); 
 END;

http://docs.oracle.com/cd/B12037_01/appdev.101/b10795/adfns_pc.htm