1) How would you find out the total number of rows in a table?
A. Use SELECT COUNT(*) … Allstate uses DB2v6. and QMF V3.3.
2) How do you eliminate duplicate values in SELECT?
A. Use SELECT DISTINCT …
3) How do you select a row using indexes?
A. Specify the indexed columns in the WHERE clause.
4) Can you use MAX on a CHAR column?
A. YES.
5) My SQL statement SELECT AVG(SALARY) FROM EMP yields inaccurate results. Why?
A. Because SALARY is not declared to have NULLs and the employees for whom the salary is not known are also counted.
6) What is the use of VALUE function?
A). Avoid -ve SQLCODEs by handling nulls and zeroes in computations
Substitute a numeric value for any nulls used in computation
7) How do you concatenate the FIRSTNAME and LASTNAME from EMP table to give a complete name?
A. SELECT FIRSTNAME || ‘ ‘ || LASTNAME FROM EMP;
8) What is the use of VALUE function?
A. Avoid -ve SQLCODE s by handling nulls and zeroes in computations by substituting a numeric value for any nulls used in computation
9) What is the restriction on using UNION in embedded SQL?
A. It has to be in a CURSOR.
10) In the WHERE clause what is BETWEEN and IN?
A. BETWEEN supplies a range of values while IN supplies a list of values.
Is BETWEEN inclusive of the range values specified?
Yes.
12) What is ‘LIKE’ used for in WHERE clause? What are the wildcard characters?
A. LIKE is used for partial string matches. ‘%’ ( for a string of any character ) and ‘_’ (for any single character ) are the two wild card characters.
13) When do you use a LIKE statement?
A) To do partial search e.g. to search employee by name, you need not specify the complete name; using LIKE, you can search for partial string matches.
14) What is the meaning of underscore ( ‘_’ ) in the LIKE statement?
A. Match for any single character.
15) What do you accomplish by GROUP BY … HAVING clause?
A. GROUP BY partitions the selected rows on the distinct values of the column on which you group by. HAVING selects GROUP s, which match the criteria specified
16) Apart from cursor, what other ways are available to you to retrieve a row from a table in embedded SQL?
A. Single row SELECTs.
17) What are correlated Sub-queries?
A. Sub-query in which the inner ( nested ) query refers back to the table in the outer query. Correlated. Sub-queries must be evaluated for each qualified row of the outer query that is referred to.
18) Consider the employee table with column PROJECT nullable. How can you get a list of employees who are not assigned to any project?
A. SELECT EMPNO
FROM EMP
WHERE PROJECT IS NULL;
19) How and where do you specify and use a cursor in a COBOL program?
A. Use DECLARE CURSOR statement either in working storage or in procedure division(before open cursor), to specify the SELECT statement. Then use OPEN, FETCH rows in a loop and finally CLOSE.
20) What is the result of this query if no rows are selected?
SELECT SUM(SALARY)
FROM EMP
WHERE QUAL=’MSC’;
A. It will return single row with NULL value
reference: http://www.idug.org/p/fo/et/thread=39999
Srinimf 