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
The requirements as follows: Create a folder called "SECURITY" on the server and upload all your project files to that folder. Please note, the "SECURITY" folder is NOT to be IN

At times, Brewbean's has changed the id number for existing products. In the past, they have had to add a new product row with the new id to the BB_PRODUCT table, modify all the co

Effect of Anonymous Columns Now, recall that a VALUES expression denotes a table with undefined column names. If an initial value is to be specified when a base table is creat

Project Description: I want a small relational database to be built. I want the database to have the subsequent information tables: Employee Information Document storage

UPDATE Command- SQL Loosely speaking, UPDATE changes some of the column values of some existing rows of its target table. Thus, although some rows disappear from the target an

Using LOCK TABLE You use the LOCK TABLE statement to lock the whole database tables in the specified lock mode so that you can share or deny the access to them. For illustrati

DELETE Statement The DELETE statement eliminates whole rows of data from the specified table or view. Syntax:

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

LAWS / RULES - Dollo's Law                     :           Living organisms do exhibit evolutionary irreversibility or evolution is irreversible. Williston's Law

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