Example of group by and collect operator, PL-SQL Programming

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







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
Related Questions
Using ROLLBACK The ROLLBACK statements end the present transaction and undo any change made during the transaction. The Rolling back is helpful for two reasons. Firstly, if yo

Relational Operators and Logical Operators It prepares the ground for subsequent sections in which each specific relational operator is paired with its logical counterpart, su

OUT Mode An OUT parameter returns values to the caller of a subprogram. Within the subprogram, an OUT parameter act like a variable. That means that you can use an OUT formal

Manipulating Individual Elements Faraway you have manipulated an entire collection. Within the SQL, to manipulate the individual elements of the collection, and then use the ope

Data Types in SQL - XML, Array, Row BINARY LARGE OBJECT for arbitrarily large bit strings. XML for XML documents and fragments. ARRAY types for arrays.

I need SQL to infopath data connection Project Description: Want data retrieval connection from SQL to SharePoint infopath Skills required are Sharepoint, SQL

Assignment Statement: The assignment statement sets the present value of the variable, parameter, field, or element. The statement consists of an assignment target followed by

Collections:   The collection is an ordered group of elements, all of similar type (for illustration, the grades for a class of students). Each element has an exclusive subsc

Challenge 1 You are required to do the project and write a test plan for it. Demo 4 is a check writer program for employees. In it, the user enters all information about the

Based on the EMPLOYEE table created in Assignment #1, write a PL/SQL anonymous block that accepts an employee ID from the user input and finds whether the employee ID is in the EMP