SQL Join Basics and Cardinality | Dataford Interview Questions

What is many-to-one, and what are the differences between LEFT JOIN and INNER JOIN?

Use the provided Amex cardmember and transaction tables to demonstrate the relationship with a query that preserves cardmembers who have no completed transactions.

Output

Return one row per cardmember with cardmember_id, cardmember_name, completed_transaction_count, and completed_amount.
Include every cardmember, order by cardmember_id ascending, and treat missing completed transactions as zero.

Schema

cardmembers

Column	Type	Description
cardmember_idPK	INT	Unique cardmember identifier
cardmember_name	VARCHAR(100)	Cardmember name
membership_tier	VARCHAR(30)	Amex membership tier

transactions

Column	Type	Description
transaction_idPK	INT	Unique transaction identifier
cardmember_id	INT	Cardmember associated with the transaction
transaction_status	VARCHAR(20)	Transaction processing status
amount	DECIMAL(10,2)	Transaction amount in dollars

Your question is SQL Join Basics and Cardinality. Start with the requirements and the two 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