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
Hi,am developing a library system and relating all the table is somehow complex,could you kindly assist me

INSERT Command in SQL Loosely speaking, INSERT takes the rows of a given source table and adds them to the specified target table, retaining all the existing rows in the targ

Use Object Types and Collections The Collection types and object types increase your efficiency by allowing for the realistic data modeling. The Complex real-world entities an

Procedural Constraint Enforcement (Triggers) SQL has an alternative method of addressing database integrity, involving event-driven procedural code. The special procedures th

Example of COALESCE operator Example: Give the total of marks for each exam (simplified solution) SELECT CourseId, COALESCE ((SELECT SUM (Mark) FROM EXAM_MARK AS EM

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

Create the four tables and populate them with the given data. Answer the following queries in SQL. 1. Get all part-color/part-city combinations. Note: Here and subsequently, the

SELECT a.child_fname,a.child_lname,concat(b.parent_title,b.parent_fname), b.parent_lname,b.parent_tphone FROM child a,parent b WHERE a.parent_id=b.parent_id ORDER BY a.child_fnam

Keyword & Parameter Description: PRAGMA: These keywords signify that the statement is a pragma (i.e. compiler directive). The Pragmas are processed at the compile time, n

Project Description: I want somebody who can help me with an idea that I have been working on for a few months now. The person will require extensive knowledge of warcraft 3 PvP