Winning Candidate
Problem
Candidate(id, name) and Vote(id, candidate_id). Return the name of the winning candidate.
cand=[(1,'A'),(2,'B'),(3,'C')] votes=[(1,2),(2,2),(3,2),(4,1)]'B'SELECT name
FROM Candidate
WHERE id = (SELECT candidate_id FROM Vote GROUP BY candidate_id ORDER BY COUNT(*) DESC LIMIT 1);Explanation
The question is really just "who got the most votes?" So we count the votes per candidate, find the candidate with the biggest count, and then look up that person's name.
The inner query does the counting: GROUP BY candidate_id bundles all vote rows by the candidate they point to, and COUNT(*) tells us how many votes each one has.
We then sort those tallies from largest to smallest with ORDER BY COUNT(*) DESC and keep only the top row using LIMIT 1. That single row hands us the winning candidate_id.
Finally the outer query matches that id against the Candidate table with WHERE id = (...) and returns the matching name.
Example: votes point to candidate 2 three times and candidate 1 once. The tally is {2: 3, 1: 1}, the top is id 2, and candidate 2's name is 'B', so the answer is 'B'.