Tag: Apache Derby 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;

Integer Numeric Data Types

Data type	Size	From	To
SMALLINT	2 bytes	-32768	32767
INTEGER	4 bytes	-2,147,483,648	2,147,483,647
BIGINT	8 bytes	-9223372036854775808	9223372036854775808

Approximate Numeric Data Types

Data type	Size	From	To
REAL	4 bytes	-3.40E + 38	3.40E + 38
DOUBLE PRECISION	8 bytes	-1.79E + 308	1.79E + 308
FLOAT		-1.79E + 308	1.79E + 308

Exact Numeric Data Types

Data type	From	To
DECIMAL	-10^38 +1	10^38 -1
NUMERIC	-10^38 +1	10^38 -1

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;

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;

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

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;

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;

Creating a Procedure

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

Dropping a procedure

DROP PROCEDURE procedure_name;

Creating a Schema

CREATE SCHEMA schema_name AUTHORIZATION id

Dropping a Schema

DROP SCHEMA my_schema RESTRICT;

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;