Example of tables within a table - sql, PL-SQL Programming

Assignment Help:

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.


Related Discussions:- Example of tables within a table - sql

Opening a cursor variable, Opening a Cursor Variable The OPEN-FOR stat...

Opening a Cursor Variable The OPEN-FOR statement relates a cursor variable with the multi-row query, executes the query, and then identifies the result set. The syntax for ope

Deleting objects in pl sql, Deleting Objects You can use the DELETE st...

Deleting Objects You can use the DELETE statement to eradicate objects from an object table. To eradicate objects selectively, you use the WHERE clause, as shown below: BEG

Keyword & parameter description - exception_init pragma, Keyword & Paramete...

Keyword & Parameter Description: PRAGMA: These keywords signify that the statement is a pragma (i.e. compiler directive). The Pragmas are processed at the compile time, n

Delete command - sql, DELETE Command - SQL Loosely speaking, DELETE re...

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

Substitution and instantiation - sql, Substitution and Instantiation - SQL ...

Substitution and Instantiation - SQL It shows how NULL might appear in substitution for a parameter of a predicate and how it might thus participate in instantiation of that p

Dynamic sql - pl sql, Dynamic SQL: The Most PL/SQL programs do a predi...

Dynamic SQL: The Most PL/SQL programs do a predictable, specific job. For illustration, a stored procedure may accept an employee number and salary increase, and then update t

Advantages of subprograms, Advantages of Subprograms The Subprograms g...

Advantages of Subprograms The Subprograms give extensibility; that is, tailor the PL/SQL language to suit your requirements. For illustration, if you require a procedure which

Controlling autonomous transactions, Controlling Autonomous Transactions ...

Controlling Autonomous Transactions The first SQL statement in an autonomous routine starts a transaction. Whenever one transaction ends, the next SQL statement starts the oth

Commit statement in pl sql, COMMIT Statement The COMMIT statement expli...

COMMIT Statement The COMMIT statement explicitly makes everlasting changes to the database during the present transaction. The Changes made to the database are not considered e

Logical operators-pl/sql expressions , Logical Operators The logical op...

Logical Operators The logical operators AND, NOT, and OR follow the tri-state logic shown in table below. The AND and OR are binary operators; NOT is a unary operator.

Write Your Message!

Captcha
Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd