DB2 provides the query language SQL (Structured Query Language) for relational databases, which is a highly conversational syntax that is both easy to learn and understand. This language is almost a necessity for every database system, used to express relational operations, including data definition (DDL) and data manipulation (DML). Originally spelled SEQUEL, the prototype of this language was completed under the name "System R" at IBM's San Jose Laboratory. After extensive usability and efficiency testing within IBM and externally, the results were quite satisfactory, leading to the development of IBM products based on System R technology. Moreover, in 1987, the American National Standards Institute (ANSI) and the International Organization for Standardization (ISO) defined a standard relational data language almost entirely based on IBM SQL.
**Data Definition Language (DDL)**
A Data Definition Language defines the format and structure of data, which is the first thing to address when establishing a database. For example, defining table relationships, fields, primary keys, and inter-table references must all be planned before starting.
**Creating Tables:**
Syntax:
```
CREATE TABLE table_name(
column1 DATATYPE [NOT NULL] [NOT NULL PRIMARY KEY],
column2 DATATYPE [NOT NULL],
...)
```
Explanation:
- **DATATYPE**: The format of the data, see the table below.
- **NOT NULL**: Specifies whether null values are allowed.
- **PRIMARY KEY**: Defines the primary key for the table.
**Modifying Tables:**
```
ALTER TABLE table_name
ADD COLUMN column_name DATATYPE
```
Explanation: Adds a new column (there is no syntax to delete a specific column).
```
ALTER TABLE table_name
ADD PRIMARY KEY (column_name)
```
Explanation: Changes the table definition to set a specific column as the primary key.
```
ALTER TABLE table_name
DROP PRIMARY KEY (column_name)
```
Explanation: Removes the primary key definition.
**Creating Indexes:**
```
CREATE INDEX index_name ON table_name (column_name)
```
Explanation: Creates an index on a specific column of a table to improve query speed.
**Deleting Tables or Indexes:**
```
DROP TABLE table_name
DROP INDEX index_name
```
**Data Types (DATATYPE):**
| Type | Description |
|--------------|-----------------------------------------------------------------------------|
| smallint | 16-bit integer |
| integer | 32-bit integer |
| decimal(p,s) | Decimal number with precision p and scale s. If not specified, p=5; s=0. |
| float | 32-bit floating-point number |
| double | 64-bit floating-point number |
| char(n) | Fixed-length string of n characters, max n=254 |
| varchar(n) | Variable-length string up to n characters, max n=4000 |
| graphic(n) | Double-byte fixed-length string, max n=127 |
| vargraphic(n)| Variable-length double-byte string, max n=2000 |
| date | Year, month, day |
| time | Hour, minute, second |
| timestamp | Year, month, day, hour, minute, second, millisecond |
**Data Manipulation Language (DML)**
After defining the data structure, the next step involves data manipulation. Data manipulation includes inserting (insert), querying (query), updating (update), and deleting (delete) data. Below are the respective syntaxes:
**Inserting Data:**
```
INSERT INTO table_name (column1,column2,...) VALUES (value1,value2,...)
```
Explanation:
1. If columns are not specified, the system will insert data according to the order of fields in the table.
2. The data type of the field must match the inserted data.
3. `table_name` can also be a view (`view_name`).
```
INSERT INTO table_name (column1,column2,...) SELECT columnx,columny,... FROM another_table
```
Explanation: Data can also be inserted via a subquery from another table.
**Querying Data:**
Basic Query:
```
SELECT column1,column2,... FROM table_name
```
Explanation: Lists all specific column data from `table_name`.
```
SELECT * FROM table_name WHERE column1 = xxx [AND column2 > yyy] [OR column3 <> zzz]
```
Explanation:
1. `*` means all columns are listed.
2. The `WHERE` clause filters data based on conditions.
Ordered Query:
```
SELECT column1,column2 FROM table_name ORDER BY column2 [DESC]
```
Explanation:
- `ORDER BY` specifies sorting by a certain column.
- `[DESC]` indicates descending order; if omitted, ascending order is assumed.
**Complex Queries:**
Combining multiple tables:
```
SELECT * FROM table1,table2 WHERE table1.column1=table2.column1
```
Explanation:
1. Queries data where `column1` values are the same in both tables.
2. Compared columns must have the same data type.
3. A complex query might involve multiple tables.
Aggregate Queries:
```
SELECT COUNT (*) FROM table_name WHERE column_name = xxx
```
Explanation: Counts how many records meet the condition.
```
SELECT SUM(column1) FROM table_name
```
Explanation:
1. Calculates the sum of a numeric column.
2. Other aggregate functions include `AVG()`, `MAX()`, and `MIN()`.
Grouped Queries:
```
SELECT column1,AVG(column2) FROM table_name GROUP BY column1 HAVING AVG(column2) > xxx
```
Explanation:
1. Groups data by `column1` and calculates the average of `column2`.
2. `HAVING` applies conditions to grouped data.
Nested Queries:
```
SELECT * FROM table_name1 WHERE EXISTS (SELECT * FROM table_name2 WHERE conditions)
```
Explanation:
1. The `WHERE` clause can contain another query.
2. `EXISTS` checks for the existence of any record.
Set Queries:
```
SELECT * FROM table_name1 WHERE column1 IN (SELECT column1 FROM table_name2 WHERE conditions)
```
Explanation:
1. `IN` checks if `column1` exists in a set of values returned by a subquery.
2. The returned data type must match `column1`.
Other Queries:
```
SELECT * FROM table_name1 WHERE column1 LIKE 'x%'
```
Explanation:
1. `LIKE` matches patterns; `'x%'` means starts with 'x'.
Set Queries:
```
SELECT * FROM table_name1 WHERE column1 IN ('xxx','yyy',..)
```
Explanation:
1. `IN` checks if `column1` exists in a set of values.
Range Queries:
```
SELECT * FROM table_name1 WHERE column1 BETWEEN xx AND yy
```
Explanation:
1. `BETWEEN` checks if `column1` falls between two values.
**Updating Data:**
```
UPDATE table_name SET column1='xxx' WHERE conditions
```
Explanation:
1. Updates a specific column to the value `'xxx'`.
2. If no `WHERE` clause is provided, the entire column in the table will be updated.
**Deleting Data:**
```
DELETE FROM table_name WHERE conditions
```
Explanation: Deletes data that meets the specified conditions.