DB2 Interview

0. How would you find out the total number of rows in a table?

Use SELECT COUNT(*) ...

1. How do you eliminate duplicate values in SELECT? –

Use SELECT DISTINCT

2. How do you select a row using indexes? –

Specify the indexed columns in the WHERE clause.

3. What are aggregate functions?

Bulit-in mathematical functions for use in SELECT clause

4. How do you find the maximum value in a column? –

Use SELECT MAX(

5. Can you use MAX on a CHAR column?

YES.

6. ) My SQL statement SELECT AVG(SALARY) FROM EMP yields inaccurate results. Why?

Because SALARY is not declared to have NULLs and the employees for whom the salary is not known are also counted.

7. How do you retrieve the first 5 characters of FIRSTNAME column of EMP table?

SELECT SUBSTR(FIRSTNAME,1,5) FROM EMP;

8. How do you concatenate the FIRSTNAME and LASTNAME from EMP table to give a complete name?

SELECT FIRSTNAME || ‘ ‘ || LASTNAME FROM EMP;

9. What is the use of VALUE function?

1. Avoid -ve SQLCODEs by handling nulls and zeroes in computations

2. Substitute a numeric value for any nulls used in computation

10. What is UNION,UNION ALL? –

UNION : eliminates duplicates

UNION ALL: retains duplicates

Both these are used to combine the results of different SELECT statements.

Suppose I have five SQL SELECT statements connected by UNION/UNION ALL, how many times should I specify UNION to eliminate the duplicate rows? -

Once.

11. What is the restriction on using UNION in embedded SQL?

It has to be in a CURSOR.

12. In the WHERE clause what is BETWEEN and IN? –

BETWEEN supplies a range of values while IN supplies a list of values.

13. Is BETWEEN inclusive of the range values specified? –

Yes.

14. What is 'LIKE' used for in WHERE clause? What are the wildcard characters? –

LIKE is used for partial string matches. ‘%’ ( for a string of any character ) and ‘_’ (for any single character ) are the two wild card characters.