Effect of anonymous columns, PL-SQL Programming

Assignment Help:

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.

Related Discussions:- Effect of anonymous columns

Projection in sql - correct version, Projection in SQL - correct version ...

Projection in SQL - correct version Student StudentId is enrolled on some course. SELECT DISTINCT StudentId FROM IS_ENROLLED_ON In more complicated examples it is someti

Mutual recursion, Mutual Recursion The Subprograms are mutually recursi...

Mutual Recursion The Subprograms are mutually recursive if they directly or indirectly call each other. In the illustration below, the Boolean functions odd & even, that dete

Assignment of variable - updating a variable, Assignment of Variable - Upda...

Assignment of Variable - Updating a Variable Syntax: SET SN = SID ('S2'); This can obviously be read as "set the variable SN to be equal in value to SID ( 'S2' )".

Dynamic ranges- iterative control, Dynamic Ranges The PL/SQL lets you det...

Dynamic Ranges The PL/SQL lets you determine the loop range dynamically at run time, as the example below shows: SELECT COUNT(empno) INTO emp_count FROM emp; FOR i IN 1..emp_cou

Tautologies, Tautologies: Above given table allows us to read the trut...

Tautologies: Above given table allows us to read the truth of the connectives in the next manner. Just expect we are looking at row three. It means this says that, if there P

Logical connectives - sql, Logical Connectives - SQL SQL's extended t...

Logical Connectives - SQL SQL's extended truth tables in which the symbol, for unknown, appears along with the usual T and F. Negation (NOT, ¬) Conjunction (

Definition of from - sql, Definition of FROM - SQL Recall that the ope...

Definition of FROM - SQL Recall that the operand of FROM is denoted by a commalist, each element of that commalist being a table expression optionally accompanied by a range v

Long and long raw in pl/sql, LONG and LONG RAW You use the LONG datatyp...

LONG and LONG RAW You use the LONG datatype to store the variable-length character strings. The LONG datatype is such as the VARCHAR2 datatype, except that the maximum length o

In operator-comparison operators, IN Operator The operator IN tests the ...

IN Operator The operator IN tests the set membership. This means "equal to any member of." The set may have nulls, but they are ignored. For illustration, the statement below do

Updating variables, Updating Variables For assignment, SQL uses the ke...

Updating Variables For assignment, SQL uses the key word SET, as in SET X = X + 1 (read as "set X equal to X+1") rather than X: = X + 1 as found in many computer languages.

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