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
%ISOPEN The %ISOPEN yields TRUE if its cursor or cursor variable is open; or else, the %ISOPEN yields FALSE. In the illustration, you use the %ISOPEN to select an action:

Benefit of the dynamic SQL: This part shows you how to take full benefit of the dynamic SQL and how to keep away from some of the common pitfalls. Passing the Names of Sc

Boolean Values Only the values TRUE, FALSE, & NULL can be assigned to a Boolean variable. For illustration, given the declaration DECLARE done BOOLEAN; the following statements

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

EXECUTE Privilege To call an invoker-rights routine straightforwardly, the users should have the EXECUTE privilege on that routine. By yielding the privilege, you permit a user

Collections:   The collection is an ordered group of elements, all of similar type (for illustration, the grades for a class of students). Each element has an exclusive subsc

NULL Statement The NULL statement clearly specifies in action; it does nothing other than to pass control to the next statement. It can, though, improve the readability. In a

Scope and Visibility The References to an identifier are resolved according to its visibility and scope. The scope of an identifier is that area of a program unit (subprogram, b

THEO R Y OF CATASTROPHISM OR CATALYSM (CUVIER 1769-1832) - The world has passed thorugh several stages and at the end of each stage there was a catastrophe killing all the

Using raise_application_error The Package DBMS_STANDARD that is supplied with Oracle gives language facilities that help your application to interact with Oracle. For illustra