Pets
+------------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| breed | varchar(255) | NO | | NULL | |
| acquisition_date | datetime | NO | | NULL | |
+------------------+--------------+------+-----+---------+----------------+

Customers
+-------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(255) | NO | | NULL | |
+-------+--------------+------+-----+---------+----------------+

Sales
+-------------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------+------+-----+---------+-------+
| customer_id | int(11) | NO | PRI | NULL | |
| pet_id | int(11) | NO | PRI | NULL | |
| date | datetime | NO | | NULL | |
+-------------+----------+------+-----+---------+-------+

Q1 How many pets of each breed has the shop ever owned?

Question

Utilisateur anonyme · Accepted Answer

it's just a query, isn't it?

SELECT breed, COUNT (id) from Pets
GROUP BY breed

ojiisan · Answer

'Anonymous' had the right idea, but forgot to add all the pets sold to customers. The question asked for all ever owned, not just currently owned (by the store).

vic · Answer

I think anonymous is right, if I only had 1 pet of a certain type and I sold it to a customer, I probably wouldn't remove that id from my database as it would corrupt my data. I'm assuming that the pet_id in the sales table is a foreign key even though it isn't formally constrained.

Ideally we'd like to have a count field in the pets table to keep track of the pets that we currently have available.

In this situation, whether we have the count field or not, anon's solution looks to be right.

Utilisateur anonyme · Answer

I couldn't answer this with confidence and the interviewer went right on.

Google

Question d’entretien chez Google

Réponses aux questions d'entretien

Entreprises suivies

Recherche d’emplois