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
Methods: In normal, a method is a subprogram declared in an object type specification using the keyword MEMBER or STATIC. The method cannot have similar name as the object typ

Manipulating Collections Within PL/SQL, the collections add procedural power and flexibility. The biggest benefit is that your program can compute subscripts to process the spec

Many of the reports generated from the system calculate the total dollars in a shopper''s purchases. Follow these steps to create a function named TOT_PURCH_SF that accepts a shopp

Providing Results of Queries Expressing queries in SQL is the (big) subject. Here I present just a simple example to give you the flavour of things to come in those chapters.

Multiset types - SQL An SQL multiset is what in mathematics is also known as a bag-something like a set except that the same element can appear more than once. The body of an

Raise_application_error -  procedure of package DBMS_STANDARD , allows to issue an user_defined error messages by stored sub-program or database trigger.

Parameter Default Values As the illustration below shows, you can initialize the IN parameters to the default values. In that way, you can pass various numbers of actual par

Explicit Cursors The set of rows returned by the query can include zero, one, or multiple rows, depending on how many rows meet your search criteria. Whenever a query returns

What are 3 good practices of modeling and/or implementing data warehouses?

Using COMMIT The COMMIT statements end the present transaction and make permanent any changes made during that transaction. Till you commit the changes, other users cannot acc