DWH - Data Validation Testing Using Minus Query Method

Data Validation in Data Warehouse has always been challenging. One of the most frequently way of data validation is by using MINUS/ EXCEPT operator. MINUS operator is used in ORACLE database while EXCEPT is used in SQL SERVER database. Before proceeding to testing part, first let us understand the functionality of MINUS operator.

      MINUS operator gives the differences between the two table. Suppose we have two tables. Lets consider table A and B. When we will do MINUS operation between table A and table B (ie. A - B), it will return all those records which are present in table A but not in B. Similarly when we do  MINUS operation between table B and table A (ie. B - A), it will return all those records which are present in table B but not in A.

       One point to remember while doing MINUS operation between first table and second table is that every row in first table is searched in second table and if not found , that row is returned as a result set. Similarly, while doing MINUS operation between second table and first table is that every row in second table is searched in first table and if not found , that row is returned as a result set. 

        If all the rows  present in first table is present in second table as well then output or result of first table MINUS second table will be empty result set or in other words nothing will be returned.  Similarly, if all the rows present in second table is also present first table, then output or result of second table MINUS first table will be empty result set or in other words nothing will be returned.
Let us understand the usage of MINUS operator in data validation. Below SourceTable and TargetTable is used for illustration. 

     SourceTable                                      TargetTable

ID	Name		ID	Name
1	ABC		1	ABC
2	DEF		2	DEF
3	GHI		3	GHI
4	JKL		4	JKL
5	LMN		5	LMN
6	OPQ		8	OPQ

SQL / ORACLE- Scenario Based Interview Questions & Answers PART- 11

SCENARIO:  

We have EMP_DETAILS table which contains employee details such as EMPID, GENDER,EMAILID and DEPT_ID columns. We want to display all emailid associated with a particular DEPT_ID to be concatenated with semicolon as shown below.

EMP_DETAILS Table

EMPID	GENDER	EMAILID	DEPT_ID
1111	M	YYYYY@gmaix.com	104
2222	M	ZZZ@gmaix.com	103
3333	F	AAAAA@gmaix.com	102
4444	F	PP@gmaix.com	104
5555	M	CCCC@yahu.com	101
6666	M	DDDDD@yahu.com	100
7777	F	E@yahu.com	102
8888	M	M@yahu.com	102
9999	F	SS@yahu.com	100

     

EXPECTED RESULT:

ORACLE - How to copy column names or header from query result set in ORACLE SQL DEVELOPER ?

When we are working on ORACLE SQL DEVELOPER, we come across many situations where we need to copy only column names of a table or column names along with all data from query result set to excel or notepad. This can be done by the following ways:

COPY ONLY COLUMN NAMES:

When you want to copy only column names from result query, select cells corresponding to the columns which you want to copy. Right click on the column name and select option "Copy Selected Column Header(s)". This way column names get copied in clipboard which you can paste wherever you want. See below screenshot .

COPY COLUMN NAMES ALONG WITH DATA:

SQL / ORACLE- Scenario Based Interview Questions & Answers PART- 10

   Consider below two tables Table_First and Table_Second for answering the below questions. Basically you will be able to check your JOIN concepts. Take your time and ponder over and then try answer questions. It is easy but very conceptual.

SELECT * FROM Table_First;

SELECT * FROM Table_Second;

Table_First

Table_Second

Take pen and paper and then try to solve SQL query.

Q1.What will be the ouput if we are doing INNER JOIN between the above two tables as follows:
SELECT X,Y
FROM Table_First F
INNER JOIN Table_Second S
ON F.X=S.Y;

SQL / ORACLE- Scenario Based Interview Questions & Answers PART- 9

   Consider below Employee table and Department table for answering the questions:-

SELECT * FROM EMP_DETAILS;

SELECT * FROM DEPT_DETAILS;

EMP_DETAILS

DEPT_DETAILS

SQL - ADVANCED JOINS

In my earlier SQL- JOINS  post , I had discussed about INNER JOIN , LEFT OUTER JOIN, RIGHT OUTER JOIN, SELF JOIN and CROSS JOIN. In this post, I am going to discuss about ADVANCED JOINS. Please visit my earlier post on JOINS in below URL if you have not visited.

http://itjunction4all.blogspot.com/2013/08/joins-we-want-to-fetch-data-from-two-or.html

                               We have seen that LEFT OUTER JOIN includes matching rows between left table and right table and non matching rows from left table. This is illustrated with table and venn diagram as below:

                                                                                               

Select Name,Productname,Quantity,

P.Productid,Mrp

From Customerinfo C

LEFT Outer Join Productinfo P

On C.Productid=P.Productid;

SQL- Scenario Based Questions PART- 8

SCENARIO:

Table Employee_Details has Gender column. In this Gender column M should be updated with F and F should be updated with M.

Employee_Details

ORACLE- Guide to install Oracle 11g in your local machine

Step 1:  Visit  to https://www.oracle.com/index.html  and make Oracle account.

Step 2: Login to Oracle account and then navigate to Download Section as below:

ORACLE- ANALYTIC FUNCTIONS LEAD and LAG

ORACLE- ANALYTIC FUNCTIONS LEAD and LAG

LEAD : It is a analytic function which fetches value of a column from next row. If the value in next row goes out of table or partition, default NULL value is populated. The best part of this function is that it gives access to more than one row in a table without using SELF JOIN.

SYNTAX:

SQL- Scenario Based Questions PART- 7

SCENARIO:  We have table EMPLOYEE_INFO which contains Employee information. We want to display all characters before '@' in EMAILID field as Name field. Please see below in details.

TABLE NAME: EMPLOYEE_INFO

Employee_info Table

EXPECTED RESULT:

SQL- Scenario Based Questions PART- 6

SCENARIO:  We have table COUNTRY_INFO which contains country names. Country_name field has some junk characters. Those junk characters are listed in Value_1 column. Those junk characters in Country_name  need to be replaced with value_2 values for all occurrences while displaying. Please see below the scenario in details.

TABLE NAME: COUNTRY_INFO

         COUNTRY_INFO Table

EXPECTED RESULT:

SQL- Scenario Based Questions PART- 5

SCENARIO:  We have table EMPLOYEE_INFO  which contains Employee information. We want the output table should display NAME field and it should contain EMPNAME field values leaving behind first and last character in it. Please see below in details.

TABLE NAME: EMPLOYEE_INFO

EMPLOYEE_INFO Table

EXPECTED RESULT:

SQL- Scenario Based Questions PART- 4

SQL- Scenario Based Questions PART- 4

SCENARIO:

Suppose there is a table which contains a column NetBalance which has both negative and positive values. Now you have to write a query to dispaly sum of all positive Balances and sum of all negative Balances.

 

Consider below BalanceInfo table for answering this scenario based questions:-

BalanceInfo Table

DWH: What is Data warehouse ?

What is Data warehouse ?
      A Data warehouse is a repositary which stores integrated information for querying and analysis purpose. It has data collected from multiple heterogeneous source system.
According to Bill Inmon who is also known as" Father of Data warehouse", Data warehouse should be :

• Subject Oriented : It helps in synthesizing data as per the subjects.
• Time Variant : It stores data over a period of time.
• Integrated : It integrates data from multiple source system.
• Non Volatile : Data in Data warehouse is never deleted.

DWH: OLTP Vs OLAP

DWH: OLTP Vs OLAP

OLTP(Online Transactional Processing)              OLAP(Online Analytical Processing)

1.It deals with Transactional / Operational Data       1.It deals with Historical Data

2.Databases are normalised to facilitate frequent      2.Databases are de-normalised to facilitate              insertion,updation and deletion                                 queries and analysis

3.Old data is purged or archived                              3.Old Data or Historical data is stored to do                                                                                        trend  analysis and future predictions.

4.Queries are small and it deals with small               4.Queries are complex and it deals with                 amount of data                                                          huge amount of data
   Eg: Updation of Account Balance                           Eg: Total sales in North India

5.Updates happens frequently                                 5.Updates does not happen frequently.

6.Response time is very fast.                                  6.Response time is slow

7.Number of Joins are more as Table is                   7. Mostly less number of joins as Table is in           Normailesd.                                                              de-normalised.

   

SQL - DELETE and TRUNCATE

SQL- DELETE and TRUNCATE

Major difference between DELETE and TRUNCATE are as follows:

          DELETE                                                            TRUNCATE
1. DELETE is  DML Command                   1. TRUNCATE is DDL Command
2. WHERE clause can be used                  2. WHERE clause cannot be used
    along with DELETE.                                   along with TRUNCATE
3. It removes data from the table                 3. It removes all data from the table
   based on WHERE clause.                        
4. It puts lock on a row .                             4. It puts locks on a table
5. It is slower as it keeps log.                      5. It is faster as it does not keep log.
6. Space is not released.                            6. Space is released.
7.Syntax:                                                  7.Syntax:
   DELETE FROM table_name                     TRUNCATE TABLE table_name
   [WHERE condition]