Apache Derby: Syntax Cheat Sheet

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;

Apache Derby: Data Types Cheat Sheet

Integer Numeric Data Types

Data typeSizeFromTo
SMALLINT2 bytes-3276832767
INTEGER4 bytes-2,147,483,6482,147,483,647
BIGINT8 bytes-92233720368547758089223372036854775808

Approximate Numeric Data Types

Data typeSizeFromTo
REAL4 bytes-3.40E + 383.40E + 38
DOUBLE PRECISION8 bytes-1.79E + 3081.79E + 308
FLOAT-1.79E + 3081.79E + 308

Exact Numeric Data Types

Data typeFromTo
DECIMAL-10^38 +110^38 -1
NUMERIC-10^38 +110^38 -1

Apache Derby: Table Cheat Sheet

Create Table

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

Create a table is that you can specify the column names and data types using a query.

CREATE TABLE table_name AS SELECT * FROM desired_table WITH NO DATA;

Drop Table

ij> DROP TABLE table_name;

Apache Derby: Data Cheat Sheet

Insert Data

  • Basic syntax of the INSERT statement
ij>INSERT INTO table_name VALUES (column_name1, column_name2, ...);
  •  Two specific columns by mentioning the column names
ij>INSERT INTO table_name VALUES (column_name1, column_name2, ...) VALUES
(value1, value2, ...);
  • Another query in the insert statement as:
INSERT INTO table_Name Query 

Retrieve Data

ij> SELECT column_name, column_name, ... FROM table_name;
Or,
Ij>SELECT * from table_name

Update Data

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

Delete Data

ij> DELETE FROM table_name WHERE condition;

Apache Derby: Clause Cheat Sheet

Where Clause

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

GROUP BY Clause

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

ORDER BY Clause

SELECT * FROM table_name ORDER BY column_name ASC|DESC.

Having Clause

ij> SELECT column1, column2 . . . from table_name GROUP BY column having

Apache Derby: Alter Table Statement Cheat Sheet

Created a table named Employees as shown below

ij> CREATE TABLE Employees (
   Id INT NOT NULL GENERATED ALWAYS AS IDENTITY,
   Name VARCHAR(255),
   Salary INT NOT NULL,
   Location VARCHAR(255),
   PRIMARY KEY (Id)
);

Adding a column to a Table

ALTER TABLE table_name ADD COLUMN column_name column_type;

Adding a constraint to a table

ALTER TABLE table_name ADD CONSTRAINT constraint_name constraint (column_name);

Dropping a constraint from a table

ALTER TABLE table_name DROP CONSTRAINT constraint_name;

Dropping a column from a table

ALTER TABLE table_name DROP COLUMN column_name;

Apache Derby: Derby Indexes Cheat Sheet

Creating an Index

CTREATE INDEX index_name on table_name (column_name);

Creating a 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;

Apache Derby: Procedures Cheat Sheet

Creating a Procedure

CREATE PROCEDURE procedure_name (parameter_type parameter_name1, parameter_type
parameter_name2, . . . .) parameter_style;

Dropping a procedure

DROP PROCEDURE procedure_name;

Apache Derby: Triggers Cheat Sheet

Creating a trigger

CREATE TRIGGER trigger_name
{ NO CASCADE BEFORE | AFTER }
{INSERT [OR] | UPDATE [OR] | DELETE}[OF col_name]
ON table_name
[REFERENCING OLD AS o NEW AS n]
[FOR EACH ROW]
Statement

Deleting a trigger

ij> Drop trigger tigger_name;