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

Change sql file into csv for product registration, Change Sql file into CSV... Change Sql file into CSV for product registration on Magento Project Description: I have a set of files that are in Sql format and could like for a developer to help me with

Using the collection methods, Using the Collection Methods The collecti... Using the Collection Methods The collection methods below help to generalize the code and make collections easier to use and also make your applications easier to maintain:

Type versus representation confusion in sql, Type versus Representation Con... Type versus Representation Confusion in SQL This describes how a value might have two or more distinct representations. For example, user-defined type POINT might have a decla

Using rename in combination with join - sql, Using RENAME in combination wi... Using RENAME in combination with JOIN - SQL Example gives pairs of ids of students having the same name, by joining two renamings of IS_CALLED. Example gives an equivalent ex

Expressions - syntax, Expressions   An expression is a randomly comple... Expressions   An expression is a randomly complex combination of the constants, variables, literals, operators, & function calls. The simplest expression is the single variabl

Pl sql code review, PL SQL Code Review HEADER ELEMENTS File Name ... PL SQL Code Review HEADER ELEMENTS File Name Clear, meaningful and descriptive about main objective of the file. Multiple words are joined using underscores which adh

Package specification, The Package Specification The package specificat... The Package Specification The package specifications contain the public declarations. The scopes of these declarations are local to your database representation and global to t

Keyword and parameter description - delete statement, Keyword and Parameter... Keyword and Parameter Description: table_reference: This specifies a table or view that should be accessible when you execute the DELETE statement, and for that you must

Calculate the number of tuples, Question: (a) The objective of query opti... Question: (a) The objective of query optimization is to choose the most efficient strategy for implementing a given relational query, thereby improving the system performance. On

Parameter modes, Parameter Modes   To define the behavior of formal pa... Parameter Modes   To define the behavior of formal parameters you use the parameter modes. The 3 parameter modes, IN, OUT, & IN OUT, can be used with any subprogram. Though, a