{"id":9807,"date":"2026-05-08T11:57:19","date_gmt":"2026-05-08T11:57:19","guid":{"rendered":"http:\/\/www.examcollection.com\/blog\/?p=9807"},"modified":"2026-11-04T17:06:57","modified_gmt":"2026-11-04T17:06:57","slug":"30-frequently-used-sql-queries-for-beginners","status":"publish","type":"post","link":"https:\/\/www.examcollection.com\/blog\/30-frequently-used-sql-queries-for-beginners\/","title":{"rendered":"30 Frequently Used SQL Queries for Beginners"},"content":{"rendered":"

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.<\/p>\n

One sample database for every example<\/h2>\n

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\u201330. All addresses use the reserved example.test<\/code> domain, and no real customer records are involved.<\/p>\n

CREATE TABLE customers (id INTEGER PRIMARY KEY, full_name TEXT NOT NULL, city TEXT, tier TEXT, email TEXT);\nCREATE 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);\nINSERT INTO customers VALUES\n(1,'Ada Stone','New York','Gold','ada@example.test'),\n(2,'Ben Reed','Austin','Silver','ben@example.test'),\n(3,'Cleo Li','New York','Gold','cleo@example.test'),\n(4,'Dario Omar',NULL,'Bronze','dario@example.test'),\n(5,'Erika Zhou','San Diego','Silver','erika@example.test');\nINSERT INTO orders VALUES\n(101,1,120.0,'2026-03-05','paid'),\n(102,1,40.0,'2026-03-06','paid'),\n(103,2,99.0,'2026-04-01','paid'),\n(104,3,205.0,'2026-04-15','paid'),\n(105,3,0.0,'2026-04-19','refunded');<\/code><\/pre>\n
The customers.id<\/code> column uniquely identifies each customer, and orders.customer_id<\/code> 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\u201330, use a transaction you roll back<\/strong> so you can rerun this guide without changing your practice database.<\/p>\n
Basic retrieval<\/h2>\n
1. Retrieve named columns<\/h3>\n
Choose named fields so downstream readers know the output contract.<\/p>\n
SELECT full_name, city FROM customers;<\/code><\/pre>\n
2. Inspect a small sample<\/h3>\n
The star is useful for a quick inspection but avoid it in durable production queries.<\/p>\n
SELECT * FROM customers LIMIT 3;<\/code><\/pre>\n
3. Filter by equality<\/h3>\n
WHERE reduces results to matching records.<\/p>\n
SELECT full_name FROM customers WHERE city = 'New York';<\/code><\/pre>\n
4. Exclude a category<\/h3>\n
The <> operator means not equal; NULL needs its own treatment.<\/p>\n
SELECT full_name FROM customers WHERE tier <> 'Bronze';<\/code><\/pre>\n
5. Filter against a set<\/h3>\n
IN is clearer than repeating many OR predicates.<\/p>\n
SELECT full_name FROM customers WHERE city IN ('New York', 'Austin');<\/code><\/pre>\n
6. Select a numeric range<\/h3>\n
BETWEEN includes both endpoints.<\/p>\n
SELECT id, total FROM orders WHERE total BETWEEN 50 AND 200;<\/code><\/pre>\n
7. Match a prefix<\/h3>\n
LIKE searches according to database collation and pattern rules.<\/p>\n
SELECT full_name FROM customers WHERE full_name LIKE 'A%';<\/code><\/pre>\n
8. Find a missing value<\/h3>\n
Use IS NULL; ordinary equality with NULL does not give a true match.<\/p>\n
SELECT full_name FROM customers WHERE city IS NULL;<\/code><\/pre>\n
Sorting and summarizing<\/h2>\n
9. Sort a result<\/h3>\n
ORDER BY makes output ordering explicit rather than accidental.<\/p>\n
SELECT full_name, city FROM customers ORDER BY full_name ASC;<\/code><\/pre>\n
10. Return two largest orders<\/h3>\n
Sort before limiting so the chosen rows represent the intended ranking.<\/p>\n
SELECT id, total FROM orders ORDER BY total DESC LIMIT 2;<\/code><\/pre>\n
11. Identify distinct cities<\/h3>\n
DISTINCT removes duplicate output rows; it does not validate data accuracy.<\/p>\n
SELECT DISTINCT city FROM customers WHERE city IS NOT NULL;<\/code><\/pre>\n
12. Count customers<\/h3>\n
COUNT(*) includes rows even when individual fields contain NULL.<\/p>\n
SELECT COUNT(*) AS customer_count FROM customers;<\/code><\/pre>\n
13. Total only paid orders<\/h3>\n
A business aggregate must state which order statuses are included.<\/p>\n
SELECT SUM(total) AS paid_total FROM orders WHERE status = 'paid';<\/code><\/pre>\n
14. Average order amount<\/h3>\n
AVG ignores NULL values; define whether canceled\/refunded orders belong in the metric.<\/p>\n
SELECT ROUND(AVG(total), 2) AS average_order FROM orders;<\/code><\/pre>\n
15. Find minimum and maximum<\/h3>\n
Range inspection is a starting point for detecting outliers.<\/p>\n
SELECT MIN(total) AS smallest, MAX(total) AS largest FROM orders;<\/code><\/pre>\n
Grouping and relationships<\/h2>\n
16. Count by status<\/h3>\n
GROUP BY produces a summary for each status.<\/p>\n
SELECT status, COUNT(*) AS n FROM orders GROUP BY status;<\/code><\/pre>\n
17. Filter groups after aggregation<\/h3>\n
HAVING filters aggregated groups, unlike WHERE, which filters source rows.<\/p>\n
SELECT customer_id, SUM(total) AS spend FROM orders GROUP BY customer_id HAVING SUM(total) > 100;<\/code><\/pre>\n
18. Join customers to orders<\/h3>\n
An inner join includes only matching customer\/order pairs.<\/p>\n
SELECT c.full_name, o.total FROM customers AS c INNER JOIN orders AS o ON o.customer_id = c.id;<\/code><\/pre>\n
19. Include customers with no orders<\/h3>\n
The left join retains customers without matching orders and yields NULL order fields.<\/p>\n
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;<\/code><\/pre>\n
20. Substitute a missing label<\/h3>\n
COALESCE handles presentation of missing data without changing the source.<\/p>\n
SELECT full_name, COALESCE(city, 'Unknown') AS city_label FROM customers;<\/code><\/pre>\n
21. Classify order size<\/h3>\n
CASE makes an explicit conditional output; threshold choice is an illustration.<\/p>\n
SELECT id, CASE WHEN total >= 100 THEN 'large' ELSE 'small' END AS basket FROM orders;<\/code><\/pre>\n
22. Summarize by month<\/h3>\n
Here ordered_on contains YYYY-MM-DD text; production systems need explicit timestamp\/time-zone rules.<\/p>\n
SELECT substr(ordered_on, 1, 7) AS month, COUNT(*) AS n FROM orders GROUP BY substr(ordered_on, 1, 7);<\/code><\/pre>\n
Nested queries and reuse<\/h2>\n
23. Filter with a subquery<\/h3>\n
An IN subquery selects customers meeting an order condition.<\/p>\n
SELECT full_name FROM customers WHERE id IN (SELECT customer_id FROM orders WHERE total >= 100);<\/code><\/pre>\n
24. Check whether a relationship exists<\/h3>\n
EXISTS tests for at least one related row rather than counting all of them.<\/p>\n
SELECT full_name FROM customers AS c WHERE EXISTS (SELECT 1 FROM orders AS o WHERE o.customer_id = c.id);<\/code><\/pre>\n
25. Use a common table expression<\/h3>\n
A CTE names an intermediate query and can make a multi-step report easier to audit.<\/p>\n
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;<\/code><\/pre>\n
26. Combine compatible result sets<\/h3>\n
UNION removes duplicates. Both queries must return compatible column counts and types.<\/p>\n
SELECT city AS label FROM customers WHERE city IS NOT NULL UNION SELECT status AS label FROM orders;<\/code><\/pre>\n
Transaction safety:<\/strong> before the remaining four examples, start a transaction using BEGIN;<\/code>. Run one statement, inspect the result, then use ROLLBACK;<\/code>; 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.<\/p>\n
Changes in a disposable transaction<\/h2>\n
27. Insert one test customer<\/h3>\n
Use an explicit column list; do not insert real customer data in a practice exercise.<\/p>\n
INSERT INTO customers (id, full_name, city, tier, email) VALUES (6, 'Fae Green', 'Boston', 'Bronze', 'fae@example.test');<\/code><\/pre>\n
28. Update one specific row<\/h3>\n
Inspect the intended row before an UPDATE; missing WHERE can modify every row.<\/p>\n
UPDATE customers SET tier = 'Gold' WHERE id = 2;<\/code><\/pre>\n
29. Delete one test order<\/h3>\n
Preview the WHERE predicate first. In a real system, follow retention and recovery policies.<\/p>\n
DELETE FROM orders WHERE id = 105;<\/code><\/pre>\n
30. Create a lookup index<\/h3>\n
An index can improve some lookups but adds storage and write overhead; validate plans and workload.<\/p>\n
CREATE INDEX IF NOT EXISTS idx_orders_customer_date ON orders(customer_id, ordered_on);<\/code><\/pre>\n
What to verify before using these queries at work<\/h2>\n
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.<\/p>\n
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.<\/p>\n
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.<\/p>\n","protected":false},"excerpt":{"rendered":"
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.\u2026 Read More »<\/a><\/span><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2354],"tags":[],"class_list":["post-9807","post","type-post","status-publish","format-standard","hentry","category-others"],"aioseo_notices":[],"aioseo_head":"\n\t\t\n\t\n\t\n\t\n\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t