I was going through "Learn SQL the hard way" and I am currently in exercise 13.
I am stuck in the part where we have to
Write a query that can find all the names of pets and their owners bought after 2004. Key to this is to map the person_pet based on the purchased_on column to the pet and parent.
My tables look like this
sqlite> select * from person ;
id first_name last_name age dead phone_number salary dob
---------- ---------- ---------- ---------- ---------- ------------ ---------- ----------
0 john doe 20 0 9929 123123.0 2015-12-09
1 foo bar 25 0 12 123123.0 2004-12-11
2 michal jordan 19 0 12 123123.0 2005-12-11
3 tom ford 30 0 12 123123.0 2002-12-11
And
sqlite> select * from pet ;
id name breed age dead dob parent_id
---------- ---------- ---------- ---------- ---------- ---------- ----------
0 fluffy Unicorn 5 0 2012-02-01
1 quora social net 10 0 2010-02-01
2 Goldie German She 6 0 2009-02-01
3 boxer golden ret 3 0 2007-02-01
4 naman kutta 10 1 2011-02-01
5 hari ohk 7 0 2015-02-01
sqlite> select * from person_pet ;
person_id pet_id purchased_on
---------- ---------- ------------
2 2 2002-03-30
2 3 2001-04-30
2 4 2005-04-30
2 5 2003-04-30
3 1 2006-04-30
3 4 2005-04-30
My schema looks like this
sqlite> .schema
CREATE TABLE person(
id INTEGER PRIMARY KEY,
first_name TEXT,
last_name TEXT,
age INTEGER
, dead INTEGER, phone_number INTEGER, salary FLOAT, dob DATETIME);
CREATE TABLE pet(
id INTEGER PRIMARY KEY,
name TEXT,
breed TEXT,
age INTEGER,
dead INTEGER
, dob DATETIME, parent_id INTEGER);
CREATE TABLE person_pet(
person_id INTEGER,
pet_id INTEGER
, purchased_on DATETIME);
CREATE TABLE cars(
id INTEGER PRIMARY KEY,
car_name TEXT
);
What I have tried
I am able to get the person_id and pet_id brought after the year "2004" with the query
sqlite> select person_id, pet_id, purchased_on from person_pet
...> where purchased_on > "2004-01-01" ;
person_id pet_id purchased_on
---------- ---------- ------------
2 4 2005-04-30
3 1 2006-04-30
3 4 2005-04-30
How should I proceed to get the names out now ?
This is my first time with SQL so any help would be much appreciated. Been stuck in this for a while.
Aucun commentaire:
Enregistrer un commentaire