Join Operations and Alternatives | Dataford Interview Questions

Explain the foreign key and demonstrate how to combine data from two tables using joins, and is there any alternate way apart from joins?

Asked in the managerial and technical interview stage. A reported follow-up asked about the difference between SQL and NoSQL databases and the scenarios where each is used.

Write a PostgreSQL query that combines contacts with their interactions and includes contacts with no interactions. Also explain the foreign-key relationship and provide an alternative query that does not use a join.

Output

One row per contact with contact_id, contact_name, interaction_count, and last_interaction_date.
Include contacts with zero interactions, ordered by contact_id ascending.

Schema

contacts

Column	Type	Description
contact_idPK	INT	Unique contact identifier
contact_name	VARCHAR(100)	Contact display name
email	VARCHAR(150)	Contact email address

interactions

Column	Type	Description
interaction_idPK	INT	Unique interaction identifier
contact_id	INT	References contacts.contact_id
interaction_date	DATE	Date of the interaction
channel	VARCHAR(30)	Interaction channel
status	VARCHAR(30)	Interaction status

Your question is Join Operations and Alternatives. 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