SQL (Structured Query Language) is a standard language for managing and manipulating databases. SQL is divided into different languages or subcategories based on the type of operation it performs. Here's a classification of SQL languages along with examples of queries:
1. **Data Query Language (DQL)**:
- **Purpose**: Used to retrieve data from a database.
- **Key Statement**: `SELECT`
Example:
SELECT * FROM students;
In this example, the `SELECT` statement retrieves all rows from the `students` table.
2. **Data Definition Language (DDL)**:
- **Purpose**: Defines the structure of the database, such as creating, altering, and dropping tables and other database objects.
- **Key Statements**: `CREATE`, `ALTER`, `DROP`, `TRUNCATE`
Example 1: `CREATE TABLE`
CREATE TABLE students (
student_id INT PRIMARY KEY,
student_name VARCHAR(100),
age INT
);
This creates a `students` table with columns for `student_id`, `student_name`, and `age`.
Example 2: `ALTER TABLE`
ALTER TABLE students ADD COLUMN gender VARCHAR(10);
This adds a `gender` column to the `students` table.
Example 3: `DROP TABLE`
DROP TABLE students;
This statement deletes the `students` table along with all its data.
---
3. **Data Manipulation Language (DML)**:
- **Purpose**: Used to manipulate data within the database. It covers inserting, updating, and deleting records.
- **Key Statements**: `INSERT`, `UPDATE`, `DELETE`
Example 1: `INSERT`
INSERT INTO students (student_id, student_name, age)
VALUES (1, 'John Doe', 20);
This inserts a new record into the `students` table.
Example 2: `UPDATE`
UPDATE students
SET age = 21
WHERE student_id = 1;
This updates the `age` of the student with `student_id` 1 to 21.
Example 3: `DELETE`
DELETE FROM students WHERE student_id = 1;
This deletes the record of the student with `student_id` 1 from the `students` table.
4. **Data Control Language (DCL)**:
- **Purpose**: Used to control access to data in the database, typically through permission management.
- **Key Statements**: `GRANT`, `REVOKE`
Example 1: `GRANT`
GRANT SELECT, INSERT ON students TO 'username';
This grants the user `username` permission to `SELECT` and `INSERT` records in the `students` table.
Example 2: `REVOKE`
REVOKE INSERT ON students FROM 'username';
This revokes the `INSERT` permission from the user `username` on the `students` table.
---
5. **Transaction Control Language (TCL)**:
- **Purpose**: Used to manage transactions in the database. Transactions allow groups of SQL statements to be executed in a way that ensures consistency and atomicity.
- **Key Statements**: `COMMIT`, `ROLLBACK`, `SAVEPOINT`
Example 1: `COMMIT`
COMMIT;
This commits the current transaction, making all changes made permanent.
Example 2: `ROLLBACK`
```sql
ROLLBACK;
```
This rolls back the current transaction, undoing all changes since the last `COMMIT`.
Example 3: `SAVEPOINT`
SAVEPOINT save1;
This creates a savepoint named `save1`, which allows partial rollback to this specific point
Classification of SQL Queries:
Each of these languages plays a critical role in working with relational databases, and depending on the specific use case, you would use different combinations of them to interact with your data effectively.
