CommandsIntermediate7-9 min reference

SQL for Testers

The SQL you'll actually write while testing — verifying data after a migration, checking for orphan rows, seeding fixtures, and tearing them down. Examples follow PostgreSQL syntax with notes where MySQL or SQL Server differ.

Basic Queries

Select all and select specific columns

SELECT * FROM users;
SELECT name, email FROM users WHERE active = true;

Distinct values

SELECT DISTINCT category FROM products;
SELECT DISTINCT status, environment FROM deployments;

Multiple conditions

SELECT *
FROM orders
WHERE amount > 100
  AND status = 'completed';
 
SELECT *
FROM orders
WHERE status = 'pending'
   OR status = 'failed';

Pattern matching with `LIKE`

SELECT * FROM users WHERE name LIKE '%john%';     -- contains 'john'
SELECT * FROM users WHERE name LIKE 'John%';      -- starts with 'John'
SELECT * FROM users WHERE email LIKE '%@example.com';
SELECT * FROM users WHERE phone LIKE '___-____';  -- exactly 7 chars
SELECT * FROM users WHERE email ILIKE '%@EXAMPLE.com';  -- PostgreSQL: case-insensitive

Null checks

SELECT * FROM users WHERE email IS NULL;
SELECT * FROM users WHERE email IS NOT NULL;
 
-- ✗ This NEVER matches — = NULL is always unknown
SELECT * FROM users WHERE email = NULL;

`IN` and `NOT IN`

SELECT * FROM users
WHERE role IN ('admin', 'editor', 'moderator');
 
SELECT * FROM products
WHERE status NOT IN ('archived', 'deleted');

`BETWEEN`

SELECT *
FROM orders
WHERE created_at BETWEEN '2025-01-01' AND '2025-12-31';
 
SELECT *
FROM products
WHERE price BETWEEN 10 AND 99.99;

BETWEEN is inclusive on both ends.

Sorting & Limiting

`ORDER BY`

SELECT * FROM users ORDER BY created_at DESC;
SELECT * FROM users ORDER BY last_name ASC, first_name ASC;
SELECT * FROM products ORDER BY category, price DESC;
 
-- NULLs handling (PostgreSQL)
SELECT * FROM users ORDER BY last_login DESC NULLS LAST;

`LIMIT` and pagination

-- PostgreSQL / MySQL / SQLite
SELECT * FROM users ORDER BY id LIMIT 10;
SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 20;   -- page 3 of 10
 
-- SQL Server
SELECT TOP 10 * FROM users ORDER BY id;
SELECT *
FROM users
ORDER BY id
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;
 
-- Oracle (12c+)
SELECT * FROM users
ORDER BY id
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

Aggregate Functions

Counting

SELECT COUNT(*) FROM users;                          -- includes nulls
SELECT COUNT(email) FROM users;                      -- excludes nulls
SELECT COUNT(DISTINCT country) FROM users;

Math

SELECT SUM(amount), AVG(amount), MIN(amount), MAX(amount)
FROM orders
WHERE status = 'completed';

`GROUP BY`

SELECT status, COUNT(*) AS total
FROM orders
GROUP BY status;
 
SELECT user_id, COUNT(*) AS order_count, SUM(amount) AS total_spent
FROM orders
GROUP BY user_id
ORDER BY total_spent DESC;

`HAVING` (filter after grouping)

SELECT category, COUNT(*) AS tool_count
FROM tools
GROUP BY category
HAVING COUNT(*) > 3;
 
-- WHERE filters before grouping; HAVING filters after.
SELECT user_id, COUNT(*) AS order_count
FROM orders
WHERE status = 'completed'
GROUP BY user_id
HAVING COUNT(*) >= 5
ORDER BY order_count DESC;

Joins

`INNER JOIN` — matching rows in both 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 o.status = 'completed';

`LEFT JOIN` — all left + matching right

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, u.name;

Users with no orders show 0 instead of being filtered out.

`RIGHT JOIN` — all right + matching left

SELECT u.name, o.id
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;

Useful for finding orders whose user_id doesn't match any user (orphans).

`FULL OUTER JOIN` — all rows from both

SELECT u.name, o.id
FROM users u
FULL OUTER JOIN orders o ON u.id = o.user_id;

Not supported in MySQL — emulate with LEFT JOIN UNION RIGHT JOIN.

`CROSS JOIN` — cartesian product

SELECT u.id, p.id
FROM users u
CROSS JOIN products p;
-- 1000 users × 50 products = 50,000 rows

Self join

-- Find users who share a manager
SELECT u1.name AS employee, u2.name AS coworker
FROM employees u1
INNER JOIN employees u2 ON u1.manager_id = u2.manager_id
WHERE u1.id <> u2.id;

Multiple joins

SELECT u.name, o.total, p.name AS product
FROM users u
INNER JOIN orders o      ON u.id = o.user_id
INNER JOIN order_items i ON o.id = i.order_id
INNER JOIN products p    ON i.product_id = p.id
WHERE o.created_at > NOW() - INTERVAL '30 days';

Subqueries

`IN (subquery)`

SELECT name, email
FROM users
WHERE id IN (
  SELECT DISTINCT user_id
  FROM orders
  WHERE total > 1000
);

`EXISTS (subquery)`

Often faster than IN because the database can stop at the first match.

SELECT u.*
FROM users u
WHERE EXISTS (
  SELECT 1 FROM orders o
  WHERE o.user_id = u.id
    AND o.status = 'failed'
);

Scalar subquery (one row, one column)

SELECT
  u.name,
  (SELECT COUNT(*)
     FROM orders
     WHERE user_id = u.id) AS order_count,
  (SELECT MAX(created_at)
     FROM orders
     WHERE user_id = u.id) AS last_order
FROM users u;

`FROM` subquery (derived table)

SELECT category, top_product
FROM (
  SELECT
    p.category,
    p.name AS top_product,
    ROW_NUMBER() OVER (PARTITION BY p.category ORDER BY SUM(o.total) DESC) AS rank
  FROM products p
  INNER JOIN order_items i ON p.id = i.product_id
  INNER JOIN orders o      ON i.order_id = o.id
  GROUP BY p.id, p.category, p.name
) ranked
WHERE rank = 1;

Common Table Expressions (`WITH`)

Cleaner than nested subqueries:

WITH high_value AS (
  SELECT user_id, SUM(total) AS lifetime
  FROM orders
  GROUP BY user_id
  HAVING SUM(total) > 10000
)
SELECT u.name, h.lifetime
FROM users u
INNER JOIN high_value h ON u.id = h.user_id
ORDER BY h.lifetime DESC;

Insert, Update, Delete

`INSERT`

-- Single row
INSERT INTO users (name, email)
VALUES ('John Doe', 'john@test.com');
 
-- Multiple rows
INSERT INTO users (name, email) VALUES
  ('Alice', 'alice@test.com'),
  ('Bob',   'bob@test.com'),
  ('Carol', 'carol@test.com');
 
-- Insert from another table
INSERT INTO archived_users (id, name, email)
SELECT id, name, email FROM users WHERE active = false;
 
-- Return inserted row(s) — PostgreSQL
INSERT INTO users (name, email)
VALUES ('Ada', 'ada@example.com')
RETURNING id, created_at;

`UPDATE`

UPDATE users
SET active = false
WHERE last_login < '2024-01-01';
 
-- Update multiple columns
UPDATE products
SET price = price * 0.9,
    updated_at = NOW()
WHERE category = 'sale';
 
-- Update with subquery
UPDATE users
SET total_orders = (SELECT COUNT(*) FROM orders WHERE user_id = users.id);
 
-- Update from join (PostgreSQL)
UPDATE users u
SET status = 'vip'
FROM orders o
WHERE u.id = o.user_id
  AND o.total > 1000;

`DELETE`

DELETE FROM test_data WHERE environment = 'staging';
 
DELETE FROM users
WHERE created_at < NOW() - INTERVAL '2 years'
  AND last_login IS NULL;
 
-- Delete with join (PostgreSQL)
DELETE FROM orders o
USING users u
WHERE o.user_id = u.id
  AND u.email LIKE '%@test.com';

`TRUNCATE`

Faster than DELETE for emptying a whole table — but it can't be rolled back in some engines and won't fire row-level triggers.

TRUNCATE TABLE test_data;
TRUNCATE TABLE test_data RESTART IDENTITY CASCADE;   -- PostgreSQL: reset sequences + cascade FKs

String Functions

SELECT
  UPPER(name)               AS upper_name,
  LOWER(email)              AS lower_email,
  TRIM(name)                AS trimmed,
  LENGTH(name)              AS name_length,    -- LEN(name) on SQL Server
  CONCAT(first_name, ' ', last_name) AS full_name,
  first_name || ' ' || last_name     AS pg_concat,    -- PostgreSQL/Oracle
  SUBSTRING(email FROM 1 FOR 10)     AS pg_substr,
  SUBSTR(email, 1, 10)               AS sqlite_substr,
  REPLACE(phone, '-', '')            AS digits_only,
  COALESCE(nickname, first_name, 'Anonymous') AS display_name
FROM users;

COALESCE returns the first non-null argument — handy for fallback values when validating data.

Date Functions

-- Current values
SELECT CURRENT_DATE, CURRENT_TIMESTAMP, NOW();
-- SQL Server:        GETDATE(), SYSDATETIME()
-- MySQL:             CURDATE(), NOW()
 
-- Formatting (PostgreSQL)
SELECT TO_CHAR(created_at, 'YYYY-MM-DD HH24:MI') FROM orders;
 
-- Difference between dates
-- PostgreSQL
SELECT AGE(NOW(), created_at) FROM users;
SELECT EXTRACT(DAY FROM NOW() - created_at) FROM users;
 
-- MySQL
SELECT DATEDIFF(NOW(), created_at) FROM users;        -- in days
 
-- SQL Server
SELECT DATEDIFF(day, created_at, GETDATE()) FROM users;
 
-- Adding / subtracting
SELECT NOW() + INTERVAL '7 days';                     -- PostgreSQL
SELECT DATE_ADD(NOW(), INTERVAL 7 DAY);               -- MySQL
SELECT DATEADD(day, 7, GETDATE());                    -- SQL Server
 
-- Extract parts
SELECT EXTRACT(YEAR  FROM created_at) AS year,
       EXTRACT(MONTH FROM created_at) AS month
FROM orders;
 
-- Truncate to a unit (PostgreSQL)
SELECT DATE_TRUNC('month', created_at) AS month_bucket,
       COUNT(*)
FROM orders
GROUP BY month_bucket
ORDER BY month_bucket;

Data Validation Queries for QA

The queries you'll keep in your toolbox.

Find duplicates

SELECT email, COUNT(*) AS dup_count
FROM users
GROUP BY email
HAVING COUNT(*) > 1
ORDER BY dup_count DESC;
 
-- See the duplicate rows themselves
SELECT *
FROM users
WHERE email IN (
  SELECT email FROM users GROUP BY email HAVING COUNT(*) > 1
)
ORDER BY email, id;

Find orphan records (broken FKs)

SELECT o.*
FROM orders o
LEFT JOIN users u ON o.user_id = u.id
WHERE u.id IS NULL;

Returns orders whose user_id points at a user that no longer exists.

Verify referential integrity in both directions

-- Orders without users
SELECT COUNT(*) AS orphan_orders
FROM orders o
LEFT JOIN users u ON o.user_id = u.id
WHERE u.id IS NULL;
 
-- Users with no orders (might be intentional, but worth knowing)
SELECT COUNT(*) AS users_with_no_orders
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.id IS NULL;

Verify migration row counts

SELECT 'old_users' AS source, COUNT(*) FROM old_users
UNION ALL
SELECT 'new_users',           COUNT(*) FROM users;
 
-- Spot rows present in old but missing from new
SELECT id FROM old_users
EXCEPT
SELECT id FROM users;     -- PostgreSQL / SQL Server
                          -- MySQL: use LEFT JOIN ... WHERE NULL pattern

Audit nulls per column

SELECT
  COUNT(*) FILTER (WHERE name  IS NULL) AS null_name,
  COUNT(*) FILTER (WHERE email IS NULL) AS null_email,
  COUNT(*) FILTER (WHERE phone IS NULL) AS null_phone,
  COUNT(*)                              AS total
FROM users;
 
-- Older syntax that works everywhere
SELECT
  SUM(CASE WHEN name  IS NULL THEN 1 ELSE 0 END) AS null_name,
  SUM(CASE WHEN email IS NULL THEN 1 ELSE 0 END) AS null_email,
  COUNT(*)                                       AS total
FROM users;

Format / data-type validation

-- Emails missing @ or .
SELECT id, email
FROM users
WHERE email NOT LIKE '%@%.%';
 
-- Negative prices
SELECT * FROM products WHERE price < 0;
 
-- Phone numbers that aren't 10 digits
SELECT id, phone
FROM users
WHERE LENGTH(REGEXP_REPLACE(phone, '[^0-9]', '', 'g')) <> 10;

Verify enum / status values

-- Should match the application's allowed set
SELECT DISTINCT status FROM orders;
 
-- Find rows with unexpected values
SELECT id, status
FROM orders
WHERE status NOT IN ('pending', 'completed', 'cancelled', 'refunded');

Stale data check

-- Orders sitting in 'pending' too long
SELECT id, status, created_at
FROM orders
WHERE status = 'pending'
  AND created_at < NOW() - INTERVAL '24 hours';

Test Data Management

Predictable test data

Use stable patterns so generated test users are easy to find and clean up:

INSERT INTO users (name, email, role) VALUES
  ('QA Smoke 001', 'qa-smoke-001@test.example', 'user'),
  ('QA Smoke 002', 'qa-smoke-002@test.example', 'user'),
  ('QA Admin 001', 'qa-admin-001@test.example', 'admin');

The @test.example domain is reserved (RFC 2606) — safe to use without hitting real inboxes.

Cleanup between runs

DELETE FROM orders
WHERE user_id IN (SELECT id FROM users WHERE email LIKE '%@test.example');
 
DELETE FROM users WHERE email LIKE '%@test.example';

Reset auto-increment sequences

-- PostgreSQL
ALTER SEQUENCE users_id_seq RESTART WITH 1;
SELECT setval('users_id_seq', (SELECT COALESCE(MAX(id), 0) FROM users));
 
-- MySQL
ALTER TABLE users AUTO_INCREMENT = 1;
 
-- SQL Server
DBCC CHECKIDENT ('users', RESEED, 0);

Transactions for test isolation

Wrap test setup + assertions in a transaction and roll back at the end — the database returns to its prior state automatically.

BEGIN;
 
INSERT INTO users (name, email) VALUES ('Test User', 'test@test.example');
-- ... test queries that read the inserted user
SELECT * FROM users WHERE email = 'test@test.example';
 
ROLLBACK;
-- The inserted user is gone — no cleanup needed

Snapshots and savepoints

BEGIN;
 
INSERT INTO users (name) VALUES ('Step 1 user');
SAVEPOINT after_step_1;
 
INSERT INTO orders (user_id, total) VALUES (currval('users_id_seq'), 99.99);
-- If something's wrong:
ROLLBACK TO SAVEPOINT after_step_1;
 
-- Or commit everything:
COMMIT;

Quick smoke checks before a test run

-- Are seed accounts present?
SELECT COUNT(*) FROM users WHERE email LIKE 'qa-%@test.example';
 
-- Is the staging environment isolated?
SELECT environment, COUNT(*) FROM tenants GROUP BY environment;
 
-- Has the latest migration applied?
SELECT version, applied_at FROM schema_migrations ORDER BY applied_at DESC LIMIT 5;