vendredi 18 septembre 2015

"Learn sql the hard way" - Exercise 13 : Trouble with a nested select statement.

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