Oracle SQL - Using INSERT statement with NULL

Introduction

The following code inserts a new department 90 without specifying column names.

It also shows how you can insert a null value with the reserved word NULL.

Demo

SQL>
SQL>-- from   w  ww  .j av  a 2 s .  co  m
SQL> drop table departments;

Table dropped.

SQL>
SQL> create table departments(
  2  deptno    NUMBER(2)     primary key,
  3  dname     VARCHAR2(10)  not null unique check (dname = upper(dname)),
  4  location  VARCHAR2(8)   not null        check (location = upper(location)),
  5  mgr       NUMBER(4)) ;
SQL>
SQL> insert into departments values (10,'ACCOUNTING','NEW YORK',7007);
SQL> insert into departments values (20,'TRAINING',  'DALLAS',  7004);
SQL> insert into departments values (30,'SALES',     'CHICAGO', 7006);
SQL> insert into departments values (40,'HR',        'BOSTON',  7009);
SQL>
SQL> insert into departments values (90,'SUPPORT','SEATTLE', NULL);
SQL>
SQL> select * from departments;

   DEPTNO | DNAME      | LOCATION |       MGR
--------- | ---------- | -------- | ---------
    1     | ACCOUNTING | NEW YORK |  07007.00
    2     | TRAINING   | DALLAS   |  07004.00
    30    | SALES      | CHICAGO  |  07006.00
    4     | HR         | BOSTON   |  07009.00
 00090.00 | SUPPORT    | SEATTLE  |  [N/A]

SQL>

Related Topic