SQL Command Categories Overview
SQL commands are classified into five main categories based on their functionality:
| Category | Acronym | Description |
|---|---|---|
| DDL | Data Definition Language | Define/alter schema structure (tables, views, indexes) |
| DML | Data Manipulation Language | Modify data (insert, update, delete) |
| DCL | Data Control Language | Manage user access (privileges) |
| TCL | Transaction Control Language | Control transaction flow (commit, rollback) |
| DQL | Data Query Language | Query and retrieve data |
3.1 Data Definition Language (DDL)
DDL is used to define or modify schema-level structures.
| Command | Purpose | Example |
|---|---|---|
| CREATE | Creates tables/views/indexes | CREATE TABLE students (id INT, name VARCHAR(50)); |
| ALTER | Modifies table structure | ALTER TABLE students ADD COLUMN age INT; |
| DROP | Deletes a table/view/index permanently | DROP TABLE students; |
| TRUNCATE | Removes all data from a table | TRUNCATE TABLE students; |
| RENAME | Renames a table or column | ALTER TABLE students RENAME TO learners; |
3.2 Data Manipulation Language (DML)
DML is used for managing records within tables.
| Command | Purpose | Example |
|---|---|---|
| INSERT | Adds new rows | INSERT INTO students (id, name) VALUES (1, 'John'); |
| UPDATE | Modifies existing rows | UPDATE students SET name = 'John Doe' WHERE id = 1; |
| DELETE | Removes rows | DELETE FROM students WHERE id = 1; |
| MERGE | Combines INSERT and UPDATE | MERGE INTO target USING source ON condition WHEN MATCHED THEN UPDATE... |
3.3 Data Control Language (DCL)
DCL controls access and privileges for users.
| Command | Purpose | Example |
|---|---|---|
| GRANT | Assigns privileges | GRANT SELECT ON students TO user1; |
| REVOKE | Removes privileges | REVOKE SELECT ON students FROM user1; |
3.4 Transaction Control Language (TCL)
TCL manages the lifecycle of transactions.
| Command | Purpose | Example |
|---|---|---|
| COMMIT | Saves changes | COMMIT; |
| ROLLBACK | Reverts changes | ROLLBACK; |
| SAVEPOINT | Sets rollback points | SAVEPOINT save1; |
| SET TRANSACTION | Sets isolation level | SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; |
3.5 Data Query Language (DQL)
DQL focuses on querying and fetching data.
| Command | Purpose | Example |
|---|---|---|
| SELECT | Retrieve records | SELECT name FROM students WHERE age > 20; |
4. CRUD Operations (SQL + Spark SQL)
CRUD stands for Create, Read, Update, Delete, the core operations of data handling.
| Operation | SQL Example | Spark SQL Example |
|---|---|---|
| Create | INSERT INTO users VALUES (1, 'Alice'); | INSERT INTO employees VALUES (1, 'Alice', 30, 'Engg'); |
| Read | SELECT * FROM users; | SELECT name, department FROM employees; |
| Update | UPDATE users SET name='Bob' WHERE id=1; | (Delta Only) UPDATE employees SET age=31 WHERE id=1; |
| Delete | DELETE FROM users WHERE id=1; | (Delta Only) DELETE FROM employees WHERE age<25; |
4.1 Creating Databases and Tables
MySQL/PostgreSQL
CREATE DATABASE my_db;
CREATE TABLE customers (
id INT PRIMARY KEY,
name VARCHAR(100),
email VARCHAR(100) UNIQUE
);
Spark SQL
CREATE TABLE students (
id INT, name STRING, age INT
);
4.2 Inserting Data
- Single Record:
INSERT INTO users (name) VALUES ('Alice'); - Multiple Records:
INSERT INTO users (name) VALUES ('A'), ('B'); - From Another Table:
INSERT INTO new_users SELECT * FROM old_users;
4.3 Reading Data
SELECT * FROM users;
SELECT name FROM users WHERE age > 18;
SELECT COUNT(*) FROM users;
4.4 Updating and Deleting Data (SQL & Delta Lake)
UPDATE employees SET age = 35 WHERE id = 5;
DELETE FROM employees WHERE department = 'HR';
4.5 MERGE / UPSERT (Delta Lake)
MERGE INTO employees AS e
USING updates AS u
ON e.id = u.id
WHEN MATCHED THEN UPDATE SET age = u.age
WHEN NOT MATCHED THEN INSERT (id, name, age) VALUES (u.id, u.name, u.age);
4.6 Additional Spark SQL Features
| Feature | Command | Example |
|---|---|---|
| View Creation | CREATE VIEW v1 AS SELECT * FROM t1; | |
| Caching | CACHE TABLE my_table; | |
| Broadcast Join | SELECT /*+ BROADCAST(dim) */ * FROM fact JOIN dim; | |
| Schema Evolution | ALTER TABLE CHANGE COLUMN col_name NEW_TYPE; |
5. Summary Table of SQL Categories
| Category | Commands | Main Purpose |
|---|---|---|
| DDL | CREATE, ALTER, DROP, TRUNCATE | Define/modify schema |
| DML | INSERT, UPDATE, DELETE | Manipulate data rows |
| DCL | GRANT, REVOKE | Control user access |
| TCL | COMMIT, ROLLBACK, SAVEPOINT | Manage transactions |
| DQL | SELECT | Query data |
6. Final Notes
- Use EXPLAIN or EXPLAIN ANALYZE to check query execution plans.
- Leverage Delta Lake for full CRUD support with