Frequently asked Interview Q & A

1) SELECT NULL + 1 FROM DUAL ?

NULL

2) Query to find the Dept wise second highest salary?

SELECT DEPTNO,SAL FROM (SELECT DEPTNO,SAL,DENSE_RANK()OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) AS DR FROM EMP) WHERE DR=2

3) Query to find the SUM of +ve and -ve values ?

select sum(decode(sign(value),1,value)) pos, sum(decode(sign(value),-1,value)) neg from Test;

4) What are nvl and nvl2 functions and their examples ?

The NVL(exp1, exp2) function converts the source expression (or value) exp1 to the target expression (or value) exp2, if exp1 contains NULL. The return value has the same data type as that of exp1.

The NVL2(exp1, exp2, exp3) function checks the first expression exp1, if it is not null then, the second expression exp2 is returned. If the first expression exp1 is null, then the third expression exp3 is returned.

5) Difference between SQL TRUNCATE & SQL DROP ?

Truncate : Trucnate is used to delete the data from table. ( Only data will be removed & Structure remains in DB)

Drop : Drop is used to delete the object from database. ( Data and Structure will be removed from DB)

6) Can you sort a column using a column alias ?

Yes. A column alias could be used in the ORDER BY clause.

7) What are the specific uses of SQL functions ?

SQL functions have the following uses −

Performing calculations on data

Modifying individual data items

Manipulating the output

Formatting dates and numbers

Converting data types

8) What are the case manipulation functions of SQL ?

LOWER, UPPER, INITCAP

9) Discuss the syntax and use of the COALESCE function ?

The COALESCE function has the expression COALESCE(exp1, exp2, …. expn)

It returns the first non-null expression given in the parameter list.

10) Which expressions or functions allow you to implement conditional processing in a SQL statement ?

There are two ways to implement conditional processing or IF-THEN-ELSE logic in a SQL statement.

Using CASE expression

Using the DECODE function

11) What is the purpose of the group functions in SQL? Give some examples of group functions ?

Group functions in SQL work on sets of rows and returns one result per group. Examples of group functions are AVG, COUNT, MAX, MIN, STDDEV, SUM, VARIANCE.

12) Which SQL statement is used to add, modify or drop columns in a database table ?

The ALTER TABLE statement

13) What is a view? Why should you use a view ?

A view is a logical snapshot based on a table or another view. It is used for −

Restricting access to data;

Making complex queries simple;

Ensuring data in-dependency;

Providing different views of same data.