Introduction to DB2 SQL Syntax

DB2 is a relational database management system that supports standard SQL syntax. Here are some basic SQL syntax and commonly used commands:

Basic Syntax

Data Types

• Integer types: INT, BIGINT, SMALLINT
• Floating-point types: FLOAT, DOUBLE, DECIMAL
• String types: CHAR, VARCHAR, TEXT

Expressions

• Arithmetic expression: SELECT * FROM table WHERE column = (SELECT sum(column) FROM another_table)
• Logical expression: SELECT * FROM table WHERE column IN (SELECT column FROM another_table)

SELECT Statement

Basic SELECT statement

SELECT column1, column2 FROM table_name;

Continue selection

SELECT column1, column2 FROM table_name
WHERE column3 = 'value';

ORDER BY option

SELECT column1, column2 FROM table_name
ORDER BY column_name ASC/DESC;

INSERT Statement

Insert basic statement

INSERT INTO table_name (column1, column2) VALUES ('value1', 'value2');

Insert multiple row statement

INSERT INTO table_name (column1, column2) VALUES 
  ('value1', 'value2'),
  ('value3', 'value4');

UPDATE Statement

Update basic statement

UPDATE table_name SET column1 = 'new_value' WHERE column3 = 'old_value';

Update multiple rows statement

UPDATE table_name SET column1 = 'new_value', column2 = 'another_new_value'
WHERE column3 IN ('value1', 'value2');

DELETE Statement

Delete basic statement

DELETE FROM table_name WHERE column3 = 'old_value';

Delete multiple rows statement

DELETE FROM table_name WHERE column3 IN ('value1', 'value2');

CREATE TABLE Statement

Create basic statement

CREATE TABLE table_name (
  column1 INT,
  column2 VARCHAR(255),
  column3 DATE
);

Create multiple rows statement

CREATE TABLE table_name (
  column1 INT,
  column2 VARCHAR(255),
  column3 DATE
)
VALUES 
  ('value1', 'value2', '2020-01-01'),
  ('value3', 'value4', '2020-02-01');

INDEX Statement

Create index statement

CREATE INDEX index_name ON table_name (column_name);

Drop index statement

DROP INDEX index_name ON table_name;