QALingo — SQL for QA: Module 2

1

DDL Command 01

CREATE TABLE

Define the structure before the data exists.

Definition

CREATE TABLE defines a new table with its columns, data types, and constraints. As a QA, you read CREATE statements to understand what fields are required, what types are expected, and what constraints protect data quality.

SQL — CREATE TABLE

CREATE TABLE users ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(100) NOT NULL, email VARCHAR(255) NOT NULL UNIQUE, status VARCHAR(20) DEFAULT 'pending', plan VARCHAR(20) DEFAULT 'free', country VARCHAR(100), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); -- QA reads this and immediately knows: -- ✅ name is required (NOT NULL) -- ✅ email must be unique (UNIQUE constraint) -- ✅ status defaults to 'pending' — test this! -- ✅ country is optional (no NOT NULL) -- ✅ created_at is auto-set — verify it matches reality

✏️ Exercise 1 — Read the Schema

EASY

Task: Write a CREATE TABLE statement for an orders table with: id (auto-increment PK), user_id (INT, required), amount (DECIMAL 10,2, required), status (VARCHAR 20, default 'pending'), created_at (TIMESTAMP, auto now).

💡 Use NOT NULL for required fields. Use DEFAULT for auto values.

CREATE TABLE orders ( id INT PRIMARY KEY AUTO_INCREMENT, user_id INT NOT NULL, amount DECIMAL(10,2) NOT NULL, status VARCHAR(20) DEFAULT 'pending', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP );

💡 QA Use Case

Reading CREATE TABLE is like reading the test requirements. Every NOT NULL is a mandatory field to test. Every UNIQUE is a duplicate test case. Every DEFAULT is a value to verify after INSERT. Schema reading is QA analysis before a single test is written.

🔍 Quick Check

CREATE TABLE

2

DML Command 01

INSERT INTO

Add test data — and verify it lands correctly.

Definition

INSERT INTO adds new rows to a table. QA uses INSERT to create test data, seed test environments, and reproduce bugs by inserting specific conditions into the database.

SQL — INSERT INTO

-- Insert a single user INSERT INTO users (name, email, status, plan, country) VALUES ('Test User', 'user@example.com', 'active', 'pro', 'Colombia'); -- Insert multiple rows at once (for test data seeding) INSERT INTO users (name, email, status, plan) VALUES ('QA User 1', 'user@example.com', 'active', 'free'), ('QA User 2', 'user@example.com', 'pending', 'pro'), ('QA User 3', 'user@example.com', 'suspended', 'free'); -- After INSERT: always verify with SELECT SELECT * FROM users WHERE email = 'user@example.com';

⚠️

QA Rule: Never INSERT directly into production databases without approval. INSERT creates permanent records. Always use test/staging environments for data seeding.

✏️ Exercise 2 — Seed Test Data

EASY

Task: Insert a test user with: name = 'Watson QA', email = 'user@example.com', status = 'active', plan = 'pro', country = 'Colombia'. Then write a SELECT to verify it was created.

💡 Write both the INSERT and the SELECT to verify.

INSERT INTO users (name, email, status, plan, country) VALUES ('Watson QA', 'user@example.com', 'active', 'pro', 'Colombia');

then SELECT * FROM users WHERE email = 'user@example.com';

💡 QA Use Case

INSERT is used to reproduce bugs. If a bug only happens with a suspended Pro user in Colombia — INSERT that exact record and test. It's also essential for test data setup before running test suites in staging environments.

🔍 Quick Check

INSERT INTO

3

DML Command 02

UPDATE

The most dangerous command — always use WHERE.

🚨

Critical Warning: UPDATE without WHERE updates EVERY row in the table. UPDATE users SET status = 'suspended' suspends ALL users. Always verify your WHERE clause with a SELECT first.

SQL — UPDATE (Safe Pattern)

-- ✅ SAFE: Always SELECT first to verify what you'll update SELECT id, name, status FROM users WHERE email = 'user@example.com'; -- Then UPDATE with the same WHERE UPDATE users SET status = 'active', plan = 'pro' WHERE email = 'user@example.com'; -- Update multiple fields at once UPDATE users SET status = 'suspended', plan = 'free' WHERE id = 4; -- ❌ DANGEROUS: No WHERE = updates ALL rows! UPDATE users SET status = 'suspended'; -- Never do this!

BEFORE UPDATE

id	name	status	plan
4	Carlos Ruiz	suspended	free

AFTER UPDATE

id	name	status	plan
4	Carlos Ruiz	active	pro

✏️ Exercise 3 — Safe UPDATE

INTERMEDIATE

Task: A QA test requires María López (user@example.com) to have status = 'active' and plan = 'pro'. Write the UPDATE query. Remember: SELECT first to verify, then UPDATE with the same WHERE.

💡 Always write SELECT before UPDATE. Use WHERE email = 'user@example.com'.

UPDATE users SET status = 'active', plan = 'pro' WHERE email = 'user@example.com'

💡 The QA Golden Rule for UPDATE

SELECT before UPDATE, every time. Run the SELECT with your WHERE clause. Count the rows. If it returns 1 row — your UPDATE is safe. If it returns 100 rows — you were about to update 100 records. This habit has saved countless QA engineers from career-ending mistakes in production.

🔍 Quick Check

UPDATE safely

4

DML Command 03

DELETE

Remove rows — permanently. No undo button.

🚨

Critical Warning: DELETE without WHERE removes ALL rows. Unlike TRUNCATE, DELETE can be rolled back if you're inside a transaction — but only if you haven't committed yet. Same rule as UPDATE: SELECT first.

SQL — DELETE

-- DELETE a specific test user after testing DELETE FROM users WHERE email = 'user@example.com'; -- DELETE with multiple conditions DELETE FROM sessions WHERE expires_at < NOW() AND user_id = 1; -- Safe pattern: use BEGIN/ROLLBACK to preview BEGIN; DELETE FROM users WHERE status = 'suspended'; SELECT ROW_COUNT(); -- Check how many rows affected ROLLBACK; -- Cancel if number is wrong, COMMIT if correct -- QA uses DELETE to clean up test data after test runs

✏️ Exercise 4 — Clean Up Test Data

INTERMEDIATE

Task: After a test run, you need to clean up test users. Write a DELETE query to remove all users whose email ends with '@test.com'. First write the SELECT to verify how many rows will be affected, then the DELETE.

💡 Use LIKE '%@test.com' to match email patterns.

SELECT COUNT(*) FROM users WHERE email LIKE '%@test.com' then DELETE FROM users WHERE email LIKE '%@test.com'

💡 DELETE vs TRUNCATE

DELETE removes specific rows with WHERE, can be rolled back, and fires triggers. TRUNCATE removes ALL rows instantly, cannot be rolled back easily, and resets auto-increment counters. Use DELETE for targeted cleanup. Use TRUNCATE only when you want to completely wipe a table.

🔍 Quick Check

DELETE safely

5

DML Command 04

TRUNCATE

Wipe a table completely — fast and irreversible.

SQL — TRUNCATE vs DELETE

-- TRUNCATE: removes ALL rows, resets auto-increment TRUNCATE TABLE sessions; -- Wipe all sessions -- DELETE equivalent (slower, but has WHERE option) DELETE FROM sessions; -- Same result, but slower -- Key differences for QA: -- TRUNCATE: faster, resets ID counter to 1, no WHERE -- DELETE: slower, keeps ID counter, supports WHERE -- TRUNCATE: hard to rollback in most databases -- DELETE: can rollback if inside a transaction -- QA uses TRUNCATE to reset test tables between test runs TRUNCATE TABLE test_results; TRUNCATE TABLE audit_log;

✏️ Exercise 5 — When to TRUNCATE

EASY

Scenario: Before each test suite run, your team needs to clean the sessions table completely and reset IDs to 1. Which command is correct? Write it — and also write the equivalent DELETE command for comparison.

TRUNCATE TABLE sessions; and DELETE FROM sessions; — TRUNCATE is faster and resets auto-increment.

🔍 Quick Check

TRUNCATE vs DELETE

6

DDL Command 02

ALTER TABLE

Modify structure after creation — the migration command.

SQL — ALTER TABLE

-- Add a new column ALTER TABLE users ADD COLUMN phone VARCHAR(20) NULL; -- Change column type or size ALTER TABLE users MODIFY COLUMN name VARCHAR(200); -- was 100, now 200 -- Rename a column ALTER TABLE users RENAME COLUMN country TO country_code; -- Add a NOT NULL constraint to existing column ALTER TABLE users MODIFY COLUMN country VARCHAR(100) NOT NULL; -- QA validates after ALTER: -- ✅ New column exists in SELECT * -- ✅ Old data still intact -- ✅ New constraints enforced (test with NULL input)

✏️ Exercise 6 — Schema Change Validation

INTERMEDIATE

Scenario: A developer ran a migration that added a 'phone' column to the users table. Write the ALTER statement they should have used, then write a SELECT to verify the column exists with the right data.

ALTER TABLE users ADD COLUMN phone VARCHAR(20) NULL; then SELECT id, name, email, phone FROM users LIMIT 5;

💡 QA and migrations

Every database migration is a QA test opportunity. After every ALTER TABLE in a deployment: verify the column exists, check old data is intact, test the new constraint with invalid input. Migrations are one of the most common sources of production bugs.

🔍 Quick Check

ALTER TABLE

7

DQL Command 01

DISTINCT

Remove duplicates from your results — see unique values only.

SQL — DISTINCT

-- See all unique statuses in the users table SELECT DISTINCT status FROM users; -- Returns: active, pending, suspended -- All unique countries SELECT DISTINCT country FROM users ORDER BY country; -- DISTINCT on multiple columns SELECT DISTINCT status, plan FROM users ORDER BY status, plan; -- QA use: verify all expected statuses exist -- If 'verified' is a valid status but doesn't appear → bug!

✏️ Exercise 7 — Status Validation

EASY

Task: After a deployment, write a query to verify all unique combinations of status and plan that exist in the users table. This helps QA confirm no unexpected status values were introduced.

SELECT DISTINCT status, plan FROM users ORDER BY status, plan;

🔍 Quick Check

DISTINCT

8

DQL Command 02

ORDER BY

Sort results — and find what's first, last, or out of order.

SQL — ORDER BY

-- Most recent users first (DESC = descending) SELECT id, name, email, created_at FROM users ORDER BY created_at DESC; -- Alphabetical by name (ASC = ascending, default) SELECT name, email FROM users ORDER BY name ASC; -- Sort by multiple columns SELECT name, status, plan, created_at FROM users ORDER BY status ASC, created_at DESC; -- QA: find the highest amount order (potential billing bug) SELECT id, user_id, amount, status FROM orders ORDER BY amount DESC LIMIT 10;

✏️ Exercise 8 — Find Recent Activity

EASY

Task: Write a query to get all users ordered by most recently created first. Show id, name, email, status, and created_at. This helps QA verify new registrations are appearing correctly.

SELECT id, name, email, status, created_at FROM users ORDER BY created_at DESC;

🔍 Quick Check

ORDER BY

9

DQL Command 03

LIMIT / TOP

Control how many rows you get back.

SQL — LIMIT (MySQL/PostgreSQL) vs TOP (SQL Server)

-- MySQL / PostgreSQL: LIMIT at the end SELECT * FROM users ORDER BY created_at DESC LIMIT 10; -- Get only 10 rows -- SQL Server / MS Access: TOP at the start SELECT TOP 10 * FROM users ORDER BY created_at DESC; -- Pagination: skip first 10, get next 10 SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 10; -- Page 2 -- QA validates pagination: -- Page 1: LIMIT 10 OFFSET 0 → rows 1-10 -- Page 2: LIMIT 10 OFFSET 10 → rows 11-20 -- Last page: may have fewer than 10 rows → test edge case!

✏️ Exercise 9 — Pagination Validation

INTERMEDIATE

Task: The app shows 3 users per page. Write two queries: one for Page 1 (first 3 users by id) and one for Page 2 (next 3 users). This simulates pagination validation.

Page 1: LIMIT 3 or LIMIT 3 OFFSET 0. Page 2: LIMIT 3 OFFSET 3.

💡 Always use ORDER BY with LIMIT

LIMIT without ORDER BY returns random rows — the database picks whatever is fastest to retrieve. This makes pagination results inconsistent and unpredictable. In QA testing, always combine LIMIT with ORDER BY to ensure deterministic, reproducible results.

🔍 Quick Check

LIMIT / TOP / Pagination

10

DQL Command 04

GROUP BY

Aggregate data — count, sum, and analyze by category.

SQL — GROUP BY + Aggregates

-- Count users by status SELECT status, COUNT(*) AS total FROM users GROUP BY status ORDER BY total DESC; -- Sum revenue by user (find highest spenders) SELECT u.name, u.email, SUM(o.amount) AS total_spent, COUNT(o.id) AS order_count FROM users u JOIN orders o ON u.id = o.user_id GROUP BY u.id, u.name, u.email ORDER BY total_spent DESC; -- Users by country and plan (cross-tab analysis) SELECT country, plan, COUNT(*) AS users FROM users GROUP BY country, plan ORDER BY country, users DESC;

✏️ Exercise 10 — Final QA Report

ADVANCED

Task: Write a query that shows how many users exist per plan (free vs pro), with the count of active vs non-active in each plan. Use GROUP BY on both plan and status. Order by plan, then count descending.

SELECT plan, status, COUNT(*) AS total FROM users GROUP BY plan, status ORDER BY plan, total DESC;

💡 GROUP BY for QA Reporting

GROUP BY is your QA reporting engine. Use it to count affected records per category, summarize test results, identify which user segments have issues, and generate the data summaries that go into bug reports and release notes. A QA who can GROUP BY is a QA who can communicate impact with numbers.

🔍 Quick Check

GROUP BY mastery

SQL for QA:DML & DDL

SQL for QA:
DML & DDL