Effect of Anonymous Columns

Now, recall that a VALUES expression denotes a table with undefined column names. If an initial value is to be specified when a base table is created, column definitions have to be implied by that initial value, so the question arises, how can a VALUES expression provide the initial value for a base table? The answer is that you have to learn an extra syntactic construct for that purpose, shown in Example.

Example: Specifying an initial value for a base table.

CREATE TABLE ENROLMENT (StudentId, Name, CourseId)

AS (VALUES (SID ('S1'), 'Anne', CID ('C1')),

(SID ('S2'), 'Boris', CID ('C1')))

WITH DATA;

Explanation:

1. (StudentId, Name, CourseId) provides the names, positionally corresponding to the anonymous columns of the AS table.
2. WITH DATA specifies that the given table is indeed to be the initial value. Curiously, this is required, unless WITHOUT DATA is written instead, in which case the AS table serves only to determine the declared types of the columns of the base table.
3. SQL does not allow constraints to be declared if AS is used in place of explicit column definitions.

Posted Date: 1/18/2013 2:56:02 AM | Location : United States

Ask an Expert

Related Discussions:- Effect of anonymous columns, Assignment Help, Ask Question on Effect of anonymous columns, Get Answer, Expert's Help, Effect of anonymous columns Discussions

Write discussion on Effect of anonymous columns
Your posts are moderated

Write your message here..

Related Questions

Using forall statement - bulk bind performance improvement, Using the FORAL... Using the FORALL Statement The keyword FORALL instruct the PL/SQL engine to bulk-bind input collections before sending them all to the SQL engine. Though the FORALL statement

Need database development with analysis tools, Need Database Development wi... Need Database Development with Analysis Tools Project Description: I want a database for large governmental and private data sets on one country that can be simply extended t

Exceptions - syntax, Exceptions An exception is the runtime error or wa... Exceptions An exception is the runtime error or warning condition that can be predefined or user-defined. The Predefined exceptions are raised implicitly through runtime system

Origin of earth - big bang hypothesis, ORIGIN OF EARTH - BIG-BAN G HY... ORIGIN OF EARTH - BIG-BAN G HYPOTHESIS - Origin of life is linked to origin of earth. Cosmos, the Universe originated 10-20 billion years ago by Big Bang (thermonu

Create a sql database, The requirements as follows: Create a folder call... The requirements as follows: Create a folder called "SECURITY" on the server and upload all your project files to that folder. Please note, the "SECURITY" folder is NOT to be IN

Using exception_init - user-defined exceptions, Using EXCEPTION_INIT T... Using EXCEPTION_INIT To handle unnamed internal exceptions, you should use the OTHERS handler or the pragma EXCEPTION_INIT. The pragma is a compiler directive that can be th

Initializing records, Initializing Records The illustration below show... Initializing Records The illustration below shows that you can initialize a record in its type definition. Whenever you declare a record of the type TimeRec, its 3 fields supp

EXCEPTION handling, set serveroutput on declare a number(5); b numb... set serveroutput on declare a number(5); b number(5); c number(5); begin a:=&a; b:=&b; c:=a/b; dbms_output.put_line(c); exception when zero_divide then d

Semidifference via not in and a subquery , Semidifference via NOT IN and a ... Semidifference via NOT IN and a subquery SELECT StudentId FROM IS_CALLED WHERE Name = 'Devinder' AND StudentId NOT IN (SELECT StudentId FROM IS_ENROLLED_ON WHER

Using limit - collection method, Using LIMIT For nested tables, that h... Using LIMIT For nested tables, that have no maximum size, the LIMIT returns NULL. For varrays, the LIMIT returns the maximum number of elements that a varray can have (that yo