Skip to main content

Databasse Interview Questions


1) Delete vs Truncate vs Drop Delete deletes all/ a few the records of the table. Truncate deletes all the records of the table, where as drop deletes the table and any indexes,privileges associated with the table. Delete is a DML operation, takes more time as it can be rolled back so will store the data in the undo space where as Truncate and Drop are DDL statements and can not be rolled back, so they are faster. From Oracle 10 the database tables can be undroppped. FLASHBACK TABLE TABLE_NAME TO BEFORE DROP;
2) SQL Joins can be classified into Equi join and Non Equi join. 1) SQL Equi joins It is a simple sql join condition which uses the equal sign as the comparison operator. Two types of equi joins are SQL Outer join and SQL Inner join. For example: You can get the information about a customer who purchased a product and the quantity of product. 2) SQL Non equi joins It is a sql join condition which makes use of some comparison operator other than the equal sign like >, <, >=, <=
STORED PROCEDURE VS FUNCTION
IN functions we can not alter database records., can not use statements like insert, update, or delete. However we can perform the same functions in a stored procedure, so use stored procedure when the statements involve these kind of operations. Unlike stored procedures functions can be used to inline in SQL statements, other functions or in procedures so use it when you need to compute some value that will be later used in some SQL statement. Both stored procedure and function can be used to achieve the same goal.


3) Select top 3 rows/ select top row/ select row with maximum value/frequency
Suppose the table named, STREET is like below.

55 2ndstreet 555 57
59 2ndstreet 555 61
63 2ndstreet 555 22
65 1ststreet    555  21
67 2ndstreet 555 69
73 2ndstreet 555 75
77 3rdstreet 555 79
81 2ndstreet 555 83
105 4thstreet 555 107
133 2ndstreet 555 135

This Query gives the maxim/max frequently occuring column.
Query

select *  from (select street_name,count(street_name) from street group by street_name order by count(street_name) desc)  where rownum=1;



Comments

Post a Comment

Popular posts from this blog

Data Structures using Java

package com.test.count; /**   *     * @author Kiran   *   */ public class LinkedList { Node headNode ; /** *   This method appends the Node at the end of the List *   * @param data */ public void appendNode( int data ) { Node currentNode = headNode ; if ( currentNode == null ) { Node newNode = new Node( data ); headNode = newNode ; return ; } while ( currentNode .getNextNode()!= null ) { currentNode = currentNode .getNextNode(); } currentNode .setNextNode( new Node( data )); } /** * This method prints all the Nodes/Values of the   * LinkedList *   */ public void printNodes() { Node currentNode = headNode ; if ( currentNode == null ) { System. out .println( " The Node is Null" ); return ; } else { System. out .print( "   " + currentNode .getData()); while ( currentNo...
Post a Comment
Read more

Gang of Four Design Patterns

Gang of Four(GOF) Design Patterns are divided in to 3 categories. Creational Design Patterns ( 5 Patterns ) AbstractFactory Builder Method Factory Method Prototype Singleton Singleton pattern is used when there is only one instance of the object need to be created per JVM instance. This pattern is used internally by the Connection Pooling to create the objects only on demand. Structural Design Patterns ( 7 Patterns ) Adapter Bridge Pattern Composite Pattern Decorator Pattern Facade FlyWeight FlyWeight Pattern is used when there are a large number of objects to be created, and they need to be created efficiently. This pattern is used by many frame works to create the objects using configuration. Proxy Behavioral Design Patterns ( 11 Patterns ) Chain Of Responsibility Pattern Command Interpreter Iterator Mediator Memento Memento is used internally by Text Editor Applications, where ctr+Z or cmd+Z operations are used to remember the snap shot of the data. State State Design Pattern is use...
Post a Comment
Read more