Designing a Lottery System

I was asked this question in an interview: You need to design a database to hold customers and their lottery tickets. A lottery ticket has a sequence of 6 numbers e.g. 04 12 18 28 35 41. Once you have designed your tables, write a query which will print out a report of all customers whose tickets match three or more numbers of the winning number. Assume every customer has only one lottery ticket.

The simplest way is to have two tables:

• Customer: with an id, name etc
• Ticket: with an id, customer id and number (which will hold one of the numbers only, so you will get six records per ticket)

Given a winning number, the query to find all customers and how many numbers they matched would be:

SELECT c.name, COUNT(t.num) AS matches
FROM customer c, ticket t
WHERE c.id = t.customer_id
AND t.num IN
(
'05', /*winning number*/
'12',
'19',
'28',
'35',
'42'
)
GROUP BY customer
HAVING COUNT(t.num) >= 3
ORDER BY matches DESC