Cheatsheet: SQL

Basic operations

SELECT: Used to retrieve data from a database

SELECT * FROM customers;

INSERT: Used to insert new data into a database

INSERT INTO customers (name, email)
VALUES ('John Doe', 'johndoe@example.com');

UPDATE: Used to update existing data in a database

UPDATE customers
SET email = 'johndoe@gmail.com'
WHERE name = 'John Doe';

DELETE: Used to delete data from a database

DELETE FROM customers
WHERE name = 'John Doe';

WHERE: Used to filter results based on specific conditions

SELECT * FROM customers
WHERE name = 'John Doe';

ORDER BY: Used to sort results in ascending or descending order

SELECT * FROM customers
ORDER BY name DESC;

DELETE: Used to delete data from a database,

DELETE FROM customers
WHERE name = 'John Doe';

Data Definition Language

CREATE TABLE: Used to create a new table in a database.

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(50) NOT NULL
);

ALTER TABLE: Used to modify the structure of an existing table

ALTER TABLE customers
ADD COLUMN address VARCHAR(100);

DROP TABLE: Used to delete a table from a database

DROP TABLE customers;

Joins

INNER JOIN: Returns only the rows where there is a match in both tables

SELECT customers.name, orders.order_number
FROM customers
INNER JOIN orders
ON customers.id = orders.customer_id;

LEFT JOIN (or LEFT OUTER JOIN): Returns all the rows from the left table and the matching rows from the right table. If there is no match, NULL values will be returned for the columns from the right table.

SELECT customers.name, orders.order_number
FROM customers
LEFT JOIN orders
ON customers.id = orders.customer_id;

RIGHT JOIN (or RIGHT OUTER JOIN): Returns all the rows from the right table and the matching rows from the left table. If there is no match, NULL values will be returned for the columns from the left table.

SELECT customers.name, orders.order_number
FROM customers
RIGHT JOIN orders
ON customers.id = orders.customer_id;

FULL OUTER JOIN: Returns all the rows from both tables, including the matching and non-matching rows. If there is no match, NULL values will be returned for the missing columns.

SELECT customers.name, orders.order_number
FROM customers
FULL OUTER JOIN orders
ON customers.id = orders.customer_id;