Apache Derby DDL Statements

Derby - Database

Derby - DML Statements

Apache Derby provides DDL statements to perform Create, Select, Describe, Drop, Alter operations on objects.

Some DDL commands supported by Apache Derby are mentioned below.

1. Create Table
2. Create View
3. Alter Table
4. Rename Table
5. Drop Table
6. Describe Table

Let us see each DDL statement in detail.

1. Create Table

A Create Table statement is used to create a table.

Syntax:

ij> CREATE TABLE table_name (
   column_name1 column_data_type1 constraint (optional),
   column_name2 column_data_type2 constraint (optional),
   column_name3 column_data_type3 constraint (optional)
)

We will start Derby in embedded mode using the command “./bin/setEmbeddedCP” and start the “ij” tool and connect with the “EMPDB” database after that we will create a table named “EMP”.

Command:

cloudduggu@ubuntu:~/derby$ ./bin/setEmbeddedCP

cloudduggu@ubuntu:~/derby$ ./bin/ij

ij> CONNECT 'jdbc:derby:EMPDB';

ij> CREATE TABLE EMP(EMPID INT PRIMARY KEY, EMPNAME  VARCHAR(12) , DEPTNAME VARCHAR(12));

Output:

2. Create View

We can use Create view statement to create a view. A view is a dictionary object that we can use until we drop it. Views are not updatable.

Syntax:

ij> CREATE VIEW view-Name
    [ ( Simple-column-Name [, Simple-column-Name] * ) ]
AS Query

We will create a view name “emp_data” based on column (EMPNAME,DEPTNAME) from “EMP” table.

Command:

ij> CREATE VIEW emp_data as select EMPNAME,DEPTNAME  from EMP;

Output:

3. Alter Table

Alter table is used to alter the structure of the existing table such as adding a column, dropping a column, adding constraints, and dropping constraints.

Syntax:

ij> ALTER TABLE table-Name
  { ADD COLUMN column-definition | ADD CONSTRAINT clause | DROP { PRIMARY KEY | FOREIGN KEY constraint-name | UNIQUE constraint-name | CHECK constraint-name | CONSTRAINT constraint-name }
    ALTER column-alteration |
    LOCKSIZE { ROW | TABLE }

3.1 Add column

We will alter the “EMP” table and add the “salary” column.

Command:

ij> ALTER TABLE EMP ADD COLUMN salary VARCHAR(26);

Output:

3.2 Drop column

We will alter the “EMP” table and drop the “salary” column.

Command:

ij> ALTER TABLE EMP DROP COLUMN salary;

Output:

3.3 Add constraint

We will use Alter command to add a constraint on the “phone” column of the “EMP” table.

Command:

ij> ALTER TABLE EMP ADD COLUMN phone INT;

ij> ALTER TABLE EMP ADD CONSTRAINT NEW_UNIQUE UNIQUE (phone);

Output:

3.4 Drop constraint

We will use Alter command to drop the constraint on the “phone” column of the “EMP” table.

Command:

ij> ALTER TABLE EMP DROP CONSTRAINT NEW_UNIQUE;

Output:

4. Rename Table

Rename command is used to rename an object to the new name.

Syntax:

ij>  RENAME TABLE table-Name TO new-table-Name;

Command:

ij>  RENAME TABLE EMP TO EMP_DETAIL;

Output:

5. Drop-Table

We can use a drop table for dropping a table.

Syntax:

ij> DROP TABLE table-Name;

Command:

ij>  RENAME TABLE EMP TO EMP_DETAIL;

Output:

6. Describe Table

Describe table is used to show the structure of the table.

Syntax:

ij>  DESCRIBE table_name;

Command:

ij>  DESCRIBE EMP;

Output: