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

Assignment Help:

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.

Related Discussions:- Example of group by and collect operator

Effects of null for unique specification - sql, Effects of NULL for UNIQUE ...

Effects of NULL for UNIQUE Specification When a UNIQUE specification u for base table t includes a column c that is not subject to a NOT NULL constraint, the appearance of sev

Do you know anyone that can do this type of coding or not?, Task 2 [12 mark...

Task 2 [12 marks] Write the package body for the following package specification (the detailed description of each function and procedure is provided in the appendix below). Place

Parameter and keyword description - insert statement, Parameter and Keyword...

Parameter and Keyword Description:   table_reference: This identifies a table or view which should be available when you execute the INSERT statement, and for that you sho

Implicit cursors, Implicit Cursors The Oracle implicitly opens a curso...

Implicit Cursors The Oracle implicitly opens a cursor to process each SQL statement not related with an explicitly declared cursor. The PL/SQL lets you refer to the most recen

Example of groupby operator - sql, Example of GROUPBY Operator Example...

Example of GROUPBY Operator Example: How many students sat each exam, using GROUP BY, NATURAL LEFT JOIN, and COALESCE SELECT CourseId, COALESCE (n, 0) AS n FROM COURS

Example of check constraints - sql, Example of Check Constraints Examp...

Example of Check Constraints Example: Workaround for when subqueries not permitted in CHECK constraints CREATE FUNCTION NO_MORE_THAN_20000_ENROLMENTS ( ) RETURNS BOOLEAN

An active database in pl-sql, Consider the following set of database tables...

Consider the following set of database tables (same tables from Assignment 6-1). Please take note of foreign keys (most of them carry the same names as the corresponding primary ke

Exception handling, set serveroutput on declare a number(5); b n...

set serveroutput on declare a number(5); b number(5); c number(5); begin a:=&a; b:=&b; c:=a/b; dbms_output.put_line(c); exception when zero_d

Explicit cursor attributes, Explicit Cursor Attributes The cursor varia...

Explicit Cursor Attributes The cursor variable or each cursor has four attributes: %FOUND, %ISOPEN, %ROWCOUNT, and %NOTFOUND. When appended to the cursor or cursor variable, th

Dbms, DBMS: The answer to this question is of course given in of the t...

DBMS: The answer to this question is of course given in of the theory book. This book is concerned with SQL DBMSs and SQL databases in particular. Soon we will be looking a

Write Your Message!

Captcha
Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd