What is SQL?

SQL (Structured Query Language) is used to interact with relational databases. Data is stored in tables (like spreadsheets) with rows (records) and columns (fields). SQL lets you query, insert, update, and manage this data easily.

Data Types

Common SQL data types include INT for numbers, VARCHAR(n) for text, DATE for dates, BOOLEAN for true/false, and DECIMAL for precise numbers. Different databases may add more.

CREATE TABLE students (
  id INT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  birthdate DATE,
  gpa DECIMAL(3,2)
);

SELECT

The SELECT command retrieves data from a table. Use column names to get specific data or * for all. AS renames columns, and DISTINCT removes duplicates.

SELECT * FROM users;
SELECT name, age FROM users;
SELECT DISTINCT city FROM users;
SELECT name AS student_name FROM users;

WHERE Clause

Filters rows based on conditions. Supports operators like =, >, <, BETWEEN, IN, LIKE (for patterns).

SELECT * FROM users WHERE age > 18;
SELECT * FROM users WHERE city IN ('Paris', 'London');
SELECT * FROM users WHERE name LIKE 'A%';

ORDER BY & LIMIT

Sort results with ORDER BY. Use ASC or DESC. Limit results with LIMIT (or TOP in some databases).

SELECT * FROM users ORDER BY age DESC LIMIT 5;

INSERT

Adds new rows to a table. You can insert one row or multiple rows at once.

INSERT INTO users (name, age) VALUES ('Alice', 25);
INSERT INTO users (name, age) VALUES 
('Bob', 30), ('Carol', 22);

UPDATE

Modifies existing rows. Always use WHERE to avoid updating all rows by mistake.

UPDATE users SET age = 26 WHERE name = 'Alice';

DELETE

Removes rows from a table. Be careful — without WHERE, it deletes all rows.

DELETE FROM users WHERE age < 18;

Joins

Joins combine rows from two tables based on a common column. Different types determine which rows appear.

-- INNER JOIN (only matches)
SELECT users.name, orders.product
FROM users
INNER JOIN orders ON users.id = orders.user_id;

-- LEFT JOIN (all users, with orders if any)
SELECT users.name, orders.product
FROM users
LEFT JOIN orders ON users.id = orders.user_id;

GROUP BY & HAVING

Groups rows by a column. Use aggregate functions to summarize.HAVING filters grouped results.

SELECT city, COUNT(*) 
FROM users 
GROUP BY city 
HAVING COUNT(*) > 5;

Aggregate Functions

SQL provides built-in functions to summarize data: COUNT(), SUM(), AVG(), MAX(), MIN().

SELECT COUNT(*) FROM users;
SELECT AVG(age) FROM users;

Constraints

Constraints enforce rules in a table: NOT NULL (no empty values), UNIQUE (no duplicates), DEFAULT (set fallback value), PRIMARY KEY (unique ID), FOREIGN KEY (relation between tables).

CREATE TABLE orders (
  id INT PRIMARY KEY,
  user_id INT,
  product VARCHAR(100) NOT NULL,
  FOREIGN KEY (user_id) REFERENCES users(id)
);

ALTER TABLE

Use ALTER TABLE to change structure: add, modify, or drop columns.

ALTER TABLE users ADD email VARCHAR(100);
ALTER TABLE users DROP COLUMN gpa;

Indexes

Indexes improve search speed on columns. They work like book indexes, but they take extra storage.

CREATE INDEX idx_name ON users(name);

Views

Views are saved queries you can reuse like virtual tables. They simplify complex queries.

CREATE VIEW user_summary AS
SELECT city, COUNT(*) as total_users
FROM users
GROUP BY city;

Subqueries

Subqueries are queries inside another query. They are useful for filtering or comparisons.

SELECT name 
FROM users 
WHERE age > (SELECT AVG(age) FROM users);

Transactions

Transactions group multiple SQL statements so they either all succeed or all fail. Use BEGIN, COMMIT, and ROLLBACK.

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

How to use this page

Start with SELECT, WHERE, and ORDER BY for basic queries.
Practice INSERT, UPDATE, and DELETE to modify data.
Move to JOIN, GROUP BY, and aggregates to analyze and combine data.
Explore advanced features like constraints, views, subqueries, and transactions for real-world projects.

SQL Cheatsheet