SQL (Structured Query Language) is the standard language for interacting with relational databases. Whether you're building an application, generating a report, or maintaining data integrity, a solid grasp of SQL's building blocks is essential. This article walks through the core concepts every developer or analyst should know.
1. SQL Categories
SQL commands are grouped into four main categories based on their function:
DDL (Data Definition Language) - defines and modifies database structure.
-
CREATE,ALTER,DROP,TRUNCATE
CREATE TABLE employees (
id SERIAL PRIMARY KEY,
name VARCHAR(100)
);
DML (Data Manipulation Language) - manages the actual data
-
SELECT,INSERT,UPDATE,DELETE
INSERT INTO employees (name) VALUES ('Alice');
UPDATE employees SET name = 'Alicia' WHERE id = 1;
DCL (Data Control Language) - controls access and permissions
-
GRANT,REVOKE
GRANT SELECT, INSERT ON employees TO analyst_role;
REVOKE INSERT ON employees FROM analyst_role;
TCL (Transaction Control Language) - manages transactions
-
COMMIT,ROLLBACK,SAVEPOINT
BEGIN;
UPDATE employees SET name = 'Bob' WHERE id = 2;
SAVEPOINT before_delete;
DELETE FROM employees WHERE id = 3;
ROLLBACK TO before_delete;
COMMIT;
2. Data Types
Common data types (illustrated with PostgreSQL-style syntax; other databases use similar variants):
Category------Examples
Numeric------INT, BIGINT, DECIMAL(10,2), FLOAT
Character------CHAR(n), VARCHAR(n), TEXT
Date/Time------DATE, TIME, TIMESTAMP
Boolean------BOOLEAN
Binary------BYTEA, BLOB
JSON------JSON, JSONB
Choosing the right type matters for storage efficiency and query performance, e.g. use VARCHAR(n) over TEXT when a length limit is meaningful, and DECIMAL rather than FLOAT for currency to avoid rounding errors.
3. Constraints and Defaults
Constraints enforce rules on data:
PRIMARY KEY- uniquely identifies each rowFOREIGN KEY- enforces referential integrity between tablesUNIQUE- ensures no duplicate valuesNOT NULL- disallows empty valuesCHECK- validates a conditionDEFAULT- supplies a fallback value
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
customer_id INT NOT NULL REFERENCES customer(id),
status VARCHAR(20) DEFAULT 'pending',
total DECIMAL(10,2) CHECK (total >= 0)
4. Schemas and the public Schema
A schema is a namespace that groups related tables, views, and other objects within a database. Schemas help organize large databases and manage access control.
In PostgreSQL, every new database includes a default schema called public. Unless specified otherwise, tables are created there:
CREATE TABLE public.customers (...);
--equivalent to
CREATE TABLE customers (...);
Custom schemas are useful for multi-tenant systems or separating concerns (e.g., sales, hr, audit):
CREATE SCHEMA sales;
CREATE TABLE sales.invoices (...);
5. Common Commands and Examples
--Retrieve data
SELECT name, salary FROM employees WHERE salary > 50000;
--Sort and limit
SELECT * FROM employees ORDER BY salary DESC LIMIT 5;
--Aggregate
SELECT department, AVG(salary) FROM employees GROUP BY department;
--Filter groups
SELECT department, COUNT(*) FROM employees
GROUP BY department HAVING COUNT(*) > 10;
6. The CASE WHEN Statement
CASE WHEN provides conditional logic within a query, similar to if-else in programming languages.
SELECT name,
CASE
WHEN salary >= 100000 THEN 'Senior'
WHEN salary >= 60000 THEN 'Mid-level'
ELSE 'Junior'
END AS pay_grade
FROM employees;
This is especially useful for creating derived categories, pivoting data, or cleaning up display values without changing the underlying data.
7. SQL Joins
A join combines rows from two or more tables based on a related column, typically a foreign key relationship.
INNER JOIN - returns only matching rows in both tables.
LEFT JOIN - returns all rows from the left table, with matched rows from the right (or NULL if no match).
RIGHT JOIN - the mirror of LEFT JOIN.
FULL OUTER JOIN - returns all rows from both tables, matched where possible.
CROSS JOIN - returns the Cartesian product of both tables
SELECT o.id, c.name
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id;
SELECT c.name, o.id
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id;
Joins are fundamental because relational databases normalize data across multiple tables to reduce redundancy, joins let you reassemble that data for meaningful queries.
8. CASCADE, DELETE, and Use Cases
ON DELETE CASCADE (and similarly ON UPDATE CASCADE) automatically propagates changes from a parent row to related child rows.
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
customer_id INT REFERENCES customers(id) ON DELETE CASCADE
);
If a customer is deleted, all their orders are automatically deleted too. Other options include:
ON DELETE SET NULL- sets the foreign key to NULL instead of deletingON DELETE RESTRICT- blocks deletion if child rows exist
Use cases: CASCADE is appropriate for dependent data with no independent meaning (e.g., order line items belonging to an order). RESTRICT or SET NULL is safer when child records should be preserved or reassigned, such as employee records tied to a department.
9. Keywords, Operators, and Filtering
Filtering conditions commonly use:
Comparison operators:
=,!=,<,>,<=,>=Logical operators:
AND,OR,NOTRange filtering:
BETWEEN
SELECT * FROM orders WHERE total BETWEEN 100 AND 500;
-
Membership filtering:
IN,NOT IN
SELECT * FROM employees WHERE department IN ('Sales', 'Marketing');
-
Pattern matching:
LIKE,ILIKE(case-insensitive)
SELECT * FROM customers WHERE name LIKE 'A%';
-
Null checks:
IS NULL,IS NOT NULL
Mastering these filters allows precise, efficient querying without over-fetching data.
Conclusion
SQL's power comes from a relatively small set of consistent concepts, categorized commands, well-defined data types, constraints that protect integrity, and joins that let normalized data come back together meaningfully. A solid foundation in these fundamentals makes it far easier to write efficient, safe, and maintainable database code.
