Example of GROUP BY and COLLECT Operator

Example: Using GROUP BY and COLLECT to obtain C_ER2

SELECT CourseId,

CAST (

COLLECT (ROW (StudentId, Mark))

AS ROW (StudentId SID, Mark INTEGER) MULTISET)

AS ExamResult

FROM EXAM_MARK

GROUP BY CourseId

Explanation

• ROW (StudentId, Mark) forms the row consisting of the StudentId and Mark values of the current row of EXAM_MARK, in that order. The two fields of this row are unnamed.
• COLLECT (ROW(StudentId, Mark)) collects together as a multiset all of those rows that are derived EXAM_MARK rows having the same CourseId value. In fact it is shorthand for FUSION (ROW (StudentId, Mark) MULTISET), where FUSION is SQL's nearest counterpart of aggregate UNION. For each value of its operand, COLLECT derives the multiset containing just that value, and returns the FUSION (see next bullet) of all the multisets thus formed.
• FUSION is aggregate multiset union (UNION ALL), not UNION per se. In general the same value (in our example, a row) might appear more than once in the result of a COLLECT invocation. Fortunately, that won't happen here because the same StudentId, Mark combination cannot appear along with the same CourseId in more than one row of EXAM_ MARK, so DISTINCT could be omitted.
• CAST (m AS ROW (StudentId SID, Mark INTEGER) MULTISET), where m is the above COLLECT expression, names the columns of the nested table, ExamResult. Note the need to spell out the entire declared type of ExamResult, even though it differs from that of the COLLECT expression only in the names of the two columns.

Posted Date: 1/18/2013 6:43:17 AM | Location : United States

Ask an Expert

Related Discussions:- Example of group by and collect operator, Assignment Help, Ask Question on Example of group by and collect operator, Get Answer, Expert's Help, Example of group by and collect operator Discussions

Write discussion on Example of group by and collect operator
Your posts are moderated

Write your message here..

Related Questions

Bulk fetching - bulk bind performance improvement, Bulk Fetching The i... Bulk Fetching The illustration below shows that you can bulk-fetch from a cursor into one or more collections: DECLARE TYPE NameTab IS TABLE OF emp.ename%TYPE; TYPE S

Inserting objects in pl sql, Inserting Objects: You can use the INSERT... Inserting Objects: You can use the INSERT statement to add objects to an object table. In the illustration below, you insert a Person object into the object table persons:

Iteration schemes- iterative control, Iteration Schemes The bounds of a ... Iteration Schemes The bounds of a loop range can be variables, literals, variables, or expressions but must compute to integers. Below are some of the examples. As you can see t

Biochemical origin of life - modern concept, BIOCHE M ICA L ORIGIN OF LI... BIOCHE M ICA L ORIGIN OF LIFE - It is generally agreed by astronomers, geologists and biologists that the earth is approximately 4500-5000 million years old. It is an

Error handling in pl/sql, Error Handling The PL/SQL makes it easy to de... Error Handling The PL/SQL makes it easy to detect and process the predefined and user-defined error conditions known as exceptions. Whenever an error occurs, an exception is ra

Comparison operators- pl/sql, Comparison Operators The Comparison operat... Comparison Operators The Comparison operators can compare one expression to another. The outcome is always true, false, or null. Usually, you use a comparison operators in condi

Opening a cursor, Opening a Cursor Opening the cursor executes the que... Opening a Cursor Opening the cursor executes the query & identifies the result set that consists of all rows that meet the query search criteria. For the cursors declared usin

Cursor variables, Cursor Variables Similar to a cursor, cursor variable... Cursor Variables Similar to a cursor, cursor variable points to the current row in the result set of a multi-row query. But, dissimilar a cursor, a cursor variable can be opene

Select into statement - syntax, SELECT INTO Statement   The SELECT INT... SELECT INTO Statement   The SELECT INTO statement retrieve data from one or more database tables, and then assigns the selected values to the variables or fields. Syntax:

Union all - sql, UNION ALL - SQL Further varieties of UNION arise when... UNION ALL - SQL Further varieties of UNION arise when we replace the key word DISTINCT by ALL in any of the foregoing examples, as in Example. ALL specifies that if row r appe