Create Table

Definition :

  • This statement is used to create new/fresh table with various column or fields name and respective datatype in a database.

Syntax :

  • CREATE TABLE schema_name.table_name (column_name1 data_type1 column_constraint1, column_name2 data_type2 column_constraint2,    . . . table_constraints );
  • CREATE TABLE table_name ( column_name1 data_type1 column_constraint1, column_name2 data_type2 column_constraint2,    . . . );

Example :

In Oracle :

CREATE TABLE system.employee(
emp_id VARCHAR2(20),
emp_first_name VARCHAR2(50) NOT NULL,
emp_last_name VARCHAR2(50) NOT NULL,
emp_dob DATE NOT NULL,
emp_mob NUMBER NOT NULL,
emp_image BLOB NOT NULL,
emp_remarks VARCHAR2(250) NULL,
PRIMARY KEY(emp_id)
);
————————————————————————————————-
CREATE TABLE employee(
emp_id VARCHAR2(20),
emp_first_name VARCHAR2(50) NOT NULL,
emp_last_name VARCHAR2(50) NOT NULL,
emp_dob DATE NOT NULL,
emp_mob NUMBER(12) NOT NULL,
emp_image BLOB NOT NULL,
emp_remarks VARCHAR2(250) NULL,
PRIMARY KEY(emp_id)
);
————————————————————————————————-

Create Table As

Definition :

  • This statement is used to create a new fresh table from one/more  existing table’s data/records by copying the columns/fields of existing table partially or fully as per need.

Syntax :

(i) CREATE TABLE new_table_name  
AS (SELECT * FROM old_table_name); 
(ii) CREATE TABLE new_table_name  
AS (SELECT * FROM old_table_name where condition);
(iii) CREATE TABLE new_table_name  
AS (SELECT  column_name1, column_name2, column_name3, column_name4, column_name5 FROM old_table_name); 
(iv) CREATE TABLE new_table_name  
AS (SELECT  column_name1, column_name2, column_name3, column_name4, column_name5 FROM old_table_name); 
(v)  CREATE TABLE new_table_name
       AS (SELECT column_name1, column_name2, … column_nameN                         FROM old_table_name1, old_table_name2, … old_table_nameN);   

Example :

(i) CREATE TABLE employeeB 
     AS (SELECT *   FROM employeeA  WHERE emp_id = “50B3”);  
(ii) CREATE TABLE employeeB 
     AS (SELECT emp_id, emp_name, emp_addr, emp_dept, emp_sal   FROM           employeeA  WHERE emp_id = “50B3”); 

 522 total views,  1 views today

Categories: SQL

0 Comments

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.