Creating a table, PL-SQL Programming

Assignment Help:

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.

Related Discussions:- Creating a table

Using lock table, Using LOCK TABLE You use the LOCK TABLE statement to...

Using LOCK TABLE You use the LOCK TABLE statement to lock the whole database tables in the specified lock mode so that you can share or deny the access to them. For illustrati

Triggers, At times, customers make mistakes in submitting their orders and ...

At times, customers make mistakes in submitting their orders and call to cancel the order. Brewbean’s wants to create a trigger that automatically updates the stock level of all pr

In out mode - parameter modes, IN OUT Mode An IN OUT parameter passes ...

IN OUT Mode An IN OUT parameter passes initial values to the subprogram being called and return efficient values to the caller. Within the subprogram, an IN OUT parameter acts

Benefit of the dynamic sql pl sql, Benefit of the dynamic SQL: This pa...

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

Sql cursor - syntax, SQL Cursor   The Oracle implicitly opens a cursor...

SQL Cursor   The Oracle implicitly opens a cursor to process each SQL statement not related with an explicit cursor. The PL/SQL refers to the most current implicit cursor as t

Object types - syntax, Object Types An object type is a user-defined co...

Object Types An object type is a user-defined complex datatype which encapsulates the data structure along with the functions and procedures required to manipulate the data. Th

Create a procedure to tax calculation task, Complete the following steps to...

Complete the following steps to create a procedure to calculate the tax on an order. The BB_TAX table contains the states that require taxes to be submitted for Internet sales. If

Committing and rolling back - autonomous transaction, Committing and Rollin...

Committing and Rolling Back The COMMIT and ROLLBACK end the active autonomous transaction but do not exit the autonomous routine. As the figure shows, if one transaction ends,

Table comparison - sql, Table Comparison - SQL The following definitio...

Table Comparison - SQL The following definitions for relation comparisons: Let r1 and r2 be relations having the same heading. Then: r1 ⊆ r2 is true if every tuple of r1

Parameter & keyword description - functions, Parameter & Keyword Descriptio...

Parameter & Keyword Description: function_name: The user-defined function is identifying by that keyword. parameter_name: This identifies the formal parameter that

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