Filtering with AND, OR, IN, BETWEEN

Your First SQL Queries: SELECT, FROM, WHERE

SQL is the language for asking questions of a relational database. Every tester eventually needs to verify that what the application shows matches what the database actually stores.

The Basic Structure

SELECT column1, column2
FROM   table_name
WHERE  condition;

Every SQL statement ends with a semicolon. Keywords (SELECT, FROM, WHERE) are conventionally uppercase, but SQL is case-insensitive for keywords.

SELECT

-- Select all columns
SELECT * FROM users;

-- Select specific columns (preferred — avoids surprises when schema changes)
SELECT id, email, created_at FROM users;

-- Rename columns in output
SELECT id, email, created_at AS registered_on FROM users;

WHERE — Filtering Rows

-- Equality
SELECT * FROM orders WHERE status = 'completed';

-- Numeric comparison
SELECT * FROM products WHERE price > 50;

-- Multiple conditions
SELECT * FROM users WHERE country = 'US' AND is_active = 1;
SELECT * FROM orders WHERE status = 'pending' OR status = 'processing';

Common Operators

LIKE Patterns

% matches any sequence of characters. _ matches exactly one character.

-- Emails ending with @gmail.com
SELECT * FROM users WHERE email LIKE '%@gmail.com';

-- Names starting with 'Al'
SELECT * FROM users WHERE name LIKE 'Al%';

Practical Test Verification

Before writing a bug report, check what the database actually contains:

SELECT id, user_id, status, total, created_at
FROM   orders
WHERE  user_id = 12345
ORDER BY created_at DESC
LIMIT 10;

If the data is there but not shown, it's a frontend bug. If the data is missing, it's a backend bug.