Ab Initio Interview Questions and Answers.
These question are asked in top companies of India.
In your project did you ever encounter an error called "depth not equal"?Answer : When there are two components that are linked together and if their layout doesn't match, this problem can occur during the compilation of the graph. As a solution to this problem, we should use a partitioning component in between if there was change in layout.
Explain what is the function that you would use to transfer a string into a decimal?
Answer : If we encounter this, no specific function is required if the size of the string and decimal is same. We can just use decimal cast with the size in the transform function and that will be sufficient. For instance, if the source field is defined as string(8) and the destination as decimal(8) then (say the field name is field1).out.field :: (decimal(8)) in.field
Again if the destination field size is lesser than the input then use of string_substring function can be used likie the following.
say destination field is decimal(5).
out.field :: (decimal(5))string_lrtrim(string_substring(in.field,1,5)) /* string_lrtrim used to trim leading and trailing spaces */
In relational database, the relationship between the two tables is represented by using Primary key and foreign key relationship.Whereas the primary key table is the parent table and foreign key table is the child table.The main requirement for both the tables is that, there should be a matching column.
Explain what do you mean by Cartesian joins?
Answer : This join returns the Cartesian product of the sets of records from the two or more joined tables. It joins two tables without a join key. Key should be {}. A Cartesian join of (A,B) and (1,2,3) is (A,1) (A,2) (A,3) (B,1) (B,2) (B,3).
SQL:
SELECT T1.cust_id, T2.product_id, T2.product_name FROM order T1, product T2
Explain what is the purpose of having stored procedures in a database? Why do you use 'with recompile' option?
Answer : The most important purpose of Stored Procedure is that it reduces the network traffic and all SQL statement executing in cursor so speed expected is much faster.
When the tables referenced by the stored procedure undergoes a lot of changes and modification like deletion or addition of data, the recompile option is used. Due to constant and heavy modification activity the execution plan becomes outdated and as such the stored procedure performance goes down. Hence, when we create the stored procedure with recompile option, the SQL server wont cache a plan for this stored procedure and it will be recompiled every time it is executed.
Explain how can you force the database optimizer to use a particular index?
Answer : To achieve this, use hints /*+ */, these acts as directives to the optimizer
select /*+ index(a index_name) full(b) */ *from table1 a, table2 bwhere b.col1 = a.col1 and b.col2= 'sid'and b.col3 = 1;
When you use multiple DML statements to perform a single unit of work, which one is more preferable to use - implicit or explicit transactions?
Answer : Because implicit is using for internal processing and explicit is using for user open data required.
Can you please describe the elements you would review to ensure multiple scheduled "batch" jobs do not "collide" with each other.
Answer : Because every job depend upon another job for example if you first job result is successful then another job will execute otherwise your job doesn't work.
Explain the process and steps involved in performing de-fragmentation of a data table.
Answer : Consider a scenario. The table contains mission critical data.
We can achieve this by several ways
- We can move the table in the same or other tablespace and rebuild all the indexes on the table alter table move this activity reclaims the de-fragmented space in the table analyze table table_name compute statistics to capture the updated statistics.
- Reorg could be done by taking a dump of the table, truncate the table and import the dump back into the table.
Answer : Technically speaking, this is a part of D.B.A responsibilities.
GRANT means permissions
For instance GRANT CREATE TABLE ,CREATE VIEW AND MANY MORE .
REVOKE means cancel the grant (permissions).So,Grant or Revoke both commands depend upon D.B.A
What do you mean by a cursor? Explain how do you update fields on the row just fetched?
Answer : The Oracle Database engine uses certain work areas for internal processing in order to the execute SQL statement, this is called cursor.Cursors can be classified into two types - Implicit cursor and Explicit cursor. Implicit cursor is using for internal processing whereas Explicit cursor is defined by the program for any query that returns more than one row of data.
- Check all Ab Initio Interview Question and Answers Here
Related Posts
No comments :