If Else in Database

Input Format

Write a query identifying the type of each record in the TRIANGLES table using its three side lengths. Output one of the following statements for each record in the table:

The TRIANGLES table is described as follows:

Sample Input

Sample Output

Isosceles

Equilateral

Scalene

Not A Triangle

SOLUTION

SELECT CASE WHEN A+B>C THEN

           CASE WHEN A=B AND B=C AND A=C THEN 'Equilateral'

                WHEN A=B OR B=C OR A=C THEN 'Isosceles'

                WHEN A!=B AND B!=C AND C!=A THEN 'Scalene'

           END

           ELSE 'Not A Triangle'

       END

FROM TRIANGLES;

Transaction Isolation Levels

Transaction isolation levels are a measure of the extent to which transaction isolation succeeds. In particular, transaction isolation levels are defined by the presence or absence of the following phenomena:

·         Dirty Reads A dirty read occurs when a transaction reads data that has not yet been committed. For example, suppose transaction 1 updates a row. Transaction 2 reads the updated row before transaction 1 commits the update. If transaction 1 rolls back the change, transaction 2 will have read data that is considered never to have existed.

           

·         Nonrepeatable Reads A nonrepeatable read occurs when a transaction reads the same row twice but gets different data each time. For example, suppose transaction 1 reads a row. Transaction 2 updates or deletes that row and commits the update or delete. If transaction 1 rereads the row, it retrieves different row values or discovers that the row has been deleted.

·         Phantoms A phantom is a row that matches the search criteria but is not initially seen. For example, suppose transaction 1 reads a set of rows that satisfy some search criteria. Transaction 2 generates a new row (through either an update or an insert) that matches the search criteria for transaction 1. If transaction 1 reexecutes the statement that reads the rows, it gets a different set of rows.

Types of protocols

     a)    Lock Based Protocol

1)    Binary Lock

2)    Shared/Exclusive Lock

     b)    Timestamp based Protocol

Binary Lock- Any number of transactions can perform READ operation. No point of locking the row

Exclusive Lock – Some transaction if want to perform writer operation thus exclusive lock.

OLTP vs. OLAP

We can divide IT systems into transactional (OLTP) and analytical (OLAP). In general we can assume that OLTP systems provide source data to data warehouses, whereas OLAP systems help to analyze it. 

- OLTP (On-line Transaction Processing) is characterized by a large number of short on-line transactions (INSERT, UPDATE, DELETE). The main emphasis for OLTP systems is put on very fast query processing, maintaining data integrity in multi-access environments and an effectiveness measured by number of transactions per second. In OLTP database there is detailed and current data, and schema used to store transactional databases is the entity model (usually 3NF). 

- OLAP (On-line Analytical Processing) is characterized by relatively low volume of transactions. Queries are often very complex and involve aggregations. For OLAP systems a response time is an effectiveness measure. OLAP applications are widely used by Data Mining techniques. In OLAP database there is aggregated, historical data, stored in multi-dimensional schemas (usually star schema). 

The following table summarizes the major differences between OLTP and OLAP system design.

	OLTP System Online Transaction Processing (Operational System)	OLAP System Online Analytical Processing (Data Warehouse)
Source of data	Operational data; OLTPs are the original source of the data.	Consolidation data; OLAP data comes from the various OLTP Databases
Purpose of data	To control and run fundamental business tasks	To help with planning, problem solving, and decision support
What the data	Reveals a snapshot of ongoing business processes	Multi-dimensional views of various kinds of business activities
Inserts and Updates	Short and fast inserts and updates initiated by end users	Periodic long-running batch jobs refresh the data
Queries	Relatively standardized and simple queries Returning relatively few records	Often complex queries involving aggregations
Processing Speed	Typically very fast	Depends on the amount of data involved; batch data refreshes and complex queries may take many hours; query speed can be improved by creating indexes
Space Requirements	Can be relatively small if historical data is archived	Larger due to the existence of aggregation structures and history data; requires more indexes than OLTP
Database Design	Highly normalized with many tables	Typically de-normalized with fewer tables; use of star and/or snowflake schemas
Backup and Recovery	Backup religiously; operational data is critical to run the business, data loss is likely to entail significant monetary loss and legal liability	Instead of regular backups, some environments may consider simply reloading the OLTP data as a recovery method

It follows that:

·         OLTP databases are meant to be used to do many small transactions, and usually serve as a "single source of truth".

·         OLAP databases on the other hand are more suited for analytics, data mining, less queries but they are usually bigger (they operate on more data).

Synonym in Oracle

A synonym is an alternative name for objects such as tables, views, sequences, stored procedures, and other database objects.

You generally use synonyms when you are granting access to an object from another schema and you don't want the users to have to worry about knowing which schema owns the object.

CREATE SYNONYM (OR REPLACE)

You may wish to create a synonym so that users do not have to prefix the table name with the schema name when using the table in a query.

Syntax

The syntax to create a synonym in Oracle is:

CREATE [OR REPLACE] [PUBLIC] SYNONYM [schema .] synonym_name

  FOR [schema .] object_name [@ dblink];

SCHEMA: - MOBI_CLEANUP_TEST1

SQL > Create table Test_Table ( id number(10));

SQL > CREATE PUBLIC SYNONYM Test_Synonym_Table

FOR MOBI_CLEANUP_TEST1.Test_Table

SQL > select * from Test_Synonym_Table

SQL > select * from Test_Table

Test_Synonym_Table can be accessed from any other schema without using Schema and dot

References: http://www.dba-oracle.com/concepts/synonyms.htm

To find maximum salary using in-build oracle functions

1)     To find the third maximum salary of an employee

Method 1 – Using rownum keyword

select * from employee order by salary desc

SALARY

4531

3231

1231

431

300

200

100

31

select * from (select * from employee order by salary desc) salary2 where rownum<=3

SALARY

4531

3231

1231

select salary2.*, rownum rnum from (select * from employee ORDER BY salary DESC) salary2 where rownum <= 3

SALARY         RNUM

4531    1

3231    2

1231    3

select * from

(select salary2.*, rownum rnum from (select * from employee ORDER BY salary DESC) salary2 where rownum <= 3) where rnum=3

SALARY, RNUM

1231, 3

Below table structure with Data :

select * from employee

SALARY

100
100
200
300
400
500
1000
500

A) Using row_number in oracle

select e.*, row_number() over (order by salary desc) as row_num from Employee e 

SALARY  ROW_NUM

1000 1
500         2
500         3
400         4
300         5
200         6
100         7

100         8

select * from ( select e.*, row_number() over (order by salary desc) as row_num from Employee e ) where row_num = 2;

SALARY   ROW_NUM
500             2

B) Using rank() in oracle

select e.*, rank() over (order by salary desc) as rank from Employee e 

SALARY RANK

1000 1
500        2
500         2
400         4
300         5
200          6
100         7
100        7

select * from (select e.*, rank() over (order by salary desc) as rank from Employee e ) where rank = 2;

SALARY RANK

500                  2

500                  2

select e.*,dense_rank() over (order by salary desc) as dense_rank from Employee e

SALARY DENSE_RANK

1000         1

500                 2 

500                 2

400                 3

300                 4

200                 5

100                 6

100                 6

select * from 
(select e.*,dense_rank() over (order by salary desc) as dense_rank from Employee e) where dense_rank=2

SALARY DENSE_RANK

500                  2

500                  2

       2)      To Find all tables with a particular column_name

select table_name from dba_tab_columns where column_name='MSISDN'

select table_name,column_name from dba_tab_columns WHERE TABLE_NAME='MTX_PARTY'