The SQL syntax you actually use — selects, filters, joins, aggregates, window functions and data changes — each with a copy-paste example. Need a query this page doesn't cover? SQLGenie writes it from plain English for free, no account needed.
Everything starts with SELECT: pick columns (or * for all), name the table, and limit what comes back.
-- All columns, all rows
SELECT * FROM customers;
-- Specific columns, renamed for readability
SELECT first_name AS name,
email,
created_at AS signup_date
FROM customers;
-- No duplicate values
SELECT DISTINCT country FROM customers;
-- Just the first 10 rows
SELECT * FROM orders LIMIT 10;WHERE keeps only the rows you want. Combine conditions with AND/OR, and use IN, BETWEEN and LIKE for sets, ranges and patterns.
SELECT * FROM orders
WHERE total > 100
AND status = 'paid';
SELECT * FROM products
WHERE category IN ('audio', 'video');
SELECT * FROM events
WHERE event_date BETWEEN '2026-01-01' AND '2026-12-31';
-- % matches any characters; _ matches exactly one
SELECT * FROM users
WHERE email LIKE '%@gmail.com';
-- NULL needs IS, not =
SELECT * FROM employees
WHERE manager_id IS NULL;ORDER BY sorts the result; pair it with LIMIT (or FETCH FIRST on SQL Server) to get top-N queries.
-- Newest orders first
SELECT * FROM orders
ORDER BY created_at DESC;
-- Sort by two columns
SELECT * FROM products
ORDER BY category ASC, price DESC;
-- Top 5 best sellers
SELECT product_id, COUNT(*) AS times_sold
FROM order_items
GROUP BY product_id
ORDER BY times_sold DESC
LIMIT 5;INNER JOIN keeps matching rows only; LEFT JOIN keeps every left row; FULL OUTER JOIN keeps both sides. Always join on a key.
-- Matching rows only
SELECT c.name, o.total
FROM customers c
INNER JOIN orders o ON o.customer_id = c.id;
-- Every customer, orders if they exist
SELECT c.name, o.total
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id;
-- Customers with NO orders
SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;
-- Join three tables
SELECT c.name, p.title, oi.quantity
FROM order_items oi
JOIN orders o ON o.id = oi.order_id
JOIN customers c ON c.id = o.customer_id
JOIN products p ON p.id = oi.product_id;COUNT, SUM, AVG, MIN and MAX summarize rows. GROUP BY summarizes per group; HAVING filters the groups afterwards.
SELECT COUNT(*) AS total_orders,
SUM(total) AS revenue,
AVG(total) AS average_order
FROM orders;
-- Revenue per customer
SELECT customer_id, SUM(total) AS revenue
FROM orders
GROUP BY customer_id;
-- Only big spenders (HAVING, not WHERE)
SELECT customer_id, SUM(total) AS revenue
FROM orders
GROUP BY customer_id
HAVING SUM(total) > 1000;CASE is SQL's if-then-else — label rows, build custom sort orders, or count conditionally.
Full guide: SQL CASE statement explained-- Label rows by value
SELECT id, total,
CASE
WHEN total >= 500 THEN 'large'
WHEN total >= 100 THEN 'medium'
ELSE 'small'
END AS order_size
FROM orders;
-- Count conditionally
SELECT COUNT(CASE WHEN status = 'paid' THEN 1 END) AS paid_orders
FROM orders;Window functions compute across related rows without collapsing them — running totals, ranks and row-over-row comparisons.
-- Rank employees by salary within each department
SELECT name, department, salary,
RANK() OVER (
PARTITION BY department
ORDER BY salary DESC
) AS dept_rank
FROM employees;
-- Running total of revenue
SELECT order_date,
SUM(total) OVER (ORDER BY order_date) AS running_total
FROM orders;
-- Compare with the previous row
SELECT sale_date, amount,
amount - LAG(amount) OVER (ORDER BY sale_date) AS change
FROM sales;A common table expression (WITH) names a query so you can build on it — much easier to read than nested subqueries.
WITH big_orders AS (
SELECT customer_id, SUM(total) AS revenue
FROM orders
GROUP BY customer_id
HAVING SUM(total) > 1000
)
SELECT c.name, b.revenue
FROM big_orders b
JOIN customers c ON c.id = b.customer_id
ORDER BY b.revenue DESC;
-- Same idea as a subquery
SELECT name
FROM customers
WHERE id IN (
SELECT customer_id FROM orders WHERE total > 500
);Writing data: INSERT adds rows, UPDATE changes them, DELETE removes them. Always test the WHERE with a SELECT first.
INSERT INTO customers (name, email, country)
VALUES ('Ada Lovelace', 'ada@example.com', 'UK');
UPDATE products
SET price = price * 1.05
WHERE category = 'audio';
-- Preview first: run the SELECT, then the DELETE
SELECT * FROM sessions WHERE expires_at < NOW();
DELETE FROM sessions WHERE expires_at < NOW();The functions you reach for daily. Syntax varies slightly between MySQL, PostgreSQL and SQL Server.
-- Combine and clean text
SELECT UPPER(first_name) || ' ' || UPPER(last_name) AS shouty_name
FROM customers;
SELECT TRIM(email) FROM users;
-- Extract date parts
SELECT EXTRACT(YEAR FROM created_at) AS signup_year
FROM users;
-- Replace NULL with a fallback
SELECT name, COALESCE(phone, 'no phone on file') AS phone
FROM customers;
-- Length and substring
SELECT LENGTH(description), SUBSTRING(description FROM 1 FOR 50)
FROM products;Learn the five building blocks first — SELECT, WHERE, JOIN, GROUP BY and ORDER BY — because most real queries are combinations of those. Then practice on questions you actually have about real data: turning a question into a query (or using a text-to-SQL tool like SQLGenie and reading the result) sticks far better than memorizing syntax tables.
The core syntax — SELECT, WHERE, JOIN, GROUP BY, HAVING, ORDER BY — is standard SQL and works the same everywhere. The differences are in the details: limiting rows (LIMIT vs TOP vs FETCH FIRST), string concatenation, and date functions. SQLGenie generates dialect-correct SQL for your specific database if you pick it from the dropdown.
WHERE filters individual rows before grouping and cannot use aggregates like SUM or COUNT. HAVING filters groups after GROUP BY and can. Execution order: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY.
INNER JOIN returns only rows that match in both tables. LEFT JOIN returns every row from the left table, filling the right side with NULL when there is no match — so it is the right choice when missing matches should still appear.
Describe what you want in plain English, paste your table structure, and SQLGenie writes the SQL for your exact database — with an explanation so you learn the syntax as you go.