NSR

Very Very important Video For Interview

Part 1

SQL INTERVIEW QUESTIONS FOR DATA ANALYST|SQL INTERVIEW QUESTIONS FOR BUSINESS ANALYST| SQL QUESTIONS

Part 2

SQL INTERVIEW QUESTIONS PART 2 |SQL INTERVIEW QUESTIONS FOR DATA/BUSINESS ANALYST| SQL QUESTIONS

Q. What are SCD’s ?

Q. Difference between SCD1, SCD2, SCD3.

Tech Coach Channel

Ans-----

A Slowly Changing Dimension (SCD) is a dimension that stores and manages both current and historical data over time in a data warehouse.

The ‘Slowly Changing Dimension’ problem is a common one particular to data warehousing. This applies to cases where the attribute for a record varies over time.

There are three types of SCD (SCD1,SCD2,SCD3)

SCD1- Overwrite

SCD2 - Append

SCD3 - Update

Advantages

Disadvantages

When To Use

SCD1

This is the easiest way to handle the SCD problem, since there is no need to keep track of old information.

All history is lost. By applying this methodology, it is not possible to trace back in history.

Should be used when it is not necessary for the data warehouse to keep track of historical changes.

SCD2

This allows us to accurately keep all historical information.

This will cause the size of the table to grow fast. In cases where the number of rows for the table is very high to start with, storage and performance can be a concern.

Should be used when it is necessary for the data warehouse to track historical changes.

SCD3

This does not increase the size of the table, since new information is updated.

This allows us to keep some part of history.

Type 3 will not be able to keep all history where an attribute is changes more than once.

Thats why type 3 is rarely used in actual practise.

Should only be used when it is necessary for the data warehouse to track historical changes, and when such changes will only occur for a finite number of time.

SCD: Slowly changing dimensions explained with real examples

Q. What are Surrogate Keys ?

Q. Difference between primary key and surrogate key.,"

Tech Coach Channel

Ans------

Primary Key

Surrogate Key

1.Primary Key is used to maintain unique records in OLTP system

1.surrogate Key is used to maintain

Unique records in DWH system.

2. Primary Key values are entered by the user.

2.Surrogate Key values are generated by the system.

(By ETL Mechanism)

3.Primary Key values can be alpha-numeric

(can be characters,numbers or combination of both)

3.Surrogate key values always numeric values.

(Surrogate Key values cannot be characters)

4. Primary Key values are belongs to business data or table data.

4.Surrogate Key is not belongs to business data or table data.

The primary key is a unique key in your table that you choose that best uniquely identifies a record in the table. All tables should have a primary key, because if you ever need to update or delete a record you need to know how to uniquely identify it.

A surrogate key is an artificially generated key. They're useful when your records essentially have no natural key (such as a Person table, since it's possible for two people born on the same date to have the same name, or records in a log, since it's possible for two events to happen such that they carry the same timestamp). Most often you'll see these implemented as integers in an automatically incrementing field, or as GUIDs that are generated automatically for each record. ID numbers are almost always surrogate keys.

Unlike primary keys, not all tables need surrogate keys, however. If you have a table that lists the states in America, you don't really need an ID number for them. You could use the state abbreviation as a primary key code.

The main advantage of the surrogate key is that they're easy to guarantee as unique. The main disadvantage is that they don't have any meaning. There's no meaning that "28" is Wisconsin, for example, but when you see 'WI' in the State column of your Address table, you know what state you're talking about without needing to look up which state is which in your State table.

Q.What are Fact Tables?

Fact Tables and Types of fact tables

Ans- where summarized or aggregated data stored such tables are called fact tables.

Ex- Orders.

Fact tables are at centre of star or snowflake schema surrounded by dimension table.

Q. What are dimension tables?

Q. explain your current project architecture?

Q. difference between delete, truncate and drop?

Delete vs Truncate vs Drop :Oracle Interview Questions - Part 3

Q. What are the functions you have used?

ANS: character function

Aggregate function

Q difference between Rank and Dense_rank?

Q. Write a sql query to check duplicates?

Use group by command.

Find and remove duplicate records in a table | delete duplicates from a table

Q. What is a lookup transformation?

Ans------ Dynamic Lookup

Dynamic Lookup Transformation in Informatica

Ans–

Lookup transformation is used to lookup data in flat files, tables or views. Lookup transformation is used to get a value from a lookup table based on the source table.

Q.Diff between UNION and UNION ALL

Union

Union All

It combines the result sets of the two or more queries and removes duplicates from result set.

It combines the result sets of the two or more queries but does not removes duplicates from the result set.

It has slower performance.

It has faster performance.

It rearranges the output result.

It does not rearrange the output result.

Q.What are Joins? What is CROSS

Join?

Joins in Oracle (SQL) Explained in detail with practical examples

Q. How will you calculate 2nd Highest Salary?

Q.How to identify duplicate records?

Use group by command

SELECT

country,

COUNT(*)

FROM

world_population

GROUP BY

country

HAVING

COUNT(*) > 1;

Company:1) DELL:

6) Source table has 1000 records; only 900 records are loaded into the target. What will be your test approach?

7) What is 'Where Clause', 'Group by Clause’ And 'Having Clause’?

8) How will you test if your source system and target systems Different Databases,

i.e source could be MySQL and target could be Oracle?

9) How will write a MINUS Query target system other than ORACLE?

10) What is Regression testing?

Company: 2) NESS rechno oeies,

I) Explain your C roject archite ure

10)Diff bet DELETE and TRUNCATE

Q. Tell me about yourself.

2. How do you identify the duplicate records? write a query.

SQL>

Select empno,count(empno) from emp group by empno having count(empno) > 1;

3. Write a query for 2nd highest salary.

ANS: select * from emp a where 2=(select count(sal)

From emp b where b.sal>=a.sal);

4. Explain about your project.

5. Explain the data flow in your project.

6. What tables were present in your project? Explain.

7. How do you associate Joiner transformation in your project?

8. How do you move a file in Unix?

9. How do you check for a particular text/line in Unix?

10. Grep command

1Q. Create directory

12. How do you find a file from the folder?

Company: 71 ITC infoTech

Q. Explain about your project.

2. How many fact and dimension tables are there in your project. Write the table names.

DWH Interview Question :Number of fact and Dimension table in project|TechCoach

3. I low many columns are there in your fact table. Apart from the columns corning from your dimension table, what other columns are there.

4. What is your source table from where dimension is getting the data

ANS: flat file,oracle db,terrdata db

5. Write duplicate query.

6. How to search a string in your file using unix.

7. How to get 4th record in the file using unix.

8. What is touch command? •

9. What is the difference between database and DWH testing?

10. Does your table get data from DB or flat file? How do you compare then.

11. What are the performance issues that you face while testing.

12. How many records are there in your table?

Ans : 10 million

13. What you observed while testing the database and DWH.

14. Unix scripting.

Company: Datamatrix: (Client Honeywell Interview )

First round:

I. Tell me about yourself

2. Which tool you use

3. Reason for change

4. Difference between test plan and test lab

5. Difference between test case and test strategy

Company: 4)1MS Health First Round

Q. Tell me about ur self?

2. What are ur responsibilities?

3. What is Tresability Matrix?

4. Rate ur self in data warehousing, SQL, unix?

5. What are Joins? And why we use therm,/

6. Types of joins?..,"

7. ExPlain all types of joins using venn diagrams.

Joins in Oracle (SQL) Explained in detail with practical examples

8. How to find the current date in SQL?

Sysdate function

Select sysdate from dual;

9. Write a query using case Statement

SELECT OrderID, Quantity,

CASE

WHEN Quantity > 30 THEN 'The quantity is greater than 30'

WHEN Quantity = 30 THEN 'The quantity is 30'

ELSE 'The quantity is under 30'

END AS QuantityText

FROM OrderDetails;

10. Difference between delete and Truncate, drop and truncate-----

11. What is V-model (he asked rne to draw v-model diag)

12. What are the data modelling techniques u use in your project.

Conceptual

Logical

Physical

13. What is star and snowflake schema

Star and Snowflake schema explained with real scenarios

14. Any idea about hybrid schema

Ans- No, Hybrid schemas are very rarely used thats why I dont have any hands on experience on hybrid schema.

15. What is a mapping?

Ans- Mapping is data flow from source system to target system.

Kind of like a set of instructions

16. What is ETL?

Ans- It is mechanism to extract data from source, transform into desired format

And load into target.

17. How does u validate the targeted data?

18. What is a fact, types of facts

Second Round

1. Tell see about ur self and ur project?

2. Rate ur self in SQL?

3. What is sequence Generator? Write syntax

4. What are views?

View is table which is derived from existing table in DB

5. Write syntax to create a view on two tables?

Create view <viewname>

As select * from emp e, dept d where e.deptno=d.deptno;

6. Write syntax to extract data from two tables of different schemas

Ans—- In oracle schema means user

7. What is an index and its syntax /-

8. Types of indices

2021/10/28 15,17

6. What is V model and agile model

7. What is Join 7--

8. What is your approach of testing (what r the ETL test steps)

9. How do you test transformations?

10. What is severity and priority (explain with examples) ,-

Second round:

Q. He gave me one report format and asked me how do you test.

2. Join query

3. Minus query

4. Tablel: empid, empname, salry, dept Table2: empid salary

5. How do u write the minus query to find missing records in the above table.

6. Group by and having clause concept

7. DB link concept

Ans ---

Purpose

Use the CREATE DATABASE LINK statement to create a database link. A database link is a schema object in one database that enables you to access objects on another database. The other database need not be an Oracle Database system. However, to access non-Oracle systems you must use Oracle Heterogeneous Services.

After you have created a database link, you can use it to refer to tables and views on the other database. In SQL statements, you can refer to a table or view on the other database by appending @dblink to the table or view name. You can query a table or view on the other database with the SELECT statement. You can also access remote tables and views using any INSERT, UPDATE, DELETE, or LOCK TABLE statement.

8. If there is one DB file and one fiat file both contain same data if u load the data into data warehouse how do u test?

9. Rownum and rowid concept

10. Department wise second max salary

11. Self join query for find in the manager name for each employee.

Ans.

Oracle Format

select e.ename,m.ename

from employee e, employee m

where e.empno = m.mgr;

Univer wesal Format

select e.ename,m.ename

from employee e inner join employee m

on e.empno = m.mgr;

Q.Convert the null values in a column to ‘NA’.

Ans. Syntax

Select NVL(<Column Name>,<Value>) from <Table Name>;

select NVL (ename,'NA') from employee;

Q. Records are updates in source table, Write SQL to compare both using source table and target table?

Q. How can we compare the source file with the target table?

Q. What is data warehouse?

Q.Count the number of lines in a file in UNIX.

ANS: USE WC -L<FILENAME>.

Q. Compare command in unix, how you compare the files and what will be the output.

Q. How you remove the duplicate records from table.

ANS: delete from <tablename> a where rowid <>(select max(rowid) from <tablename> b

where a.id=b.id);

Q. How you design the test cases to test the ETL jobs.

ANS:by referring STM and BRD docs.

Q. Difference between Union and Union All.

Q. Difference between Delete, truncate, Drop.

Q. Difference between view and materialized view.

Ans-

View

Materialized View

Data will store logically

Data will store physically

It occupies less memory

It occupies more memory.

It reduces performance at time of reports.

It increases performance at time of reports.

With user interaction it can refresh at the time of calling.

It can refresh without user interaction based on time interval.

Possibility of duplicate rows in the view.

We can avoid duplicate rows by assigning PK at the time of creation.

Capgemini Interview 29-05-2021

Q. What are the 3 layers in ETL?

Q. What is BI(Business Intelligence)?

Q. What are ETL tools which you worked with?

Q. What is the difference between DWH and Data Mining?

Q. What is OLTP and OLAP?

Q. What is Data Mart and give me example.

Ans-

Data mart is one which is used to store subject oriented data.

Example: HR data

Q. What is difference between ETL and Database Testing?

DATABASE TESTING

DWH TESTING

Smaller in scale

Larger in scale

Usually used to test data at the source instead of testing using GUI.

2) Include several facts, ETL mech.

Being major one.

Usually homogeneous

3) heterogeneous data

Normalized data

4) de-normalized data

CRUD operation

5) Read only operation

Q. What are the characteristics of DWH?

ANS> 1. SUBJECT ORIENTED,

2. NON-VOLATILE,

3. TIME - VARIENT,

4. INTEGRATED

Q. What are types of DWH systems?

ANS >

Q. What are the possibilities of ETL bugs?

ANS >

Duplicate data

Null data

Data discrepancy

Table Structure issues

Index unable to drop issue

Data issue in source table

Index is not created after job run

Data count mismatch between source and target

Data not matching between source and target

Duplicate data loaded issue

Trim and null issue

Data precision issue

Date Format issue

Business Transformation rules issue

Performance issue.

Q. What is ODS?

ODS database (Operation data Store ), Its properties and purpose explained with examples

ODS: ODS is also a similar small DWH which will help analyst to analysis the business. It will have data for less number of days. Generally it will be around 30-45 days. Like DWH here also surrogate keys will be generated, error and reject handling will be done.

Operational Data store is used by many organizations for analysis purpose as well as for data backup and data recovery. Data stored in ODS is usually in Normalized form as in transactional DBs.

While in DWH data will be denormalized. ODS is actually a replica of Transactional Database, collecting two or more Business functions data. Ex ODS may store CRM as well as ERP data.

Q. What is partitioning?

Q. What is ETL mapping?

ANS> It represents data flow from source to target.

Q. What is a mapplet?

ANS> Maplets creates or configures a set of transformation which is reusable.

Q. What is Data Purging?

ANS> permanently deleting data which is not relevant for business purpose from DWH.

Q. What is Normalization and denormalization?

What is Normalization in SQL? | Database Normalization Forms - 1NF, 2NF, 3NF, BCNF | Edureka

ANS>

Normalization : - it is a technique of db design which is used to reduce the redundant data or to avoid duplicate data.

De- Normalization : - it is a technique of db design which is used to add the duplicate data.

Q. What is Index and its types?

Index Types

ANS>

Index is a pointer which is used to point the data.

TYPES : B-TREE, BITMAP indexing

Q. What trigger?

Q. What are the constraints?

ANS: It is used to limit the type of data that can go into the table.

Specified while creating the table or after table is created with alter command.

NOT NULL

UNIQUE

PRIMARY KEY

CHECK

FOREIGN KEY/REFERENCE KEY

Q. What is Data Integrity?

Q. What are the advantages of stored procedures?

1) Tell me about yourself .

2) Roles and responsibilities.

3) Tell me about your project.

4) What is your role in your project?

5) Explain the complete data flow of your project.

6) You have source and target. And you have test case to check if expected data is loaded correctly or not. Then will you validate?

Ans- Yes, Why not.

7) I have 10 tables in oracle database. The data is loaded into sequel database. How will you validate if data has migrated successfully?

Ans-

First I will check if Db link is established or not.

If established then I can compare data with column mapping

If not established then I have to export both source and target to flat files and then I have to compare them.

8) How will you check if any record is missing in target table?

Ans-MINUS Query

Or Column Mapping Validations

9) Can you tell me the duplicate queries.

Ans-

Select empno,count(empno) from emp group by empno having count(empno)>1;

10) What is join? What are the different types of joins?

11) What is self join?

12) What are different SQL functions you have used in your project?

13) How to get the 3rd highest salary?

select * from

(select emp.*, dense_rank() over (order by sal desc) as highest_sal from emp)

where highest_sal =&n;

14) What are set operators?

ANS> UNION

UNION ALL

INTERSECT

MINUS

15) Tell me the difference between union and union all?

16) Which tool are you using for defect management?

17) What is priority and severity?

18) What is data masking?

ACCENTURE R1

1) Which methodology is used in your project?

2) What is sprint?

3) What is sprint review and sprint retrospective meeting in agile?

4) What is sprint backlog?

5) What is story point?

6) What is estimated time for one story point?

7) Who will assign the story points?

8) What are joins in SQL?

9) Tell me the difference between inner join and self join.

10) What is star scheme?

11) From where the data is coming into fact table?

12) Tell me some column names in fact table in your project.

13) How will you relate the fact to the dimension table?

14) Can you tell me the defect that you have raised recently?

15) How will you define severity and priority?

16) If you find a defect and assign it to development team and dev team says it’s not a defect. Then what is your response?

17) Tell me the output of the following queries.

A) Select substr(‘Accenture’,1,3) from dual;

Output- Acc

B) Select substr(‘Accenture’,1) from dual;

Output- Accenture

C) Select substr(‘Accenture’,1,3,4) from dual;

Output- Too many arguments for function

18) Consider a table having salary as one of the column. Wherever salary is null that should be displayed as NOT APPLICABLE. Write a query for the same.

19) Where, order by, having, select, group by, distinct. Write these clauses in correct order.

Select > Distinct>from>where>group by>having>order by.

21) Which tool are you using for test management?

22) Which ETL tool are you using?

23) What is job scheduling? Which tool are you using for it?

Ans- Tidal

25) What is regression testing?

26) What is retesting?

ACCENTURE L2

1) Tell me about yourself.

2) What is star schema?

3) What is fact table and dimension table?

4) What is snowflake schema?

5) Tell me the difference between star schema and snowflake schema.

6) What is the difference between normalization and denormalization?

7) What is SCD? Explain the types of SCD.

8) Can you tell me the difference between the database and data warehouse?

9) Tell me about your project.

10) What is your role in your project? What are all activities you do as tester?

11) How many tables you are testing in your project?

12) Tell me the source table names that you have tested.

13) What are the validations you perform to test the data in target system?

14) Tell me the duplicate query.

15) What are transformations you have used in your project?

UGAM SOLUTIONS R1

1) Tell me about your project.

2) What are the different sources in your project? In which format the data is coming?

3) Can you tell me the complete data flow of your project?

4) What are the validations you perform from source to target?

5) Which query will you write to validate data completeness test?

6) Tell me the the query to find the duplicates.

7) Which query will you write to check if any duplicates present in target table?

8) What are the test cases you are validating when data is coming from flat files?

9) How will you compare the data if data is coming from flat files?

10) What are the documents you refer in your project?

11) What is BRD and FSD document?

12) What are the contents of mapping document?

13) Who provides you mapping document?

Ans - Developer

14) If you have any doubt regarding requirements, then to whom will you contact?

15) What is star schema and snowflake schema?

16) Which schema you are following in your project?

17) Which methodology you are using in your project?

Ans- Agile Methodology

18) What are the meetings you are involved in?

Ans- Scrum Call Meeting (Stand up Call) (Daily 20 minutes)

Sprint Retrospective

Sprint Planning Meeting

19) What are your daily activities as tester?

20) Tell me the difference between primary, surrogate key and natural key.

21) Can you explain the difference between delete, drop and truncate?

22) What are the analytical functions you have used?

23) If I have some void spaces in column and I have to remove those spaces from left side. Then which function will you use?

Ltrim() function

24) WAQ to remove the spaces from both left and right side.

SQL> Select trim (' prasad ') from dual;

25) WAQ.

Input string: Ug am

Output string: Ugam

SQL> select ('ug'||'am') from dual;

SQL>select replace('ug am',' ') from dual;

26) Which function do you use to get the position of character? Give an example.

Ans— Instr

27) Consider in a table column, there is data in some rows, blank spaces in some rows and null values in remaining rows. Wherever there is null value or blank space, it should be displayed as ‘UNKNOWN’ and remaining data should be displayed as it is.

WAQ for this.

SQL> Select replace(to_char(nvl(comm,0.1)),0.1,'UNKNOWN')from emp

28) What are the different types of join

1) As an ETL tester, what do you think is an most challenging aspect when you start testing?

2) What do you prefer to use, joins or subqueries? Why?

3) What is the basic difference between inner join and outer joins? When will you use which join?

ANS: joins

4) Can you tell me briefly about your QA team in the project?

5) How will you coordinate with client?

6) Who assign you the work? And To whom do you update the work status?

7) If you have any question on the task assigned to you, to whom will you contact?

8) If you are working on a ticket and you have a question. Then what will you do?

9) What is the work of BA ?

10) How your business team is structured? Where does the BA fit and where does the product owner fit in?

11) Let’s say you are working on a ticket and you identify a defect? When you talk to the developer, developer says that it’s an expected scenario. What will you do next?

13) Display ID of the employee who is getting third highest salary in the organization.

SQL>

select empno from

(select emp.*, dense_rank() over (order by sal desc) as highest_sal from emp)

where highest_sal =&n;

14) What is dense_ rank?

15) What kind of technologies have you used?

16) What is the use of aggregator transformation?

17) How to update the records in the target?

18) What is the difference between Source qualifier transformation and Joiner transformation?

19) How the flags are changing when records are updated in source table?

20) Can you explain the SCD type 2 map you have built?

L&T R1

1) What is referential integrity?

2) What is foreign key?

3) How will you validate the source data loaded properly in target or not if both source & target are tables?

4) How will you validate there is no duplicates in target table?

5) Do you have any idea of composite key?

6) How will you validate if the composite key is implemented correctly or not?

7) What is left join?

8) Can you tell me one scenario where left join can be used?

9) What do you know about inner join?

11) What is surrogate key?

12) Do you have any idea about slowly changing dimension?

13) Let us consider there is a target table which is SCD type 2. How will you validate the

table?

14) Do you have any idea about view?

15) What commands you are aware of in Unix?

16) What is the use of grep command?

17) I want to rename a file. Then which command will you use?

Ans- mv <old filename><new filename>

18) Do you know joins?

Ans- Yes.

19) What is the difference between Union and Join?

Union

Joins

Column Count should be same

1.Column Count may or may not be same

2.Data type should be same

Column Data type May or may not be same

But condition column data type should be same

Column order should be same

3. Column Order may or may not be the same

20) What is differed defect?

21) How will you decide the severity of defect?

22) What was the last defect you found?

Ans- Duplicate Records Found

23) Let us consider your mail id. The output should be only gmail.com.

WAQ.

SQL>

select substr ('PrasadBhakalya@gmail.com',

Instr('PrasadBhakalya@gmail.com','@' )+1)

from dual;

24) Let us consider there is table where there is Gender column having ‘Male’ and ‘Female’ as data in it.

WAQ to replace male as female and female as male.

SQL>

UPDATE INPUT_TABLE SET GENDER =

CASE

WHEN GENDER = ’M’ THEN ‘F’

WHEN GENDER = ’F’ THEM ‘M’

ELSE NULL

END;

L&T R2

1) Describe your project.

2) What is the data flow of your project?

3) What are the validations you do in your project?

4) Display second highest salary in each department.

ANS-

sql>

SELECT DEPTNO,MAX(SAL) FROM EMP WHERE SAL NOT IN

(SELECT MAX(SAL) FROM EMP GROUP BY deptno)

GROUP BY deptno

5) Display the department which gives maximum aggregated salary.

SQL>

SELECT DEPTNO FROM

(SELECT DEPTNO,SUM(SAL)AS TOTAL FROM EMP

GROUP BY deptno ORDER BY TOTAL DESC)

WHERE ROWNUM=1

SLK

1) Tell me about your project.

2) Can you tell me the end to end details of your project and different databases used?

3) What kind of scheme have you used in the data warehouse?

Ans- STAR SCHEMA

4) Why do you prefer star schema? Why not snowflake or other schema?

Ans-

Because of denormalized data it is easy to implement.

According to clients requirement DWH needs high performance

It cannot be achieved by any schema other than star schema due to joins

Thats why Star schema is preferred.

5) What is metadata in DWH?

6) What is an ODS?

Operational Data store is used by many organizations for analysis purpose as well as for data backup and data recovery. Data stored in ODS is usually in Normalized form as in transactional DBs.

While in DWH data will be denormalized. ODS is actually a replica of Transactional Database, collecting two or more Business functions data. Ex ODS may store CRM as well as ERP data.

7) What is the difference between OLTP and OLAP databases?

8) What is SCD? Explain me SCD type 1, type 2 & type 3. 9) What are the aggregate functions in SQL?

10) What are the constraints in SQL?

11) What is the difference between self join and inner join?

12) What is data integrity?

13) What is E-R diagram?

14) What is view?

15) What is difference between Delete and Truncate statements?

16) Can you explain the defect life cycle?

17) Consider employee table with ID and Ename columns in it. The pattern of Ename is like prefix_name for each employee. I want you to perform Select query to display only names without any prefixes.

Ans-

SQL> select substr(ename,instr(ename,'_')+1) from employee;

18) In oracle database if you want to compare two tables, which keyword do you use?

19) If source table is having 4 columns and target table is having 6 columns, can you perform Minus query to compare?

Ans- No, We cannot perform Minus query.

For Minus query to work Column order, Column data type and column count should be match.

20) What is the error when column count is not same in Minus query?

Ans- ORA-01789: query block has incorrect number of result columns

Data Mismatch,

Column Mapping failed

FIRST ROUND

1) Tell me about yourself, Roles and Responsibilities & about your Project.

2) Which Methodology you are using in your project?

3) Can you Explain briefly about Agile Methodology?

4) Which Tool you are using for Sprint Board Planning?

5) How you are attending the scrum call, from where you pick the stories, if you got stucked somewhere how will you give your status overall? which Tool you are using for this?

6) Suppose if you are getting a Defect in particular story then how will you comment, to whom you are assigning and How you are sending that Defect?

7) What data integration Tool you are using (which ETL tool you are using?)

8) Can you explain me the concept of ETL?

9) Data is coming from multiple sources how will you verify OR identify that all data is loaded into target table means suppose I have 1000 records then how will you verify 1000 records loaded into target table n if 10 records are missing how you are going to validate?

10) Where you can see the log file?

11) Can you tell me why we need the staging layer in ETL process?

12) Suppose my source is flat file how you are going to validate those flat files?

13) What is the difference between OLTP & OLAP?

14) What is Dimension table?

15) How it is different from the Fact table?

16) What is Datamart? Give Example.

17) What type of ETL Validations you are doing?

18) Recent bug that you have encountered?

19) What are SCD Type-1, Type-2, Type-3?

20) Any function you are using for validating the SCD Type-2?

21) What is a Maplet?

Ans-

Mapplet- A Mapplet creates or configures a set of transformations which is reusable.

22) What is worklet?

23) What is workflow?

Ans- Workflow- A workflow is set of instructions that tells the Informatica server how to execute the task.

24) What is a session?

Ans-

Session- A session is a set of instructions that describe how and when to move (scheduling to run the jobs) data from source to target.

25) What is the difference between junk dimension and conformed dimension?

26) Customer dimension is a junk dimension or conformed dimension?

Ans- Conformed Dimension

27) Tell me the difference between Joins & Subquery?

28) Can you find out the first 3 characters of your name?

SQL> Select substr (‘Prasad’,1,3) from dual;

29) Suppose the string is ‘PrasadBhakalya@gmail.com’ then how will remove @gmail.com?

SQL> select

substr ('PrasadBhakalya@gmail.com' ,1,

Instr('PrasadBhakalya@gmail.com','@' )-1)

from dual;

30) Do you have experience on MDM?

31) Suppose I have EMP table then how will you display 50% records of the data?

SQL> select * from emp where rownum <=(select count(*)/2 from emp)

SECOND ROUND

1) Introduce yourself

2) Do you have any idea on writing Test Scenarios, Test cases?

3) Which tool you are using for writing Test cases, Test strategy?

Ans- Quality Center From Mercury Softwares

4) Can you take a particular scenario and Explain me and tell me your Approach on that?

5) Your project is running on which methodology?

Ans- Agile Methodology.

6) What is the duration of your SPRINT?

Ans- 2 Weeks.

7) Assume that you have given a story to test then what is the basis for writing your test cases?

8) During your testing if you encountered a DEFECT, What will you do?

Ans- Raise the defect in defect management tool like HP ALM or Jira

9) Explain Defect life cycle?

Ans-

10) What are various types of the TESTING usually you do in a software projects?

11) Who will do the UNIT Testing?

Ans- Unit and Integration Testing is done by Developer.

12) What is Integration Testing? Who will be responsible for that?

Ans- Unit and Integration Testing is done by Developer.

13) What is Regression Testing?

14) Have you ever done Performance Testing?

15) Who will be responsible for UAT(User Acceptance Testing?

16) Suppose you have completed one story without getting any Defect how is tested code move in Production Environment?

17) Have you ever developed any report status while testing?

18) What kind of scenarios you have used to test in the Database?

19) In your project what is the source for your application and what is the destination?

20) What are the challenges you have faced while testing the data?

21) I want to hear any one specific example where u said that ok I was putted into this challenge, I gained this much knowledge because of that it made me more professional in terms of testing.

22) In your __years of experience have ever felt that I m doing repetitive work?

23) Have you ever thought that in your project we can bring some automation tools?

24) What do you think is your biggest strength?

Ans- Ability To learn new things.

25) What is your weakness?

Ans- I dont have much patience.. I have to work on that.

26) Have you ever heard about data lakes?

Ans-

WIPRO

1) Tell me about yourself.

2) Which version of INFORMATICA tool are you using?

Ans- 9.0 Version

3) Could you tell me what is your source and target system?

(which database and in which format)

Ans- Flat Files as Source to

Oracle Database as Target

4) Which tool you are using in order to connect to database?

Ans- Oracle SQL developer, TOAD for Oracle

(Any one of them)

5) Which Methodology you are using in your project?

Ans- Agile Methodology

6) While Assigning the stories, Resolving the stories or updating the stories which tool you are using?

7) Can you tell me the difference between BUG & DEFECT?

Ans- As per my understanding both are same.

8) Can you explain BUG / DEFECT life cycle.

Ans-

9) What is the difference between RETEST & REGRESSION TESTING.

10) Can you tell me the difference between TEST SCENARIO & TEST CASES with

example.

11) I want to find the duplicate records then which SQL query do you use?

12) In EMP table I want to check salary column is having null value, which query will you Write?

SQL> select * from emp where sal is null;

13) Can you tell me the difference between STAR SCHEMA & SNOWFLAKE SCHEMA.

14) Suppose I have two tables EMP & DEPT table both are having relationship of foreign key with department ID. Can you let me know which table is called as FACT table and which table is called as DIMENSION table.

15) In order to search a particular string which command is used? (Unix)

Ans- GREP <string><filename>

16) If I want to find duplicate records in particular file which command is used?

Ans-

17) In Informatica, How and where do you run the work flow?

Ans- In Workflow Manager and Workflow Monitor we can run work flow

In Workflow Manager Window

Right Click on Workflow tab then Click on Run Workflow

Right Click on Workflow Designer Window Then Click on Run Workflow

In Workflow Monitor

If Workflow is Running or Failed

Then we can restart the workflow from here only

18) If the session get failed where will you check?

Click on Session then Get Run Properties

Or you can check in Session Log also

19) If the error was due to database connection failure. Where will you check the connection details?

20) Have you ever worked with a parameter file?

21) Suppose Developer has given the connection details to you in PARAMETER file to test with 3 different locations America, Europe & Asia on only one work flow & the connection have been parameterized, so if you want to execute for 3 different locations but default is America. I want to run Europe or Asia then how will you execute?

Answer: It will be executed through command, By using pm and cmd line commands where we can change the parameter and we can run the workflow.

22) Why the views are created on tables?

23) Suppose I am going to execute a view so will it go to its base table or give the result?

24) Why INDEXES are created?

25) Suppose I am loading data into a table and that table contains index then what will happen OR which steps I need to take care before loading?

ANS: In order to load the data you need to drop the INDEX n then after loading the data you need to create the index.

26) What is INITIAL & DELTA LOADING.

Initial Load

First time loading source data into data warehouse called initial load.

Initial load will completely truncate existing data from all the target tables and reload with fresh data.

Delta Load/ Incremental Load

Second time aur modified source data loading to data warehouse is called incremental load.

Incremental load apply on going changes to one or more tables based on predefined changes.

Once a data is updated in source then corresponding target table showing updated data needs to be tested.

In the case of SCD is data behaving as expected needs to be tested.

Citius Tech

1) Smoke testing and sanity testing difference

2) defect life cycle diagram and explain

3) verification and validation difference

4) quality center each Tab explain and how u implement in ur project

5) regression testing in Ur project

6)what is retesting

7) explain Unix commands used in ur project

8) grep command use and syntax

9) star schema and snow flake schema diagram and explaination

10) data mart and data warehouse difference

11) ur project architecture and explaination

12) what are the bugs u found in ur project

13) project validations

14) difference between agile and waterfall

15) joins and sub queries related questions and need to write queries

16) decode, substring,instring, rank, dense rank, set operators, constraint related queries We need to write (they given simple tables)

17) what are the transformation u used in ur project and how u use

18) work flow manager, work flow moniter, mapping,maplet

19) incremental load explain and how u use this in ur project

20) what is normalization and types of normalization

ANS-

There are three types of Normalization

1NF,

2NF,

3 NF

21) how you compare data from different database?

HCL first round

1. Tell me about yourself.?

2.project architecture.

3.diff b/w oltp n olap

3.when we use rank, dense rank n rownum

4.Write a query to get phone number as 123-456-7890 format.

SQL> select substr(number,1,3)||'-'||substr(number,3,6)||'-'||substr(number,6,4) from customer;

5. Write a query to get nth value by using sub query n correlated query.?

Ans-

SQL>select * from

(select emp.*,rownum as rank from emp)

Where rank=&putnumber;

B}with abc as

(select emp.*,rownum as ranknum from emp)

select * from abc where ranknum =1;

Co-Related Query

A}SQL>

select * from emp a where

(select count(rowid) from emp b where b.rowid<=a.rowid) =(&n);

6.what is primary and composite key.?

7.write query on coalesce

8.write a query for self join

9 different types of loads.

10.What is fact table , different types of fact tables.?

11.Difference b/w star n snowflake.?

12.In which of schema's we normalized n denormalized data.?

13.Tel me test scenarios,cases you wrote in ur project.?

14.Which etl tool ur using.?

15.What is workflow, mapping, session n mapplets.?

Ans-

Workflow- A workflow is set of instructions that tells the Informatica server how to execute the task.

Mapping- Mapping represents dataflow from sources to targets.

Session- A session is a set of instructions that describe how and when to move (scheduling to run the jobs) data from source to target.

Mapplet- A Mapplet creates or configures a set of transformations which is reusable.

INFY INTERVIEW QUESTIONS

1st round

1. About project & arch

2. About SQL queries

3. Defects - DLC, My defects seen

4. How I work

5. Work types

6. Roles & responsibilites

7. Experience in SQL

8. Experience in Stored procedures

9. ETL & basic info,

10. ETL jobs questions

11. About Informatica & all stages

etc.

2nd round

1. All practical questions

2. Gave user story & asked me my approach

3. Made changes in the user story & now how will I work

4. What is test data - all I did in my project

5. Gave stored procedure & then asked questions on that - USER story on this as well - made changes in this as well & asked me to answer

6. About AGILE & how I take part

7. What is my role in the Agile

8. Most of the questions on the practical scenario & dynamic questions

KPMG interview qsns

1 tell me about yourself

2 project architecture

3 joins types with example

4 duplicate query

5 star schema v/s snowflakes

6 if we use snow flake schema in our project what all the problem we face in production

7 what is the featured and forward in ur project

8 what are the vessels present in ur project

9 what is BRD in details

10 what type of defects u found during testing.

Forwarded as received

interview questions..

1. Tell me about yourself..

2. Brief about your project architecture.

3. Unix Commands.

4. SQL Joins ( types, explain with real-time examples)

5. Nth highest salary

6. Group by function

7. What is difference between where clause and having clause.

8. Types of schema, explain star schema.

9. Which type of scd u have in your project.

10. Explain scd and it's types.

11. Tell the validations for incremental loading.

12. Tell the query to retrieve newly added data from scd2 target table.

13. What are the technical attribute you have in scd2 target table.

14. T1 table have 200 records.

100 records copied to T2 table.

Remaining 100 records copied to T2 table

Write query to check data copied correctly..

Today I have attended interview in UST global

Saturday

1st round

Tel me about urself and project details and tools

Today

2nd

Qsns asked in UST global

1 tel me about yourself

2 duplicate query

3 join query for four table with four diff clm

4 diff PK vs SK

5 Fact table

6 dimension table

7 look up transformation

8 hp alm defect life cycle

9 record count query

10 instring and substring qsns

11 joins query

HCL first round

1. Tell me about yourself.?

2.project architecture.

3.diff b/w oltp n olap

3.when we use rank, dense rank n rownum

4.Write a query to get phone number as 123-456-7890 format.

ANS- select

substr(number,1,3)||'-'||substr(number,3,6)||'-'||substr(number,6,4)

from customer;

5. Write a query to get nth value by using sub query n correlated query.?

6.what is the primary and composite key.?

7.write query on coalesce

8.write a query for self join

9 different types of loads.

10.What is a fact table , different types of fact tables.?

11.Difference b/w star and snowflake.?

12.In which of schema's we normalized n denormalized data.?

13.Tel me test scenarios,cases you wrote in your project.?

14.Which etl tool ur using.?

15.What is workflow, mapping, session n mapplets.?

Ans-

20. Input Table

GENDER

A)Display the Output

GENDER

SELECT * FROM

(SELECT INPUT.*,DENSE_RANK() OVER (PARTITION BY GENDER ORDER BY ID) ORD FROM INPUT)

ORDER BY ORD,GENDER;

B) Update input table to get

GENDER

ANS-

UPDATE INPUT_TABLE SET GENDER =

CASE

WHEN GENDER = ’M’ THEN ‘F’

WHEN GENDER = ’F’ THEM ‘M’

ELSE NULL

END;

Q.Name few ETL bugs that you found?

Ans-

Table Structure Issue

Index unable to drop issue

Index is not created after job run

Data issue in source table

Data Count mismatch between source and target

Data not matching between source and target

Duplicate data loaded issue

Trim and Null issue

Data precision issues

Data format issue

Business transformation rules issue

Performance issue.

Tech Mahindra:

30/08/2021

1. What is your skill area,what is the core area having presently let us know?

2. How many years of Experience in ETL Now?

3. As Part of ETL Testing what all activities you have done? how you go with your ETL procedures?

4. What and all testing you done in ETL? null check? How Good you are at SQL?

5. How do you identify duplicates in a column?

6. In the source table particular column having a PK and In destination it is mapped to particular table column is not a PK how do u identify, what all the things you verify?

7. As part of the Schema validation what and all you do?

8. As part of Completeness testing what and all you do?

9. As part of mapping documents is concerned what and all activities you do?What is RTM?

10. Have you been invloved in migration kind of a project?

11, Presently whatever the data and target you are working?like Source & Target DB?

12. Are u aware of something related to Terra data?

13. How big is your team? How many memebers in your team?

14. What is the volume of data that you usually verify? In that case how do you mange data consistancy?

15. Where are presently based out of?you are basically from?

16. Is that for any of these when you do once transformation and all is done in millions of data, is that you performance testing done by you? or any other guy?

17. What exactly you mean by partioning?

18. Coming to the requirement gathering and all those things right what are the process usually followed?

19. For example in one of column in the table is null, if you pass it as 'Blank' value what will happen?

Iqvia Interview Questions

Round 1

Tell me about yourself.

About Project

Duplicate query

Delete Duplicate Record

Dense Rankl

Remove Junk Data in Aus@tr#al&ia —---> Australia

Select 3 from Dual Where 3 not in (1,2, Null); What is the output?

Write a query to get name from email Id Aishwarya12@gmail.com—-------> Aishwarya12

Unix Command to move a file from one source to another source.

Get the employee and their manager name from emp table.

How many log files are there in Informatica?

Validation that you performed.

WAQ to get the following output. Source Target 20210608 —--> 2021–06-08

Active and Passive Transformation.

WAQ to get employee name, Avg age,their city. WAQ to get employees where age is > 30. EMP

Empno

Name

CityID

ABC

DEF

GHI

CITY

CityID

CityName

Age

BNG

MUMBAI

PUNE

ROUND 2

Tell me about yourself.

Project

WAQ to get desired output in target

Source

Target

Complex Query you worked on recently.

What Transformations have you used in your project?

Unix command to find 5th line in a file

Toad/head command to find 5th line in a file

Command to search a string in a file.

Command to get 5th column from a table.

WAQ to get data of the last two previous months.

Aish#wa12ry@a —------> Aishwarya

Case Statement Query

Sid

Product_name

Sales_Quantity

Date

Apr2020

Mobile

Mar2020

Laptop

Apr2020

Smartwatch

May2020

Apr2020

Mobile

Mar2020

WAQ to get third highest Sales_Quantity from the product table.

WAQ to replace ‘LG’ to ‘LGY’ in the target table.

14.CITY

CityId

CityName

Population

Bangalore

2,04,768

Mumbai

640000

Chennai

32,467

WAQ to get population of the city greater than that of Banglore;

Select population from city where population >

(select population from city where cityName =’Bangalore’)

15.

16.

SQL>

select a.id,b.indicator

from a left join b

on a.id=b.id;

17.

Tell me about yourself.

Types of DCL and TCL commands

Ans— DCL (Data Control Language) —--> Commit, Rollback,savepoint

TCL(Transaction Control Language) —----> Grant, Revoke

Asked about rollback syntax, savepoint

What is syntax for rollback for savepoint.

Ans- Save point command

By using this statement you can and undo parts of transaction, instead of the whole transaction.

When you execute the savepoint statement you create a mark in the transaction Which can be used later to undo changes that were done after that point.

Everytime you execute the save point statement you have to provide a name for the savepoint you created.

Syntax

Rollback to savepoint c;

What is mean by Normalization and types, Why it is required?

What are types of Shell Scripts. Which Shell Script using for current project.

Tell about Agile Method.

Explain about Joins, types

Difference between equi join, inner join, self join, left join and left outer join

Team size of your current project.

About waterfall model and V-model.

What are the validations u r doing between source nd landing area, landing area nd staging, staging area and dwh

Offline Questions

L & T

1.Tell me about Yourdelf & Roles and responsibilities.

2.What do you mean a views and write a query to create view.

Ans-

SOL> create view view_example as

Select * from emp;

3Query to create table

4. Query to create a copy of table.

5.Diff between minus and intersect with example.

6.How to fetch last record from table and write query.

SQL>

Select * from emp where rowid =

(select max(rowid) from emp);

7. What is ‘First’ function.

8.Diff between normalization and denormalization.

9.Types of normalization.

10.Diff between SQL and Mysql

11. Types of relations in SQL.

12.SDLC and STLC and its process

13.In which defect tool you have experience?

14.What are the activities you will do in ALM?

15.Do you have experience in Jira tool?

16. have you prepared scenarios?

17. Do you know about ISTP?

18. What is Alias in SQL?

19.In which domain you worked?

20 what is Policy Life Cycle?

21.Types of Insurances

1. Defect Life Cycle.

2. How do you compare flat file and database data?

3. How will you compare flat file with target table in database if flat file data has loaded into target by applying some business logic?

4. Diff between delete and truncate?

5. Have you prepared test plan? Ans-No

6. Have you prepared test strategy document?

Ans- No

7. Different Unix Commands?

Ans-

cd(change directory),pwd(print working directory), diff, mv, grep, cat, wc(word count), touch

vi editor commands

8. Project Architecture?

9. What are test strategies you followed for validation of data. For incremental, initial For SCD2,SCD1

10. Why you choose snowflake over star schema? Tell Differnece

11. What are day-to_day activities of your work?

12. Difficult scenario you have written and write the query?

13. What are negative approach you have done in validation?

14. Why you use QC when you are using agile methodology?

15. What are difficulties you faced in your project?

16. Components of Informatica PowerCenter.

Ans- Repository Manager, Workflow Manager, Designer Window, Mapping Manager

हा ब्लॉग शोधा

Parshu.

NSR

टिप्पण्या

टिप्पणी पोस्ट करा

या ब्लॉगवरील लोकप्रिय पोस्ट

सी. डी देशमुख (चिंतामणराव देशमुख)

तंबाखू