Ans: A Data warehouse is a subject oriented,
integrated ,time variant, non volatile collection of data in support of
management's decision making process.
Subject
oriented : means that the data addresses a specific subject such as sales,
inventory etc.
Integrated
: means that the data is obtained from a variety of sources.
Time
variant : implies that the data is stored in such a way that when some data is
changed.
Non
volatile : implies that data is never removed. i.e., historical data is also
kept.
2. What is the
difference between database and data warehouse
Ans: A database is a collection of related data.
Where as Data Warehouse stores historical data, the business users take their
decisions based on historical data only.
3. What is the difference between dimensional table and fact
table
Ans: A dimension table consists of tuples of
attributes of the dimension. A fact table can be thought of as having tuples,
one per a recorded fact. This fact contains some measured or observed variables
and identifies them with pointers to dimension tables.
4.
What is the difference between Data Mining and
Data Warehousing
Ans: Data mining - analyzing data from different perspectives and concluding
it into useful decision making information. It can be used to increase revenue,
cost cutting, increase productivity or improve any business process. There are
lot of tools available in market for various industries to do data mining.
Basically, it is all about finding correlations or patterns in large relational
databases.
Data warehousing comes before data mining. It is the process of
compiling and organizing data into one database from various source systems
where as data mining is the process of extracting meaningful data from that
database (data warehouse).
5. What is Data
Mart
Ans : A data mart is a simple form of a data
warehouse that is focused on a single subject (or functional area), such as
Sales, Finance, or Marketing. Data marts are often built and controlled by a
single department within an organization. Given their single-subject focus,
data marts usually draw data from only a few sources. The sources could be
internal operational systems, a central data warehouse, or external data.
6. Difference
between OLTP and OLAP
Ans: Online transactional processing (OLTP) is
designed to efficiently process high volumes of transactions, instantly
recording business events (such as a sales invoice payment) and reflecting
changes as they occur.
Online analytical processing (OLAP) is designed
for analysis and decision support, allowing exploration of often hidden
relationships in large amounts of data by providing unlimited views of multiple
relationships at any cross-section of defined business dimensions.
7. What is
ETL?
Ans: ETL - extract, transform,
and load.
Extracting data from outside source
systems.
Transforming raw data to make it fit for use
by different departments.
Loading transformed data into target systems
like data mart or data warehouse.
8.
Why ETL testing is required
Ans: To verify the correctness of data
transformation against the signed off business requirements and rules.
To verify
that expected data is loaded into data mart or data warehouse without loss of
any data.
To validate the accuracy of reconciliation
reports (if any e.g. in case of comparison of report of transactions made via
bank ATM – ATM report vs. Bank Account Report).
To make
sure complete process meet performance and scalability requirements
Data security is also sometimes part of ETL
testing
To evaluate the reporting efficiency
9.
What are ETL tester responsibilities
Ans :An ETL tester is responsible for
writing SQL queries for various scenarios. They run a number of tests including
primary key, duplicate, default, and attribute tests of the process. In
addition, they are in charge of running record count checks as well as
reconciling records with source data. They also confirm the quality of the data
and the loading process overall.
10. What are the
Key benefits of ETL Testing
Ans: Minimise the risk of Data loss
Data Security
Data Accuracy
Reporting effciency
11. To get the list
of tables and views in Database
Ans : SELECT * FROM information_schema.tables (will
display both tables,views)
SELECT * FROM information_schema.views (will
display on views)
12. List the
details about “SMITH”
Ans: Select * from employee where last_name=’SMITH’;
13. List out the
employees who are working in department 20
Ans: Select * from employee where department_id=20
14. List out the
employees who are earning salary between 3000 and 4500
Ans: Select * from employee where salary between
3000 and 4500
15. List out the
employees who are working in department 10 or 20
Ans: Select * from employee where department_id in
(20,30)
16. Find out the
employees who are not working in department 10 or 30
Ans :Select last_name, salary, commission,
department_id from employee where department_id not in (10,30)
17. List out the
employees whose name starts with “S”
Ans: Select *
from employee where last_name like ‘S%’
18. List out the
employees whose name start with “S” and end with “H”
Ans: Select *
from employee where last_name like ‘S%H’
19. List out the
employees whose name length is 4 and start with “S”
Ans : Select * from employee where last_name like
‘S___’
20. List out the
employees who are working in department 10 and draw the salaries more than 3500
Ans: Select *
from employee where department_id=10 and salary>3500
21. List out the
employees who are not receiving commission.
Ans: Select *
from employee where commission is Null
22. List out the
employee id, last name in ascending order based on the employee id.
Ans: Select
employee_id, last_name from employee order by employee_id
23. List out the
employee id, name in descending order based on salary column
Ans : Select employee_id, last_name, salary from
employee order by salary desc
24. list out the
employee details according to their last_name in ascending order and salaries
in descending order
Ans: Select
employee_id, last_name, salary from employee order by last_name, salary desc
25. list out the
employee details according to their last_name in ascending order and then on
department_id in descending order.
Ans: Select
employee_id, last_name, salary from employee order by last_name, department_id
desc
26. How many employees
who are working in different departments wise in the organization
Ans : Select department_id, count(*), from employee
group by department_id
27. List out the
department wise maximum salary, minimum salary, average salary of the employees
Ans: Select
department_id, count(*), max(salary), min(salary), avg(salary) from employee
group by department_id
28. List out the
job wise maximum salary, minimum salary, average salaries of the employees.
Ans: Select
job_id, count(*), max(salary), min(salary), avg(salary) from employee group by
job_id
29. List out the
no.of employees joined in every month in ascending order.
Ans: Select
to_char(hire_date,’month’)month, count(*) from employee group by
to_char(hire_date,’month’) order by month
30. List out the
no.of employees for each month and year, in the ascending order based on the
year, month.
Ans: Select
to_char(hire_date,’yyyy’) Year, to_char(hire_date,’mon’) Month, count(*) “No.
of employees” from employee group by to_char(hire_date,’yyyy’),
to_char(hire_date,’mon’).
