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

HashMap in Java

1) Implement HashMap in Java, with the put and get operations   HashMap can be implemented in Java Using Arrays. Use the same logic that the Out of the Box   HashMap follows, for resizing, and load factor, when ever the HashMap reaches the size of the   resize with the load factor a new Array is created, and the previous array contents are copied over   to the new Array.  HashMap is Not Synchronized by default. We can synchronize the whole map by using Synchronization, or by using collection.synchronizedmap(map), which synchronizes all the operations on the map. Alternatively We can use the CocurrentHashMap which does not lock the read operations, rather locks the segments that are being written. 2) HashMap vs LinkedHashMap vs IdentityHashMap 3) HashMap vs ConcurrentHashMap 4) Implement a Cache using LinkedHashMap
Post a Comment
Read more

Data Structures - Strings

1) Find if a given First String is a SubSequence of a Second String 2) Find Max Occurring Frequency Character in  a String 3) Check if Sequence of Characters of Two Strings can become same by removal of a Character 4) Remove all Duplicate Words from a String 5) Find for a Given Two Strings, if one is the rotation of the Other. 6) Find if two Strings are K-Anagrams 7) Longest Common Subsequence where character occurs at least k times. 8) Find if the two given Strings are anagrams of Each Other. 9) Anagram SubString Search
Post a Comment
Read more