Important Interview Question,Placement Question,Company Questions by MuthuKumar.v

VIEWS: 15 PAGES: 4

Important Interview Question,Placement Question,Company Questions.

More Info
									What is a package ? What are the advantages of packages ?
Answer: Package is a database object that groups logically related
procedures.
The advantages of packages are Modularity, Easier
Applicaton Design, Information. Hiding,. reusability and
Better Performance.



What are Anti joins
Answer: Anti-joins are written using the NOT EXISTS or NOT IN
constructs. An anti-join between two tables returns rows
from the first table for which there are no corresponding
rows in the second table. In other words, it returns rows
that fail to match the sub-query on the right side.

Suppose you want a list of departments with no employees.
You could write a query like this:
SELECT   d.department_name
    FROM departments d
MINUS
SELECT   d.department_name
    FROM departments d, employees e
   WHERE d.department_id = e.department_id
ORDER BY department_name;

The above query will give the desired results, but it might
be clearer to write the query using an anti-join:
SELECT   d.department_name
    FROM departments d
   WHERE NOT EXISTS (SELECT NULL
                        FROM employees e
                       WHERE e.department_id = d.department_id)
ORDER BY d.department_name;



What are Nested Tables? How will u delete 5 rows from Nested Tables
Answer: CREATE Or Replace TYPE AddressType AS OBJECT (
       street VARCHAR2(15),
    city    VARCHAR2(15),
     state CHAR(2),
       zip    VARCHAR2(5)
    );

CREATE Or Replace TYPE nested_table_AddressType AS TABLE OF
AddressType;

CREATE TABLE employee (
      id         INTEGER PRIMARY KEY,
      first_name VARCHAR2(10),
      last_name VARCHAR2(10),
      addresses nested_table_AddressType
    )
    NESTED TABLE
      addresses
    STORE AS
     nested_addresses;

INSERT INTO employee VALUES (
       1, 'Steve', 'Brown',
       nested_table_AddressType(
         AddressType('2 Ave', 'City', 'MA', '12345'),
         AddressType('4 Ave', 'City', 'CA', '54321')
       )
    );

DELETE FROM TABLE (
      SELECT addresses FROM employee WHERE id = 1
    ) addr
    WHERE
      VALUE(addr) = AddressType(
         '4 Ave', 'City', 'CA', '54321'
      );



When we can declare a column as Unique and Not Null both at the same time. What
is the use pf Primary Key then?
Answer: Unique Key creates Non-Cluster index in default,
But in Primary Key creates Cluster index in default



what is outer join? what is self join? what is difference between them? what is
cartesion join?
Answer: Outer join displays result set containing all the rows from
one table and the matching rows from another table. There
are three types of Outer Join -- Left Outer Join (All rows
of the left table and matchng rows of the right), Right
Outer Join (All rows of the right table and matching rows
of the left table) and Full Outer Join (All rows of both
tables).
In self join, table is joined with itself. One row
correlates with other rows in the same table. Hence a table
name with two alias names is used.
Difference between outer join and self join is that outer
join uses atleast two or more tables where one column is
common while self join uses only one table and two fields
of the same table.
Cartesian Product or cross join is between two tables where
each row from one table with each row of the other table.
If table A has 5 rows and table B has 10 rows then after
cross join the total number of rows would be 50.

There are 2 variables called x and y ,x contains 1,2 and y
contains 3,4 we have to swap the values from x to y and y
to x with out using dummy variables and it can be done only
by using a single statement ? how?
Declare lowerl number:= 1; upperl number:= 3; num varchar2(10); begin for i into
lowerl..upperl loop num:=num||to_char(lowerl); if i=3 then upperl:=5; end loop;
message(num); What will be the output ?
Answer:declare
   lowerl number:= 1;
   upperl number:= 3;
   num varchar2(10);
  begin
  for i in lowerl..upperl
   loop
     num:=num||to_char(lowerl);
     if i=3 then upperl:=5;
     end if;
    end loop;
   message(num);
  end;
some changes in the programme.......
it will result 111



suppose we have values like 1 5 7 in a colum.Now we want numbers like(2 3 4 6) that
exists between 1 5 7.How can we do this using sql query??
Answer:This Query shall do the trick.
select x
from   (select rownum x from all_tables ) a,
       (select max(a) mx, min(a) mi from a) b
where  a.x between b.mi
       and b.mx
       and a.x not in (select * from a);


How instead of triger is helpful for mutating tables errors handlling ?
Answer:
"Mutating" means "changing". A mutating table is a table
that is currently being modified by an update, delete, or
insert
statement. When a trigger tries to reference a table that is
in state of flux (being changed), it is considered
"mutating" and
raises an error since Oracle should not return data that has
not yet reached its final state.
Another way this error can occur is if the trigger has
statements to change the primary, foreign or unique key
columns of the
table off which it fires. If you must have triggers on
tables that have referential constraints, the workaround is
to enforce the
referential integrity through triggers as well.
There are several restrictions in Oracle regarding triggers:
•        A row-level trigger cannot query or modify a
mutating table. (Of course, NEW and OLD still can be
accessed by the
trigger) .
•        A statement-level trigger cannot query or modify a
mutating table if the trigger is fired as the result of a
CASCADE delete.

So some time we can not use triggers to put DMl operations
on table. that time we can use INSTEAD OF TRIGGER.


What is the difference between SQL table and the PLSQL table?
Answer:sql tables are used to store data permanently.pl/sql tables
are used to hold data just like other variables during the
execution of programs.


What is the difference between CHAR and VARCHAR2? If VARCHAR2 serves the
uses of CHAR why CHAR is still used and not been discarded yet?
Answer:in char it allocates the memory space as static where as in
varchar2 it allocates the memory space as dynamic
OR
char is a space mapping function where as in varchar2 it is
not mapping the space it occupies the exact size of the
string


How will we see framework of a table?
Answer:select dbms_metadata.get_ddl('TABLE','DEPT','SCOTT') from
dual;


why we go for package? what are the advantages of using instead of seperate
procuderes or functions
Answer:Packages are bunch of procedures and functions instead of
granting one by one procedure and function DBA can grant
packge to user.


I have a package in which a table was used in a procedure and compiled later i have
dropped the table used in the package what would be the status of package
specification and body
Answer:Package specification remain valid package body will become
invalid

								
To top