A CO-RELATED SUBQUERY is one that has a correlation name as table or view designator in the FROM clause of the outer query and the same correlation name as a qualifier of a search condition in the WHERE clause of the subquery. eg
SELECT field1 from table1 X
WHERE field2>(select avg(field2) from table1 Y
where
field1=X.field1);
(The subquery in a correlated subquery is revaluated for every row of the table or view named in the outer query.)
2. What are various joins used while writing SUBQUERIES
Self join-Its a join foreign key of a table references the same table.
Outer Join--Its a join condition used where One can query all the rows of one of the tables in the join condition even though they don't satisfy the join condition.
Equi-join--Its a join condition that retrieves rows from one or more tables in which one or more columns in one table are equal to one or more columns in the second table.
3. What are various constraints used in SQL
NULL NOT NULL CHECK DEFAULT
4. What are different Oracle database objects
TABLES VIEWS INDEXES SYNONYMS SEQUENCES TABLESPACES etc
5. What is difference between Rename and Alias
Rename is a permanent name given to a table or column whereas Alias is a temporary name given to a table or column which do not exist once the SQL statement is executed.
6. What is a view
A view is stored procedure based on one or more tables, its a virtual table.
7. What are various privileges that a user can grant to another user
SELECT CONNECT RESOURCE
8. What is difference between UNIQUE and PRIMARY KEY constraints
A table can have only one PRIMARY KEY whereas there can be any number of UNIQUE keys. The columns that compose PK are automatically define NOT NULL, whereas a column that compose a UNIQUE is not automatically defined to be mandatory must also specify the column is NOT NULL.
9. Can a primary key contain more than one columns
Yes
10. How you will avoid duplicating records in a query
By using DISTINCT
11. What is difference between SQL and SQL*PLUS
SQL*PLUS is a command line tool where as SQL and PL/SQL language interface and reporting tool. Its a command line tool that allows user to type SQL commands to be executed directly against an Oracle database. SQL is a language used to query the relational database(DML,DCL,DDL). SQL*PLUS commands are used to format query result, Set options, Edit SQL commands and PL/SQL.
12. Which datatype is used for storing graphics and images
LONG RAW data type is used for storing BLOB's (binary large objects).
13. How will you delete duplicating rows from a base table
DELETE FROM table_name A WHERE rowid>(SELECT min(rowid) from table_name B where B.table_no=A.table_no);
CREATE TABLE new_table AS SELECT DISTINCT * FROM old_table;DROP old_table RENAME new_table TO old_table DELETE FROM table_name A WHERE rowid NOT IN (SELECT MAX(ROWID) FROM table_name GROUP BY column_name)
14. What is difference between SUBSTR and INSTR
SUBSTR returns a specified portion of a string eg SUBSTR('BCDEF',4) output BCDE INSTR provides character position in which a pattern is found in a string.
eg INSTR('ABC-DC-F','-',2) output 7 (2nd occurence of '-')
15. There is a string '120000 12 0 .125' ,how you will find the position of the decimal place
INSTR('120000 12 0 .125','.',1) output 13
16. There is a '%' sign in one field of a column. What will be the query to find it. '\' Should be used before '%'.
17. When you use WHERE clause and when you use HAVING clause
HAVING clause is used when you want to specify a condition for a group function and it is written after GROUP BY clause The WHERE clause is used when you want to specify a condition for columns, single row functions except group functions and it is written before GROUP BY clause if it is used.
18. Which is more faster - IN or EXISTS
EXISTS is more faster than IN because EXISTS returns a Boolean value whereas IN returns a value.
Appropriate answer will be....
Result of the subquery is small Then "IN" is typicaly more appropriate. and Result of the subquery is big/large/long Then "EXIST" is more appropriate.
19. What is a OUTER JOIN
Outer Join--Its a join condition used where you can query all the rows of one of the tables in the join condition even though they dont satisfy the join condition.
20. How you will avoid your query from using indexes
SELECT * FROM emp Where emp_no+' '=12345;
i.e you have to concatenate the column name with space within codes in the where condition.
SELECT /*+ FULL(a) */ ename, emp_no from emp where emp_no=1234;
i.e using HINTS
1. What is a pseudo column. Give some examples
It is a column that is not an actual column in the table.
eg USER, UID, SYSDATE, ROWNUM, ROWID, NULL, AND LEVEL.
Suppose customer table is there having different columns like customer no, payments.What will be the query to select top three max payments.
For top N queries, see http://www.orafaq.com/forum/mv/msg/160920/472554/102589/#msg_472554 post
2. What is the purpose of a cluster.
Oracle does not allow a user to specifically locate tables, since that is a part of the function of the RDBMS. However, for the purpose of increasing performance, oracle allows a developer to create a CLUSTER. A CLUSTER provides a means for storing data from different tables together for faster retrieval than if the table placement were left to the RDBMS.
3. What is a cursor.
Oracle uses work area to execute SQL statements and store processing information PL/SQL construct called a cursor lets you name a work area and access its stored information A cursor is a mechanism used to fetch more than one row in a Pl/SQl block.
4. Difference between an implicit & an explicit cursor.
PL/SQL declares a cursor implicitly for all SQL data manipulation statements, including quries that return only one row. However,queries that return more than one row you must declare an explicit cursor or use a cursor FOR loop.
Explicit cursor is a cursor in which the cursor name is explicitly assigned to a SELECT statement via the CURSOR...IS statement. An implicit cursor is used for all SQL statements Declare, Open, Fetch, Close. An explicit cursors are used to process multirow SELECT statements An implicit cursor is used to process INSERT, UPDATE, DELETE and single row SELECT. .INTO statements.
5. What are cursor attributes
%ROWCOUNT %NOTFOUND %FOUND %ISOPEN
6. What is a cursor for loop.
Cursor For Loop is a loop where oracle implicitly declares a loop variable, the loop index that of the same record type as the cursor's record.
7. Difference between NO DATA FOUND and %NOTFOUND
NO DATA FOUND is an exception raised only for the SELECT....INTO statements when the where clause of the querydoes not match any rows. When the where clause of the explicit cursor does not match any rows the %NOTFOUND attribute is set to TRUE instead.
8. What a SELECT FOR UPDATE cursor represent.
SELECT......FROM......FOR......UPDATE[OF column-reference][NOWAIT] The processing done in a fetch loop modifies the rows that have been retrieved by the cursor. A convenient way of modifying the rows is done by a method with two parts: the FOR UPDATE clause in the cursor declaration, WHERE CURRENT OF CLAUSE in an UPDATE or declaration statement.
9. What 'WHERE CURRENT OF ' clause does in a cursor.
LOOP
SELECT num_credits INTO v_numcredits FROM classes
WHERE dept=123 and course=101;
UPDATE students
SET current_credits=current_credits+v_numcredits
WHERE CURRENT OF X;
END LOOP
COMMIT;
END;
10. What is use of a cursor variable? How it is defined.
A cursor variable is associated with different statements at run time, which can hold different values at run time. Static cursors can only be associated with one run time query. A cursor variable is reference type(like a pointer in C). Declaring a cursor variable: TYPE type_name IS REF CURSOR RETURN return_type type_name is the name of the reference type,return_type is a record type indicating the types of the select list that will eventually be returned by the cursor variable.
11. What should be the return type for a cursor variable.Can we use a scalar data type as return type.
The return type for a cursor must be a record type.It can be declared explicitly as a user-defined or %ROWTYPE can be used. eg TYPE t_studentsref IS REF CURSOR RETURN students%ROWTYPE
12. How you open and close a cursor variable.Why it is required.
OPEN cursor variable FOR SELECT...Statement CLOSE cursor variable In order to associate a cursor variable with a particular SELECT statement OPEN syntax is used.In order to free the resources used for the query CLOSE statement is used.
13. How you were passing cursor variables in PL/SQL 2.2.
In PL/SQL 2.2 cursor variables cannot be declared in a package.This is because the storage for a cursor variable has to be allocated using Pro*C or OCI with version 2.2,the only means of passing a cursor variable to a PL/SQL block is via bind variable or a procedure parameter.
14. Can cursor variables be stored in PL/SQL tables.If yes how.If not why.
No, a cursor variable points a row which cannot be stored in a two-dimensional PL/SQL table.
15. Difference between procedure and function.
Functions are named PL/SQL blocks that return a value and can be called with arguments procedure a named block that can be called with parameter. A procedure all is a PL/SQL statement by itself, while a Function call is called as part of an expression.
16. What are different modes of parameters used in functions and procedures.
IN OUT INOUT
17. What is difference between a formal and an actual parameter
The variables declared in the procedure and which are passed, as arguments are called actual, the parameters in the procedure declaration. Actual parameters contain the values that are passed to a procedure and receive results. Formal parameters are the placeholders for the values of actual parameters
18. Can the default values be assigned to actual parameters.
Yes
19. Can a function take OUT parameters.If not why.
Yes. A function return a value, but can also have one or more OUT parameters. it is best practice, however to use a procedure rather than a function if you have multiple values to return.
20. What is syntax for dropping a procedure and a function .Are these operations possible.
Drop Procedure procedure_name
Drop Function function_name
21. What are ORACLE PRECOMPILERS.
Using ORACLE PRECOMPILERS ,SQL statements and PL/SQL blocks can be contained inside 3GL programs written in C,C++,COBOL,PASCAL, FORTRAN,PL/1 AND ADA. The Precompilers are known as Pro*C,Pro*Cobol,... This form of PL/SQL is known as embedded pl/sql,the language in which pl/sql is embedded is known as the host language. The prcompiler translates the embedded SQL and pl/sql ststements into calls to the precompiler runtime library.The output must be compiled and linked with this library to creater an executable.
22. What is OCI. What are its uses.
Oracle Call Interface is a method of accesing database from a 3GL program. Uses--No precompiler is required,PL/SQL blocks are executed like other DML statements.
The OCI library provides
-functions to parse SQL statemets
-bind input variables
-bind output variables
-execute statements
-fetch the results
23. Difference between database triggers and form triggers.
a) Data base trigger(DBT) fires when a DML operation is performed on a data base table.Form trigger(FT) Fires when user presses a key or navigates between fields on the screen b) Can be row level or statement level No distinction between row level and statement level. c) Can manipulate data stored in Oracle tables via SQL Can manipulate data in Oracle tables as well as variables in forms. d) Can be fired from any session executing the triggering DML statements. Can be fired only from the form that define the trigger. e) Can cause other database triggers to fire.Can cause other database triggers to fire,but not other form triggers.
24. What is an UTL_FILE.What are different procedures and functions associated
with it. UTL_FILE is a package that adds the ability to read and write to operating system files Procedures associated with it are FCLOSE, FCLOSE_ALL and 5 procedures to output data to a file PUT, PUT_LINE, NEW_LINE, PUTF, FFLUSH.PUT, FFLUSH.PUT_LINE,FFLUSH.NEW_LINE. Functions associated with it are FOPEN, ISOPEN.
25. Can you use a commit statement within a database trigger.
No
26. What is the maximum buffer size that can be specified using the DBMS_OUTPUT.ENABLE function?
1,000,000
[edit] Oracle Interview Questions
1. When looking at the estat events report you see that you are getting busy buffer waits. Is this bad? How can you find what is causing itBuffer busy waits could indicate contention in redo, rollback or data blocks. You need to check the v$waitstat view to see what areas are causing the problem. The value of the "count" column tells where the problem is, the "class" column tells you with what. UNDO is rollback segments, DATA is data base buffers.
2. If you see contention for library caches how can you fix it
Increase the size of the shared pool.
3. If you see statistics that deal with "undo" what are they really talking about
Rollback segments and associated structures.
4. If a tablespace has a default pctincrease of zero what will this cause (in relationship to the smon process)
The SMON process won?t automatically coalesce its free space fragments.
5. If a tablespace shows excessive fragmentation what are some methods to defragment the tablespace? (7.1,7.2 and 7.3 only)
In Oracle 7.0 to 7.2 The use of the 'alter session set events 'immediate trace name coalesce level ts#';? command is the easiest way to defragment contiguous free space fragmentation. The ts# parameter corresponds to the ts# value found in the ts$ SYS table. In version 7.3 the ?alter tablespace coalesce;? is best. If the free space isn?t contiguous then export, drop and import of the tablespace contents may be the only way to reclaim non-contiguous free space.
6. How can you tell if a tablespace has excessive fragmentation
If a select against the dba_free_space table shows that the count of a tablespaces extents is greater than the count of its data files, then it is fragmented.
7. You see the following on a status report: redo log space requests 23 redo log space wait time 0 Is this something to worry about? What if redo log space wait time is high? How can you fix this Since the wait time is zero, no. If the wait time was high it might indicate a need for more or larger redo logs.
8. What can cause a high value for recursive calls? How can this be fixed
A high value for recursive calls is cause by improper cursor usage, excessive dynamic space management actions, and or excessive statement re-parses. You need to determine the cause and correct it By either relinking applications to hold cursors, use proper space management techniques (proper storage and sizing) or ensure repeat queries are placed in packages for proper reuse.
9. If you see a pin hit ratio of less than 0.8 in the estat library cache report is this a problem? If so, how do you fix it
This indicate that the shared pool may be too small. Increase the shared pool size.
10. If you see the value for reloads is high in the estat library cache report is this a matter for concern
Yes, you should strive for zero reloads if possible. If you see excessive reloads then increase the size of the shared pool.
11. You look at the dba_rollback_segs view and see that there is a large number of shrinks and they are of relatively small size, is this a problem? How can it be fixed if it is a problem
A large number of small shrinks indicates a need to increase the size of the rollback segment extents. Ideally you should have no shrinks or a small number of large shrinks. To fix this just increase the size of the extents and adjust optimal accordingly.
12. You look at the dba_rollback_segs view and see that you have a large number of wraps is this a problem
A large number of wraps indicates that your extent size for your rollback segments are probably too small. Increase the size of your extents to reduce the number of wraps. You can look at the average transaction size in the same view to get the information on transaction size.
1. You have just started a new instance with a large SGA on a busy existing server. Performance is terrible, what should you check for
The first thing to check with a large SGA is that it isn?t being swapped out.
2. What OS user should be used for the first part of an Oracle installation (on UNIX)
You must use root first.
3. When should the default values for Oracle initialization parameters be used as is
Never
4. How many control files should you have? Where should they be located
At least 2 on separate disk spindles. Be sure they say on separate disks, not just file systems.
5. How many redo logs should you have and how should they be configured for maximum recoverability
You should have at least three groups of two redo logs with the two logs each on a separate disk spindle (mirrored by Oracle). The redo logs should not be on raw devices on UNIX if it can be avoided.
6. You have a simple application with no "hot" tables (i.e. uniform IO and access requirements). How many disks should you have assuming standard layout for SYSTEM, USER, TEMP and ROLLBACK tablespaces
At least 7, see disk configuration answer above.
7. Describe third normal form
Something like: In third normal form all attributes in an entity are related to the primary key and only to the primary key
8. Is the following statement true or false:
"All relational databases must be in third normal form" False. While 3NF is good for logical design most databases, if they have more than just a few tables, will not perform well using full 3NF. Usually some entities will be denormalized in the logical to physical transfer process.
9. What is an ERD
An ERD is an Entity-Relationship-Diagram. It is used to show the entities and relationships for a database logical model.
10. Why are recursive relationships bad? How do you resolve them
A recursive relationship (one where a table relates to itself) is bad when it is a hard relationship (i.e. neither side is a "may" both are "must") as this can result in it not being possible to put in a top or perhaps a bottom of the table (for example in the EMPLOYEE table you couldn?t put in the PRESIDENT of the company because he has no boss, or the junior janitor because he has no subordinates). These type of relationships are usually resolved by adding a small intersection entity.
11. What does a hard one-to-one relationship mean (one where the relationship on both ends is "must")
Expected answer: This means the two entities should probably be made into one entity.
12. How should a many-to-many relationship be handled
By adding an intersection entity table
13. What is an artificial (derived) primary key? When should an artificial (or derived) primary key be used
A derived key comes from a sequence. Usually it is used when a concatenated key becomes too cumbersome to use as a foreign key.
No comments:
Post a Comment