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

Sequential control - pl/sql, Sequential Control Dissimilar to the IF and ... Sequential Control Dissimilar to the IF and LOOP statements, the GOTO and NULL statements are not important to the PL/SQL programming. The configuration of PL/SQL is such that th

Sql query for testing triggers and stored procedures , Description: Dem... Description: Demonstrate your knowledge of PL/SQL programming by writing and thoroughly testing triggers and stored procedures associated with an e-commerce application that pr

Declarations in sql-pl/sql, Declarations in SQL Your program stores value... Declarations in SQL Your program stores values in the variables and constants. As the program executes, the value of the variables can change, but the values constants cannot.

Parameter and keyword description - sql cursor, Parameter and Keyword Descr... Parameter and Keyword Description: SQL: This SQL is the name of the implicit SQL cursor. %FOUND: This attribute results TRUE if an INSERT, DELETE, or UPDATE state

Ending transactions, Ending Transactions A good quality programming pr... Ending Transactions A good quality programming practice is to commit or roll back every transaction explicitly. Whether you rollback or issue the commit in your PL/SQL program

Declaring exceptions - user-defined exceptions, Declaring Exceptions T... Declaring Exceptions The Exceptions can be declared only in the declarative part of the PL/SQL subprogram, block, or package. By introducing its name, you can declare an excep

Rowid - sql pseudocolumns, ROWID The ROWID returns the rowid (binary a... ROWID The ROWID returns the rowid (binary address) of a row in the database table. You can use the variables of the type UROWID to store rowids in a readable format. In the il

Operator precedence-pl/sql expressions , Operator Precedence The operati... Operator Precedence The operations within an expression are completed in a particular order depending on their precedence (priority). The table shows the default order of the op

Obtaining a natural join by specifying the common columns, Obtaining a natu... Obtaining a natural join by specifying the common columns Synatax: SELECT * FROM IS_CALLED JOIN IS_ENROLLED_ON USING ( StudentId ) However, a named columns join doe

Using for update, Using FOR UPDATE If you declare a cursor which will ... Using FOR UPDATE If you declare a cursor which will be referenced in the CURRENT OF clause of an UPDATE or DELETE statement, you should use the FOR UPDATE clause to obtain an