# Basics

## Commands

### Data Definition Language (DDL)

* a set of statements used to define database objects

### Data Control Language (DCL)

* a set of statements used to manage security permissions for users and objects

### Data Manipulation Language (DML)

* a set of statements that are used to query or modify data

| DDL              | DCL      | DML      |
| ---------------- | -------- | -------- |
| `CREATE`         | `GRANT`  | `SELECT` |
| `ALTER`          | `REVOKE` | `INSERT` |
| `DROP`           | `DENY`   | `UPDATE` |
| `RENAME` (T-SQL) |          | `DELETE` |

## Constraints

a set of rules to maintain accuracy, integrity and reliability of a database

<table><thead><tr><th width="174">Constraints</th><th>Description</th></tr></thead><tbody><tr><td><code>NOT NULL</code></td><td>Ensures that a column cannot have a NULL value</td></tr><tr><td><code>UNIQUE</code></td><td>Ensures that all values in a column are different</td></tr><tr><td><code>PRIMARY KEY</code></td><td>A combination of a <code>NOT NULL</code> and <code>UNIQUE</code>. Uniquely identifies each row in a table</td></tr><tr><td><code>FOREIGN KEY</code></td><td>Prevents actions that would destroy links between tables. A <code>foreign key</code> is a column in one table that is a <code>primary key</code> in a different table.</td></tr><tr><td><code>CHECK</code></td><td>Ensures that the values in a column satisfies a specific condition</td></tr><tr><td><code>DEFAULT</code></td><td>Sets a default value for a column if no value is specified</td></tr><tr><td><code>CREATE INDEX</code></td><td>Used to create and retrieve data from the database very quickly</td></tr></tbody></table>

```sql
CREATE TABLE Person (
    ID int NOT NULL UNIQUE,
    Name varchar(255) NOT NULL,
    Age int,
    Country varchar(255) DEFAULT 'India',

    CHECK (Age >= 60),
    PRIMARY KEY (ID),
);
CREATE TABLE Order (
    O-ID int NOT NULL,
    P-ID int,
    
    PRIMARY KEY (O-ID),
    FOREIGN KEY (P-ID) REFERENCES Person(P-ID)
);

CREATE INDEX P-Index ON Person (ID);
```

> A table with a **Composite Key** does not have a unique key for each row in the table. Instead a combination of two or more columns serves as a unique identifier for each row.