CREATE TABLE statement in Oracle SQL with constraint names

CREATE TABLE statement in Oracle SQL with constraint names

With constraint name

Syntax:

CREATE TABLE <tablename1> (

<column name1> <datatype>,

<column name2> <datatype>,

<conlumn name3> <datatype>,

CONSTRAINT < constraint name1 > PRIMARY KEY ( <column name1>),

CONSTRAINT <constraint name2> FOREIGN KEY (<column name2>)

REFERENCES <tablename2> (<column name1>)

CONSTRAINT < constraint name3 > UNIQUE ( <column name1>),

);

As shown in the syntax above, the constraints can be given a name using the keyword CONSTRAINT. This will help us in handling the constraints efficiently. For example, one may wants to remove a constraint, or one may wants to change the constraint etc. In simple words, the constraints with constraint names can be manipulated like any attribute or table.

Example:

CREATE TABLE Furnitures (

Furniture_ID CHAR(5),

Furniture_Name VARCHAR(25) NOT NULL,

Manufacturer VARCHAR(25) NOT NULL,

Quantity NUMBER(5),

Design_No NUMBER(5),

CONSTRAINT pk PRIMARY KEY (Furniture_ID),

CONSTRAINT uq1 UNIQUE (Manufacturer),

CONSTRAINT uq2 UNIQUE (Design_No)

);

Use of CONSTRAINT keyword:

If you want to remove the UNIQUE constraint from Manufacturer attribute, you can easily remove by using its name (uq1). In case, if you have created the table without constraint name (ie, without CONSTRAINT constraint_name), then it is difficult because of two UNIQUE attributes.

In occasions where more than one similar type of constraints used, the use of constraint name is very very important.

*********

Go to CREATE TABLE page

Go to SQL home page

 

Create table statement in SQL simple explanation with examples

Create table statement in SQL simple explanation with examples

CREATE TABLE statements

• CREATE TABLE - simple table creation

• CREATE TABLE - with constraints and without constraints name

• CREATE TABLE - constraints with constraint names

• CREATE TABLE - with CHECK constraints

***********

CREATE TABLE in Oracle SQL Simple version

CREATE TABLE in Oracle SQL Simple version

Simple table creation in Oracle SQL

[Note: in the syntax below, the contents of angular brackets along with angular brackets to be replaced with user defined words. Refer example below]

Syntax:

CREATE TABLE <tablename> (

<column name1> <datatype>,

<column name 2> <datatype>,

<column name 3> < datatype>,

…

<column name n> < datatype>

);

• This DDL statement creates a table with n attributes. This is the simplest of table creation.

• Some of the basic data types are CHAR, VARCHAR, DATE, and NUMBER.

• Remember that DDL statement will update the Data Dictionary.

Example:

CREATE TABLE Furnitures (

Furniture_ID CHAR(5),

Furniture_Name VARCHAR(25),

Manufacturer VARCHAR(25),

Quantity NUMBER(5)

);

• This statement will create the table FURNITURES with the following attributes;

• FURNITURE_ID - character attribute stores alpha-numeric characters,

• FURNITURE_NAME - variable length character attribute stores alpha-numeric characters,

• MANUFACTURER - variable length character attribute stores alpha-numeric characters and

• QUANTITY - number attribute stores numbers only.

• Each record that will be stored in this table will occupy maximum of 60 bytes (Furniture_id 5 bytes + Furniture_name 25 bytes + Manufacturer 25 bytes + Quantity 5 bytes = 60 bytes).

• The details regarding the names of the table and its attributes, types of the attributes and size of the attributes all will be stored in the data dictionary. [Note: for MySQL the data dictionary is called Information schema and for Oracle database it is Oracle metadata]

• This statement will not include any additional constraints other than data type and size.

****************

Go to CREATE TABLE page

Go to SQL home page

 

 

CREATE TABLE in Oracle SQL with constraints and without constraint names

CREATE TABLE in Oracle SQL with constraints and without constraint names

With constraints and without constraint names

A table can be created with constraints [conditions/integrity constraints]. Some of the constraints are NULL, NOT NULL, PRIMARY KEY, UNIQUE, FOREIGN KEY, and CHECK.

• An attribute with NULL constraint can have NULL values [or empty].

• An attribute with NOT NULL constraint must have a value otherwise the record cannot be stored.

• An attribute with PRIMARY KEY constraint is the primary key for the table.

• An attribute with UNIQUE constraint cannot have duplicate values.

• An attribute with FOREIGN KEY constraint is the foreign key attribute that refers some other table for permitted values.

• An attribute with CHECK constraint will check for the conditions given before accepting values.

All these above said constraints will be validated on insertion of each record into the table.

These constraints can be attached at the end of each column definition [one column definition is highlighted in green color] as given in syntax 1 or can be attached at the end of table definition as given in syntax 2 or both.

An attribute without constraints is by default NULL.

It is advisable not to have NULL attributes.

Syntax 1:

CREATE TABLE < tablename> (

<column name 1> < datatype> [constraint],

<column name 2> < datatype> [constraint],

<column name 3> < datatype> [constraint],

…

<column name n> < datatype> [constraint],

);

Example:

CREATE TABLE Furnitures (

Furniture_ID CHAR(5) PRIMARY KEY,

Furniture_Name VARCHAR(25) NOT NULL,

Manufacturer VARCHAR(25) NOT NULL,

Quantity NUMBER(5),

Design_No NUMBER(5) UNIQUE

);

OR

Syntax 2:

CREATE TABLE < tablename> (

<column name 1> < datatype> [constraint],

<column name 2> < datatype> [constraint],

<column name 3> < datatype> [constraint],

…

<column name n> < datatype> [constraint],

[constraint],

[constraint],

[constraint],

…

);

Example:

CREATE TABLE Furnitures (

Furniture_ID CHAR(5),

Furniture_Name VARCHAR(25) NOT NULL,

Manufacturer VARCHAR(25) NOT NULL,

Quantity NUMBER(5),

Design_No NUMBER(5),

PRIMARY KEY (Furniture_ID),

UNIQUE (Design_No)

);

*************

Go to CREATE TABLE page

Go to SQL home page

 

Join operation in relational algebra and SQL solved exercises

Join operation in relational algebra and SQL solved exercises

Question:

Consider two relations R and S with instances as follows;

Relation R	Relation S
a b c ------ 1 2 3 4 5 6 7 8 9	c d e ------ 3 4 5 7 8 9 9 0 1 5 5 5

How many rows would the following operations yield for the given data?

(a) cartesian product of R and S.

(b) natural join of R and S

(c) left outer join of R and S

(d) right outer join of R and S

(e) full outer join of R and S

Solution:

Operation	No. of rows	How?	Result
Cartesian product	12	No. of records in R multiply No. of records in S 3 * 4 = 12	a b c c d e -------------- 1 2 3 3 4 5 1 2 3 7 8 9 1 2 3 9 0 1 1 2 3 5 5 5 4 5 6 3 4 5 4 5 6 7 8 9 4 5 6 9 0 1 4 5 6 5 5 5 7 8 9 3 4 5 7 8 9 7 8 9 7 8 9 9 0 1 7 8 9 5 5 5
Natural join	2	Compares the value of the common attribute between relations R and S. In our example, attribute C is the common attribute. If C’s value is common for pair of tuples from both relations, then that pair will form a new tuple in the result. Between R and S we have only 2 tuples with common C values.	a b c d e ------------- 1 2 3 4 5 7 8 9 0 1
Left outer join	3	It is the natural join that includes all the records from left side relation (in our case R) but not all records from right side relation (in our case S). If there is no tuple in right side relation that are related to some records of left side relation, then the right side values will be NULL. Number of records = Number of records in right relation R	a b c d e -------------- 1 2 3 4 5 4 5 6 n n 7 8 9 0 1
Right outer join	4	Same as above with inclusion of all records from right side relation (S) with NULL values of missing left side relation (R) Number of records = Number of records in left relation S	a b c d e ------------- 1 2 3 4 5 n n 7 8 9 7 8 9 0 1 n n 5 5 5
Full outer join	5	Same as above. Now both sides the missing records will be replaced with NULL. 2 with common C values in R and S + 1 record in R with S values as null + 2 records in S with R values as null = 5 records	a b c d e ------------- 1 2 3 4 5 4 5 6 n n n n 7 8 9 7 8 9 0 1 n n 5 5 5

**************

Go to Natural join in DBMS page (SQL)

Go to Equi-join in DBMS page (SQL)

Go to Multiple choice questions in SQL/Relational algebra page

result of join operation in sql

result of natural join in relational algebra

outer joins in sql and relational algebra

left and right outer joins examples

join solved exercises in dbms

how join operation is performed in sql

how join operation is performed in relational algebra

join operation exercises