VIEWS: 15 PAGES: 4 CATEGORY: Tech and Engineering Careers POSTED ON: 10/15/2012
Important Interview Question,Placement Question,Company Questions.
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
Pages to are hidden for
"Important Interview Question,Placement Question,Company Questions"Please download to view full document