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
Like this:
LikeLoading…
Discover more from HintsToday
Subscribe to get the latest posts sent to your email.
Leave a Reply