Creating a table, PL-SQL Programming

Creating a Table

Syantax:

CREATE TABLE ENROLMENT

(StudentId SID,

Name   VARCHAR (30) NOT NULL,

CourseId CID,

PRIMARY KEY (StudentId, CourseId)) ;

Explanation:

  1. CREATE TABLE ENROLMENT announces that what follows defines a variable in the database, named ENROLMENT. A variable in an SQL database is necessarily a table variable, just as in a relational database every variable is a relation variable. SQL does not use the term variable, instead referring to the variable as a base table (its value being called a table, of course).
  2. StudentId SID defines the first column of ENROLMENT, giving its name and either its declared type (a user-defined type) or its domain-we cannot tell which. If SID is a domain, then the definition of that domain specifies the declared type of the column StudentId. Similarly, Name VARCHAR(30) and CourseId CID define the second and third columns of ENROLMENT, respectively. A system-defined type is explicitly given for the column Name but the remarks on the declared type of StudentId apply in similar fashion to CourseId. Note carefully that in SQL it is correct, in ordinary prose, to identify columns by their ordinal position. By contrast there is no such thing as "the first attribute" of a relation or a relation variable.
  3. NOT NULL, appended to the definition of Name, specifies a constraint to the effect that the table assigned to ENROLMENT cannot contain a row in which "the null value of type VARCHAR(30)" appears for that column. The constraint is needed for accurate emulation of Example 2.6 in the theory book because relational theory does not admit any counterpart of SQL's NULL (so nor does Tutorial D). See the next bullet for an explanation of why NOT NULL is not appended to the other two column definitions.
  4. PRIMARY KEY ( StudentId, CourseId ) specifies that at no time can two distinct rows appear in the current value of ENROLMENT having the same value for StudentId and also the same value for CourseId. In enterprise terms, no two enrolments can involve the same student and the same course. In addition, it implies that the NOT NULL constraint applies to each those two columns.
Posted Date: 1/18/2013 2:46:47 AM | Location : United States







Related Discussions:- Creating a table, Assignment Help, Ask Question on Creating a table, Get Answer, Expert's Help, Creating a table Discussions

Write discussion on Creating a table
Your posts are moderated
Related Questions
Parameter and Keyword Description: record_type_name: This identifies the user-defined type specifier that is used in the subsequent declarations of the records. NOT N

Varrays versus Nested Tables The Nested tables are differing from varrays in the following ways: 1)  Varrays have a maximum size, while nested tables do not. 2)  Varrays are

Updating a Variable Assignment of an attribute value in a variable of a structured type Synatx: SET SN.C = 'S2'; As in Example the entire statement is equivalent to a

How Calls Are Resolved? The figure shows that how the PL/SQL compiler resolves the subprogram calls. When the compiler encounters the procedure or function call, it tries to di

PITS Depressions in secondary cell wall is called pit. A pit present on the free cell wall surface without its partner is called Blind pit. It consists of 2 parts -

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

Using PRIOR and NEXT The PRIOR(n) returns the index number that precede index n in a collection. The NEXT(n) returns the index number which succeed the index n. If n has no pr

UNION and OR - SQL SQL supports UNION explicitly but differently from the way it supports JOIN explicitly. As we have seen, JOIN is used exclusively within the FROM clause, su

ROWID and UROWID Internally, every database table has a ROWID pseudo column that stores binary values known as rowids. Each rowid shows the storage address of a row. A physical

Use the RETURNING Clause Frequently, the application requires information about the row affected by a SQL operation, for illustration, to produce a report or take a subsequent