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

Sigiloso · 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.

Sigiloso · Answer

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

Google

Pergunta de entrevista da empresa Google

Respostas da entrevista

Empresas seguidas

Buscas de vagas