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

Lob types in pl/sql, LOB Types The large object (LOB) datatypes like BF... LOB Types The large object (LOB) datatypes like BFILE, BLOB, CLOB, and NCLOB store the blocks of unstructured data (like graphic images, text, video clips, and sound waveforms)

Out mode - parameter modes, OUT Mode An OUT parameter returns values t... OUT Mode An OUT parameter returns values to the caller of a subprogram. Within the subprogram, an OUT parameter act like a variable. That means that you can use an OUT formal

Operators on tables and rows, Operators on Tables and Rows Row Extrac... Operators on Tables and Rows Row Extraction TUPLE FROM r, SQL has row subqueries. These are just like scalar subqueries except that they may specify more than one column.

Package utl file in pl/sql, UTL_FILE: The Package UTL_FILE permits you... UTL_FILE: The Package UTL_FILE permits your PL/SQL programs to read & write operating system (OS) text files. It gives a restricted version of the standard OS stream file I/O,

Using commit, Using COMMIT The COMMIT statements end the present trans... Using COMMIT The COMMIT statements end the present transaction and make permanent any changes made during that transaction. Till you commit the changes, other users cannot acc

Update command- sql, UPDATE Command- SQL Loosely speaking, UPDATE chan... 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

How to use the explain plan for statement, Using the student and faculty ta... Using the student and faculty tables create a select query that outputs all students for a specific advisor. Generate the execution plan, select out the explain plan . Create an

Using raise_application_error - user-defined exceptions, Using raise_applic... Using raise_application_error The Package DBMS_STANDARD that is supplied with Oracle gives language facilities that help your application to interact with Oracle. For illustra

Scoping-naming conventions, Scoping Within the similar scope, all the de... Scoping Within the similar scope, all the declared identifiers should be unique. So, even if their datatypes differ, the variables and parameters cannot share the similar name.

Sql queries-oracle , 1- You can check attribute names from each table in D... 1- You can check attribute names from each table in DBF11 by running for example:  desc dbf11.Member;  desc dbf11.Agent;  desc dbf11.Producer; Because some attribute names in