DB2 Frequently Asked Questions and Answers:Part 4

11/30/2009 No Comment

IBM DB2 Frequently Asked Questions with Answers.

Question: What are the max. & min. no. of partitions allowed in a partition tablespace?
Answer : minimum is 4.maximum is 64.

Question: What is the maximum number of tables that can be joined ?
Answer : fifteen

Question: What technique is used to retrieve data from more than one table in a single SQL statement?
Answer : The Join statement combines data from more that two tables

Question: What is a foreign key?
Answer : It identifies a releated row in another table and establishes a logical relationship between rows in two tables.

Question: Explain the use of the WHERE clause.

Answer : It directs DB2 to extract data from rows where the value of the column is the same as the current value of the host variable.

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

Answer : Use SELECT COUNT(*)

How do you eliminate duplicate values in SELECT?


How do you select a row using indexes?

Answer : Specify the indexed columns in the WHERE clause

What are aggregate functions?

Answer : Built-in mathematical functions for use in SELECT clause

How do you find the maximum value in a column?

Answer : Use SELECT MAX(

Can you use MAX on a CHAR column?

Answer : YES

My SQL statement SELECT AVG(SALARY) FROM EMP yields inaccurate results Why?
Answer : Because SALARY is not declared to have NULLs and the employees for whom the salary is not known are also counted

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


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


What is the use of VALUE function?

Answer : Avoid -ve SQL CODE by handling nulls and zeroes in computations Substitute a numeric value for any nulls used in computation


Answer : 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?

Answer : Once

What is the restriction on using UNION in embedded SQL?

Answer : It has to be in a CURSOR

In the WHERE clause what is BETWEEN and IN?

Answer : BETWEEN supplies a range of values while IN supplies a list of values

Is BETWEEN inclusive of the range values specified?

Answer : Yes

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

Answer : LIKE is used for partial string matches '%' ( for a string of any character ) and '_' (for any single character ) are the two wild card characters

When do you use a LIKE statement?

Answer : To do partial search eg to search employee by name, you need not specify the complete name; using LIKE, you can search for partial string matches

What is the meaning of underscore ( '_' ) in the LIKE statement?

Answer : Match for any single character

What do you accomplish by GROUP BY HAVING clause?

Answer : GROUP BY partitions the selected rows on the distinct values of the column on which you group by HAVING selects GROUPs which match the criteria specified. Consider the employee table with column PROJECT nullable How can you get a List of employees who are not assigned to any project?
SELECT EMPNO FROM EMP WHERE PROJECT IS NULL; What is the result of this uery if no rows are selected: SELECT SUM(SALARY) FROM EMP WHERE UAL='MSC'; NULL

Why SELECT * is not preferred in embedded SQL programs?

Answer : For three reasons: If the table structure is changed ( a field is added ), the program will have to be modified Program might retrieve the columns which it might not use, leading on I/O over head The chance of an index only scan is lost

What are correlated sub queries?

Answer : 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.

What is a CURSOR?why should it be used?

Answer : Cursor is a programming device that allows the SELECT to find a set of Rows but return them one at a time Cursor should be used because the host language can deal with only one row at a time.

CICS Interview QuestionsJCL Interview Questions
DB2 Interview QuestionsCOBOL Interview Questions
VSAM Interview QuestionsIDMS Interview Questions
IMS Interview QuestionsMVS Interview Questions
Related Posts

No comments :


Aired | The content is copyrighted and may not be reproduced on other websites. | Copyright © 2009-2016 | All Rights Reserved 2016

Contact Us | About Us | Privacy Policy and Disclaimer