Cheatsheet: SQL

This cheatsheet is built from my notes collected over time. Can apply to PostgreSQL, MySQL, or SQLite, these commands are for querying, filtering, modifying, and joining data.

SELECT Statements

-- Select everything from a table
SELECT * FROM Country;

-- Rename columns using AS and order alphabetically
SELECT Name, Lifeexpectancy AS "Life Expectancy" FROM Country ORDER BY Name;

-- Conditional query with LIMIT and OFFSET
SELECT Name, Continent, Region 
FROM Country 
WHERE Continent = 'Europe' 
ORDER BY Name 
LIMIT 5 OFFSET 15;

-- Count how many countries have population over 1 million
SELECT COUNT(*) FROM Country WHERE Population > 1000000;

-- Remove duplicates (DISTINCT)
SELECT DISTINCT Continent FROM Country;

Filtering with WHERE

-- Filter by multiple conditions
SELECT Name, Continent, Population 
FROM Country 
WHERE Population < 10000 AND Continent = 'Oceania' 
ORDER BY Population DESC;

-- Use LIKE and wildcards
SELECT Name FROM Country WHERE Name LIKE '%island%' ORDER BY Name;

-- Second letter is A
SELECT Name FROM Country WHERE Name LIKE '_a%' ORDER BY Name;

INSERT Statements

-- Insert data into specified columns
INSERT INTO customer (name, city, state) 
VALUES ('Jimi', 'Renton', 'WA');

-- Insert values from another table
INSERT INTO test (a, b, c) 
SELECT id, name, description FROM item;

UPDATE and DELETE

-- Update specific fields by ID
UPDATE customer 
SET address = '123 music ave', zip = '98056' 
WHERE id = 5;

-- Delete a specific record
DELETE FROM customer WHERE id = 5;

Table Structure

-- Drop table if it exists
DROP TABLE IF EXISTS test;

-- Add a new column with a default value
ALTER TABLE test ADD columna TEXT DEFAULT 'panda';

JOIN Statements

-- Inner Join
SELECT customers.name, orders.amount 
FROM customers 
INNER JOIN orders ON customers.id = orders.customer_id;

-- Left Join
SELECT customers.name, orders.amount 
FROM customers 
LEFT JOIN orders ON customers.id = orders.customer_id;

-- Right Join (may not be supported in all DBs)
SELECT customers.name, orders.amount 
FROM customers 
RIGHT JOIN orders ON customers.id = orders.customer_id;

-- Full Outer Join (PostgreSQL)
SELECT customers.name, orders.amount 
FROM customers 
FULL OUTER JOIN orders ON customers.id = orders.customer_id;

Aggregation & Grouping

-- Group data and get counts
SELECT Continent, COUNT(*) as total_countries 
FROM Country 
GROUP BY Continent;

-- Average population by region
SELECT Region, AVG(Population) as avg_pop 
FROM Country 
GROUP BY Region;

Other Useful Commands

-- Find max/min values
SELECT MAX(Population), MIN(LifeExpectancy) FROM Country;

-- Order by multiple columns
SELECT Name, Continent, Population 
FROM Country 
ORDER BY Continent, Population DESC;

-- Limit number of results
SELECT * FROM Country LIMIT 10;

This cheatsheet is meant to be a quick reference.

— Franco