SQL CREATE TABLE Statement

If you want to create a table, you should name the table and define its column and each column's data type.

Create Table:- Below are the ways to create table

1) Table without any constraint
2) Table with constraint
        a) with column level constraint
                i) with system defined constraint name
                ii) with user-defined constraint name
        b) with table level constraint
                i) with system defined constraint name
                ii) with user-defined constraint name
3) Table with a default value of the column
4) Table with virtual column
5) Table from another table

1) Table without any constraint :-
Example:-
SQL>create table student1 (regno number(10),name varchar2(25),dob date,course varchar2(10));

2) Table with constraint:-
    a) with column level constraint
                i) with system defined constraint name

Example:-
SQL>create table student2 (regno number(10) primary key ,name varchar2(25),dob date,course varchar2(10));

ii) with user defined constraint name

Example:-
SQL>create table student3 (regno number(10) constraint pk_regno primary key ,name varchar2(25),dob date,course varchar2(10));

b) with table level constraint
                i) with system defined constraint name

Example:-
SQL>create table student4 (regno number(10),name varchar2(25),dob date,course varchar2(10),primary key(regno));
SQL>create table student5 (regno number(10),name varchar2(25),dob date,course varchar2(10),
                       primary key(regno,name));

                ii) with user defined constraint name

Example:-
SQL>create table student6 (regno number(10),name varchar2(25),dob date,course varchar2(10),
constraint pk_regno_name primary key(regno,name));

3) Table with default value of column :-

Example:-
SQL>create table student1 (regno number(10),name varchar2(25),dob date,course varchar2(10) default 'MCA');

4) Table with the virtual column:- From 11g onward, we can create a virtual column in a table. The virtual column does not store any data. Data of the virtual column is calculated based on the given expression.
We can not perform any DML operation on the virtual column but we can use the virtual column in "where" condition.
Example:-
SQL>create table employee (empno number(5),ename varchar2(20),sal number(5),
                         annual_sal generated always as (sal*12) virtual, deptno number(5));

SQL>CREATE TABLE employees (id NUMBER,first_name VARCHAR2(10),last_name VARCHAR2(10),salary NUMBER(9,2),
                        comm1 NUMBER(3),comm2 NUMBER(3),
                        salary1 AS (ROUND(salary*(1+comm1/100),2)),
                        salary2 NUMBER GENERATED ALWAYS AS (ROUND(salary*(1+comm2/100),2)) VIRTUAL,CONSTRAINT employees_pk PRIMARY KEY (id));

5) Table from another table:-We can create a table based on another table

Example 1:- Create a table with data and same structure from another table
SQL>create table employee1 as select * from employee;

Example 2:- Create a table with data from another table with selected columns
SQL>create table employee1 as select empno,ename,sal,deptno from employee;

Example 3:- Create table structure only from another table
SQL>create table employee1 as select * from employee where 1=2;
Note:- "1=2" is a false condition, we can give any false condition and the query will not select any data.
SQL>create table employee1 as select * from employee where rownum<1;
Note:- "rownum<1" will select no data. that's why only structure will be created

Pin it

About Mariano

I'm Ethan Mariano a software engineer by profession and reader/writter by passion.I have good understanding and knowledge of AngularJS, Database, javascript, web development, digital marketing and exploring other technologies related to Software development.

SQL CREATE TABLE Statement