Oracle: Create table with examples

This tutorial helps you to create table in Oracle.

Oracle table creating script examples

With tablespace and storage declared.

CREATE TABLE oraerr_users 
( 
    usrno NUMBER(5) PRIMARY KEY, 
    uname VARCHAR2(15) NOT NULL, 
    job VARCHAR2(10), 
    mgr NUMBER(5), 
    regdate DATE DEFAULT (sysdate), 
    photo BLOB, 
    sal NUMBER(7,2), 
    clickrate NUMBER(7,2) GENERATED ALWAYS AS (sal/2080), 
    comm NUMBER(7,2), 
    refno NUMBER(3) NOT NULL CONSTRAINT admin_dept_fkey REFERENCES oraerr_departments (department_id)
) 
TABLESPACE oraerr_tbs 
STORAGE ( INITIAL 50K); 

COMMENT ON TABLE oraerr_users IS 'Oraerr.com user table'; 

Note on that script:

  • BLOB is a part of LOBs, LOBs are used to store semi-structured data (such as an XML tree) and unstructured data (such as the stream of bits in a color image).
  • clickrate is a virtual column, this column computes the employee’s hourly rate as the yearly salary divided by 2,080
  • COMMENT statement is used to store a comment for the table
  • refno is a FOREIGN KEY which references to ORAERR_DEPARTMENTS’s department_id column.
  • You have to create tablespace ORAERR_TBS before executing this script.
  • If no tablespace statement found, Oracle will use default schema’s tablespace for the tables.
Related:  How to use sum() function inside stored procedure in oracle?