Understanding SQL Commands: A Comprehensive Guide with Examples
SQL commands are the foundation of database manipulation and management. This
guide explores each type of SQL command with practical examples to help you
understand their applications.
Data Manipulation Language (DML)
DML commands are your daily tools for working with data. Think of them as the
UDIS family:
UPDATE
Modifies existing data within tables.
-- Update employee salary
UPDATE employees
SET salary = 60000
WHERE department = 'Engineering' AND experience_years > 5;
DELETE
Removes specific rows based on conditions.
-- Remove inactive customers
DELETE FROM customers
WHERE last_login < '2023-01-01' AND status = 'inactive';
INSERT
Adds new records to tables.
-- Add a new employee
INSERT INTO employees (name, department, salary)
VALUES ('John Smith', 'Marketing', 55000);
-- Add multiple records
INSERT INTO products (name, price, category)
VALUES
('Laptop', 999.99, 'Electronics'),
('Desk Chair', 199.99, 'Furniture');
SELECT
Retrieves data with powerful filtering and sorting capabilities.
-- Basic select with conditions
SELECT name, department, salary
FROM employees
WHERE salary > 50000
ORDER BY department;
-- Select with joins
SELECT o.order_id, c.name, p.product_name
FROM orders o
JOIN customers c ON o.customer_id = c.id
JOIN products p ON o.product_id = p.id
WHERE o.order_date >= '2024-01-01';
Data Definition Language (DDL)
DDL commands shape your database structure:
CREATE
Builds database objects.
-- Create a new table
CREATE TABLE products (
id INTEGER PRIMARY KEY,
name VARCHAR(100) NOT NULL,
price DECIMAL(10,2),
category VARCHAR(50),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- Create an index
CREATE INDEX idx_product_name ON products(name);
ALTER
Modifies existing structures.
-- Add a column
ALTER TABLE products ADD COLUMN description TEXT;
-- Modify column type
ALTER TABLE products ALTER COLUMN price TYPE NUMERIC(12,2);
TRUNCATE
Efficiently removes all data while keeping the table structure.
-- Clear all product data
TRUNCATE TABLE products;
-- Clear multiple tables
TRUNCATE TABLE orders, order_items CASCADE;
DROP
Completely removes database objects.
-- Remove a table
DROP TABLE IF EXISTS old_products;
-- Remove multiple objects
DROP TABLE products CASCADE;
Data Querying Language (DQL)
DQL commands help analyze and aggregate data:
-- Distinct values
SELECT DISTINCT category
FROM products
WHERE price > 100;
-- Aggregation functions
SELECT
category,
COUNT(*) as total_products,
AVG(price) as avg_price,
MIN(price) as lowest_price,
MAX(price) as highest_price,
SUM(price) as inventory_value
FROM products
GROUP BY category
HAVING COUNT(*) > 5;
Data Control Language (DCL)
DCL manages security and access:
-- Grant specific permissions
GRANT SELECT, INSERT ON products TO analyst_role;
-- Grant all permissions
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO admin_role;
-- Revoke permissions
REVOKE INSERT, UPDATE ON orders FROM junior_analyst;
Transaction Control Language (TCL)
TCL ensures data integrity during transactions:
-- Basic transaction
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
SAVEPOINT transfer_complete;
-- If something goes wrong
ROLLBACK TO transfer_complete;
-- If all is good
COMMIT;
-- Set transaction properties
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
Common Use Cases
Data Migration: Combine DDL and DML for structure and data transfer
Reporting: Use DQL for complex analytics and aggregations
User Management: DCL for role-based access control
Data Maintenance: Regular cleanup using DML and TCL
