Tuesday, April 7, 2015

PL/SQL Interview Questions

What are ACID properties of database ?

Atomicity: It refers to the ability of the database to guarantee that either all of the tasks of a transaction are performed or none of them are. Each transaction is said to be atomic if when one part of the transaction fails, the entire transaction fails.

Consistency: The consistency property ensures that the database remains in a consistent state before the start of the transaction and after the transaction is over (whether successful or not). It avoids inconsistent view such as inventory falls below 0. Weak consistency aka eventual consistency is were the database eventually reaches a consistent state. Such database is replicated were the latest version is sitting somewhere in the cluster while older versions are still out there.

Isolation: It refers that other operations cannot access or see the data in an intermediate state during a transaction. It ensures the consistency between transactions as well as maintains their performance. Hence each transaction is unaware of another transactions executing concurrently in the system.

Durability: It refers to the guarantee that once the user has been notified of success, the transaction will persist and cannot be undone. It will survive system failures, and that the database system has checked the integrity constraints and won't need to abort the transaction. Many databases implement durability by writing all transactions into a transaction log that can be played back to recreate the system state right before a failure.

We have a table containing email_Ids with duplicates allowed. Write a query to get all the email_Ids having duplicates entries ? (Note: We need to get unique email_Ids, but all of which occur more than once in the table).
Select email_ids, count(email_ids) from emails group by email_ids having count (email_ids) > 1;

What is the difference between Inner and Outer Join ?
Inner join: An inner join of A and B gives the result of A intersect B, i.e. the inner part of a venn diagram intersection. Inner join gives the intersection of the two tables, i.e. the two rows they have in common.
Select * from A INNER JOIN B on A.id = B.id;
Select * from A, B where A.id = B.id;

Outer join: An outer join of A and B gives the results of A union B, i.e. the outer parts of a Venn diagram union.

Left outer join: A left outer join will give all rows in A, plus any common rows in B.
select * from A LEFT OUTER JOIN B on A.id = B.id;

Full outer join: A full outer join will give us the union of A and B, i.e. all the rows in A and all the rows in B. If something in A doesn't have a corresponding datum in B, then the B portion is null, and vice versa.
Select * from A FULL OUTER JOIN B on A.id = B.id;

INNER JOIN: Returns all rows when there is at least one match in BOTH tables
LEFT JOIN: Return all rows from the left table, and the matched rows from the right table
RIGHT JOIN: Return all rows from the right table, and the matched rows from the left table
FULL JOIN: Return all rows when there is a match in ONE of the tables