Create Table in Oracle Database

In oracle, CREATE TABLE statement is used to create table.

Syntax:

CREATE TABLE table_name  
(   
  column1 datatype [ NULL | NOT NULL ],  
  column2 datatype [ NULL | NOT NULL ],  
  ...  
  column_n datatype [ NULL | NOT NULL ]  
);

table_name here you specify table name.

coumn 1...column n here you specify column name(Entities).

NULL | NOT NULL your columns is null or not null, you must specify.

In oracle database,  no need to create database, it's an advantage over other database.

Example :

CREATE TABLE student  
( student_id number(10) NOT NULL,  
  student_name varchar2(50) NOT NULL,  
  dob date,  
  CONSTRAINT student_pk PRIMARY KEY (student_id)  
);

Output :

Table created.

Here we create a table "student" and its attributes(columns) student id,student name and its date of birth.

PRIMARY KEY IS student id.

Example :

CREATE TABLE department  
( dept_id number(10) NOT NULL,  
   dept_name varchar2(50) NOT NULL,
  CONSTRAINT department_pk PRIMARY KEY (dept_id)  
);

Output :

Table created.

Here we create a table "department" and its attributes(columns) department id and department name

PRIMARY KEY IS department id.

Example :

CREATE TABLE admission  
( admission_id number(10) NOT NULL,
  student_id number(10) NOT NULL,
  dept_id number(10) NOT NULL,
  admission_date date NOT NULL,
  CONSTRAINT admission_pk PRIMARY KEY (admission_id), 
  CONSTRAINT admission_student_fk FOREIGN KEY(student_id) REFERENCES students(student_id),
  CONSTRAINT admission_department_fk FOREIGN KEY(dept_id) REFERENCES department(dept_id) 
);

Output :

Table created.

Here we create a table "admission" and its attributes(columns) admission id and department id,student id and admission date.

PRIMARY KEY is department id. and Foreign Key is depertment id and student id

Primary key:

A primary key is a single field or combination of fields that contains a unique record. 
It must be filled. None of the field of primary key can contain a null value.
 A table can have only one primary key.

CREATE TABLE AS Statement

you can create table from existing table.

Syntax:

CREATE TABLE new_table  
AS (SELECT * FROM old_table(existing table);

Example

CREATE TABLE newstudent  
AS (SELECT *   FROM student  WHERE student_id < 60);

Here you can create new table from student table.

This discussion is over from now. We will discuss next Statement in over next Post.

Thank You for reading.

0 comments