SQL Joins for Test Data | Dataford Interview Questions

Write a SQL query using different types of joins (Inner, Left, Right) to retrieve test data across multiple relational tables.

Use the provided test-management tables and return related suite, case, execution, and environment information. Preserve environments even when no matching test case exists, and preserve test cases when no execution exists.

Output

One row per test execution, test case without an execution, or environment without a test case.
Columns: environment_name, suite_name, test_case_id, test_case_name, run_id, status, and executed_at.
Order by environment, test case, and run, placing missing identifiers last.

Schema

test_suites

Column	Type	Description
suite_idPK	INTEGER	Unique test suite identifier
suite_name	VARCHAR(100)	Name of the test suite

test_cases

Column	Type	Description
test_case_idPK	INTEGER	Unique test case identifier
suite_id	INTEGER	Referenced test suite
environment_id	INTEGER	Referenced test environment
test_case_name	VARCHAR(150)	Test case description

test_runs

Column	Type	Description
run_idPK	INTEGER	Unique test execution identifier
test_case_id	INTEGER	Executed test case
status	VARCHAR(20)	Execution result
executed_at	TIMESTAMP	Execution timestamp

test_environments

Column	Type	Description
environment_idPK	INTEGER	Unique test environment identifier
environment_name	VARCHAR(80)	Environment or browser name

Your question is SQL Joins for Test Data. Start with the requirements and the four tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem