ETL Testing Blogspace: IBM Interview Questions

1) How do you validate source and target tables are correct?

Ans)Count match,

Transformation happening correctly,

Default values are populated correctly,

Null values are not found in foundation table ,

No truncation of data, mapping done correctly,

No duplicates are present ,

Data are moving to error tables if there is some error

2) What is order by?

Ans)ordering the columns so that the least or highest value can be the result

3) What is the syntax to sort the table in ascending order by not using default one?

Ans) Some SQL implementations may well return rows in the order of their primary keys or clustered indexes, but SQL itself is a relational algebra that returns arbitrarily ordered sets unless specifically told otherwise.

There's a good chance that the order in which rows are returned may well depend on the insertion and deletion activity since the table was created.

Some SQL implementations may well return rows in the order of their primary keys or clustered indexes, but SQL itself is a relational algebra that returns arbitrarily ordered sets unless specifically told otherwise.

There's a good chance that the order in which rows are returned may well depend on the insertion and deletion activity since the table was created.

4) Will it sort by both sal and comm?

Sal	Comm
100	1
200	3
100	4
300	2
200	4
400	5
500	1

Ans:

Sal	Comm
100	1
100	4
200	3
200	4
300	2
400	5
500	1

In case, if input is as below,

Sal	Comm
100	1
200	3
300	2
400	5
500	1

Will it consider comm for sorting?

For ex: select * from emp order by sal,comm;

Ans: No. because if there are no duplicates in the first sort column (Sal) it will not consider the next column(comm) for sorting.

5) Questions more on NULL

Ans)Ex: In the source Column A with data type varchar there is no value for it.

But it migrates to a column where -1 is present .During data match this will mismatch.

Hence by using coalesce(A,-1) or by using CASE STATEMENT we can data match it.

CASE when A is NULL then -1 else A end;

6) write a query to sort the calculated column in the select list.

Ex: select (sal*100)+1000,sal,empid from emp

Order by ?

Create table emp(

Empid number(5),

Sal number(10,2));

Insert into emp values (1,100);

Insert into emp values (2,330);

Insert into emp values (3,400);

Select * from emp;

Empid	Sal
1	100
2	330
3	400
4	50
5	345

Ans:

Select (sal+100)*7 sal1,sal,empid from emp

order by 1;

Observation: 1 refers to columns in select list and not in table.

Sal1	sal	empid
1050	50	4
1400	100	1
3010	330	2
3115	345	5
3500	400	3

7) What is MINUS query?

Ans) Finding the difference of records between the source and target tables.

8) Can we use aliases name in the order by list?

Ans) We cannot use the aliases name in the order by list.

9) Write a syntax for inner join

Ans)select * from tablename a inner join table name b

On a.column name=b.column name

10) What is correlated query?

Ans)The main query is dependent on the the sub query for every record or result of the sub query.

Select * from tab name a where col_name in (select * from tablename where a.col_name=b.col_name )

11)What is subquery?

Ans)The main query is not dependent on the sub query for every record of the sub query.

Select * from tab name a where col_name in (select * from tablename where b.col_name=’some value’)

12) Inner join: Instead of writing the condition on the query, is there any way to make the query very simpler?

Ex:

Select * from

Table 1 join table2

On table1.col1 = table2.col2;

Query is he don’t want condition to be present in the ‘ON’ clause. Instead he want to make the query short.

Ans)By ‘using’ clause

Select * from Table 1 join Table 2 using col_name …(The col_name should be same in both tables).

13) What is sequence?

Ans)In Oracle, you can create an autonumber field by using sequences. A sequence is an object in Oracle that is used to generate a number sequence. This can be useful when you need to create a unique number to act as a primary key.

The syntax for a sequence is:

CREATE SEQUENCE sequence_name

MINVALUE value

MAXVALUE value

START WITH value

INCREMENT BY value

CACHE value;

For example:

CREATE SEQUENCE supplier_seq

MINVALUE 1

MAXVALUE 999999999999999999999999999

START WITH 1

INCREMENT BY 1

CACHE 20;

14) What is the difference between Database testing and ETL testing

Ans)

DB TESTING	ETL TESTING

Tesing on OLTP data	Testing on OLAP data
Less volume of data as compared to OLAP	Very Large volume of data
No Dimensions or facts testing.. Mostly functions and procedural testing	Dimensions and Facts testing.
Data might be from Extracted from single source	Multiple sources to test the data
Data is still not cleansed so data might be impure	Data is cleansed after transformation
CRUD (Create,read,update,delete) operation is performed	Only select operation is performed
Normalized db are used for testing	De-normalized db's are used

15) What is test scenarios?

16) What are testing techniques u followed in ur project?

17) What is the test management tool u r using?

18) What are the cons of V-model?

Ans) Pros & Cons of Water Fall Model

* Enforced discipline through documents.
* No phase is complete until the docs are done & checked by SQA group.
* Concrete evidence or progress.
* Testing is inherent in every phase.

* No fair division of phases in the life cycle.
* The following phase should not start until the previous phase has finished
* Document driven model as a result customers cannot understand these.
* Re-design is problematic.

Pros & Cons of V -Model

* Simple and easy to use.
* Each phase has specific deliverables.
* Higher chance of success over the waterfall model due to the development early on during the life cycle.
* Works well for small projects where requirements are easily understood.

* Very rigid, like the waterfall model.
* Little flexibility and adjusting scope is difficult and expensive.
* Software is developed during the implementation phase, so no early prototype software are produced.
* Model doesn’t provide a clear path for problems found during testing phases.

19) What is Agile testing?

Ans)Quick testing once the build id out.

No need of much documentation.

Bugs will be found quickly and also fixed quickly.

Saves time and money

Any change request can be implemented easily @ any time in the project.

Daily meetings and discussions help to determine the issue well in advance.

quicker development, testing and constant feedbacks from the user, the Agile methodology becomes the appropriate approach for the projects to be delivered in a short span of time.

20) How will you make sure the data that are shown in the UI screen is correct?

21) What is full outer join?

The FULL OUTER JOIN will return all rows, as long as there's matching data in one of the tables. It includes all the rows from both the participating tables and does not select either the LEFT or RIGHT table from the JOIN key word.

The FULL OUTER JOIN combines the results of both left and right outer joins. When no matching rows exist for rows on the left side of the JOIN key word, NULL values will be returned from the result set on the right. On the other hand , when no matching rows exist for rows on the right side of the JOIN key word, NULL values will be returned from the result set on the left.

22) What is the difference between Union all and Full outer join?

Ans)Unionall –result will be in single column,All the values from both the tables would be present.

Full outer Join :Result will be in 2 columns (i.e) from 2 tables. The matching columns will be present and the unmatched will have NULL as values.

23) What is cross join?

Ans)The SQL CROSS JOIN produces a result set which is the number of rows in the first table multiplied by the number of rows in the second table, if no WHERE clause is used along with CROSS JOIN. This kind of result is called as Cartesian Product

24)What is a NATURAL JOIN ?

Ans)The SQL NATURAL JOIN is a type of equi-join and is structured in such a way that, columns with same name of associate tables will appear once only

1. Select *

2. FROM table1

3. NATURAL JOIN table2;

25) The below are the scenarios.

Table 1