Monday, June 30, 2014


Hi Guys, I have come up with my new post to put Oracle interview questions which I came across during my interview with various companies. I believe that this post will be helpful to freshers as well as experience candidate in interview. These interview questions are mostly straight forward. I will come up with more tricky questions later. Please post your comments and suggestions ,which will make learning better for all. Some questions are having answer and some are not in below list of questions .You can post queries and doubts related with questions below, so I can answer them. Please let me know if you like the post, so you can see more post on Oracle!

Keep Sharing,Keep Learning guys :)

51 Oracle Interview Questions and Answers -

1. Reference Cursor ? A reference cursor is a pointer to a memory location that can be passed between different PL/SQL clients, thus allowing query result sets to be passed back and forth between clients. A reference cursor is a variable type defined using the PL/SQL TYPE statement within an Oracle package, much like a PL/SQL table: TYPE ref_type_name IS REF CURSOR RETURN return_type; Here, ref_type_name is the name given to the type and return_type represents a record in the database. You do not have to specify the return type as this could be used as a general catch-all reference cursor. Such non-restrictive types are known as weak, whereas specifying the return type is restrictive, or strong. The following example uses %ROWTYPE to define a strong return type that represents the record structure of the emp table: DECLARE TYPE EmpCurType IS REF CURSOR RETURN emp%ROWTYPE; ================================================================================ 2. Anonymous Block ? An unnamed PL/SQL block is called an anonymous block. A PL/SQL block contains three parts or sections. They are: • The optional Declaration section. • The mandatory Execution section. • The optional Exception (or Error) Handling section. ================================================================================ 3. Explain how cursor is implemented in PL/SQL A cursor is a variable that runs through the rows of some table or answer to some query. CURSOR employee_cur IS SELECT * FROM employee; Once I have declared the cursor, I can open it: OPEN employee_cur; And then I can fetch rows from it: FETCH employee_cur INTO employee_rec; and, finally, I can close the cursor: CLOSE employee_cur; *********** DECLARE CURSOR joke_feedback_cur IS SELECT, R.laugh_volume, FROM joke J, response R, comedian C WHERE J.joke_id = R.joke_id AND J.joker_id = C.joker_id; BEGIN ... END; ================================================================================ 4. Constraints ? Integrity constraints are used to enforce Business logic. Enforcing rules with integrity constraints is less costly than enforcing the equivalent rules by issuing SQL statements in the application. Only define NOT NULL constraints for columns of a table that absolutely require values at all times. Use the combination of NOT NULL and UNIQUE key integrity constraints to force the input of values in the UNIQUE key; this combination of data integrity rules eliminates the possibility that any new row's data will ever attempt to conflict with an existing row's data. Choosing a Table's Primary Key : Each table can have one primary key. A primary key allows each row in a table to be uniquely identified and ensures that no duplicate rows exist. Use the following guidelines when selecting a primary key: Choose a column whose data values are unique. The purpose of a table's primary key is to uniquely identify each row of the table. Therefore, the column or set of columns in the primary key must contain unique values for each row. Choose a column whose data values are never changed. A primary key value is only used to identify a row in the table; primary key values should never contain any data that is used for any other purpose. Therefore, primary key values should rarely need to be changed. Choose a column that does not contain any nulls. A PRIMARY KEY constraint, by definition, does not allow the input of any row with a null in any column that is part of the primary key. Choose a column that is short and numeric. Short primary keys are easy to type. You can use sequence numbers to easily generate numeric primary keys. Avoid choosing composite primary keys. Although composite primary keys are allowed, they do not satisfy the previous recommendations. For example, composite primary key values are long and cannot be assigned by sequence numbers. Foreign Key(Referential Integrity): Whenever two tables are related by a common column (or set of columns), define a PRIMARY or UNIQUE key constraint on the column in the parent table, and define a FOREIGN KEY constraint on the column in the child table, to maintain the relationship between the two tables. Using CHECK Integrity Constraints Use CHECK constraints when you need to enforce integrity rules that can be evaluated based on logical expressions. Never use CHECK constraints when any of the other types of integrity constraints can provide the necessary checking. *The condition must be a Boolean expression that can be evaluated using the values in the row being inserted or updated. *The condition cannot contain subqueries or sequences. *The condition cannot include the SYSDATE, UID, USER, or USERENV SQL functions. *The condition cannot contain the pseudocolumns LEVEL, PRIOR, or ROWNUM; *The condition cannot contain a user-defined SQL function. Eg. CREATE TABLE dept ( deptno NUMBER(3) PRIMARY KEY, dname VARCHAR2(15), loc VARCHAR2(15), CONSTRAINT dname_ukey UNIQUE (dname, loc), CONSTRAINT loc_check1 CHECK (loc IN ('NEW YORK', 'BOSTON', 'CHICAGO'))); ================================================================================ 5. Difference between Primary Key and Unique key ? * Primary keys are used to identify each row of the table uniquely. Unique keys should not have the purpose of identifying rows in the table. * A primary key field cannot be Null, whereas a Unique column can have a Null value. * There could be only 1 Primary Key per table, but there could be any number of Unique Key columns. * A primary key should be a Unique column, but all Unique Key column need not be a PRimary Key ================================================================================ 6. what are diffenent Joins explain . outer ? how would you write outer join(=+) if specify + to the right of equal to sign, which table data will be in full. A join is a query that combines rows from two or more tables. An equijoin is a join with a join condition containing an equality operator. ( where A.b = C.d) A self join is a join of a table to itself. Cartesian Products If two tables in a join query have no join condition, then Oracle returns their Cartesian product. Oracle combines each row of one table with each row of the other. A Cartesian product always generates many rows and is rarely useful. For example, the Cartesian product of two tables, each with 100 rows, has 10,000 rows. Always include a join condition unless you specifically need a Cartesian product. If a query joins three or more tables and you do not specify a join condition for a specific pair, then the optimizer may choose a join order that avoids producing an intermediate Cartesian product. An inner join (sometimes called a "simple join") is a join of two or more tables that returns only those rows that satisfy the join condition. An outer join returns all rows that satisfy the join condition and also returns some or all of those rows from one table for which no rows from the other satisfy the join condition. To write a query that performs an outer join of tables A and B and returns all rows from A( left outer join), apply the outer join operator (+) to all columns of B in the join condition. For example, SELECT ename, job, dept.deptno, dname FROM emp, dept WHERE emp.deptno (+) = dept.deptno; will select all departments even if there is no employee for a particular dept. Similarly to get all rows from B form a join of A & B , apply the outer join operator(+) to A. Example, SELECT ename, job, dept.deptno, dname FROM emp, dept WHERE emp.deptno = dept.deptno (+) ================================================================================ 7. Is a function in SELECT possible ? Yes. FOr example, Select ltrim(price) from Item. User defined functions work only from Oracle 8i onwards. ================================================================================ 8. EXCEPTION handling in PL/SQL An exception is a runtime error or warning condition, which can be predefined or user-defined. Predefined exceptions are raised implicitly (automatically) by the runtime system. User-defined exceptions must be raised explicitly by RAISE statements. To handle raised exceptions, you write separate routines called exception handlers. Example of an exception handler: DECLARE pe_ratio NUMBER(3,1); BEGIN SELECT price / earnings INTO pe_ratio FROM stocks WHERE symbol = 'XYZ'; -- might cause division-by-zero error INSERT INTO stats (symbol, ratio) VALUES ('XYZ', pe_ratio); COMMIT; EXCEPTION -- exception handlers begin WHEN ZERO_DIVIDE THEN -- handles 'division by zero' error INSERT INTO stats (symbol, ratio) VALUES ('XYZ', NULL); COMMIT; ... WHEN OTHERS THEN -- handles all other errors ROLLBACK; END; -- exception handlers and block end here Unlike predefined exceptions, user-defined exceptions must be declared and must be raised explicitly by RAISE statements. Example: DECLARE past_due EXCEPTION; acct_num NUMBER; BEGIN ... ... IF ... THEN RAISE past_due; -- this is not handled END IF; EXCEPTION WHEN past_due THEN -- does not handle RAISEd exception WHEN OTHERS THEN -- handle all other errors END; The procedure RAISE_APPLICATION_ERROR lets you issue user-defined ORA- error messages from stored subprograms. That way, you can report errors to your application and avoid returning unhandled exceptions. To call RAISE_APPLICATION_ERROR, use the syntax raise_application_error(error_number, message[, {TRUE | FALSE}]); where error_number is a negative integer in the range -20000 .. -20999 and message is a character string up to 2048 bytes long. If the optional third parameter is TRUE, the error is placed on the stack of previous errors. If the parameter is FALSE (the default), the error replaces all previous errors. RAISE_APPLICATION_ERROR is part of package DBMS_STANDARD, and as with package STANDARD, you do not need to qualify references to it. An application can call raise_application_error only from an executing stored subprogram (or method). When called, raise_application_error ends the subprogram and returns a user-defined error number and message to the application. The error number and message can be trapped like any Oracle error. Another way to handle exception is to use DECODE statements. For example: INSERT INTO stats (symbol, ratio) SELECT symbol, DECODE(earnings, 0, NULL, price / earnings) FROM stocks WHERE symbol = 'XYZ'; Here the DECODE function checks whether the earnings is 0 and if it is zero, then returns earnings, else price/earnings. ================================================================================ 9. When an exception is triggered in a loop, how you continue to next itereration ? By having an exception andling block within the loop. ================================================================================ 10. Decode ? Using Decode map this logic, If A>B Display 'A is Big', If A=B Display 'A equals B' Else 'B is Big' DECODE(Greatest(A,B), A, 'A is big', B, 'B is Big', 'A equals B') ================================================================================ 11. What is an index? what are diff type of indices? what is Clustered and Non Clustered Indeces ? Indexing is typically listing of keywords alongwith its location and is done to speed up the Data base. Create index WORKERSKILL_NAME_SKILL on WORKERSKILL(Name,Skill); A clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table can have only one clustered index. The leaf nodes of a clustered index contain the data pages. A nonclustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk. The leaf node of a nonclustered index does not consist of the data pages. Instead, the leaf nodes contain index rows. ================================================================================ 12. Mutating Error ? When we use a row level triger on a table, and at the same time if we query/insert/delete/update on the same table, it will give the mutating error. ================================================================================ 13. How to avoid mutating error? Two possible solutions. 1. Change the design of the table so as to avoid querying or any update on the table on which the row level trigger is holding on. 2. Create a package which holds a PL/SQL table with the RowID of the rows need to insert/update and a counter of the rows a. In BEFORE STATEMENT trigger set the counter to zero. b. In every ROW trigger call a procedure from the package which will register the RowId and modify the counter c. In AFTER STATEMENT trigger call a procedure which will make the needed checks and update the particular rows of the table. If you are not familiar with PL/SQL tables alternatively you can use temporary Oracle tables. It is also a good idea. ================================================================================ 14. Difference between Delete and Truncate. Truncate will permenantly delete the record and no rollback capability will exist. The delete command remove records from a table. Delete moves records to the rollback segment enabling rollback capability. Excessive use of triggers can result in complex interdependencies, which can be difficult to maintain in a large application. ================================================================================ 15. How many types of triggers are there and what are ? (Before Insert, after Insert, update, delete etc..) Triggers are similar to stored procedures. A trigger stored in the database can include SQL and PL/SQL or Java statements to run as a unit and can invoke stored procedures. However, procedures and triggers differ in the way that they are invoked. A procedure is explicitly run by a user, application, or trigger. Triggers are implicitly fired by Oracle when a triggering event occurs, no matter which user is connected or which application is being used. Different Types --------------- Row Triggers and Statement Triggers BEFORE and AFTER Triggers INSTEAD OF Triggers ( ON views,for INSERT etc, instaed of Inserting, it will carry out the trigger statements) Triggers on System Events(shut down) and User Events ( log on) ================================================================================ 16. What all composite datatypes in oracle Composite Data types: Table 1. Is similar but not the same as a database table 2. Must contain only one column of any scalar datatype 3. Is like a one-dimensional array of any size 4. Has its elements indexed with a binary integer column called the primary key of the table Record 1. Contains uniquely defined columns of different data types, 2. Enables us to treat dissimilar columns that are logically related as a single unit. ================================================================================ 17. what is PL/SQL tables? ================================================================================ 18. What are the default packages provided by oracle The ones with "DBMS_" prefix. Eg. DBMS_Output, DBMS_ALERT ================================================================================ 19. What is the diff between Where and Having Having clause is used where group by clasue is used to restrict the groups of returned rows to those groups for which the specified condition is TRUE. The HAVING condition cannot contain a scalar subquery expression eg: SELECT department_id, MIN(salary), MAX (salary) FROM employees GROUP BY department_id HAVING MIN(salary) < 5000; ================================================================================ 20. How is error Handling done? ================================================================================ 21. I have table that contains state codes, I might have more than 1 rows with the same state code, how can I find out? select count(*), state_code from table. IF this is > 1 , then we can assure that there are duplicates. ================================================================================ 22. How do you copy rows from Schema a , table p to table p in schema b ? To copy between tables on a remote database, include the same username, password, and service name in the FROM and TO clauses: COPY FROM HR/your_password@SchemaA - TO HR/your_password@SchemaB - INSERT tableP- USING SELECT * FROM tableP ================================================================================ 23. What is %Rowtype and %Type PROCEDURE Get_emp_names(Dept_num IN Emp_tab.Deptno%TYPE) ..Means the INput Parameter Dept_num should have the same Data Type as Emp_tab.DeptNo. Use the %ROWTYPE attribute to create a record that contains all the columns of the specified table. The following example defines the Get_emp_rec procedure, which returns all the columns of the Emp_tab table in a PL/SQL record for the given empno: PROCEDURE Get_emp_rec (Emp_number IN Emp_tab.Empno%TYPE, Emp_ret OUT Emp_tab%ROWTYPE) IS BEGIN SELECT Empno, Ename, Job, Mgr, Hiredate, Sal, Comm, Deptno INTO Emp_ret FROM Emp_tab WHERE Empno = Emp_number; END; You could call this procedure from a PL/SQL block as follows: DECLARE Emp_row Emp_tab%ROWTYPE; -- declare a record matching a -- row in the Emp_tab table BEGIN Get_emp_rec(7499, Emp_row); -- call for Emp_tab# 7499 DBMS_OUTPUT.PUT(Emp_row.Ename || ' ' || Emp_row.Empno); DBMS_OUTPUT.PUT(' ' || Emp_row.Job || ' ' || Emp_row.Mgr); DBMS_OUTPUT.PUT(' ' || Emp_row.Hiredate || ' ' || Emp_row.Sal); DBMS_OUTPUT.PUT(' ' || Emp_row.Comm || ' '|| Emp_row.Deptno); DBMS_OUTPUT.NEW_LINE; END; ================================================================================ 24. How to use the cursor with using open, fetch and close.? ================================================================================ 25. using select Statement how you will retrieve the user who is logged in? SELECT SYS_CONTEXT ('USERENV', 'SESSION_USER') FROM DUAL; ================================================================================ 26. When Exception occurs, i want to see the error generated by Oracle. How to see it? SQLERRM ================================================================================ 27. how to generate the out of the select statement in a file? SPOOL ================================================================================ 28. how to find the duplicate records? select count(*), job from emp group by job having count(*) > 1 then there are duplicates. ================================================================================ 29. What is Procedure, function, package and anonymous block? Procedures are named groups of SQl statements. Functions are like Procedures, but can return a value. Packages are groups of procedures, functions, variables and SQL statements. An unnamed PL/SQL block is called an anonymous block. ================================================================================ 30. What is group by funcion and where it is used.? Specify the GROUP BY clause if you want Oracle to group the selected rows based on the value of expr(s) for each row and return a single row of summary information for each group. Expressions in the GROUP BY clause can contain any columns of the tables, views, or materialized views in the FROM clause, regardless of whether the columns appear in the select list. It is used for aggraegate functions. ================================================================================ 31. What is the difference between RDBMS and DBMS? Sol: Chords 12 rules. ================================================================================ 32. What is normalised and denormalised data? process of removing redundancy in data by separating the data into multiple tables. ================================================================================ 33. What is a VIEW? A view is a custom-tailored presentation of the data in one or more tables. A view can also be thought of as a "stored query." Views do not actually contain or store data; they derive their data from the tables on which they are based. ================================================================================ 34. Can a view update a table? Can, but with restrictions. Like tables, views can be queried, updated, inserted into, and deleted from, with some restrictions. All operations performed on a view affect the base tables of the view. ================================================================================ 35. What happens if there is an exception in the cursor? How do we ensure that the execution for other records in the cursor does not stop. ================================================================================ 36. What are cursors ? After retrieving the records into the cursor can we update the record in the table for the retrieved record. What effect will it have on the cursor? Cursors Oracle uses work areas to execute SQL statements and store processing information. A PL/SQL construct called a cursor lets you name a work area and access its stored information. There are two kinds of cursors: implicit and explicit. PL/SQL implicitly declares a cursor for all SQL data manipulation statements, including queries that return only one row. For queries that return more than one row, you can explicitly declare a cursor to process the rows individually. An example follows: DECLARE CURSOR c1 IS SELECT empno, ename, job FROM emp WHERE deptno = 20; The set of rows returned by a multi-row query is called the result set. Its size is the number of rows that meet your search criteria Multi-row query processing is somewhat like file processing. For example, a COBOL program opens a file, processes records, then closes the file. Likewise, a PL/SQL program opens a cursor, processes rows returned by a query, then closes the cursor. Just as a file pointer marks the current position in an open file, a cursor marks the current position in a result set. You use the OPEN, FETCH, and CLOSE statements to control a cursor. The OPEN statement executes the query associated with the cursor, identifies the result set, and positions the cursor before the first row. The FETCH statement retrieves the current row and advances the cursor to the next row. When the last row has been processed, the CLOSE statement disables the cursor. ================================================================================ 37. What are user defined data types ? SUBTYPE subtype_name IS base_type; Eg. SUBTYPE EmpDate IS DATE; -- based on DATE type SUBTYPE ID_Num IS emp.empno%TYPE; -- based on column type CURSOR c1 IS SELECT * FROM dept; SUBTYPE DeptFile IS c1%ROWTYPE; -- based on cursor rowtype To specify base_type, you can use %TYPE, which provides the datatype of a variable or database column. Also, you can use %ROWTYPE, which provides the rowtype of a cursor, cursor variable, or database table. A subtype does not introduce a new type; it merely places an optional constraint on its base type. ================================================================================ 38. How to copy the Structure and data from one table to another in one SQL stmt? COPY {FROM database | TO database | FROM database TO database} {APPEND|CREATE|INSERT|REPLACE} destination_table [(column, column, column, ...)] USING query where database has the following syntax: username[/password]@connect_identifier ================================================================================ 39. Describe UNION and UNION ALL. UNION returns distinct rows selected by both queries while UNION ALL returns all the rows. Therefore, if the table has duplicates, UNION will remove them. If the table has no duplicates, UNION will force a sort and cause performance degradation as compared to UNION ALL. ================================================================================ 40. What is 1st normal form? Each cell must be one and only one value, and that value must be atomic: there can be no repeating groups in a table that satisfies first normal form. ================================================================================ 41. What is 2nd normal form? Every nonkey column must depend on the entire primary key. ================================================================================ 42. What is 3rd normal form? (another explanation than #1) No nonkey column depends on another nonkey column. ================================================================================ 43. What is 4th normal form? Fourth normal form forbids independent one-to-many relationships between primary key columns and nonkey columns. ================================================================================ 44. What is 5th normal form? Fifth normal form breaks tables into the smallest possible pieces in order to eliminate all redundancy within a table. Tables normalized to this extent consist of little more than the primary key. 45. How to find out top n salary from a employee table? ================================================================================ 46.If we will modify a function , which is defined in package even though stored package will be valid ? ================================================================================ 47.How do you define public and private procedure in oracle? ================================================================================ 48.If same kind of logic we put in function as well as procedure then which one will be faster? ================================================================================ 49.If we deleted any rows in table and commited then is it possible to retrieve the deleted data? ================================================================================ 50. While handiling excecption if we have written “When others then” first before other exception handler then what will happen? ================================================================================ 51. What is sql injection attack???
<form method="post" action="">
<input name="tfUName" type="text" id="tfUName">
<input name="tfUPass" type="password" id="tfUPass">
The easiest way for the login.asp to work is by building a database query that looks like this:
FROM logins
WHERE username = '$username'
AND password = '$password’

If the variables $username and $password are requested directly from the user's input, this can easily be compromised. Suppose that we gave "Joe" as a username and that the following string was provided as a password: anything' OR 'x'='x
FROM logins
WHERE username = 'Joe'
AND password = 'anything' OR 'x'='x'

As the inputs of the web application are not properly sanitised, the use of the single quotes has turned the WHERE SQL command into a two-component clause.
The 'x'='x' part guarantees to be true regardless of what the first part contains.
This will allow the attacker to bypass the login form without actually knowing a valid username / password combination!

                                                            COPYRIGHT © 2013 TECH BRAINS

Friday, May 30, 2014

ETL Testing Interview Questions & Answers - Part 4

Advanced ETL Testing Interview Questions & Answers 

Q1. How can you create BPT test scripts?

A1. Please follow the below steps to create BPT:
First you have to go Business Components section of ALM,
  1. create a new component there
  2. create component steps with parameters
  3. put parameter variables in bracket like <<< input1 >>>
  4. check it’s reflecting in parameters
  5. add details for business component
  6. Make status ready once all the steps are written.
Now go to the Test Plan in ALM,
  1. Create a new test script; type will be Business Process for the same.
  2. Put all the required details for script
  3. In test script tab, you have to pull component now which you have created (you can pull more than one component as required and integrate them in particular test script)
  4. After that you can see the parameters which you defined earlier for components here (suppose you have defined 4 parameters for the component then you can see 4 i/o parameters , iteration will be default 1 )
  5. If you want to check the same parameters for different iterations then you can increase the iteration here
  6. Now, BPT is ready (you can see green box)

Q2. How can you run BPT test scripts?

A2. Please follow the below steps to run BPT test scripts:

First go to Test Plan in ALM,

  1. Go to test script tab for BPT test scripts, click on iterations
  2. Default 1 iterations is mentioned there, after clicking this, you get the parameters list
  3. Define the variable value which you want to pass for all the parameters
  4. If you want to add one more iteration then, click on add iteration above
  5. Now you can define parameter values for added iterations as well.
  6. After defining all the values, we can pull this script in test lab
Now go to Test Lab in ALM,
  1. Go to the particular BPT which you want to run, click on run with manual runner
  2. Now all the defined parameter values will be substituted
  3. You need to follow the steps now
  4. check all the iterations are checked for mentioned steps and pass it
  5. BPT will be passed.

Q3. How can we link the existing defects with particular test case?

A3. Go to particular test case in Test Lab in ALM, double click on it, windows will appear. Click on Linked Defects tab, then click on Link Existing Defects then we can put defect id and click on Link. That defect will be linked with that particular test case.

Q4. Let’s suppose for making table loading faster, index will be disabled as pre-load activity, and then duplicates got loaded. Again after loading, in pos load activity index will be enabled that time it will fetch error. How to resolve this issue?

A4. You can follow the below steps to mitigate this issue –
  1. First we have to disable primary constraint
  2. drop the index after that
  3. Remove the duplicates using rowid
  4. Now create the index on the table
  5. At last we can enable the primary constraint

Q5.How can we pass the variable value through command box in teradata?

A5. We can use the below query for this –
WHERE <date_field> = CAST('?yyyymmdd' AS DATE FORMAT 'yyyymmdd')

Q6.How can we search particular job in control M? can follow the below steps to find out the job in control m –
  1. Login to control m
  2. Click customizable filter in view point tab of control m
  3. Filter based on folder name and server name ( we can give wild characters here *)
  4. After filtering we can see all the instances for jobs coming under that folder
  5. In particular folder, all the jobs coming under that will be shown

Q7. How can we check the roles assigned to particular tables/view in teradata?

A7. We can use dbc.allrolerights table to find out the roles assigned to table/view
Select * from dbc.allrolerights where tablename= <table_name>
We can see the fields for role names, access rights, and grantor names here.

Q8. In control m, how can we run the job instantly which is in waiting due to dependencies or schedule?

A8. Do right click on that particular job, free that job and click run now.

Q9. In control m, how can we order a new job which is not scheduled?

A9.Click order button on home tab, select control m server, folder, particular job (if all jobs selected then all the jobs for particular folder will be submitted to run). Also check boxes ignore scheduling criteria and order as independent flow.

Q10. How can we modify command for particular job in control m?

A10. Do right click on particular job, go to properties, click on modify job, now we can change the commands and other things like schedule for that job.

Q11. In Tivoli, some job is stuck due to dependencies and resource constraints then how can we run that job?

A11. Right click on job stream, increase the priority to 101, release all dependencies, and remove time restrictions, then job should run. If still it’s stuck then do the same thing at job level; it will definitely run.

Q12. In Tivoli, how can we submit the jobstream which are not scheduled?

A12. Go to default database jobstream , search that particular jobstream , you can submit that jobstream from there. It will be reflected in scheduled job stream.

Note : If jobstream is in draft mode, then we can't submit that job. Jobstream should be active then only we can submit to plan. 

Wednesday, April 30, 2014

Basic ETL Testing Interview Questions and Answers - Part 3

Basic Data Warehouse Testing Interview Questions and Answers - Part 3

Let's Continue discussing basic Conceptual ETL or Data Warehouse Interview Questions and answers...

Q43. How to delete one year older file in Unix?
A43. Find ./your_dir -mtime +365 -type f -delete
Note : f-> file only, -mtime +365 -> 365 days older
Q44. What's the difference between delete and truncate command?
A44 : Difference are given below -
a) Delete is dml statement (which requires commit after that) while Truncate is ddl statement (which is autocommit)
b) In Delete statement we can put conditions, so we can delete whatever required; while Truncate deletes all the data in tables ( we can't put conditions here)
c) Delete activates a trigger as the individual row deletions are logged, while truncate never activates a trigger (no logs maintained) that's why rollback is possible in Delete but not in Truncate.
d) Truncate tables always locks the table while Delete Statement uses rowlock before deleting the row.
e)Truncate is faster than delete as it doesn't keep the logs.
Q45.Write a query for the below -
To Find out the name of the employees whose salary is greater than their
manager salary.
A45 Query-
Select from Employee Emp, Employee Mgr
where EMP.managerid = Mgr.empid
and Emp.sal > Mgr.sal ;
Q46. What is BPT (Business Process testing) ?
A46. BPT stands for business process testing. It enables SMEs to design test script early in the development cycle. It consist of reusable business components which got converted into business process test.
Q47. How can we add partitions in Teradata? Please tell me the query.
A47. Query -
Alter table table_name
modify primary index
Add range between date '2014-04-01' and date '2015-06-01' Each interval '1' Month;
Q48. What is multi-file system (MFS) ? How can we copy a mfs file?
A48. Multi-file system(MFS) is a file which is divided into different files . Using MFS file we can process a big input file in efficient manner as files are divided. So, we can process the divided files parallel and make the processing faster.
We can use the below commands to copy MFS file -
m_cp file1 file2
Q48. You worked on Informatica then tell me the difference between FILTER and ROUTER ?
A48. There are many differences between FILTER and Router, which are given below -
a) Filter transformation has a single i/p and single o/p while Router has single i/p and multiple o/p
b) Router is like Case statement in database while filter is like where clause in database
c) Router transformation doesn't block any row i.e no. of rows equal in i/p and o/p while filter block rows i.e no. of rows in o/p <= no. of rows in i/p
Q49. In Abinitio graph , how can we check DML and XFR for the transformation?
A49. Right Click on Transformation Properties -> go to file layout -> check path for dml
(or embedded DML)
Right Click on Transformation Properties -> check transformation logic -> check path for XFR(or embedded logic)
Q50. Are you aware of IBM Tivoli Scheduler..How can get the Job stream in plan ? How can we execute that job stream even though dependencies are there?
A50. IBM TIVOLI is used to schedule the job streams which calls the wrapper script/graph to perform the job.
Go to database of job stream and then submit the particular job stream from there; it will appear in the Plan. We can increase the priority to high(101) and release all the dependencies to run that stream.
Q51. What do you understand by full load and incremental load?
A52. Full load is basically dumping of entire data from source table to target table . Every time when full load is happening then we are truncating the table and loading the entire data again.

Incremental load is basically loading of delta data from source to target table in
regular interval so that source and target can be synchronized. while applying the delta
into target table, Normally we do capture data change (CDC) i.e what is getting inserted,
updated or deleted.


Thursday, March 20, 2014

Basic ETL Testing Interview Questions and Answers - Part 2

Hi Guys, this post is based on some basic ETL testing questions which every ETL tester will know if he has worked on it. I believe that this post will be helpful to experience candidate in interview.These interview questions are somewhat related with real time tasks and issues faced by ETL QA during the day to day work. I have also written down answers for them, so it will be very informative for you. Please post your comments and suggestions ,which will make learning better for all. Please let me know if you like the post, so you can see more post on ETL!
Keep Sharing,Keep Learning guys :)

Data Warehouse QA
21 Basic Data Warehouse Testing Interview Questions and Answers:
Q1. Which basic tasks primarily done by ETL tester?
A1. A ETL Tester primarily test source data extraction, business transformation logic and target table loading . There are so many tasks involved for doing the same , which are given below -
1. Stage table / SFS or MFS file created from source upstream system - below checks come under this :
a) Record count Check
b) Reconcile records with source data
c) No Junk data loaded
d) Key or Mandatory Field not missing
e) duplicate data not there
f) Data type and size check
2) Business transformation logic applied - below checks come under this :
a) Business data check like telephone no cant be more than 10 digit or character data
b) Record count check for active and passing transformation logic applied
c) Derived Field from the source data is proper
d) Check Data flow from stage to intermediate table
e) Surrogate key generation check if any
3. Target table loading from stage file or table after applying transformation - below check come under this
a) Record count check from intermediate table or file to target table
b) Mandatory or key field data not missing or Null
c) Aggregate or derived value loaded in Fact table
d) Check view created based on target table
e) Truncate and load table check
f) CDC applied on incremental load table
g) dimension table check & history table check
h) Business rule validation on loaded table
i) Check reports based on loaded fact and dimension table
Q2. Generally how enevironemt variables and paths are set in Unix?
A2. dot(.) profile , normally while logging this will be executed or we can execute as dot(.) dot(.)profile
3. If a column is added into a table, tell me the test cases you will write for this?
A3. Following test cases you can write for this -
1. Check that particular column data type and size is as per the data model.
2. Check data is getting loaded into that column as per the DEM (data element mapping)
3. Check the valid values , null check and boundary value check for that column
Q4.Let's suppose you are working on a project where requirement keeps changing. How would you tackle this?
A4. If the requirement is getting changed frequently then we need to lot of regression for the same functionality which has been tested. Then you need to be ready with all your input test data and expected result, so after checking changed part , you can run all the test cases and check the results in no time.
Q5. How do you modify your test data while doing the testing?
A5. If input test data is ASCII file, then you can easliy prepare it in notepad+ based on the interface and then ftp it to unix server and if it's table then you can insert the rows into table as per the data model. If file other than ascii format then we can use abinitio graph to covert excel sheet into required format or use other tools are available for doing the same.
Q6. A table has partitions by range on data_dt, suppose it has already defined monthly partitions PTN_01 (values less than (TO_DATE ('01-Feb-2014' , 'dd-mon-yyyy' ))) for january 2014 data only and we are trying to load data for feb 2014 then what will happen? If you find any error then how to solve the same.
A6. It will fetch error - “Inserted partition key does not map to any partition” (ORA -14400) . It means parition is not there for feb data which we are trying to load, so add a new partition in the table for feb month data as below :
Alter table table_name add partition partition_name values less than (TO_DATE ('01-MAR-2014' , 'dd-mon-yyyy' ))
Note : Remember we can only create new partition for higher value than the previous created partition (it means here we can't add partition for dec 2013 as we have higher value is feb 2014 here.
Q7. How will you connect oracle database from unix server?
A7. sqlplus username/password@dbserver
Q8. If one of the Oracle procedure fetches error - “No data found” then what is the issue here?
A8. In that procedure definitely we are retrieving the data from table and passing it to a variable. If that select statement is not fetching any row then we will get this error.
Q9. If one of your wrapper unix script is fetching the error - “not enough memory” then what you will do?
A9. First we can check the disk usage by command df -h , then we can clean up accordingly and run the script again.
Q10. let's suppose we have to two tables, item (Primary Key : item id)and order (Primary Key order id, Foreign Key : item id) . If we try to delete items from order table then will we able to delete? If not then how can we do that?
A10. If we make an attempt to delete or truncate a table with unique or primary keys referenced by foreign keys enabled in another table then we get error : “ORA-02266 unique/primary keys in table referenced by enabled foreign keys”
So, before deleting or truncating the table, disable the foreign key constraints in other tables or delete the data from foreign table item first then from the primary table order here.
Q11. Why do we create index on a table? Please explain
A11. In nutshell I can say - for faster retrieval of data we use indexes, let's suppose I created a table order which will contain billions of data and I know - most of the time I will be querying this table using order id then I should make index on Order table for faster result.
Q12. What will be the default permission of a file created in UNIX? How can we provide all access to all?
A12. When a file is created, the permission flags are set according to the file mode creation mask, which can be set using the "umask" command. If umask value is set as 002 then it means file permission is 664. (-rw-rw-r--). we can chnage the permission of file as below :
chmod 777 filename (4: read , 2: write , 1: execute)
Q13. How we can link a defect with a test script in QC?
A13. First We should fail the test case step in test lab , then we can click on new defect (red color symbol) then enter the defect details there and raise it. Then that defect is linked with that particular step of test case. One more thing as we mention issues in actual result that will come in defect description automatically (no need to put issue details again)
Q14. What are the different methods to load table from files in Oracle? Also tell me methods for teradata.
A14. SQL Loader, External table loading, loading through driver JDBC . In teradata we use multiload or fastload usually.
Q15. What are the things you will check before you start testing? What will be your deliverables?
A15. Before starting the testing, requirement document, functional spec . Technical spec , interface, dem and unit test result should be availble atleast. My deliverables will be – test plan, test spec, test script, defect summary with root causal analysis, test execution or result report and automation script (if created).

Q16. What do you understand by active and passive transformation in informatica?
A16. Active transformation - 
No or records in input != No of records in output (like - filter, router, source qualifier)
Passive transformation-
No or records in input = No of records in output (like - Expression, look-up, stored procedure)


Q17. Let's suppose we are having order table which are having duplicate order_id. How we can delete the duplicate rows from the table? Tell atleast two methods you know.
A17. First we can do the below :
create table order_new as select distinct * from order ;
drop table order ;
rename order_new to order ;
Note : This method is faster , but we need to recreate index, partitions, constraints....
Second method
delete from order a where rowid > (select min(rowid) from order b where a.order_id = b.order_id);
Note : here we deleting the duplicate rows based on rowid which is uniquely assigned to each row by Oracle.
Q18. How you will find the second highest salary from the employee table? Tell me two methods atleast.
A18. First method – we can use sub query to find this as below :
select max(sal)
from emp
where sal not in (select max(sal) from emp ) ;
Note : first we fond the hoghest salary here then next highest will be the second salary only
Second method – we can use row_number for the same as below :
SELECT empno, sal
select empno, sal, ROW_NUMBER() OVER (order by sal desc) RN
from emp
order by sal desc
Q19. How we can find out the last two modified file for a particular file mask abc* in unix?
A19. We can do this using very simple command : ls -lrt abc* | tail -2
Note: To check the last command was successful or not – we can use echo $?
20. How you will delete the last line of a file? Tell atleast two methods
A20 first , we can do using Sed as : Sed -i '$d' file_name
second method -
cp file_main file_bkp
sed '$d' file_bkp > file_main
rm -f file_bkp
Note : In direction > operator is used to move all the contents of file into another, >> used to append the one file data into another file.
Q21. Let's suppose we are migrating the data from legacy file system to oracle database tables. How would you validate that the data is migrated propely? Tell me the imp. test scenario and test cases you will write to test this requirement and how will you test that scenario?
A21 . Following scenario can be written for the same:
1) Check that all table DDL as per data model (desc table name )
2) Check that all records are loaded from source to target (match record count)
3) Check null value or junk data is not loaded into table ( check record count by putting not null conditions)
4) Check valid values for columns ( based on group by find all the counts for particular values for a field)
5) Check same data is loaded ( put a join between source table (if there) stage table and taget table and check the result)
6) Check key fields ( add number fields for target and source and match them)
7) Check business logics (create a sheet with input and output)
8) After initial loading, check incremental loading ( insert/ delete /update check all the CDC cases)
9) check the output file layout (if any)
                                                                                COPYRIGHT ©  2014 TECH BRAINS