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]