Using aggregation on nested tables - sql, PL-SQL Programming

Using Aggregation on Nested Tables

Example is the most direct translation of its counterpart in the theory book that can be obtained in SQL but it is so over-elaborate that no SQL practitioner would consider using it. It uses the aggregate operator COUNT on the table values for column ExamResult to obtain the number of students who sat each exam. Unfortunately, as already noted, we cannot operate directly on ExamResult as a FROM clause element. Instead, we need to use an artifice that is specially devised for the sake of this example.

Example: How many students sat each exam

WITH C_ER AS (

 SELECT CourseId,

 CAST (

 TABLE (SELECT DISTINCT StudentId, Mark

 FROM EXAM_MARK AS EM

 WHERE EM.CourseId = C.CourseId)

 AS ROW ( StudentId SID, Mark INTEGER ) MULTISET)

 AS ExamResult

 FROM COURSE AS C)

SELECT CourseId, (SELECT COUNT (*)

FROM TABLE (ER (ExamResult)) AS t) AS n

FROM C_ER

Posted Date: 1/18/2013 6:12:11 AM | Location : United States







Related Discussions:- Using aggregation on nested tables - sql, Assignment Help, Ask Question on Using aggregation on nested tables - sql, Get Answer, Expert's Help, Using aggregation on nested tables - sql Discussions

Write discussion on Using aggregation on nested tables - sql
Your posts are moderated
Related Questions
Deriving Predicates from Predicates in SQL The corresponding section in the theory book describes how predicates can be derived from predicates using (a) the logical connectiv

Parameter SELF in pl/sql The MEMBER methods recognize a built-in parameter named SELF that is an instance of the object type. Whether declared explicitly or implicitly, it is

Grouping and Ungrouping in SQL Example specifying EXAM_MARK in place of COURSE in the main FROM clause. Example: Obtaining C_ER2 from EXAM_MARK SELECT CourseId, CAST

Structure of an Object Type: Similar to package, an object type has 2 parts: the specification and the body. The specification is the interface to your applications; it declar

Positional and Named Notation You can write the actual parameters when calling a subprogram, using either positional or named notation. That is, you can point to the relationsh

Cursor Variables As Parameters You can declare the cursor variables as the formal parameters of the functions and procedures. In the illustration below, you define the REF CUR

Write a pl/sql block that declares and uses cursors with parameters. In a loop, use a cursor to retrieve the department number and the department name from the departments table

Transaction context As the figure shows, the major transaction shares its context with the nested transactions, but not with the autonomous transactions. Similarly, If one aut

Using Savepoints The scope of the savepoint is a transaction in which it is defined. The Savepoints defined in the major transaction are not related to the savepoints defined

Opening a Cursor Opening the cursor executes the query & identifies the result set that consists of all rows that meet the query search criteria. For the cursors declared usin