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
SQL outer join SELECT * FROM IS_CALLED NATURAL LEFT JOIN IS_ENROLLED_ON Note that adding LEFT to an invocation of CROSS JOIN has no effect unless the right-hand operand

JOIN and AND in SQL In this Section is all about one operator, JOIN. SQL's closest counterpart, NATURAL JOIN, has already been covered. Here we look at several other "join" op

Example of DELETE - SQL As with UPDATE, a FOR PORTION OF clause can be specified if the target table has a defined period name, as illustrated in Example. Example: Deleting

DELETE Command - SQL Loosely speaking, DELETE removes some existing rows from its target table. Suppose the university decides that course C3 is to be withdrawn. Example shows

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

Error Handling The PL/SQL makes it easy to detect and process the predefined and user-defined error conditions known as exceptions. Whenever an error occurs, an exception is ra

How Transactions Guard Your Database The transaction is a sequence of SQL data manipulation statements which does a logical unit of work. The Oracle treats the sequence of SQL

Sequential Control Dissimilar to the IF and LOOP statements, the GOTO and NULL statements are not important to the PL/SQL programming. The configuration of PL/SQL is such that th

Referencing Records Unlike the elements in a collection, that are accessed using subscripts, the fields in a record are accessed by name. To reference an individual field, you

I need to write one function and one procedure to query a Oracle 10.1 DB using PL SQL. I have the schema and exact queries...along with work Ive started and a template to put the a