Data Definition Language (DDL) And Data Manipulation Language (DML) (Copy)

Cheat Sheet: A Level Computer Science – SQL, DDL & DML

1. Structured Query Language (SQL)

• SQL is the industry standard for interacting with relational databases.
• Divided into:
  • DDL (Data Definition Language): Defines/modifies structure
  • DML (Data Manipulation Language): Handles data within tables

2. DDL – Data Definition Language

Used to create or alter database structure (e.g., databases, tables, fields).

Common SQL DDL Statements

CREATE TABLE Students (
  StudentID INTEGER,
  Name VARCHAR(50),
  Age INTEGER,
  DOB DATE,
  Enrolled BOOLEAN
);
``` |
| `ALTER TABLE` | Modifies a table (add, drop, modify columns) | `ALTER TABLE Students ADD Gender CHAR(1);` |
| `PRIMARY KEY` | Defines a field as the unique identifier | `PRIMARY KEY (StudentID)` |
| `FOREIGN KEY` | Links field to another table's primary key |  
```sql
FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)
``` |

#### **SQL Data Types (used in CREATE/ALTER TABLE)**

| **Type** | **Purpose** |
|----------|-------------|
| `CHARACTER` / `CHAR(n)` | Fixed-length character string |
| `VARCHAR(n)` | Variable-length character string |
| `BOOLEAN` | True/False values |
| `INTEGER` | Whole numbers |
| `REAL` | Decimal/floating point numbers |
| `DATE` | Calendar date |
| `TIME` | Time of day |

---

### **3. DML – Data Manipulation Language**

Used to **insert, modify, delete, and retrieve data** in database tables.

#### **Data Queries**

| **Command** | **Purpose** | **Example** |
|-------------|-------------|-------------|
| `SELECT` | Retrieves data | `SELECT Name FROM Students;` |
| `FROM` | Specifies the table(s) | `FROM Students` |
| `WHERE` | Applies conditions | `WHERE Age > 18` |
| `ORDER BY` | Sorts results | `ORDER BY Name DESC` |
| `GROUP BY` | Groups rows for aggregation | `GROUP BY ClassID` |
| `INNER JOIN` | Combines rows from two tables |  
```sql
SELECT Students.Name, Courses.Title  
FROM Students  
INNER JOIN Enrollments ON Students.StudentID = Enrollments.StudentID;
``` |
| `SUM`, `COUNT`, `AVG` | Aggregate functions | `SELECT COUNT(*) FROM Students;` |

#### **Data Maintenance**

| **Command** | **Purpose** | **Example** |
|-------------|-------------|-------------|
| `INSERT INTO` | Adds data |  
```sql
INSERT INTO Students (StudentID, Name, Age)  
VALUES (101, 'Ali', 17);
``` |
| `DELETE FROM` | Removes data | `DELETE FROM Students WHERE Age < 10;` |
| `UPDATE` | Modifies existing data |  
```sql
UPDATE Students  
SET Age = 18  
WHERE StudentID = 101;
``` |

---

### **4. Practical Example – Two-Table Query**

Assume two tables: `Students(StudentID, Name)` and `Marks(StudentID, Score)`

```sql
SELECT Students.Name, Marks.Score  
FROM Students  
INNER JOIN Marks  
ON Students.StudentID = Marks.StudentID  
WHERE Score > 80  
ORDER BY Score DESC;