Multiset types - sql, PL-SQL Programming

Multiset types - SQL

An SQL multiset is what in mathematics is also known as a bag-something like a set except that the same element can appear more than once. The body of an SQL table is in general a bag of rows, rather than a set of rows, because SQL does indeed permit the same row to appear more than once in the same table. Although SQL has no names for table types, it does support multisets in general and it does have names for multiset types. A multiset type name consists of a type name followed by the key word MULTISET. For example, INTEGER MULTISET is the name of the type each of whose values is either (a) a bag, consisting of zero or more appearances of each value of type INTEGER and zero or more appearances of the null value of type INTEGER, or (b) the null value of type INTEGER MULTISET.

It would seem at first glance, then, that we perhaps do have a type name for a table type after all. For example, our enrolments table could perhaps be of type

ROW ( Name VARCHAR(50), StudentId VARCHAR(5),


In fact one could declare a local variable to be of this type and its value could indeed consist of the rows. However, such a type cannot be the declared type of a base table, in spite of the fact that the elements of a base table are indeed rows of the same type. Moreover, as I have already mentioned, there is such a thing as the null value of that multiset type, whereas NULL can never appear in place of a table-no table expression in SQL can ever evaluate to NULL-nor can NULL appear in place of a row in a table. So the set of values of a multiset type whose element type is a row type includes bags that are not tables as well as bags that are.

Posted Date: 1/18/2013 2:12:50 AM | Location : United States

Related Discussions:- Multiset types - sql, Assignment Help, Ask Question on Multiset types - sql, Get Answer, Expert's Help, Multiset types - sql Discussions

Write discussion on Multiset types - sql
Your posts are moderated
Related Questions
%TYPE Attribute The %TYPE attribute gives the datatype of a record, field, nested table, database column, or the variable. You can use the %TYPE attribute as the datatype speci

Project Description: This is stage 1 of a larger conversion project. We are converting a traditional Server/Client application written in Access 2007 into a web interface with S

Bulk Binds advantages In the Embedded Oracle RDBMS, the PL/SQL engines accept any valid PL/SQL subprogram or block. As the figure shows, the PL/SQL engine executes all procedur

Using Cursor Attributes: Every cursor has 4 attributes: %NOTFOUND, %FOUND, %ISOPEN, and %ROWCOUNT. If appended to the cursor name, they return the helpful information about

SQL Pseudocolumns The PL/SQL recognizes the following SQL pseudocolumns, that returns the specific data items: LEVEL, NEXTVAL, CURRVAL, ROWID, & ROWNUM. The Pseudocolumns are n

Challenge 1 You are required to do the project and write a test plan for it. Demo 4 is a check writer program for employees. In it, the user enters all information about the

Positional Notation The first procedure call uses the positional notation. The PL/SQL compiler relates the first actual parameter, account, with the first proper parameter, ac

Definition of CROSS JOIN - SQL Let s = t1 CROSS JOIN t2, where t1 and t2 are table expressions optionally accompanied by range variables. Then: Note: Here T denotes Table

Authorisations - Privileges As relational theory is silent on the issue of authorisation, it offers nothing with which SQL's vast edifice in support of what it calls privilege

Based on the EMPLOYEE table created in Assignment #1, write a PL/SQL anonymous block that accepts an employee ID from the user input and finds whether the employee ID is in the EMP