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
CHECK Constraints in SQL A CHECK constraint is a table constraint defined using the key word CHECK, as already illustrated in several examples in this chapter. In particular,

ROWID and UROWID Internally, every database table has a ROWID pseudo column that stores binary values known as rowids. Each rowid shows the storage address of a row. A physical

Parameter and Keyword Description: collection_name: This keyword identifies the index-by table, nested table, or varray formerly declared within the present scope. cu

Defining and Declaring Collections To create the collections, you must define a collection type, and then declare the collections of that type. You can define the VARRAY types a

%TYPE Attribute The %TYPE attribute gives the datatype of a record, field, nested table, database column, or the variable. You can use the %TYPE attribute as the datatype speci

Keyword and Parameter Description: table_reference: This specifies a table or view that should be accessible when you execute the DELETE statement, and for that you must

Keyword and Parameter Description: label_name: This is an undeclared identifier which labels an executable statement or the PL/SQL block. You can use a GOTO statement to

How Bulk Binds Improve Performance The assigning of values to the PL/SQL variables in SQL statements is known as binding. The binding of the whole collection at once is know

Ensuring Backward Compatibility   The PL/SQL Version 2 permits some abnormal behavior which Version 8 disallows. Particularly, Version 2 permits you to (i) Make the forw

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