Essential SQL Commands

If you work with relational databases, knowing SQL (Structured Query Language) is essential. Here is a list of the most important SQL commands, grouped by category, with a few complete examples along the way.

The syntax below is standard SQL. Where the main databases (PostgreSQL, MySQL, SQL Server, SQLite) behave differently, it is pointed out.

1. DDL (Data Definition Language)

#

CREATE DATABASE <name> - create a new database

DROP DATABASE <name> - delete an existing database

CREATE TABLE <table> (...) - create a new table

DROP TABLE <table> - delete a table and its data

ALTER TABLE <table> ADD <column> <type> - add a column to a table

TRUNCATE TABLE <table> - empty a table while keeping its structure

CREATE INDEX <name> ON <table> (column) - create an index to speed up searches on a column

Example tables with a primary key, a foreign key and constraints:

CREATE TABLE customers (
    id     INT PRIMARY KEY,
    name   VARCHAR(100) NOT NULL,
    email  VARCHAR(255) UNIQUE
);

CREATE TABLE orders (
    id           INT PRIMARY KEY,
    customer_id  INT NOT NULL,
    total        DECIMAL(10, 2) CHECK (total >= 0),
    created_on   DATE,
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);

2. DML (Data Manipulation Language)

#

INSERT INTO <table> (col1, col2) VALUES (val1, val2) - insert a new record

UPDATE <table> SET col1 = val1 WHERE condition - update existing records

DELETE FROM <table> WHERE condition - delete records from a table

Careful: an UPDATE or DELETE without a WHERE changes every row in the table. Before running it, run a SELECT with the same condition to see which rows will be affected.

3. DQL (Data Query Language)

#

SELECT * FROM <table> - select all columns from a table

SELECT col1, col2 FROM <table> - select specific columns

SELECT DISTINCT column FROM <table> - select unique values

SELECT * FROM <table> WHERE condition - filter the results (e.g. id = 1)

SELECT * FROM <table> ORDER BY column ASC|DESC - sort the results

SELECT * FROM <table> LIMIT 10 - limit the number of results (SQL Server uses SELECT TOP 10 * FROM <table>)

SELECT col AS alias FROM <table> - rename a column in the result

4. DCL (Data Control Language)

#

GRANT <privileges> ON <table> TO <user> - grant privileges to a user

REVOKE <privileges> ON <table> FROM <user> - revoke privileges from a user

5. TCL (Transaction Control Language)

#

BEGIN (or START TRANSACTION) - start a new transaction

COMMIT - permanently save the changes of the current transaction

ROLLBACK - undo the changes of the current transaction

SAVEPOINT <name> - set a savepoint inside a transaction

Classic example: a bank transfer must update both accounts, or neither of them.

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT; -- on error: ROLLBACK;

6. Joins

#

INNER JOIN - records with matching values in both tables

LEFT JOIN - all records from the left table and the matching ones from the right table

RIGHT JOIN - all records from the right table and the matching ones from the left table

FULL JOIN - all records from both tables, matched where possible (not supported by MySQL)

-- Every customer with their orders, including customers who haven't ordered yet
SELECT c.name, o.id AS order_id, o.total
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
ORDER BY c.name;

7. Aggregate Functions and Grouping

#

COUNT(column) - count the rows with a non-NULL value (COUNT(*) counts every row)

SUM(column) - calculate the sum of the values

AVG(column) - calculate the average of the values

MIN(column) / MAX(column) - find the minimum / maximum value

GROUP BY column - group rows that have the same value

HAVING condition - filter the groups (like WHERE, but after GROUP BY)

-- Customers who spent more than 500, from best to worst
SELECT c.name, COUNT(*) AS num_orders, SUM(o.total) AS spent
FROM customers c
JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name -- c.id keeps two customers with the same name apart
HAVING SUM(o.total) > 500
ORDER BY spent DESC;

Logical order of execution: FROM → JOIN → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT. That’s why many databases don’t let you use a SELECT alias inside WHERE.

8. Logical and Comparison Operators

#

=, <>, !=, <, >, <=, >= - comparison operators (<> is the standard, != is widely supported)

AND, OR, NOT - logical operators to combine conditions

BETWEEN val1 AND val2 - check whether a value is within a range (bounds included)

LIKE 'pattern' - search for a pattern: % = any sequence of characters, _ = a single character (e.g. '%test%')

IN (val1, val2, ...) - check whether a value is in a list

IS NULL / IS NOT NULL - check whether a value is or isn’t NULL (= NULL never works!)

Mastering these commands will let you query and manage any relational database effectively!