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
Keyword and Parameter Description: table_reference: This specifies a table or view that should be accessible when you execute the DELETE statement, and for that you must

Effects of NULL in Aggregate Operator - SQL Let aggop(x) be an invocation of some aggregate operator aggop in SQL, where x is an expression (usually an open expression) to be

IS NULL Operator The IS NULL operator returns the Boolean value TRUE whenever its operand is null or FALSE if it is not null. The comparisons including the nulls always yield NU

Anatomy of a Command Figure, showing a simple SQL command, is almost identical to its counterpart in the theory book. The only difference arises from the fact that SQL uses a

Redeclaring Predefined Exceptions Keep in mind that, the PL/SQL declares predefined exceptions globally in the package STANDARD; Therefore you need not declare them yourself.

Rollback Behavior When a FORALL statement fails, the database changes are rolled back to an implicit savepoint marked before each of the SQL statement execution. The Changes t

Second Step at defining type SID in SQL CREATE TYPE SID AS VARCHAR(5) ; Explanation: TYPE SID announces that a type named SID is being defined to the system.

Parameter Modes: You do not require to specify a parameter mode for the input bind arguments (those used, for illustration, in the WHERE clause) as the mode defaults to IN. Th

Using SET TRANSACTION You use the SET TRANSACTION statement to begin the read-only or read-write transaction, start an isolation level, or assign your present transaction to a

what is the use of declare keyword