↓ Skip to main content
Roman Empire
Roman Empire
SQL Quick Reference
  1. Posts/

SQL Quick Reference

·1020 words·5 mins·
Tech Sql
Roman
Author
Roman
Photographer with MSci in Computer Science and a Home Lab obsession
Table of Contents

Selection Queries
#

Basic SELECT
# 

-- Select all columns
SELECT * FROM table_name;

-- Select specific columns
SELECT column1, column2 
FROM table_name;

-- Select with alias
SELECT column_name AS alias_name 
FROM table_name;

WHERE Clause
# 

-- Basic conditions
SELECT * FROM table_name 
WHERE condition;

-- Multiple conditions
SELECT * FROM table_name 
WHERE condition1 AND condition2;

SELECT * FROM table_name 
WHERE condition1 OR condition2;

-- Pattern matching
SELECT * FROM table_name 
WHERE column_name LIKE 'pattern%';

-- Range conditions
SELECT * FROM table_name 
WHERE column_name 
    BETWEEN value1 AND value2;

-- List conditions
SELECT * FROM table_name 
WHERE column_name IN (
    value1, value2, value3
);

ORDER BY
# 

-- Ascending order (default)
SELECT * FROM table_name 
ORDER BY column_name ASC;

-- Descending order
SELECT * FROM table_name 
ORDER BY column_name DESC;

-- Multiple columns
SELECT * FROM table_name 
ORDER BY column1 ASC, 
         column2 DESC;

DISTINCT
# 

-- Unique values
SELECT DISTINCT column_name 
FROM table_name;

-- Unique combinations
SELECT DISTINCT column1, column2 
FROM table_name;

LIMIT
# 

-- Limit results
SELECT * FROM table_name 
LIMIT 10;

-- With offset
SELECT * FROM table_name 
LIMIT 10 OFFSET 5;

Data Definition Language (DDL)
#

Database Operations
# 

-- Create database
CREATE DATABASE database_name;

-- Drop database
DROP DATABASE database_name;

Table Operations
# 

-- Create table
CREATE TABLE table_name (
    column1 datatype PRIMARY KEY,
    column2 datatype NOT NULL,
    column3 datatype DEFAULT 'default_value',
    FOREIGN KEY (column2) 
        REFERENCES other_table(column_name)
);

-- Alter table
ALTER TABLE table_name 
    ADD COLUMN new_column datatype;

ALTER TABLE table_name 
    DROP COLUMN column_name;

ALTER TABLE table_name 
    MODIFY COLUMN column_name new_datatype;

-- Drop table
DROP TABLE table_name;

-- Truncate table (remove all data, but not structure)
TRUNCATE TABLE table_name;

Data Manipulation Language (DML)
#

INSERT
# 

-- Insert all columns
INSERT INTO table_name 
VALUES (value1, value2, value3);

-- Insert specific columns
INSERT INTO table_name (column1, column2) 
VALUES (value1, value2);

-- Insert multiple rows
INSERT INTO table_name (column1, column2) VALUES 
    (value1, value2),
    (value3, value4),
    (value5, value6);

-- Insert from another table
INSERT INTO table_name (column1, column2)
SELECT column1, column2 
FROM other_table 
WHERE condition;

UPDATE
# 

-- Update specific rows
UPDATE table_name 
SET column1 = value1, 
    column2 = value2
WHERE condition;

-- Update all rows
UPDATE table_name 
SET column_name = new_value;

-- Update with subquery
UPDATE table_name 
SET column_name = (
    SELECT value 
    FROM other_table 
    WHERE condition
)
WHERE condition;

DELETE
# 

-- Delete specific rows
DELETE FROM table_name 
WHERE condition;

-- Delete all rows
DELETE FROM table_name;

Aggregate Functions
#

Basic Aggregates
# 

-- Count records
SELECT COUNT(*) FROM table_name;
SELECT COUNT(column_name) 
FROM table_name; 
-- Excludes NULL values

-- Sum values
SELECT SUM(column_name) 
FROM table_name;

-- Average values
SELECT AVG(column_name) 
FROM table_name;

-- Minimum and maximum
SELECT MIN(column_name) 
FROM table_name;
SELECT MAX(column_name) 
FROM table_name;

-- Round numbers, to 2 decimal places
SELECT ROUND(AVG(column_name), 2) 
FROM table_name;

GROUP BY
# 

-- Group by single column
SELECT column_name, COUNT(*) 
FROM table_name 
GROUP BY column_name;

-- Group by multiple columns
SELECT column1, column2, SUM(column3)
FROM table_name
GROUP BY column1, column2;

-- With WHERE clause
SELECT column_name, AVG(value_column)
FROM table_name
WHERE condition
GROUP BY column_name;

HAVING
# 

-- Filter grouped results
SELECT column_name, COUNT(*)
FROM table_name
GROUP BY column_name
HAVING COUNT(*) > 5;

-- Complex HAVING conditions
SELECT category, AVG(price)
FROM products
GROUP BY category
HAVING AVG(price) > 100 
   AND COUNT(*) >= 10;

String Functions
#

Case Conversion
# 

-- Convert to uppercase
SELECT UPPER(column_name) 
FROM table_name;

-- Convert to lowercase
SELECT LOWER(column_name) 
FROM table_name;

String Manipulation
# 

-- Get string length
SELECT LENGTH(column_name) 
FROM table_name;

-- Extract substring
SELECT SUBSTR(column_name, 
              start_position, 
              length) 
FROM table_name;

-- Concatenate strings
SELECT CONCAT(column1, ' ', column2) 
    AS full_name 
FROM table_name;

-- Replace text
SELECT REPLACE(column_name, 'old_text', 'new_text') 
FROM table_name;

Conditional Queries
#

CASE Statements
# 

-- Simple CASE
SELECT column_name,
    CASE column_name
        WHEN 'condition' THEN 'result1'
        WHEN 'condition' THEN 'result2'
        ELSE 'default_result'
    END AS new_column
FROM table_name;

NULL Handling
# 

-- COALESCE (return first non-null value)
SELECT COALESCE(column1, column2, 'default_value') 
FROM table_name;

Joins
#

INNER JOIN
# 

-- Basic inner join
SELECT a.column1, b.column2
FROM table1 a
INNER JOIN table2 b 
    ON a.id = b.table1_id;

LEFT JOIN
# 

-- Include all records from left table
SELECT a.column1, b.column2
FROM table1 a
LEFT JOIN table2 b 
    ON a.id = b.table1_id;

RIGHT JOIN
# 

-- Include all records from right table
SELECT a.column1, b.column2
FROM table1 a
RIGHT JOIN table2 b 
    ON a.id = b.table1_id;

FULL OUTER JOIN
# 

-- Include all records from both tables
SELECT a.column1, b.column2
FROM table1 a
FULL OUTER JOIN table2 b 
    ON a.id = b.table1_id;

Multiple Joins
# 

-- Chain multiple joins
SELECT o.order_id, 
       c.customer_name, 
       p.product_name
FROM orders o
JOIN customers c 
    ON o.customer_id = c.id
JOIN order_details od 
    ON o.order_id = od.order_id
JOIN products p 
    ON od.product_id = p.id;

Subqueries
#

Subquery in SELECT
# 

SELECT column1,
       (SELECT AVG(price) 
        FROM products) AS avg_price
FROM table_name;

Subquery in FROM
# 

SELECT category, avg_price
FROM (
    SELECT category, 
           AVG(price) AS avg_price
    FROM products
    GROUP BY category
) AS category_averages
WHERE avg_price > 100;

Subquery in WHERE
# 

-- Single value subquery
SELECT * 
FROM products
WHERE price > (
    SELECT AVG(price) 
    FROM products
);

EXISTS
# 

-- Check for existence
SELECT * 
FROM customers c
WHERE EXISTS (
    SELECT 1 
    FROM orders o
    WHERE o.customer_id = c.id
      AND o.order_date >= '2023-01-01'
);

Set Operations
#

UNION
# 

-- Combine results and remove duplicates
SELECT column1, column2 
FROM table1

UNION

SELECT column1, column2 
FROM table2;

UNION ALL
# 

-- Combine results and keep duplicates
SELECT column1, column2 
FROM table1

UNION ALL

SELECT column1, column2 
FROM table2;

INTERSECT
# 

-- Common rows between queries
SELECT column1, column2 
FROM table1

INTERSECT

SELECT column1, column2 
FROM table2;

EXCEPT/MINUS
# 

-- Rows in first query but not in second
SELECT column1, column2 
FROM table1

EXCEPT

SELECT column1, column2 
FROM table2;

Common Table Expressions (CTEs)
#

Temporary named result set that exists only during query execution.

Syntax
# 

WITH cte_name AS (
    SELECT columns
    FROM table
    WHERE condition
)
SELECT * FROM cte_name;

Example
# 

WITH AvgSalary AS (
    SELECT Department, 
           AVG(Salary) AS Avg
    FROM Employees
    GROUP BY Department
)
SELECT * FROM AvgSalary;

Why Use?
#

  • Simplifies complex queries
  • Reusable in same query
  • More readable than subqueries