What is SQL? A Complete Guide to Structured Query Language

What is SQL?

SQL means Structured Query Language. It is a standardized language used to create, query, update, and manage data in relational databases.

SQL is declarative: you describe what data you want, and the database engine decides how to retrieve it efficiently. That makes SQL different from step-by-step programming languages like JavaScript or Python.

SQL remains a core technology because it powers databases such as PostgreSQL, MySQL, SQLite, SQL Server, Oracle Database, and many analytics systems.

A Brief History of SQL

SQL began with Edgar F. Codd's relational model in 1970. IBM researchers Donald Chamberlin and Raymond Boyce later created SEQUEL, which became SQL, to query relational data.

Relational Databases Explained

A relational database organizes data into tables. Each table usually has:

• Columns, which define fields such as name, email, price, or created_at.
• Rows, where each row is one record.
• Primary keys, which uniquely identify rows.
• Foreign keys, which connect rows across tables.

users(id, name, email)
orders(id, user_id, total, order_date)

orders.user_id -> users.id

This structure reduces duplication. For example, customer details can live once in a users table while orders reference that customer by id.

SQL Syntax Basics

SQL statements are often grouped into categories:

SELECT users.name, orders.total
FROM users
JOIN orders ON orders.user_id = users.id
WHERE orders.total > 100
ORDER BY orders.total DESC;

CRUD Operations

CRUD means create, read, update, and delete. These are the basic operations most apps perform on stored data.

-- Create
INSERT INTO products (name, price) VALUES ('Keyboard', 49.99);

-- Read
SELECT id, name, price FROM products WHERE price > 20;

-- Update
UPDATE products SET price = 44.99 WHERE id = 1;

-- Delete
DELETE FROM products WHERE id = 1;

Filtering, Sorting, and Aggregation

Real queries usually filter rows, sort results, group data, and calculate aggregates such as counts, sums, averages, minimums, and maximums.

• WHERE filters rows.
• ORDER BY sorts results.
• GROUP BY groups rows for aggregation.
• HAVING filters grouped results.

JOINs: Combining Tables

JOINs combine rows from multiple tables. They are the feature that lets relational databases model real-world relationships without copying data everywhere.

• INNER JOIN: returns only matching rows from both tables.
• LEFT JOIN: returns all rows from the left table plus matching rows from the right table.
• RIGHT JOIN: returns all rows from the right table plus matching rows from the left table.
• FULL OUTER JOIN: returns rows from both sides, matched where possible.

SELECT users.name, orders.order_date, orders.total
FROM users
INNER JOIN orders ON orders.user_id = users.id;

Subqueries and CTEs

A subquery is a query inside another query. A CTE, written with WITH, names a temporary result set so complex SQL is easier to read and reuse.

Indexes and Performance

An index is a data structure, often a B-tree, that helps the database find rows faster. Indexes speed up reads but cost extra storage and can slow writes because each INSERT, UPDATE, or DELETE may also update indexes.

CREATE INDEX idx_products_category ON products(category);
EXPLAIN SELECT * FROM products WHERE category = 'Electronics';

Transactions and ACID

BEGIN TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

COMMIT;
-- ROLLBACK;

SQL Dialects Compared

SQL is standardized, but every database adds features and small syntax differences.

SQL Injection and Security

SQL injection happens when user input is directly concatenated into SQL text. Attackers can change the meaning of the query and read, modify, or delete data.

-- Vulnerable: string concatenation
query = "SELECT * FROM users WHERE name = '" + userInput + "'";

-- Safe: parameterized query
query = "SELECT * FROM users WHERE name = ?";
db.execute(query, [userInput]);

Best Practices

• Format SQL consistently with uppercase keywords and clear indentation. A SQL formatter can help.
• Use meaningful table and column names.
• Avoid SELECT * in production queries; request only the columns you need.
• Add indexes strategically and validate them with EXPLAIN.
• Use transactions for related changes that must succeed or fail together.
• Use parameterized queries for every user-supplied value.
• Back up regularly and test restore procedures.

SQL vs NoSQL

NoSQL databases such as MongoDB, Redis, Cassandra, and DynamoDB are useful for workloads where a relational model is not ideal.

Many modern systems use both: SQL for transactional records such as users and orders, and NoSQL for caching, search, events, or document storage.