Example of Tables within a Table - SQL

Example: Obtaining C_ER from COURSE and EXAM_MARK

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

Explanation

• The SELECT clause operates on each row of the result of the FROM clause-i.e., on each row of the COURSE table, deriving two columns, CourseId and ExamResult.
• CourseId is self-explanatory, merely carrying forward the column values from the column of that name in COURSE.
• TABLE ( SELECT DISTINCT StudentId, Mark FROM EXAM_MARK AS EM WHERE EM.CourseId = C.CourseId ) denotes a multiset whose elements are rows, obtained by taking the StudentId and Mark values from those rows of EXAM_MARK that match the current row of COURSE on CourseId. Note very carefully, however, that this multiset does not necessarily inherit the column names, StudentId and Mark, from the table that is the operand to the invocation of TABLE. The SQL standard allows the column names to be "implementation-dependent" (i.e., undefined) so long as no two columns have the same name. An implementation that nevertheless carried forward the unique names StudentId and Mark would be both sensible and conforming, and would obviate the need for the CAST invocation explained in the next bullet. The same multiset would result if the word DISTINCT had been omitted, thanks to the WHERE condition, but I include it because the example in the theory book uses COMPOSE, which is defined as a projection of a join, and SQL's counterpart of projection uses SELECT DISTINCT.
• CAST (t AS ROW ( StudentId SID, Mark INTEGER ) MULTISET ), where t is the above TABLE expression, addresses the aforementioned possible problem by assigning the required column names. Note that we need to know and write down the declared types of those columns as well as their names. The "type conversion" operator CAST. Here it is being used to convert a value of some incompletely defined multiset type to one whose multiset type is explicitly defined.
• AS ExamResult then gives the resulting column the name ExamResult. Note that here the name comes after AS and the expression defining it comes before, in the same style as the use of AS to define the range variables C and EM in the example.

The values for columns such as ExamResult in this example have sometimes been referred to informally as nested tables, being "tables within a table", so to speak. Unfortunately, however, they are not actually tables, but rather multisets of rows. Because of that fact, a column such as ExamResult cannot appear as an element in a FROM clause.

Posted Date: 1/18/2013 6:08:33 AM | Location : United States

Ask an Expert

Related Discussions:- Example of tables within a table - sql, Assignment Help, Ask Question on Example of tables within a table - sql, Get Answer, Expert's Help, Example of tables within a table - sql Discussions

Write discussion on Example of tables within a table - sql
Your posts are moderated

Write your message here..

Related Questions

Functions - syntax, Functions The function is a subprogram which can ta... Functions The function is a subprogram which can take parameters and be invoked. Normally, you can use a function to calculate a value. The function has 2 sections: the specifi

Packaging cursors, Packaging Cursors   You can split a cursor specific... Packaging Cursors   You can split a cursor specification from its body for placement in a package. In that way, you can change the cursor body without changing the cursor spec

Theory of panspermia - origin of life, THEO R Y OF PANSPERMIA - Arrh... THEO R Y OF PANSPERMIA - Arrhenius (1908) postulated the cosmic panspermia theory that claims that organisms existed throughout the universe, and their spores, etc., could

Procedures in pl/sql, Procedures   The procedure is a subprogram which... Procedures   The procedure is a subprogram which performs a specific action. You write procedures using the syntax as shown below: PROCEDURE name [(parameter[, parameter, .

Cursors, What is Cursors how to use it in Real time application ? What is Cursors how to use it in Real time application ?

Controlling cursor variables, Controlling Cursor Variables You use 3 s... Controlling Cursor Variables You use 3 statements to control the cursor variable: OPEN-FOR, FETCH, & CLOSE. At First, you OPEN a cursor variable FOR a multi-row query. Then, y

Maintain an audit trail of product table changes, The accuracy of product t... The accuracy of product table data is critical and the Brwebean's. owner wants to have an audit file that contains information regarding all DML activity on the BB_PRODUCT table. T

Comparison operators- pl/sql, Comparison Operators The Comparison operat... Comparison Operators The Comparison operators can compare one expression to another. The outcome is always true, false, or null. Usually, you use a comparison operators in condi

Other monadic - sql, Other monadic - SQL In 2VL there are just 4 (2 2 ... Other monadic - SQL In 2VL there are just 4 (2 2 ) monadic operators, of which negation is really the only "useful" one. When a third truth value is introduced we have 27 (3 3

Between operator-comparison operators, BETWEEN Operator The operator BET... BETWEEN Operator The operator BETWEEN, tests whether the value lies in a specified series. That means "greater than or equivalent to low value and less than or equivalent to hig