Data Type is an attribute that specifies the type of data of any object. Each column, variable and expression has a related data type. You can use these data types while creating your tables. You can choose a data type for a table column based on your requirement.

Derby Server offers several categories of data types for your use as listed below −

Integer Numeric Data Types

Following is the list of integer numeric data types −

Approximate Numeric Data Types

Following is the list of approximate numeric data types −

Exact Numeric Data Types

Following is the list of exact numeric data types −

This chapter gives you the syntax of all the Apache Derby SQL statements.

All the statements start with any of the keywords like SELECT, INSERT, UPDATE, DELETE, ALTER, DROP, CREATE, USE, SHOW and all the statements end with a semicolon (;).

The SQL statements of Apache Derby are case in sensitives including table names.

CREATE Statement

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)
);

DROP TABLE

DROP TABLE table_name;

INSERT Statement

INSERT INTO table_name VALUES (column_name1, column_name2, ...);

SELECT Statement

SELECT column_name, column_name, ... FROM table_name;

UPDATE Statement

UPDATE table_name
   SET column_name = value, column_name = value, ...
   WHERE conditions;

DELETE Statement

DELETE FROM table_name WHERE condition;

DESCRIBE Statement

Describe table_name

SQL TRUNCATE TABLE Statement

TRUNCATE TABLE table_name;

ALTER Statement – Adding column

ALTER TABLE table_name ADD COLUMN column_name column_type;

ALTER Statement – Adding constraint

ALTER TABLE table_name ADD CONSTRAINT constraint_name constraint (column_name);

ALTER Statement – Dropping column

ALTER TABLE table_name DROP COLUMN column_name;

ALTER Statement – Dropping constraint

ALTER TABLE table_name DROP CONSTRAINT constraint_name;

WHERE Clause

SELECT * from table_name WHERE condition;
or,
DELETE from table_name WHERE condition;
or,
UPDATE table_name SET column_name = value WHERE condition;

GROUP BY Clause

SELECT column1, column2, . . . table_name GROUP BY column1, column2, . . .;

ORDER BY Clause

SELECT * FROM table_name ORDER BY column_name ASC|DESC.

Having Clause

SELECT column1, column2 . . . from table_name GROUP BY column having
condition;

Creating Index

CTREATE INDEX index_name on table_name (column_name);

Creating an UNIQUE index

CREATE UNIQUE INDEX index_name on table_name (column_name);

Creating a COMPOSITE index

CREATE INDEX index_name on table_name (column_name1, column_name2);

Displaying the Indexes

SHOW INDEXES FROM table_name;

Dropping Indexes

DROP INDEX index_name;

The DROP TABLE statement is used to remove an existing table including all its triggers, constraints, permissions.

Syntax

Following is the Syntax of the DROP TABLE statement.

ij> DROP TABLE table_name;

Example

Suppose you have a table named Student in the database. The following SQL statement deletes a table named Student.

ij> DROP TABLE Student;
0 rows inserted/updated/deleted

Since we have removed the table if we try to describe it, we will get an error as follows

ij> DESCRIBE Student;
IJ ERROR: No table exists with the name STUDENT

Drop Table using JDBC program

This section teaches you how to drop a table in Apache Derby database using JDBC application.

If you want to request the Derby network server using network client, make sure that the server is up and running. The class name for the Network client driver is org.apache.derby.jdbc.ClientDriver and the URL is jdbc:derby://localhost:1527/DATABASE_NAME;create=true;user=USER_NAME;passw ord=PASSWORD“

Follow the steps given below to drop a table in Apache Derby

Step 1: Register the driver

To communicate with the database, first of all, you need to register the driver. The forName() method of the class Class accepts a String value representing a class name loads it in to the memory, which automatically registers it. Register the driver using this method.

Step 2: Get the connection

In general, the first step we do to communicate to the database is to connect with it. The Connection class represents the physical connection with a database server. You can create a connection object by invoking the getConnection() method of the DriverManager class. Create a connection using this method.

Step 3: Create a statement object

You need to create a Statement or PreparedStatement or, CallableStatement objects to send SQL statements to the database. You can create these using the methods createStatement(), prepareStatement() and, prepareCall() respectively. Create either of these objects using the appropriate method.

Step 4: Execute the query

After creating a statement, you need to execute it. The Statement class provides various methods to execute a query like the execute() method to execute a statement that returns more than one result set. The executeUpdate() method execute queries like INSERT, UPDATE, DELETE. The executeQuery() method to results that returns data etc. Use either of these methods and execute the statement created previously.

Example

Following JDBC example demonstrates how to drop a table in Apache Derby using JDBC program. Here, we are connecting to a database named sampleDB (will create if it does not exist) using the embedded driver.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class DropTable {
   public static void main(String args[]) throws Exception {
      //Registering the driver
      Class.forName("org.apache.derby.jdbc.EmbeddedDriver");

      //Getting the Connection object
      String URL = "jdbc:derby:sampleDB;create=true";
      Connection conn = DriverManager.getConnection(URL);

      //Creating the Statement object
      Statement stmt = conn.createStatement();

      //Executing the query
      String query = "DROP TABLE Employees";
      stmt.execute(query);
      System.out.println("Table dropped");
   }
}

Output

On executing the above program, you will get the following output −

Table dropped

You can deploy apache derby in two modes, namely embedded mode and server mode.

Embedded mode

You can run derby in embedded mode using Java application (using embedded driver). If you deploy Derby in embedded mode, the database engine will run in the same JVM as the Java application. It starts and stops with the application. You can access the database only with this application.

Server mode

In the server mode, derby will be run in the JVM of an application server where you can send a request to the server to access it. Unlike in embedded mode, multiple applications (java) can send a request to the server and access the database.