SQL Create Table

Create Table

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 default value of 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));

More details on constraint please click on constraint-in-oracle-sql

 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 virtual column :- From 11g onward,we can create virtual column in a table. Virtual column does not store any data. Data of virtual column is calculated based on the given expression.
We can not perform any DML operation on virtual column but we can use 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 table with data and same structure from another table
SQL>create table employee1 as select * from employee;

Example 2:- Create 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 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 created