30 Frequently Used SQL Queries for Beginners

Thirty SQL examples are useful only if they actually run and if beginners can see what data each query reads or changes. This guide uses one small, entirely fictional SQLite database throughout. The syntax was executed locally against SQLite; other SQL engines use different conventions for row limits, dates, outer joins, data types and transactions. Do not run the INSERT, UPDATE, DELETE or CREATE INDEX statements against production tables while learning.

One sample database for every example

The sample has five fictional customers and five orders. Use a disposable SQLite database or an in-memory connection. Run the following setup once before examples 1–30. All addresses use the reserved example.test domain, and no real customer records are involved.

CREATE TABLE customers (id INTEGER PRIMARY KEY, full_name TEXT NOT NULL, city TEXT, tier TEXT, email TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER NOT NULL REFERENCES customers(id), total REAL NOT NULL, ordered_on TEXT NOT NULL, status TEXT NOT NULL);
INSERT INTO customers VALUES
(1,'Ada Stone','New York','Gold','ada@example.test'),
(2,'Ben Reed','Austin','Silver','ben@example.test'),
(3,'Cleo Li','New York','Gold','cleo@example.test'),
(4,'Dario Omar',NULL,'Bronze','dario@example.test'),
(5,'Erika Zhou','San Diego','Silver','erika@example.test');
INSERT INTO orders VALUES
(101,1,120.0,'2026-03-05','paid'),
(102,1,40.0,'2026-03-06','paid'),
(103,2,99.0,'2026-04-01','paid'),
(104,3,205.0,'2026-04-15','paid'),
(105,3,0.0,'2026-04-19','refunded');

The customers.id column uniquely identifies each customer, and orders.customer_id references it. One customer deliberately has no city, another has no order, and one order is marked refunded; these test cases make NULL, joins, and filters meaningful. For queries 27–30, use a transaction you roll back so you can rerun this guide without changing your practice database.

Basic retrieval

1. Retrieve named columns

Choose named fields so downstream readers know the output contract.

SELECT full_name, city FROM customers;

2. Inspect a small sample

The star is useful for a quick inspection but avoid it in durable production queries.

SELECT * FROM customers LIMIT 3;

3. Filter by equality

WHERE reduces results to matching records.

SELECT full_name FROM customers WHERE city = 'New York';

4. Exclude a category

The <> operator means not equal; NULL needs its own treatment.

SELECT full_name FROM customers WHERE tier <> 'Bronze';

5. Filter against a set

IN is clearer than repeating many OR predicates.

SELECT full_name FROM customers WHERE city IN ('New York', 'Austin');

6. Select a numeric range

BETWEEN includes both endpoints.

SELECT id, total FROM orders WHERE total BETWEEN 50 AND 200;

7. Match a prefix

LIKE searches according to database collation and pattern rules.

SELECT full_name FROM customers WHERE full_name LIKE 'A%';

8. Find a missing value

Use IS NULL; ordinary equality with NULL does not give a true match.

SELECT full_name FROM customers WHERE city IS NULL;

Sorting and summarizing

9. Sort a result

ORDER BY makes output ordering explicit rather than accidental.

SELECT full_name, city FROM customers ORDER BY full_name ASC;

10. Return two largest orders

Sort before limiting so the chosen rows represent the intended ranking.

SELECT id, total FROM orders ORDER BY total DESC LIMIT 2;

11. Identify distinct cities

DISTINCT removes duplicate output rows; it does not validate data accuracy.

SELECT DISTINCT city FROM customers WHERE city IS NOT NULL;

12. Count customers

COUNT(*) includes rows even when individual fields contain NULL.

SELECT COUNT(*) AS customer_count FROM customers;

13. Total only paid orders

A business aggregate must state which order statuses are included.

SELECT SUM(total) AS paid_total FROM orders WHERE status = 'paid';

14. Average order amount

AVG ignores NULL values; define whether canceled/refunded orders belong in the metric.

SELECT ROUND(AVG(total), 2) AS average_order FROM orders;

15. Find minimum and maximum

Range inspection is a starting point for detecting outliers.

SELECT MIN(total) AS smallest, MAX(total) AS largest FROM orders;

Grouping and relationships

16. Count by status

GROUP BY produces a summary for each status.

SELECT status, COUNT(*) AS n FROM orders GROUP BY status;

17. Filter groups after aggregation

HAVING filters aggregated groups, unlike WHERE, which filters source rows.

SELECT customer_id, SUM(total) AS spend FROM orders GROUP BY customer_id HAVING SUM(total) > 100;

18. Join customers to orders

An inner join includes only matching customer/order pairs.

SELECT c.full_name, o.total FROM customers AS c INNER JOIN orders AS o ON o.customer_id = c.id;

19. Include customers with no orders

The left join retains customers without matching orders and yields NULL order fields.

SELECT c.full_name, o.id AS order_id FROM customers AS c LEFT JOIN orders AS o ON o.customer_id = c.id ORDER BY c.id, o.id;

20. Substitute a missing label

COALESCE handles presentation of missing data without changing the source.

SELECT full_name, COALESCE(city, 'Unknown') AS city_label FROM customers;

21. Classify order size

CASE makes an explicit conditional output; threshold choice is an illustration.

SELECT id, CASE WHEN total >= 100 THEN 'large' ELSE 'small' END AS basket FROM orders;

22. Summarize by month

Here ordered_on contains YYYY-MM-DD text; production systems need explicit timestamp/time-zone rules.

SELECT substr(ordered_on, 1, 7) AS month, COUNT(*) AS n FROM orders GROUP BY substr(ordered_on, 1, 7);

Nested queries and reuse

23. Filter with a subquery

An IN subquery selects customers meeting an order condition.

SELECT full_name FROM customers WHERE id IN (SELECT customer_id FROM orders WHERE total >= 100);

24. Check whether a relationship exists

EXISTS tests for at least one related row rather than counting all of them.

SELECT full_name FROM customers AS c WHERE EXISTS (SELECT 1 FROM orders AS o WHERE o.customer_id = c.id);

25. Use a common table expression

A CTE names an intermediate query and can make a multi-step report easier to audit.

WITH spend AS (SELECT customer_id, SUM(total) AS amount FROM orders GROUP BY customer_id) SELECT customer_id, amount FROM spend WHERE amount >= 100;

26. Combine compatible result sets

UNION removes duplicates. Both queries must return compatible column counts and types.

SELECT city AS label FROM customers WHERE city IS NOT NULL UNION SELECT status AS label FROM orders;

Transaction safety: before the remaining four examples, start a transaction using BEGIN;. Run one statement, inspect the result, then use ROLLBACK;; start another transaction for the next statement. This prevents a practice session from quietly altering shared data. In the local QA fixture each of the four statements was run inside a separate savepoint and then rolled back.

Changes in a disposable transaction

27. Insert one test customer

Use an explicit column list; do not insert real customer data in a practice exercise.

INSERT INTO customers (id, full_name, city, tier, email) VALUES (6, 'Fae Green', 'Boston', 'Bronze', 'fae@example.test');

28. Update one specific row

Inspect the intended row before an UPDATE; missing WHERE can modify every row.

UPDATE customers SET tier = 'Gold' WHERE id = 2;

29. Delete one test order

Preview the WHERE predicate first. In a real system, follow retention and recovery policies.

DELETE FROM orders WHERE id = 105;

30. Create a lookup index

An index can improve some lookups but adds storage and write overhead; validate plans and workload.

CREATE INDEX IF NOT EXISTS idx_orders_customer_date ON orders(customer_id, ordered_on);

What to verify before using these queries at work

Use explicit column names and reproducible test data. A successful query is not necessarily a correct business report: totals depend on refund rules, customer identities, date interpretation and who is authorized to see the records. Parameterize values from users instead of constructing SQL with string concatenation. For writes, review which rows a predicate affects, establish backup and recovery expectations, and record the change owner.

The core SQLite SELECT documentation explains retrieval, grouping and joins. The transaction reference describes BEGIN, COMMIT and ROLLBACK. These are technical references, not a claim that the same exact SQL runs unchanged on every database platform.

A good learning test is to add a sixth fictional order or change one tier and predict how the aggregate and join results will differ before running them. Understanding those consequences is more valuable than memorizing a long list of commands.

• Category: Others