15. Open SQL Extensions

Question 1

SELECT DISTINCT

Answer 1

Option is used to return only one record for each unique occurrence of data in a column based on a field name (or combination of field names). All other records that have the same matching data, will not be included in the result set.

Question 2

Aggregate Functions

Answer 2

MIN, MAX, AVG, SUM and COUNT

Question 3

CONCATENATE statement

Answer 3

is a string processing statement which enables the programmer to combine the contents of one or more source fields into a single target field.

Question 4

SEPARATED BY statement

Answer 4

is useful for embedding spaces or special characters between the multiple source fields before they are transported to the target field.

Question 5

PACKAGE SIZE

Answer 5

Reading selected rows into an internal table in packages of a predefined size is possible using the PACKAGE SIZE option with the INTO clause.

Question 6

CURSOR

Answer 6

is used to fetch the next row (or set of rows) from almost any SELECT statement that produces a result set of more than one row.

Question 7

OPEN CURSOR statement

Answer 7

The CURSOR is associated with a SELECT statement in the OPEN CURSOR statement.

Question 8

FETCH statement

Answer 8

Retrieves the rows from the CURSOR one by one until there are no more rows left

Question 9

CLOSE CURSOR

Answer 9

is used to terminate CURSOR processing. When the statement is issued, the internal result set is destroyed and access is no longer possible unless a new OPEN CURSOR statement is executed.

Question 10

joins

Answer 10

Are more efficient than logical databases and nested selects. They access multiple tables with one select statement.
• Joins have the advantage of accessing multiple tables with one Select statement, thereby reducing the amount of server overhead.

Question 11

Inner joins

Answer 11

Allow access to multiple tables with a single select statement by creating a temporary table based on the conditions in the ON statement. Inner Joins are equivalent to views created in the Dictionary (see Data Dictionary part).

Question 12

HAVING clause

Answer 12

assigns the selection criteria handling to the database instead of the application server. Similar to the subquery, the advantage of this is the reduction in the amount of data being transferred between the two layers.