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

Records - syntax, Records Records are the items of the type RECORD. The... Records Records are the items of the type RECORD. The Records have exclusively named fields that can store the data values of various types. And hence, a record treat associate

Explicit cursor attributes, Explicit Cursor Attributes The cursor varia... Explicit Cursor Attributes The cursor variable or each cursor has four attributes: %FOUND, %ISOPEN, %ROWCOUNT, and %NOTFOUND. When appended to the cursor or cursor variable, th

Keyword and parameter description - cursors, Keyword and Parameter Descript... Keyword and Parameter Description select_statement: This is a query which returns a result set of the rows. Its syntax is such that of select_ into_statement without the IN

Count operator in sql, Count Operator in SQL Example: Counting the stu... Count Operator in SQL Example: Counting the students who have scored more than 50 in some exam (SELECT COUNT (*) FROM (SELECT DISTINCT StudentId FROM EXAM_MARK WHE

Assignment of DBMS in SQL server, i have an assignment of DBMS subject, thi... i have an assignment of DBMS subject, this assignment is based on SQL server and power BI

Python function, This task involves developing some functions that extract ... This task involves developing some functions that extract data from an SQL database. The scenario is that a company which owns an online vehicle search website wants to generate so

Using host arrays - bulk bind performance improvement, Using Host Arrays ... Using Host Arrays The Client-side programs can use anonymous PL/SQL blocks to bulk-bind input and output host arrays. However, this is the well-organized way to pass the colle

Using aggregation on nested tables - sql, Using Aggregation on Nested Table... 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

Sequential control - pl/sql, Sequential Control Dissimilar to the IF and ... 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

Pl/sql engine, Architecture The PL/SQL run-time system and compilation ... Architecture The PL/SQL run-time system and compilation is a technology, not an independent product. Consider this technology as an engine that compiles and executes the PL/SQL