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]

SQL- Scenario Based Questions PART- 3

Q. How to get distinct rows from SQL table without using DISTINCT keyword .

CREATE TABLE ProductDetail
( ProductID Int,
  ProductName Varchar(30),
  Price int)

  INSERT INTO ProductDetail Values(101,'Dove',55)
  INSERT INTO ProductDetail Values(101,'Dove',55)
  INSERT INTO ProductDetail Values(102,'Hamam',30)
  INSERT INTO ProductDetail Values(103,'Cinthol',35)
  INSERT INTO ProductDetail Values(103,'Cinthol',35)

ProductDetail

To achieve distinct records from a table, we can do by following two methods:

Solution#1: By using GROUP BY clause. By selecting all columns and then grouping by all columns gives distinct records.

  SELECT ProductID,ProductName,Price

  FROM ProductDetail

  GROUP BY ProductID,ProductName,Price

Solution#1_ProductDetail

Solution#2: By using UNION . We know that UNION always gives distinct record from a table. So By doing UNION between the same table will give unique records.

  SELECT ProductID,ProductName,Price

  FROM ProductDetail

  UNION

  SELECT ProductID,ProductName,Price

  FROM ProductDetail

Solution#2_ProductDetail

For other SQL Scenario Based Questions, please visit below link
http://itjunction4all.blogspot.com/2014/04/sql-scenario-based-questions-part-2.html

ORACLE : ROWNUM and ROWID

ROWID is the physical location or address of row.It is permanent. It is the fastest means of accessing data.

ROWNUM is the sequential number which is assigned to each row during query execution. It basically represents the sequential order in which the rows are fetched. It is temporary.

Lets see below the illustration of ROWNUM and ROWID .

ROWNUM and ROWID