SQL Cheat Sheet

1. Basic SQL Commands

SELECT:

SELECT

username@machine_name:~$ SELECT column1, column2
FROM table_name;

INSERT:

INSERT

username@machine_name:~$ INSERT INTO table_name (column1, column2)
VALUES (value1, value2);

UPDATE:

UPDATE

username@machine_name:~$ UPDATE table_name
SET column1 = value1, column2 = value2
WHERE condition;

DELETE:

DELETE

username@machine_name:~$ DELETE FROM table_name
WHERE condition;

2. Filtering and Sorting Data

WHERE:

WHERE

username@machine_name:~$ SELECT column1, column2
FROM table_name
WHERE condition;

ORDER BY:

ORDER BY

username@machine_name:~$ SELECT column1, column2
FROM table_name
ORDER BY column1 [ASC|DESC];

LIMIT:

LIMIT

username@machine_name:~$ SELECT column1, column2
FROM table_name
LIMIT number;

3. Joining Tables

INNER JOIN:

INNER JOIN

username@machine_name:~$ SELECT a.column1, b.column2
FROM table1 a
INNER JOIN table2 b
ON a.common_field = b.common_field;

LEFT JOIN:

LEFT JOIN

username@machine_name:~$ SELECT a.column1, b.column2
FROM table1 a
LEFT JOIN table2 b
ON a.common_field = b.common_field;

RIGHT JOIN:

RIGHT JOIN

username@machine_name:~$ SELECT a.column1, b.column2
FROM table1 a
RIGHT JOIN table2 b
ON a.common_field = b.common_field;

4. Aggregating Data

COUNT:

COUNT

username@machine_name:~$ SELECT COUNT(column_name)
FROM table_name
WHERE condition;

SUM:

SUM

username@machine_name:~$ SELECT SUM(column_name)
FROM table_name
WHERE condition;

AVG:

AVG

username@machine_name:~$ SELECT AVG(column_name)
FROM table_name
WHERE condition;

MAX:

MAX

username@machine_name:~$ SELECT MAX(column_name)
FROM table_name
WHERE condition;

MIN:

MIN

username@machine_name:~$ SELECT MIN(column_name)
FROM table_name
WHERE condition;

5. Grouping Data

GROUP BY:

GROUP BY

username@machine_name:~$ SELECT column1, COUNT(column2)
FROM table_name
GROUP BY column1;

HAVING:

HAVING

username@machine_name:~$ SELECT column1, COUNT(column2)
FROM table_name
GROUP BY column1
HAVING COUNT(column2) > value;

6. Subqueries

Subquery in SELECT:

Subquery in SELECT

username@machine_name:~$ SELECT column1,
(SELECT MAX(column2) FROM table2 WHERE table2.column1 = table1.column1) AS max_value
FROM table1;

Subquery in WHERE:

Subquery in WHERE

username@machine_name:~$ SELECT column1, column2
FROM table_name
WHERE column1 IN (SELECT column1 FROM table2 WHERE condition);

7. Data Definition Language (DDL)

CREATE TABLE:

CREATE TABLE

username@machine_name:~$ CREATE TABLE table_name (
    column1 datatype constraint,
    column2 datatype constraint,
    ...
);

ALTER TABLE:

ALTER TABLE

username@machine_name:~$ ALTER TABLE table_name
ADD column_name datatype;

ALTER TABLE

username@machine_name:~$ ALTER TABLE table_name
DROP COLUMN column_name;

DROP TABLE:

DROP TABLE

username@machine_name:~$ DROP TABLE table_name;

8. Indexes

CREATE INDEX:

CREATE INDEX

username@machine_name:~$ CREATE INDEX index_name
ON table_name (column1, column2);

DROP INDEX:

DROP INDEX

username@machine_name:~$ DROP INDEX index_name;

9. Transactions

BEGIN TRANSACTION:

BEGIN TRANSACTION

username@machine_name:~$ BEGIN;

COMMIT:

COMMIT

username@machine_name:~$ COMMIT;

ROLLBACK:

ROLLBACK

username@machine_name:~$ ROLLBACK;

10. Views

CREATE VIEW:

CREATE VIEW

username@machine_name:~$ CREATE VIEW view_name AS
SELECT column1, column2
FROM table_name
WHERE condition;

DROP VIEW:

DROP VIEW

username@machine_name:~$ DROP VIEW view_name;