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;