 |
Page
1, Page
2, Page
3
|
|
How would you find out the total number of
rows in a DB2 table?
|
Use SELECT COUNT(*)
in db2
query
|
How
do you eliminate duplicate values in DB2 SELECT ?
|
Use SELECT DISTINCT
in db2
query
|
How
do you select a row using indexes in DB2?
|
Specify the indexed columns in
the WHERE clause of db2 query.
|
How
do you find the maximum value in a column in db2?
|
Use SELECT MAX(
) .. in db2 query
|
How
do you retrieve the first 5 characters
of FIRSTNAME column of DB2 table EMP ?
|
SQL Query : SELECT
SUBSTR(FIRSTNAME,1,5) FROM EMP;
|
What
are aggregate functions?
|
Bulit-in mathematical functions
for use in SELECT clause.
|
Can
you use MAX on a CHAR column?
|
YES.
|
| 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.
|
How
do you concatenate the FIRSTNAME and LASTNAME from EMP table to give a
complete name?
|
SELECT FIRSTNAME || ||
LASTNAME FROM EMP;
|
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
|
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.
|
What
is the restriction on using UNION in embedded SQL?
|
It has to be in a CURSOR.
|
In
the WHERE clause what is BETWEEN and IN?
|
BETWEEN supplies a range of
values while IN supplies a list of values.
|
Is
BETWEEN inclusive of the range values specified?
|
Yes.
|
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.
|
| |
When
do you use a LIKE statement?
|
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.
|
What
is the meaning of underscore ( _ ) in the LIKE statement?
|
Match for any single character.
|
What
do you accomplish by GROUP BY
HAVING clause?
|
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 query if no rows are selected:
|
SELECT SUM(SALARY) FROM
EMP WHERE QUAL=MSC; NULL
|
Why
SELECT * is not preferred in embedded SQL programs?
|
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 subqueries? -
|
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.
|
What
is a cursor? why should it be used?
|
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.
|
How
would you retrieve rows from a DB2 table in embedded SQL?
|
Either by using the single row
SELECT statements, or by using the CURSOR.
Apart from cursor, what other ways are available to you to retrieve a
row from a table in embedded SQL? -
Single row SELECTs.
|
Where
would you specify the DECLARE CURSOR statement?
|
See answer to next question.
|
How
do you specify and use a cursor in a COBOL program?
|
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.
|
What
happens when you say OPEN CURSOR?
|
If there is an ORDER BY clause,
rows are fetched, sorted and made available for the FETCH statement.
Other wise simply the cursor is placed on the first row.
|
Is
DECLARE CURSOR executable?
|
No.
|
Can
you have more than one cursor open at any one time in a program ?
|
Yes.
|
When
you COMMIT, is the cursor closed? - drona questions
|
Yes.
|
|
Page
1, Page
2, Page
3
|
|
| |
|
