ETL Testing Interview Questions and answers for beginners and experts. List of frequently asked ETL Testing Interview Questions with answers by Besant Technologies. We hope these ETL Testing Interview Questions and answers are useful and will help you to get the best job in the networking industry. This ETL Testing Interview Questions and answers are prepared by ETL Testing Professionals based on MNC Companies expectations. Stay tuned we will update New ETL Testing Interview Questions with Answers Frequently. If you want to learn Practical ETL Testing Training then please go through this ETL Testing Training in Chennai.
Besant Technologies supports the students by providing ETL Testing Interview Questions and answers for the job placements and job purposes. ETL Testing is the leading important course in the present situation because more job openings and the high salary pay for this ETL Testing and more related jobs. We provide the ETL Testing online training also for all students around the world through the Gangboard medium. These are top ETL Testing Interview Questions and answers, prepared by our institute experienced trainers.
Here is the list of most frequently asked ETL Testing Interview Questions and answers in technical interviews. These questions and answers are suitable for both freshers and experienced professionals at any level. The questions are for intermediate to somewhat advanced ETL Testing professionals, but even if you are just a beginner or fresher you should be able to understand the answers and explanations here we give.
Q1) What is ETL Testing and how its not the same as database Testing?
ETL testing applies to Data Warehouses or Data joining ventures while Database Testing applies to any database holding information (regularly exchange frameworks). Here are the abnormal state tests done in each:
ETL Testing: Primary objective is to check if the information moved appropriately not surprisingly
Database Testing: Primary objective is to check if the information is following the guidelines/benchmarks characterized in the Data Model
ETL Testing :
- Confirm that the checks in the source and target are coordinating
- Confirm that the information is coordinating among source and target
- Confirm that the changed information is according to desire
- Confirm that the information is gradually getting refreshed
- Confirm that the remote – essential key relations are safeguarded amid the ETL
- Confirm if there are any copies in the stacked information
Database Testing :
- Check that the remote – essential key relations are kept up and there are no vagrant records
- Check that the information in the sections had substantial qualities. eg. On the off chance that there is an area (encoded list) characterized for a section, check if the qualities in the segment meet that prerequisite
- Check that the information in segments is precise. eg. In the event that you have an age segment, does it have values more than 100
- Confirm if information is absent in sections where required. eg. On the off chance that is a section is relied upon to have information dependably, check if there are any invalid qualities.
Q2) what are the smoke test can be performed on the ETL Testing?
- primary key
- duplicate key check
- table completeness
- Referential interigity
- cdc check
Q3) What are ETL tester responsibilities?
- prepare the test plan,test scenario and test case according to the requirement
- walkthrough all the test artificat with team
- ensure that environemt is ready before running
- ensure to perform smoke test
- verify that data is correctly moved from src to target system with necessary transformation
Q4) if there are ten thousand records in a source system, how do we ensure that all ten thousand are loaded to the target without any function
src query
minus
target query
output should result 0 records if src and target is in sync
Q5) How you will display all the columns in the table?
select * from tablename (where * indicates all the columns in the columns)
Q6) How to chk the Primary Key?
assume we have (student_id as a primary key in the student table)
select
student_id,
count(*)
from
student
having
count(*) >1
Q7) how to chk is there any duplicate records is exists in the table?
assume we have student table with columns student_id,student_name,dept_id,phone_number
select
student_id,student_name,dept_id,phone_number,count(*) from
student
group by
student_id,student_name,dept_id,phone_numb
having count(*)>1
Q8) Assume we have employees table (around 100) record. write a query to find the which employess as joined first in the organisation?
select a.* from
(
select employee_id,first_name,last_name,rank() over(order by hire_date asc) as rk from
employees
)a
where a.rk=1
Q9) what are various set operators available in SQL?
- union
- union all
- intersect
- minus
Q10) write a query to calcualted how many employees on each department?
select
department_id,
count(*)
from
employees
group by department_id
Q11) select all the employees where third charcter of first_name is ‘a'
select * from
employees
where first_name like ‘__a%’;
Q12) how to check whether table has been loaded with data are not?
select count(*) from employees
having count(*) >=1
Q13) write a query to updated all the employess salary base don department
if dept_id in 10,20 then increase by 0.25
if dept_id =30 then increase by 0.20
if dept_id in 40,50 then increase by 0.15
if dept_id > then increase by 0.10
update employees
set salary =salary + (case when dept_id in (10,20) then salary *0.25
when dept_id=30 then salary*0.20
when dept_id in (40,50) then salary*0.15
else salary *0.10
)
Q14) write the query to find second max salary in each department
select a.* from
(
select employee_id,first_name,last_name,rank() over (partition by department_id order by salary desc) as rk
from employees
)a
where a.rk=2
Q15) compose a question to refreshed employess somewhere in the range of 150 and 180 compensation dependent on office
on the off chance that dept_id in 10,20, increment by 0.25
on the off chance that dept_id =30, increment by 0.20
on the off chance that dept_id in 40,50, increment by 0.15
on the off chance that dept_id >, increment by 0.10
refresh representatives
set compensation =salary + (situation when dept_id in (10,20) at that point pay *0.25
at the point when dept_id=30 then salary*0.20
at the point when dept_id in (40,50) at that point salary*0.15
else compensation *0.10
)
where employee_id somewhere in the range of 150 and 180;
Q16) how to make another table from effectively existing table with no information
make tablename as
select * from tablename where 1=2
Q17) how to make another table from effectively existing table with any information make table tablename as
select * from tablename where 1=1
Q18) what is the contrast among subquery and correclated subquery
Related Subquery is a sub-question that utilizes values from the external inquiry. For this situation the inward question must be executed for each column of external inquiry.
Basic subquery doesn’t utilize values from the external inquiry and is being determined just once
Q19) what are the joins accessible in sql
- inner join
- outer join
- left external join
- right external join
- full external join
- self join
- cross join or cartesian item
Q20) write a question to discover the which supervisor has overseeing in excess of 5 representatives.
SELECT FIRST_NAME FROM EMPLOYEES
WHERE EMPLOYEE_ID IN
(SELECT MANAGER_ID FROM EMPLOYEES
Gathering BY MANAGER_ID
HAVING COUNT(*)>5)
Q21) write a query to find all the jobs for which employees are joined in the current year.
SELECT * FROM JOBS WHERE JOB_ID IN
(SELECT JOB_ID FROM EMPLOYEES WHERE TO_CHAR(HIRE_DATE,’YYYY’)=TO_CHAR(SYSDATE,’YYYY’))
Q22) write a query to find all the employees working in deptartment 10,50,60
select * from employees where department_id in (10,50,60)
Q23) what are the commands are available in SQL” open=”no” style=”default” icon=”plus” anchor=”” class=””]
- DDL-data definition lanag
- DML-data muniplation lanag
- DCL-data control lanag
- TCL-transcation control lanag
Q24) Difference among rank and thick position?
RANK gives you the positioning inside your arranged segment. Ties are appointed a similar position, with the following ranking(s) skipped. Along these lines, on the off chance that you have 3 things at rank 2, the following position recorded would be positioned 5.
DENSE_RANK again gives you the positioning inside your arranged parcel, yet the positions are continuous. No positions are skipped if there are positions with different things.
Q25) Write a question to discover how long representatives has functioned from is hire_date
select sysdate – to_date(Hire_date) from representatives
Q26) write a question to show every one of the workers where initial 3 letter of first_name is bab
select * from representatives where instr(first_name,1,3)=’bab’
compose an inquiry to discover total of salries for each department_id
select department_id,
sum(salary)
from
workers
amass by department_id
Q27) write an inquiry to discover total of salries of departement_id is 50000
select department_id,
sum(salary)
from
workers
gather by department_id
having sum(salary)>50000
Q28) Write an inquiry to discover what number of workers has joined following 15 of the present month
SELECT COUNT(*) FROM EMPLOYEES WHERE TO_CHAR(HIRE_DATE,’DD’) > 15
Q29) A question to show full name(first_name, last_name) and join_date for every one of the workers who have joined somewhere in the range of 2015 and 2018
SELECT concat(FIRST_NAME,concat(“,”,last_name)) as fullname, HIRE_DATE FROM EMPLOYEES
WHERE TO_CHAR(HIRE_DATE, ‘YYYY’) BETWEEN 2015 AND 2018 ORDER BY HIRE_DATE
Q30) write a question to number of month representatives has working in organization
select employee_id,months_between(sysdate,hire_date) from representatives
Q31) write a question to discover every one of the representatives who have participated in the long stretch of Jan
SELECT * FROM EMPLOYEES WHERE TO_CHAR(HIRE_DATE, ‘MON’)= ‘Jan’
Q32) write a query to match abc=ABC
select upper(abc)=’ABC’ from dual
Q33) write a query to display all the employees salary in 10 digit
example 2600->0000002600
17000->0000017000
select salary,rpad(salary,10,0) from employees
Q34) difference between lpad and rpad
select ltrim(‘ hello world’),rtrim(‘hello world ‘) from dual;
o/p
ltrim(‘ hello world’) rtrim(‘hello world ‘)
hello world hello world
Q35) write a query to find number of employees under each manager
select manager_id,count(employees_id) from employees group by manager_id
Q36) various aggregate function
- max()
- min()
- sum()
- avg()
- count()
Q37) various logicaloperators
Q38) list few ETL tool available in the market
Q39) various way to compare txt file with data in the table
- load the txt file data into access and bring sql data into access and compare both in access database
- by using unix script we can compare (ensure both the output are extracted in txt file)
