SQL Queries Every Software Tester Should Know (2026)

Last updated: June 27, 2026 · 7 min read

Every QA engineer needs SQL in 2026. This guide covers the 30+ queries you'll use daily for test data setup, verification, and debugging. Pair it with our API Testing Tutorial and Postman API Testing guide for full backend coverage.

• Verify test outcomes — was the order actually created in the database?
• Set up test data — seed the DB before tests run
• Debug failing tests — figure out why the API returned the wrong data
• Investigate production issues — query logs and metrics

1. SELECT — Read data

SELECT id, name, email FROM users WHERE id = 123;

2. SELECT with filtering

SELECT * FROM orders WHERE status = 'pending' AND created_at > '2026-06-01';

3. SELECT with sorting

SELECT * FROM users ORDER BY created_at DESC LIMIT 10;

4. COUNT — Count rows

SELECT COUNT(*) FROM users WHERE role = 'admin';

5. SUM / AVG / MIN / MAX

SELECT 
    SUM(amount) as total_revenue,
    AVG(amount) as avg_order,
    MIN(amount) as min_order,
    MAX(amount) as max_order
FROM orders
WHERE status = 'completed';

6. GROUP BY — Aggregate by category

SELECT 
    country,
    COUNT(*) as user_count
FROM users
GROUP BY country
ORDER BY user_count DESC;

7. HAVING — Filter aggregates

SELECT 
    country,
    COUNT(*) as user_count
FROM users
GROUP BY country
HAVING COUNT(*) > 100;

8. JOIN — Combine tables

SELECT 
    u.name,
    o.id as order_id,
    o.total
FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE u.email = 'admin@example.com';

9. LEFT JOIN — Include rows with no match

SELECT 
    u.name,
    COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id;

This finds users who have never placed an order (count = 0).

10. INSERT — Create test data

INSERT INTO users (name, email, role, created_at) 
VALUES ('Alice', 'alice@example.com', 'admin', NOW());

11. INSERT multiple rows

INSERT INTO users (name, email, role) VALUES
    ('Alice', 'alice@example.com', 'admin'),
    ('Bob', 'bob@example.com', 'viewer'),
    ('Carol', 'carol@example.com', 'guest');

12. UPDATE — Modify test data

UPDATE users SET role = 'admin' WHERE email = 'alice@example.com';

13. DELETE — Remove test data

DELETE FROM users WHERE email = 'test@example.com';

14. TRUNCATE — Clear a table (faster than DELETE)

TRUNCATE TABLE test_users;

⚠️ Cannot be rolled back in some databases. Use with care.

15. Transaction — Group operations

BEGIN;

INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');
INSERT INTO orders (user_id, total) VALUES (LAST_INSERT_ID(), 100.00);

COMMIT;

If anything fails, use ROLLBACK to undo all changes.

16. ROLLBACK — Undo changes

BEGIN;

UPDATE users SET balance = balance - 100 WHERE id = 1;
UPDATE users SET balance = balance + 100 WHERE id = 2;

-- Oops, something went wrong
ROLLBACK;

17. CREATE TABLE for test data

CREATE TABLE test_users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    role VARCHAR(50) DEFAULT 'viewer',
    created_at TIMESTAMP DEFAULT NOW()
);

18. ALTER TABLE — Add a column

ALTER TABLE users ADD COLUMN last_login TIMESTAMP;

19. DROP TABLE — Delete a table

DROP TABLE test_users;

20. INDEX — Speed up queries

CREATE INDEX idx_users_email ON users(email);

EXPLAIN SELECT * FROM users WHERE email = 'admin@example.com';

Shows whether the query uses an index or scans the full table.

22. DISTINCT — Unique values

SELECT DISTINCT country FROM users;

23. IN — Match multiple values

SELECT * FROM users WHERE role IN ('admin', 'viewer');

24. BETWEEN — Range filter

SELECT * FROM orders 
WHERE created_at BETWEEN '2026-06-01' AND '2026-06-30';

25. LIKE — Pattern matching

SELECT * FROM users WHERE email LIKE '%@example.com';

26. IS NULL — Find missing values

SELECT * FROM users WHERE last_login IS NULL;

27. CASE — Conditional logic

SELECT 
    name,
    CASE 
        WHEN balance > 10000 THEN 'VIP'
        WHEN balance > 1000 THEN 'Regular'
        ELSE 'New'
    END as tier
FROM users;

28. Window functions — Ranked results

SELECT 
    name,
    amount,
    RANK() OVER (PARTITION BY user_id ORDER BY amount DESC) as rank
FROM orders;

29. Common Table Expressions (CTE)

WITH active_users AS (
    SELECT id, name FROM users WHERE last_login > NOW() - INTERVAL '30 days'
)
SELECT COUNT(*) FROM active_users;

30. EXISTS — Check for related rows

SELECT u.name 
FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.user_id = u.id
);

Verify a test outcome

After running a test that creates an order:

SELECT * FROM orders WHERE user_id = 123 ORDER BY created_at DESC LIMIT 1;

Verify the expected fields.

Clean up test data before each test

DELETE FROM orders WHERE user_id = 123;
DELETE FROM users WHERE id = 123;

Find data inconsistencies

-- Find orders without a user (orphaned records)
SELECT * FROM orders o
LEFT JOIN users u ON o.user_id = u.id
WHERE u.id IS NULL;

Generate test data with random values

INSERT INTO users (name, email, role)
SELECT 
    'User_' || i,
    'user_' || i || '@example.com',
    CASE WHEN i % 10 = 0 THEN 'admin' ELSE 'viewer' END
FROM generate_series(1, 100) AS i;

For more on backend testing, see our Manual Testing Guide and SQL Interview Questions.