76) Suppose I have five SQL SELECT statements connected by UNION/UNION ALL, how many times should I
specify UNION to eliminate the duplicate rows?
77) What is the restriction on using UNION in embedded SQL?
A) It has to be in a CURSOR.
78) In the WHERE clause what is BETWEEN and IN?
A) BETWEEN supplies a range of values while IN supplies a list of values.
79) Is BETWEEN inclusive of the range values specified?
80) 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.
81) 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.
82) What is the meaning of underscore ( '_' ) in the LIKE statement?
A) Match for any single character.
83) 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
GROUPs which match the criteria specified
84) 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;
85) What is the result of this query if no rows are selected:
SELECT SUM(SALARY) FROM EMP WHERE QUAL='MSC';
86) Why SELECT * is not preferred in embedded SQL programs?
A)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.
87) What are correlated subqueries?
A)A subquery in which the inner ( nested ) query refers back to the table in the outer query. Correlated
subqueries must be evaluated for each qualified row of the outer query that is referred to.
88) What is a cursor? Why should it be used?
A)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.
89) How would you retrieve rows from a DB2 table in embedded SQL?
A)Either by using the single row SELECT statements,or by using the CURSOR.
90) Apart from cursor, what other ways are available to you to retrieve a row from a table in embedded SQL?
A)Single row SELECTs.