Essential SQL Commands: Complete Guide with Code Examples, Best Practice Tips & Resources

Hello friend! Welcome to our comprehensive guide on Essential SQL Commands.

Whether you work with MySQL, PostgreSQL, Microsoft SQL Server, SQLite, or Oracle, mastering SQL (Structured Query Language) commands is mandatory for backend development, data engineering, and database administration.

In this guide, we break down SQL commands into their 5 core categories (DQL, DML, DDL, TCL, DCL), provide clean code examples with input/output data tables, share pro tips for performance & security, and provide a curated list of related resources that open in a new tab. 🛢️⚡

📊 Quick Overview: 5 SQL Command Sub-Languages

SQL commands are logically grouped into 5 distinct categories depending on their function:

Category	Full Name	Core Commands	Purpose
DQL	Data Query Language	SELECT	Retrieve data from tables without modifying data.
DML	Data Manipulation Language	INSERT, UPDATE, DELETE	Create, modify, and delete table records.
DDL	Data Definition Language	CREATE, ALTER, DROP, TRUNCATE	Define, alter, and destroy database schemas/tables.
TCL	Transaction Control Language	COMMIT, ROLLBACK, SAVEPOINT	Manage transaction atomic execution and integrity.
DCL	Data Control Language	GRANT, REVOKE	Manage user access permissions and security privileges.

---

1. Data Query Language (DQL) — Reading Data

DQL is used exclusively for querying and fetching records from one or more tables.

Sample Data Table: tb_employees

emp_id	first_name	department	salary	hire_date
101	Alice Johnson	Engineering	85000.00	2022-03-15
102	Bob Smith	Marketing	62000.00	2021-06-01
103	Charlie Brown	Engineering	95000.00	2020-01-10
104	Diana Prince	HR	58000.00	2023-09-20
105	Evan Wright	Engineering	78000.00	2024-02-01

---

1.1 Basic SELECT & Column Aliasing

-- Retrieve specific columns with user-friendly aliases
SELECT 
    first_name AS employee_name,
    department,
    salary
FROM tb_employees;

---

1.2 Filtering with WHERE

Filter results using comparison operators (=, <>, >, <, >=, <=, IN, BETWEEN, LIKE, IS NULL).

-- Retrieve Engineering employees earning over $80,000
SELECT emp_id, first_name, salary 
FROM tb_employees 
WHERE department = 'Engineering' 
  AND salary > 80000.00;

-- Using LIKE pattern matching (% wildcard)
SELECT emp_id, first_name 
FROM tb_employees 
WHERE first_name LIKE 'A%';

---

1.3 Aggregation with GROUP BY & HAVING

GROUP BY collapses multiple rows into summary groups. HAVING filters group results after aggregation.

-- Find average salary per department for departments with average > $65,000
SELECT 
    department,
    COUNT(emp_id) AS total_staff,
    AVG(salary) AS avg_salary
FROM tb_employees
GROUP BY department
HAVING AVG(salary) > 65000.00
ORDER BY avg_salary DESC;

---

1.4 Table JOINs Explained

Combine data from multiple tables based on related foreign key columns.

-- Sample Table: tb_departments (dept_id, dept_name, location)

-- INNER JOIN: Only returns rows matching in both tables
SELECT 
    e.emp_id, 
    e.first_name, 
    d.dept_name, 
    d.location
FROM tb_employees e
INNER JOIN tb_departments d ON e.department = d.dept_name;

-- LEFT JOIN: Returns ALL rows from left table + matching rows from right
SELECT 
    e.first_name, 
    d.dept_name
FROM tb_employees e
LEFT JOIN tb_departments d ON e.department = d.dept_name;

---

2. Data Manipulation Language (DML) — Modifying Data

DML statements create, modify, and delete data records inside tables.

2.1 INSERT INTO — Inserting Rows

-- Single row insert
INSERT INTO tb_employees (emp_id, first_name, department, salary, hire_date)
VALUES (106, 'Fiona Apple', 'Marketing', 67000.00, '2025-01-15');

-- Multi-row bulk insert
INSERT INTO tb_employees (emp_id, first_name, department, salary, hire_date)
VALUES 
    (107, 'George Clark', 'Finance', 72000.00, '2025-02-01'),
    (108, 'Hannah Abbott', 'Finance', 69000.00, '2025-02-10');

---

2.2 UPDATE — Modifying Existing Rows

⚠️ Warning: Always specify a WHERE clause unless you intend to update every single row in the database table!

-- Give a 10% raise to all Engineering employees
UPDATE tb_employees
SET salary = salary * 1.10
WHERE department = 'Engineering';

---

2.3 DELETE — Removing Specific Rows

-- Remove a specific employee by ID
DELETE FROM tb_employees
WHERE emp_id = 108;

---

3. Data Definition Language (DDL) — Managing Schemas

DDL statements create, alter, and delete database objects like tables, views, indexes, and schemas.

3.1 CREATE TABLE

CREATE TABLE tb_products (
    product_id INT PRIMARY KEY AUTO_INCREMENT,
    product_name VARCHAR(150) NOT NULL,
    category VARCHAR(50) DEFAULT 'General',
    price DECIMAL(10, 2) NOT NULL CHECK (price >= 0),
    stock_qty INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

---

3.2 ALTER TABLE

-- Add a new column
ALTER TABLE tb_products 
ADD COLUMN is_active BOOLEAN DEFAULT TRUE;

-- Modify existing column data type
ALTER TABLE tb_products 
MODIFY COLUMN category VARCHAR(100);

-- Drop a column
ALTER TABLE tb_products 
DROP COLUMN is_active;

---

3.3 DELETE vs TRUNCATE vs DROP

Feature	DELETE FROM table	TRUNCATE TABLE	DROP TABLE
Command Type	DML	DDL	DDL
What it removes	Specific or all rows	All rows instantly	Entire table structure & data
Speed	Slower (logs row by row)	Extremely fast (deallocates pages)	Instant
Transaction Rollback	Yes (Rollback supported)	Dependent on DB engine	Cannot rollback in some DBs
Reset Identity/Auto-Increment	No	Yes (resets counter to 1)	Deletes table schema entirely

-- Fast removal of all rows (resets auto-increment)
TRUNCATE TABLE tb_products;

-- Complete deletion of table structure
DROP TABLE IF EXISTS tb_products;

---

4. Transaction Control (TCL) & Data Control (DCL)

4.1 TCL: Transactions (COMMIT & ROLLBACK)

Transactions ensure your operations adhere to ACID properties (Atomicity, Consistency, Isolation, Durability).

-- Bank Transfer Example
START TRANSACTION; -- Or BEGIN TRANSACTION in SQL Server / Postgres

-- Step 1: Deduct $500 from Account A
UPDATE tb_accounts 
SET balance = balance - 500.00 
WHERE account_id = 'ACC_A';

-- Step 2: Add $500 to Account B
UPDATE tb_accounts 
SET balance = balance + 500.00 
WHERE account_id = 'ACC_B';

-- Save point creation
SAVEPOINT step_one_complete;

-- Check if both succeeded, then commit
COMMIT;

-- If error occurred, cancel changes:
-- ROLLBACK;

---

4.2 DCL: Security & Permissions (GRANT & REVOKE)

-- Grant SELECT and INSERT access to a database user
GRANT SELECT, INSERT ON database_name.tb_employees TO 'app_user'@'localhost';

-- Revoke permissions
REVOKE INSERT ON database_name.tb_employees FROM 'app_user'@'localhost';

---

💡 Pro Tips & Best Practices for Writing SQL

Tip 1: Avoid SELECT * in Production Queries

• Explicitly select required columns (SELECT id, name) to reduce network bandwidth, disk I/O, and CPU load on database servers.

Tip 2: Use Indexes Wisely

• Index columns heavily queried in WHERE, JOIN ... ON, and ORDER BY clauses.
• Do not over-index write-heavy tables because every INSERT, UPDATE, and DELETE will require updating index structures.

Tip 3: Test UPDATE and DELETE with SELECT First

• Convert your UPDATE or DELETE query to a SELECT statement before execution to verify exact affected row counts.

Tip 4: Protect Against SQL Injection with Parameterized Queries

• Never concatenate user inputs directly into raw SQL query strings. Use prepared statements or parameterized queries in your backend API code (Node.js, Python, PHP, Java, C#).

Tip 5: Understand Logical Query Processing Order

Remember that database engines process queries in this order, NOT the order written:

1. FROM & JOIN
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT
6. DISTINCT
7. ORDER BY
8. LIMIT / OFFSET

---

🌐 Related Resources & Further Reading

Explore these curated official guides, interactive tools, and internal tutorials. All external and reference links open in a new browser tab for your convenience.

📚 Official Database Documentation (New Tab)

• MySQL 8.0 Reference Manual — SQL Statements
• PostgreSQL Documentation — SQL Commands Syntax
• Microsoft T-SQL Language Reference (SQL Server)
• SQLite Syntax & Language Description

⚡ Practice & Interactive SQL Platforms (New Tab)

• DB-Fiddle — Test & Share SQL Queries Online
• SQLBolt — Interactive Lessons & Exercises for Beginners
• LeetCode Database Problems — Practice Real-World SQL Interviews

🏷️ Related Tutorials on dev.rean.me (New Tab)

• Complete Database & SQL References & Tips with Code Examples
• SQL Cheat Sheet — Free Download for Beginner
• SQL SELECT Statement — All Forms Explained with Example
• SQL Operation Logic Order Explained (Execution Flow)
• SQL Data Types in Relational Database Explained
• All Database & SQL Tutorial Series & Course List

---

Happy querying! If you found this SQL commands guide helpful, bookmark it and share it with fellow developers! 🚀