Ex16: no results

mechmiel · December 30, 2018, 6:34pm

I’m not getting expected results when following the EX16.
I have an sql file generated by python.
I have it merged into thw.db database.
So I have complete database and now I’m trying to repeat the same actions as in the excercise.

I start from the easiest:

/* simple query to get a related table */
select * from person, person_pet, pet
    where person.id = person_pet.person_id and pet.id = person_pet.pet_id;

but I don’t get any results, silence…

The same lack of results i get for the next line of code which is following

/* add a basic count column and append the GROUP BY */
select person.first_name, pet.breed, pet.dead, count(dead)
    from person, person_pet, pet
    where person.id = person_pet.person_id and pet.id = person_pet.pet_id
    group by pet.breed, pet.dead;

I see that the ex15.sql generated by python has NULLs in the place of IDs for “person” and “pet”.
Is it how it is supposed to be? Cause my assumption is that because there’re no IDs it’s not possible to match them…

Example of sql tables in ex15.sql:
INSERT INTO pet VALUES(NULL, “Authority”, “Cat”, “16”, “1”);
INSERT INTO pet VALUES(NULL, “Blow”, “Unicorn”, “14”, “0”);
INSERT INTO pet VALUES(NULL, “Bird”, “Fish”, “3”, “0”);

zedshaw · January 1, 2019, 12:13am

Hmmmm, so can you show me how you’re running the sqlite3 command? Also, try dumping the database and see if it’s just empty for some reason. Just type .dump inside sqlite3 and it’ll print it all out.

mechmiel · January 2, 2019, 9:11pm

Thanks for a quick answer.
In the meantime I’ve created the file once again from the scratch and filled it with sql but the issue remains.
As you suggested - I’ve used .dump - and it shows all records, correctly - the database is the same as in ex15.sql.

Once again what I do is:

/* run sqlite:
/.sqlite3 thw.db

select * from person, person_pet, pet
where person.id = person_pet.person_id and pet.id = person_pet.pet_id;

and i just don’t get any results
if I try to do the simple:
select * from pet;
then I get all desired records - all pets

Previously I mentioned that I’m suspicious about those NULL records in pet.id and person.id because when I just try to ask for them
select id from pet;

then I get several empty lines, which is quite logical as the id = NULL. Maybe I’m wrong but I just don’t get how SQL can connect person.id with person_pet.person_id if the person.id = NULL. Isn’t it be the reason why I don’t get any results for previous queries?

_just for the record: I’m working on WIN10, powershell

zedshaw · January 4, 2019, 9:45pm

Yes, you should type:

.dump

I bet you actually have nothing in the database because you didn’t really do INSERT for any new data.

mechmiel · January 6, 2019, 5:08pm

You’re right I didn’t do any INSERT action, i’ve only initialised it by -init ex15.sql action. But is it the reason why it doesn’t work? Look below - the database seems complete to me.

PS C:\Users\mechm\lsqlthw> ./sqlite3 thw2.db
SQLite version 3.25.2 2018-09-25 19:08:10
Enter ".help" for usage hints.
sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE person_pet (
person_id INTEGER,
pet_id INTEGER,
purchased_on DATETIME
);
INSERT INTO person_pet VALUES(20,60,'2008-03-27 04:23:21.085385');
INSERT INTO person_pet VALUES(25,58,'1981-10-07 03:23:21.085406');
INSERT INTO person_pet VALUES(38,21,'1978-01-17 20:23:21.085427');
INSERT INTO person_pet VALUES(64,20,'1981-04-24 07:23:21.085447');
INSERT INTO person_pet VALUES(92,34,'2011-06-30 02:23:21.085466');
INSERT INTO person_pet VALUES(15,65,'1990-11-07 20:23:21.085485');
INSERT INTO person_pet VALUES(91,80,'2007-04-14 23:23:21.085505');
INSERT INTO person_pet VALUES(8,56,'1986-05-19 18:23:21.085525');
INSERT INTO person_pet VALUES(95,88,'2015-04-01 12:23:21.085545');
INSERT INTO person_pet VALUES(57,30,'1986-03-17 09:23:21.085565');
INSERT INTO person_pet VALUES(17,9,'1981-09-03 21:23:21.085585');
INSERT INTO person_pet VALUES(27,42,'1965-12-20 13:23:21.085604');
INSERT INTO person_pet VALUES(41,51,'1985-11-04 07:23:21.085626');
INSERT INTO person_pet VALUES(36,39,'1984-10-31 21:23:21.085646');
INSERT INTO person_pet VALUES(32,59,'2001-12-31 19:23:21.085665');
INSERT INTO person_pet VALUES(43,71,'1986-07-17 21:23:21.085684');
INSERT INTO person_pet VALUES(38,69,'1992-03-11 18:23:21.085703');
INSERT INTO person_pet VALUES(39,48,'2014-01-01 13:23:21.085723');
INSERT INTO person_pet VALUES(93,40,'1975-02-13 10:23:21.085743');
INSERT INTO person_pet VALUES(86,33,'1980-02-14 11:23:21.085762');
INSERT INTO person_pet VALUES(92,79,'1971-09-24 03:23:21.085783');
INSERT INTO person_pet VALUES(11,17,'1996-07-18 20:23:21.085802');
INSERT INTO person_pet VALUES(84,48,'1991-01-28 20:23:21.085822');
INSERT INTO person_pet VALUES(83,38,'1993-06-19 03:23:21.085842');
INSERT INTO person_pet VALUES(24,74,'1998-06-11 07:23:21.085861');
INSERT INTO person_pet VALUES(75,73,'1999-03-10 06:23:21.085883');
INSERT INTO person_pet VALUES(78,20,'1992-01-27 03:23:21.085903');
INSERT INTO person_pet VALUES(47,88,'2004-12-05 04:23:21.085922');
INSERT INTO person_pet VALUES(26,45,'1990-02-22 21:23:21.085942');
INSERT INTO person_pet VALUES(30,55,'1988-06-08 14:23:21.085962');
INSERT INTO person_pet VALUES(18,11,'1974-10-18 23:23:21.085981');
INSERT INTO person_pet VALUES(64,35,'1967-02-20 03:23:21.086001');
INSERT INTO person_pet VALUES(30,45,'2006-12-01 15:23:21.086020');
INSERT INTO person_pet VALUES(9,79,'1975-02-25 00:23:21.086040');
INSERT INTO person_pet VALUES(93,8,'2011-12-19 09:23:21.086062');
INSERT INTO person_pet VALUES(17,85,'1987-02-18 04:23:21.086082');
INSERT INTO person_pet VALUES(11,43,'1975-09-20 15:23:21.086101');
INSERT INTO person_pet VALUES(59,52,'1985-08-06 13:23:21.086121');
INSERT INTO person_pet VALUES(9,37,'1983-01-26 01:23:21.086141');
INSERT INTO person_pet VALUES(20,32,'2013-08-22 13:23:21.086161');
INSERT INTO person_pet VALUES(37,39,'1974-02-17 22:23:21.086180');
INSERT INTO person_pet VALUES(75,75,'2012-03-31 05:23:21.086200');
INSERT INTO person_pet VALUES(48,19,'1976-04-02 06:23:21.086219');
INSERT INTO person_pet VALUES(74,35,'1970-05-02 21:23:21.086239');
INSERT INTO person_pet VALUES(62,87,'2012-01-06 19:23:21.086259');
INSERT INTO person_pet VALUES(5,71,'1978-02-25 00:23:21.086278');
INSERT INTO person_pet VALUES(70,35,'1994-11-17 05:23:21.086297');
INSERT INTO person_pet VALUES(46,27,'2001-07-22 04:23:21.086318');
INSERT INTO person_pet VALUES(93,32,'1985-08-19 07:23:21.086340');
INSERT INTO person_pet VALUES(51,68,'1978-03-05 19:23:21.086363');
INSERT INTO person_pet VALUES(37,81,'2005-11-02 11:23:21.086383');
INSERT INTO person_pet VALUES(59,29,'2005-05-24 17:23:21.086403');
INSERT INTO person_pet VALUES(76,68,'1991-10-20 07:23:21.086424');
INSERT INTO person_pet VALUES(86,8,'1967-01-14 09:23:21.086443');
INSERT INTO person_pet VALUES(62,92,'1978-05-05 14:23:21.086463');
INSERT INTO person_pet VALUES(69,19,'1971-07-08 13:23:21.086483');
INSERT INTO person_pet VALUES(55,54,'1994-01-24 12:23:21.086502');
INSERT INTO person_pet VALUES(8,39,'1991-11-25 00:23:21.086521');
INSERT INTO person_pet VALUES(68,10,'1970-11-26 07:23:21.086542');
INSERT INTO person_pet VALUES(69,49,'2004-05-20 15:23:21.086562');
INSERT INTO person_pet VALUES(49,51,'2012-11-02 07:23:21.086582');
INSERT INTO person_pet VALUES(70,33,'2013-11-05 22:23:21.086603');
INSERT INTO person_pet VALUES(42,44,'1967-01-27 08:23:21.086623');
INSERT INTO person_pet VALUES(43,32,'2001-05-06 20:23:21.086644');
INSERT INTO person_pet VALUES(10,25,'1972-12-20 15:23:21.086663');
INSERT INTO person_pet VALUES(63,26,'1999-04-16 03:23:21.086683');
INSERT INTO person_pet VALUES(12,41,'1966-11-11 04:23:21.086703');
INSERT INTO person_pet VALUES(12,12,'1982-01-29 14:23:21.086723');
INSERT INTO person_pet VALUES(76,86,'2011-10-20 18:23:21.086743');
INSERT INTO person_pet VALUES(58,72,'1992-03-24 21:23:21.086763');
INSERT INTO person_pet VALUES(37,83,'2011-08-26 00:23:21.086783');
INSERT INTO person_pet VALUES(58,73,'1995-01-28 22:23:21.086803');
INSERT INTO person_pet VALUES(73,33,'1988-05-05 20:23:21.086823');
INSERT INTO person_pet VALUES(12,36,'1973-04-19 16:23:21.086842');
INSERT INTO person_pet VALUES(69,14,'1994-10-16 04:23:21.086862');
CREATE TABLE person (
id INTEGER,
first_name TEXT,
last_name TEXT,
age INTEGER,
dob DATETIME
);
INSERT INTO person VALUES(NULL,'Deer','Death',63,'1971-01-20 08:23:21.082947');
INSERT INTO person VALUES(NULL,'Cat','Dinosaurs',56,'1992-06-22 06:23:21.083348');
INSERT INTO person VALUES(NULL,'Distance','Comb',100,'1968-05-13 18:23:21.083387');
INSERT INTO person VALUES(NULL,'Cherry','Chain',80,'2012-01-07 04:23:21.083409');
INSERT INTO person VALUES(NULL,'Crow','Crate',22,'1999-07-16 07:23:21.083428');
INSERT INTO person VALUES(NULL,'Cart','Corn',60,'2010-08-14 23:23:21.083448');
INSERT INTO person VALUES(NULL,'Cup','Dirt',62,'1972-03-03 21:23:21.083469');
INSERT INTO person VALUES(NULL,'Duck','Chin',31,'1993-04-19 16:23:21.083490');
INSERT INTO person VALUES(NULL,'Duck','Crayon',52,'1979-09-23 10:23:21.083510');
INSERT INTO person VALUES(NULL,'Dirt','Crate',70,'1972-04-05 04:23:21.083530');
INSERT INTO person VALUES(NULL,'Cemetery','Dust',74,'1981-08-15 00:23:21.083549');
INSERT INTO person VALUES(NULL,'Dock','Curtain',79,'1972-03-08 11:23:21.083569');
INSERT INTO person VALUES(NULL,'Clock','Dinner',78,'2011-03-14 16:23:21.083589');
INSERT INTO person VALUES(NULL,'Chain','Digestion',21,'2006-02-24 15:23:21.083608');
INSERT INTO person VALUES(NULL,'Crow','Duck',61,'1991-11-10 19:23:21.083628');
INSERT INTO person VALUES(NULL,'Discussion','Discovery',35,'2009-10-22 07:23:21.083651');
INSERT INTO person VALUES(NULL,'Drawer','Doctor',58,'2011-05-21 19:23:21.083672');
INSERT INTO person VALUES(NULL,'Cow','Decision',57,'2009-06-21 10:23:21.083692');
INSERT INTO person VALUES(NULL,'Competition','Disgust',89,'1998-11-02 02:23:21.083712');
INSERT INTO person VALUES(NULL,'Development','Collar',64,'1972-11-19 16:23:21.083732');
INSERT INTO person VALUES(NULL,'Daughter','Crush',75,'1984-06-05 22:23:21.083752');
INSERT INTO person VALUES(NULL,'Dad','Control',24,'2010-02-05 12:23:21.083772');
INSERT INTO person VALUES(NULL,'Drug','Destruction',66,'1983-04-17 14:23:21.083792');
INSERT INTO person VALUES(NULL,'Drop','Company',46,'2002-09-03 11:23:21.083813');
INSERT INTO person VALUES(NULL,'Coach','Drug',100,'1985-02-17 15:23:21.083833');
INSERT INTO person VALUES(NULL,'Celery','Crib',86,'1977-06-17 14:23:21.083854');
INSERT INTO person VALUES(NULL,'Dress','Downtown',20,'1987-03-21 22:23:21.083874');
INSERT INTO person VALUES(NULL,'Dad','Day',57,'2002-04-03 23:23:21.083903');
INSERT INTO person VALUES(NULL,'Cushion','Detail',90,'1972-08-20 11:23:21.083924');
INSERT INTO person VALUES(NULL,'Cattle','Cemetery',35,'2005-11-30 06:23:21.083945');
INSERT INTO person VALUES(NULL,'Celery','Copper',62,'1987-08-27 18:23:21.083965');
INSERT INTO person VALUES(NULL,'Division','Cracker',71,'1988-10-19 01:23:21.083985');
INSERT INTO person VALUES(NULL,'Coach','Detail',86,'1969-06-23 08:23:21.084005');
INSERT INTO person VALUES(NULL,'Competition','Destruction',39,'1993-05-09 11:23:21.084025');
INSERT INTO person VALUES(NULL,'Company','Cent',31,'2000-10-21 23:23:21.084045');
INSERT INTO person VALUES(NULL,'Collar','Clam',19,'2014-11-25 08:23:21.084066');
INSERT INTO person VALUES(NULL,'Drawer','Crush',16,'1973-12-16 16:23:21.084086');
INSERT INTO person VALUES(NULL,'Death','Doll',4,'1972-07-19 16:23:21.084108');
INSERT INTO person VALUES(NULL,'Decision','Comfort',41,'1982-12-31 12:23:21.084128');
INSERT INTO person VALUES(NULL,'Dime','Design',49,'2004-11-22 17:23:21.084147');
INSERT INTO person VALUES(NULL,'Curtain','Daughter',28,'2012-04-03 12:23:21.084168');
INSERT INTO person VALUES(NULL,'Dime','Digestion',100,'1967-11-14 20:23:21.084187');
INSERT INTO person VALUES(NULL,'Crate','Direction',22,'2015-02-08 16:23:21.084207');
INSERT INTO person VALUES(NULL,'Desire','Dinosaurs',77,'1971-06-16 19:23:21.084227');
INSERT INTO person VALUES(NULL,'Day','Cave',49,'2015-09-15 00:23:21.084246');
INSERT INTO person VALUES(NULL,'Drop','Crate',23,'1982-05-17 15:23:21.084267');
INSERT INTO person VALUES(NULL,'Cattle','Cushion',14,'1988-02-20 12:23:21.084287');
INSERT INTO person VALUES(NULL,'Cover','Committee',65,'1999-12-30 04:23:21.084307');
INSERT INTO person VALUES(NULL,'Cherry','Corn',53,'1966-09-26 08:23:21.084327');
INSERT INTO person VALUES(NULL,'Carpenter','Club',40,'1973-09-01 16:23:21.084348');
INSERT INTO person VALUES(NULL,'Debt','Condition',74,'1967-10-21 07:23:21.084367');
INSERT INTO person VALUES(NULL,'Creature','Crown',98,'2016-04-16 03:23:21.084388');
INSERT INTO person VALUES(NULL,'Creator','Cause',46,'1969-09-10 06:23:21.084411');
INSERT INTO person VALUES(NULL,'Downtown','Donkey',22,'1971-04-16 16:23:21.084431');
INSERT INTO person VALUES(NULL,'Condition','Chair',9,'2003-10-31 07:23:21.084450');
INSERT INTO person VALUES(NULL,'Curve','Design',34,'1978-08-03 14:23:21.084471');
INSERT INTO person VALUES(NULL,'Deer','Copy',21,'2009-09-05 15:23:21.084492');
INSERT INTO person VALUES(NULL,'Creature','Company',10,'1996-04-07 17:23:21.084513');
INSERT INTO person VALUES(NULL,'Curtain','Carpenter',40,'1985-07-09 07:23:21.084532');
INSERT INTO person VALUES(NULL,'Circle','Destruction',77,'2012-10-13 08:23:21.084553');
INSERT INTO person VALUES(NULL,'Direction','Day',17,'1969-07-18 19:23:21.084573');
INSERT INTO person VALUES(NULL,'Disgust','Creature',53,'1974-08-26 21:23:21.084593');
INSERT INTO person VALUES(NULL,'Country','Dust',73,'1991-03-27 08:23:21.084613');
INSERT INTO person VALUES(NULL,'Doll','Card',59,'1984-08-28 04:23:21.084633');
INSERT INTO person VALUES(NULL,'Design','Clock',73,'1984-05-09 00:23:21.084653');
INSERT INTO person VALUES(NULL,'Cherry','Cloth',53,'1983-10-25 07:23:21.084673');
INSERT INTO person VALUES(NULL,'Destruction','Dinosaurs',98,'1974-02-09 01:23:21.084692');
INSERT INTO person VALUES(NULL,'Cloud','Development',14,'2012-03-01 15:23:21.084713');
INSERT INTO person VALUES(NULL,'Coach','Cobweb',60,'2011-07-21 23:23:21.084733');
INSERT INTO person VALUES(NULL,'Comfort','Death',38,'1978-12-08 20:23:21.084753');
INSERT INTO person VALUES(NULL,'Door','Destruction',11,'1980-04-02 05:23:21.084773');
INSERT INTO person VALUES(NULL,'Crib','Donkey',84,'2005-05-06 19:23:21.084794');
INSERT INTO person VALUES(NULL,'Discovery','Cave',28,'1999-03-02 10:23:21.084813');
INSERT INTO person VALUES(NULL,'Connection','Cattle',84,'1997-01-26 18:23:21.084833');
INSERT INTO person VALUES(NULL,'Cent','Carriage',27,'1982-01-11 07:23:21.084853');
INSERT INTO person VALUES(NULL,'Coach','Coil',84,'1967-11-18 19:23:21.084875');
INSERT INTO person VALUES(NULL,'Card','Dime',26,'1981-04-26 13:23:21.084894');
INSERT INTO person VALUES(NULL,'Cork','Desire',62,'2014-03-15 08:23:21.084915');
INSERT INTO person VALUES(NULL,'Cream','Distance',44,'2014-02-04 16:23:21.084935');
INSERT INTO person VALUES(NULL,'Driving','Church',12,'1981-01-18 01:23:21.084957');
INSERT INTO person VALUES(NULL,'Crowd','Control',26,'2009-02-16 02:23:21.084976');
INSERT INTO person VALUES(NULL,'Celery','Dinner',98,'2014-07-15 19:23:21.084996');
INSERT INTO person VALUES(NULL,'Cheese','Clover',70,'1968-11-03 21:23:21.085016');
INSERT INTO person VALUES(NULL,'Drink','Crown',22,'2009-06-09 14:23:21.085036');
INSERT INTO person VALUES(NULL,'Cent','Death',20,'2010-05-07 15:23:21.085056');
INSERT INTO person VALUES(NULL,'Comfort','Cave',27,'1976-05-13 01:23:21.085076');
INSERT INTO person VALUES(NULL,'Dinosaurs','Cart',21,'1976-04-02 21:23:21.085097');
INSERT INTO person VALUES(NULL,'Circle','Destruction',32,'2005-11-18 08:23:21.085116');
INSERT INTO person VALUES(NULL,'Door','Distribution',15,'2009-01-15 20:23:21.085136');
INSERT INTO person VALUES(NULL,'Children','Chess',38,'1977-01-24 01:23:21.085157');
INSERT INTO person VALUES(NULL,'Distribution','Card',47,'1995-12-11 18:23:21.085180');
INSERT INTO person VALUES(NULL,'Crack','Cast',6,'1986-12-06 07:23:21.085200');
INSERT INTO person VALUES(NULL,'Door','Drop',74,'1981-06-01 08:23:21.085220');
INSERT INTO person VALUES(NULL,'Connection','Cheese',80,'1972-01-07 04:23:21.085239');
INSERT INTO person VALUES(NULL,'Circle','Dime',72,'2001-06-13 10:23:21.085260');
INSERT INTO person VALUES(NULL,'Dog','Drug',86,'1967-04-15 07:23:21.085281');
INSERT INTO person VALUES(NULL,'Cook','Desire',54,'1968-04-17 14:23:21.085301');
INSERT INTO person VALUES(NULL,'Dock','Disease',55,'2002-08-18 18:23:21.085321');
INSERT INTO person VALUES(NULL,'Dinosaurs','Curtain',95,'1972-01-23 05:23:21.085341');
INSERT INTO person VALUES(NULL,'Church','Division',44,'2004-01-15 05:23:21.085361');
CREATE TABLE pet (
id INTEGER,
name TEXT,
breed TEXT,
age INTEGER,
dead BOOLEAN
);
INSERT INTO pet VALUES(NULL,'Boot','Fish',19,1);
INSERT INTO pet VALUES(NULL,'Attack','Dog',3,1);
INSERT INTO pet VALUES(NULL,'Burst','Fish',2,1);
INSERT INTO pet VALUES(NULL,'Bag','Fish',2,1);
INSERT INTO pet VALUES(NULL,'Border','Unicorn',1,1);
INSERT INTO pet VALUES(NULL,'Ant','Fish',7,0);
INSERT INTO pet VALUES(NULL,'Bottle','Unicorn',3,0);
INSERT INTO pet VALUES(NULL,'Bit','Cat',1,1);
INSERT INTO pet VALUES(NULL,'Bath','Dog',9,1);
INSERT INTO pet VALUES(NULL,'Bucket','Unicorn',9,0);
INSERT INTO pet VALUES(NULL,'Ant','Cat',19,1);
INSERT INTO pet VALUES(NULL,'Apparatus','Dog',2,0);
INSERT INTO pet VALUES(NULL,'Bath','Dog',4,0);
INSERT INTO pet VALUES(NULL,'Back','Dog',14,1);
INSERT INTO pet VALUES(NULL,'Basket','Dog',10,1);
INSERT INTO pet VALUES(NULL,'Actor','Fish',8,1);
INSERT INTO pet VALUES(NULL,'Basket','Unicorn',7,0);
INSERT INTO pet VALUES(NULL,'Aunt','Cat',11,0);
INSERT INTO pet VALUES(NULL,'Boat','Cat',16,1);
INSERT INTO pet VALUES(NULL,'Aftermath','Dog',1,1);
INSERT INTO pet VALUES(NULL,'Brick','Cat',16,1);
INSERT INTO pet VALUES(NULL,'Amusement','Unicorn',5,1);
INSERT INTO pet VALUES(NULL,'Book','Cat',17,0);
INSERT INTO pet VALUES(NULL,'Building','Fish',3,1);
INSERT INTO pet VALUES(NULL,'Arithmetic','Cat',14,1);
INSERT INTO pet VALUES(NULL,'Beef','Cat',14,0);
INSERT INTO pet VALUES(NULL,'Blood','Fish',17,1);
INSERT INTO pet VALUES(NULL,'Bee','Unicorn',4,1);
INSERT INTO pet VALUES(NULL,'Cap','Unicorn',3,1);
INSERT INTO pet VALUES(NULL,'Body','Unicorn',10,1);
INSERT INTO pet VALUES(NULL,'Brush','Dog',14,0);
INSERT INTO pet VALUES(NULL,'Canvas','Fish',9,0);
INSERT INTO pet VALUES(NULL,'Bread','Unicorn',11,1);
INSERT INTO pet VALUES(NULL,'Baby','Cat',4,0);
INSERT INTO pet VALUES(NULL,'Animal','Unicorn',13,1);
INSERT INTO pet VALUES(NULL,'Argument','Dog',7,0);
INSERT INTO pet VALUES(NULL,'Balance','Cat',5,1);
INSERT INTO pet VALUES(NULL,'Brush','Unicorn',0,1);
INSERT INTO pet VALUES(NULL,'Bucket','Dog',3,1);
INSERT INTO pet VALUES(NULL,'Apparel','Unicorn',11,1);
INSERT INTO pet VALUES(NULL,'Bite','Fish',3,1);
INSERT INTO pet VALUES(NULL,'Attempt','Cat',17,0);
INSERT INTO pet VALUES(NULL,'Box','Fish',2,1);
INSERT INTO pet VALUES(NULL,'Apparatus','Unicorn',18,0);
INSERT INTO pet VALUES(NULL,'Advertisement','Fish',18,1);
INSERT INTO pet VALUES(NULL,'Apparel','Dog',8,0);
INSERT INTO pet VALUES(NULL,'Burn','Dog',15,0);
INSERT INTO pet VALUES(NULL,'Approval','Unicorn',9,0);
INSERT INTO pet VALUES(NULL,'Ball','Unicorn',3,0);
INSERT INTO pet VALUES(NULL,'Cabbage','Dog',13,0);
INSERT INTO pet VALUES(NULL,'Balance','Fish',3,0);
INSERT INTO pet VALUES(NULL,'Advertisement','Fish',15,1);
INSERT INTO pet VALUES(NULL,'Birth','Unicorn',7,0);
INSERT INTO pet VALUES(NULL,'Bottle','Cat',6,1);
INSERT INTO pet VALUES(NULL,'Brass','Cat',7,0);
INSERT INTO pet VALUES(NULL,'Adjustment','Dog',5,0);
INSERT INTO pet VALUES(NULL,'Angle','Cat',7,0);
INSERT INTO pet VALUES(NULL,'Back','Fish',5,1);
INSERT INTO pet VALUES(NULL,'Brake','Cat',13,1);
INSERT INTO pet VALUES(NULL,'Brick','Dog',7,0);
INSERT INTO pet VALUES(NULL,'Argument','Fish',2,1);
INSERT INTO pet VALUES(NULL,'Bird','Cat',0,1);
INSERT INTO pet VALUES(NULL,'Cable','Cat',9,0);
INSERT INTO pet VALUES(NULL,'Camera','Fish',0,1);
INSERT INTO pet VALUES(NULL,'Bead','Cat',11,0);
INSERT INTO pet VALUES(NULL,'Camp','Cat',3,1);
INSERT INTO pet VALUES(NULL,'Argument','Dog',14,0);
INSERT INTO pet VALUES(NULL,'Aftermath','Cat',10,1);
INSERT INTO pet VALUES(NULL,'Balloon','Unicorn',11,0);
INSERT INTO pet VALUES(NULL,'Bridge','Dog',3,0);
INSERT INTO pet VALUES(NULL,'Attention','Cat',7,1);
INSERT INTO pet VALUES(NULL,'Attempt','Unicorn',11,1);
INSERT INTO pet VALUES(NULL,'Bee','Cat',18,1);
INSERT INTO pet VALUES(NULL,'Advice','Dog',10,0);
INSERT INTO pet VALUES(NULL,'Baseball','Cat',11,0);
INSERT INTO pet VALUES(NULL,'Badge','Unicorn',6,1);
INSERT INTO pet VALUES(NULL,'Attraction','Cat',12,1);
INSERT INTO pet VALUES(NULL,'Amusement','Fish',6,0);
INSERT INTO pet VALUES(NULL,'Bubble','Unicorn',19,0);
INSERT INTO pet VALUES(NULL,'Apparatus','Cat',7,0);
INSERT INTO pet VALUES(NULL,'Burst','Fish',17,1);
INSERT INTO pet VALUES(NULL,'Bear','Cat',0,1);
INSERT INTO pet VALUES(NULL,'Beggar','Fish',14,1);
INSERT INTO pet VALUES(NULL,'Belief','Cat',14,1);
INSERT INTO pet VALUES(NULL,'Answer','Unicorn',14,0);
INSERT INTO pet VALUES(NULL,'Bubble','Cat',3,1);
INSERT INTO pet VALUES(NULL,'Bulb','Cat',5,0);
INSERT INTO pet VALUES(NULL,'Arch','Dog',8,0);
INSERT INTO pet VALUES(NULL,'Box','Dog',7,0);
INSERT INTO pet VALUES(NULL,'Bear','Fish',19,1);
INSERT INTO pet VALUES(NULL,'Cactus','Fish',16,1);
INSERT INTO pet VALUES(NULL,'Bread','Unicorn',5,1);
INSERT INTO pet VALUES(NULL,'Body','Fish',19,1);
INSERT INTO pet VALUES(NULL,'Bait','Dog',4,1);
INSERT INTO pet VALUES(NULL,'Cactus','Unicorn',3,0);
INSERT INTO pet VALUES(NULL,'Business','Dog',13,0);
INSERT INTO pet VALUES(NULL,'Belief','Cat',0,0);
INSERT INTO pet VALUES(NULL,'Amount','Cat',9,1);
INSERT INTO pet VALUES(NULL,'Burst','Unicorn',10,0);
INSERT INTO pet VALUES(NULL,'Bag','Cat',20,0);

zedshaw · January 7, 2019, 2:47am

Alright! Score. So what do you notice about these three lines:

INSERT INTO pet VALUES(NULL,'Bag','Cat',20,0);
INSERT INTO person VALUES(NULL,'Church','Division',44,'2004-01-15 05:23:21.085361');
INSERT INTO person_pet VALUES(69,14,'1994-10-16 04:23:21.086862');

Let’s see, 69, 14, NULL, NULL, 44, 20, 0.

See something wrong there? You have NULL instead if IDs for person and pet.

Now, I’m going to request something else: When you come do a bug report like this trim it down for everyone, and include all of the information. So, in your first message you would have done better reporting to include that .dump but with only 1 row per table to show that it has contents. You should also list out all the things you tried.

This will actually help you see the problem yourself in the future.

mechmiel · January 13, 2019, 6:18pm

Yes, I agree - I have NULLs instead of IDs - I was trying to communicate that fact in the previous posts, that I think this is the cause of the issue. But I still don’t understand why do I have those NULLS? I mean - in the video you show, all select actions work smooth - didn’t you have NULLS as well? I thought it’s the same file we’re working on .

zedshaw · January 15, 2019, 8:22pm

I bet there’s something wrong with how you created the tables so that the id is not autoincrement integers. Double check how you created it.

mechmiel · January 21, 2019, 10:56pm

Below are my tables, can you see what might be wrong with them?
eh… this is really confusing

sqlite> .schema
CREATE TABLE person_pet (
person_id INTEGER,
pet_id INTEGER,
purchased_on DATETIME
);
CREATE TABLE person (
id INTEGER,
first_name TEXT,
last_name TEXT,
age INTEGER,
dob DATETIME
);
CREATE TABLE pet (
id INTEGER,
name TEXT,
breed TEXT,
age INTEGER,
dead BOOLEAN
);

Is there any specific command that I shoud use to turn on the autoincrementation of integers that you mentioned?

mechmiel · January 23, 2019, 9:34pm

any idea? I’m stuck at this point…

zedshaw · January 25, 2019, 6:52pm

Ok, so it works in my book right? I mean, I’m doing it in the videos and everything is working for me, so the first thing is for you to find out what’s different about your code and mine. Let’s go back to Ex1:

https://shop.learncodethehardway.org/paid/sql/ex1.html

CREATE TABLE person (
    id INTEGER PRIMARY KEY,
    first_name TEXT,
    last_name TEXT,
    age INTEGER
);

What’s different?