SQLite Forum

SELECT question
Login
I couldn't find an email for you so posting the .sql here.

<pre>
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE IF NOT EXISTS "docs" (
id integer not null primary key autoincrement,
authorid integer,
docid integer,
title text,
source text,
pubyear integer,
notes text,
author text,
filename text);
INSERT INTO docs VALUES(1,501,1,'Ocean Viruses','Quanta Magazine',2019,NULL,'Lambert,Jonathan','Ocean Viruses.pdf');
INSERT INTO docs VALUES(2,649,1,'20 Things You Didn''t Know About Glaciers','Discover',2020,NULL,'Keats,Jonathan','20 Things You Didn''t Know About Glaciers.pdf');
INSERT INTO docs VALUES(3,650,1,'Crystal Colony','Discover',2020,NULL,'Walter,Jennifer','Crystal Colony.pdf');
INSERT INTO docs VALUES(4,651,1,'Smarter Cities','MIT Technology Review',2020,NULL,'Yang,Lynne Pesko','Smarter Cities.pdf');
INSERT INTO docs VALUES(5,652,1,'Scientist in Toyland','Discover',2020,NULL,'Ornes,Stephen','Scientist in Toyland.pdf');
INSERT INTO docs VALUES(6,502,1,'The Sixth Extinction','Canadian Geographic',2020,NULL,'Anthony,Leslie;Haasdyk,Mary','The Sixth Extinction.pdf');
INSERT INTO docs VALUES(20,653,1,'Engineering A Culture of Privacy','IEEE Consumer Electronics',2020,'','Kosmala,Peter B.','Engineering A Culture of Privacy.pdf');
INSERT INTO docs VALUES(26,654,1,'All Puffed Up','www.admin-magazine.com',2020,NULL,'Hein,Matthias','All Puffed Up.pdf');
INSERT INTO docs VALUES(27,503,1,'Move','New Scientist',2020,NULL,'Williams,Caroline','Move.pdf');
INSERT INTO docs VALUES(28,656,1,'Covid-19''s AI Revolution','New Scientist',2020,NULL,'Ong,Sandy','Covid-19''s AI Revolution.pdf');
INSERT INTO docs VALUES(29,657,1,'DDoS State Of Affairs','IEEE Computer',2020,NULL,'Osterweil,Eric;Stavrou,Angelos;Zhang,Lixia','DDoS State Of Affairs.pdf');
INSERT INTO docs VALUES(30,671,1,'Double-edged Sword','www.admin-magazine.com',2020,NULL,'Gerling,Rainer W.','Double-edged Sword.pdf');
INSERT INTO docs VALUES(31,999,1,'Evolving Evolution','New Scientist',2020,NULL,'Various','Evolving Evolution.pdf');
INSERT INTO docs VALUES(32,660,1,'Feeling Queasy','New Scientist',2020,NULL,'Thompson,Helen','Feeling Queasy.pdf');
INSERT INTO docs VALUES(33,654,2,'Fleet of Foot','www.admin-magazine.com',2020,NULL,'Hein,Matthias','Fleet of Foot.pdf');
INSERT INTO docs VALUES(34,504,1,'Just A Second','New Scientist',2020,NULL,'Nuwer,Rachel','Just A Second.pdf');
INSERT INTO docs VALUES(35,507,1,'New Top in Town','www.admin-magazine.com',2020,NULL,'Layton,Jeff','New Top in Town.pdf');
INSERT INTO docs VALUES(36,672,1,'Pass The Test','www.admin-magazine.com',2020,NULL,'Wubbeling,Matthias','Pass The Test.pdf');
INSERT INTO docs VALUES(37,658,1,'Relationship Status','www.admin-magazine.com',2020,NULL,'Dodlek,Zeljko','Relationship Status.pdf');
INSERT INTO docs VALUES(38,659,1,'Relationships','www.admin-magazine.com',2020,NULL,'Brendel,Jens-Christoph','Relationships.pdf');
INSERT INTO docs VALUES(39,670,1,'The Ultimate Battery','New Scientist',2020,NULL,'Hambling,David','The Ultimate Battery.pdf');
INSERT INTO docs VALUES(40,1000,1,'The Universe According to Emmy Noether','Discover 2017-06',2017,'','Nadis,Steve','The Universe According to Emmy Noether.pdf');
INSERT INTO docs VALUES(41,1001,1,'A Profound Plan To Save the Seas','Science 2020-10',2020,NULL,'Hannibal,Mary Ellen','A Profound Plan.pdf');
INSERT INTO docs VALUES(42,1002,1,'A Technology of Kinetic Art','Scientific American 1993-02',1993,NULL,'Rickey,George','A Technology of Kinetic Art.pdf');
INSERT INTO docs VALUES(43,1003,1,'Context Is Key to Olfaction','Science 2020-07',2020,NULL,'McGann,John P.','Context Is Key to Olfaction.pdf');
INSERT INTO docs VALUES(44,1004,1,'Lizard Man','Science 2020-07',2020,NULL,'Pennisi,Elizabeth','Lizard Man.pdf');
INSERT INTO docs VALUES(45,1005,1,'Sean Connery','Scotland Magazine 2021-01',2021,NULL,'Rowe,Jenny;Adolph,Anthony','Sean Connery.pdf');
INSERT INTO docs VALUES(46,1006,1,'The Talented Luthier','Scotland Magazine 2021-01',2021,NULL,'Gilchrist,Jim','The Talented Luthier.pdf');
INSERT INTO docs VALUES(47,1007,1,'Alphabet''s Now Building Cities','Wired 2018-01-02',2018,NULL,'Wright,Emily','Alphabet Cities.pdf');
INSERT INTO docs VALUES(48,1008,1,'A Partly True Story','Scientific American 1993-02',1993,NULL,'Stewart,Ian','Partly True Story.pdf');
INSERT INTO docs VALUES(49,1009,1,'Redeeming Charles Babbage''s Mechanical Computer','Scientific American 1993-02',1993,NULL,'Swade,Doron D.','Babbage.pdf');
CREATE TABLE authors (
id integer not null primary key autoincrement,
author text,
authorid integer,
docid integer);
INSERT INTO authors VALUES(1,'Lambert,Jonathan',501,1);
INSERT INTO authors VALUES(2,'Keats,Jonathan',649,1);
INSERT INTO authors VALUES(3,'Walter,Jennifer',650,1);
INSERT INTO authors VALUES(4,'Yang,Lynne Pesko',651,1);
INSERT INTO authors VALUES(5,'Ornes,Stephen',652,1);
INSERT INTO authors VALUES(6,'Anthony,Leslie;Haasdyk,Mary',502,1);
INSERT INTO authors VALUES(20,'Kosmala,Peter B.',653,NULL);
INSERT INTO authors VALUES(26,'Hein,Matthias',654,1);
INSERT INTO authors VALUES(28,'Ong,Sandy',656,1);
INSERT INTO authors VALUES(29,'Osterweil,Eric;Stavrou,Angelos;Zhang,Lixia',657,1);
INSERT INTO authors VALUES(30,'Gerling,Rainer W.',671,1);
INSERT INTO authors VALUES(32,'Thompson,Helen',660,1);
INSERT INTO authors VALUES(34,'Nuwer,Rachel',504,1);
INSERT INTO authors VALUES(35,'Layton,Jeff',507,1);
INSERT INTO authors VALUES(36,'Wubbeling,Matthias',672,1);
INSERT INTO authors VALUES(37,'Dodlek,Zeljko',658,1);
INSERT INTO authors VALUES(38,'Brendel,Jens-Christoph',659,1);
INSERT INTO authors VALUES(39,'Hambling,David',670,1);
INSERT INTO authors VALUES(40,'Williams,Caroline',503,1);
INSERT INTO authors VALUES(42,'Various',999,1);
INSERT INTO authors VALUES(43,'Nadis,Steve',1000,NULL);
INSERT INTO authors VALUES(44,'Hannibal,Mary Ellen',1001,1);
INSERT INTO authors VALUES(45,'Rickey,George',1002,1);
INSERT INTO authors VALUES(46,'McGann,John P.',1003,1);
INSERT INTO authors VALUES(47,'Pennisi,Elizabeth',1004,1);
INSERT INTO authors VALUES(48,'Rowe,Jenny;Adolph,Anthony',1005,1);
INSERT INTO authors VALUES(49,'Gilchrist,Jim',1006,1);
INSERT INTO authors VALUES(50,'Wright,Emily',1007,1);
INSERT INTO authors VALUES(51,'Stewart,Ian',1008,1);
INSERT INTO authors VALUES(52,'Swade,Doron D.',1009,1);
CREATE TABLE keywords (
id integer not null primary key autoincrement,
keyword text,
authorid integer,
docid integer);
INSERT INTO keywords VALUES(1,'Marine Virus',501,1);
INSERT INTO keywords VALUES(2,'Oceanography',501,1);
INSERT INTO keywords VALUES(3,'Biodiversity',649,1);
INSERT INTO keywords VALUES(4,'Glaciers',649,1);
INSERT INTO keywords VALUES(5,'Geology',649,1);
INSERT INTO keywords VALUES(6,'Climate',649,1);
INSERT INTO keywords VALUES(7,'Bees',650,1);
INSERT INTO keywords VALUES(8,'Mathematics',650,1);
INSERT INTO keywords VALUES(9,'Computer Networks',651,1);
INSERT INTO keywords VALUES(10,'Urban Development',651,1);
INSERT INTO keywords VALUES(11,'Geometry',652,1);
INSERT INTO keywords VALUES(12,'Architecture',652,1);
INSERT INTO keywords VALUES(13,'Kinematic Sculpture',652,1);
INSERT INTO keywords VALUES(14,'Species Conservation',502,1);
INSERT INTO keywords VALUES(27,'Computer Networks',657,1);
INSERT INTO keywords VALUES(28,'DNS Encryption',671,1);
INSERT INTO keywords VALUES(29,'Network Security',654,1);
INSERT INTO keywords VALUES(30,'Network Privacy',653,1);
INSERT INTO keywords VALUES(31,'Computer Networks',671,1);
INSERT INTO keywords VALUES(32,'Computer Networks',654,1);
INSERT INTO keywords VALUES(33,'Network Security',657,1);
INSERT INTO keywords VALUES(34,'Artificial Intelligence',656,1);
INSERT INTO keywords VALUES(35,'Coronavirus Pandemic',656,1);
INSERT INTO keywords VALUES(36,'Human Vestibular System',503,1);
INSERT INTO keywords VALUES(37,'Human Cognitive Behavior',503,1);
INSERT INTO keywords VALUES(38,'TCP/IP Maintenance',654,1);
INSERT INTO keywords VALUES(39,'TCP/IP Bufferbloat',654,1);
INSERT INTO keywords VALUES(40,'DNS Maintenance',671,1);
INSERT INTO keywords VALUES(41,'Evolution',999,1);
INSERT INTO keywords VALUES(42,'Battery Power',670,1);
INSERT INTO keywords VALUES(43,'Energy Conservation',504,1);
INSERT INTO keywords VALUES(44,'Nuclear Power',670,1);
INSERT INTO keywords VALUES(45,'Regular Expressions',672,1);
INSERT INTO keywords VALUES(46,'Programming',672,1);
INSERT INTO keywords VALUES(47,'Shell Programming',507,1);
INSERT INTO keywords VALUES(48,'Bash Shell',507,1);
INSERT INTO keywords VALUES(49,'Horology',504,1);
INSERT INTO keywords VALUES(50,'Climate',502,1);
INSERT INTO keywords VALUES(51,'Biodiversity',502,1);
INSERT INTO keywords VALUES(52,'Wi-Fi Networking',654,2);
INSERT INTO keywords VALUES(53,'Database Design',658,1);
INSERT INTO keywords VALUES(54,'Graph Database',658,1);
INSERT INTO keywords VALUES(55,'Database Design',659,1);
INSERT INTO keywords VALUES(56,'Graph Database',659,1);
INSERT INTO keywords VALUES(57,'Mathematics',1000,1);
INSERT INTO keywords VALUES(58,'Physics',1000,1);
INSERT INTO keywords VALUES(59,'Marine Conservation',1001,1);
INSERT INTO keywords VALUES(60,'Oceanography',1001,1);
INSERT INTO keywords VALUES(61,'Art',1002,1);
INSERT INTO keywords VALUES(62,'Kinematics',1002,1);
INSERT INTO keywords VALUES(63,'Physics',1002,1);
INSERT INTO keywords VALUES(64,'Olfaction',1003,1);
INSERT INTO keywords VALUES(65,'Neuroscience',1003,1);
INSERT INTO keywords VALUES(66,'Smell',1003,1);
INSERT INTO keywords VALUES(67,'Evolution',1004,1);
INSERT INTO keywords VALUES(68,'Lizards',1004,1);
INSERT INTO keywords VALUES(69,'Sean Connery',1005,1);
INSERT INTO keywords VALUES(70,'Biography',1005,1);
INSERT INTO keywords VALUES(71,'Scottish Ancestry',1005,1);
INSERT INTO keywords VALUES(72,'Music - Violin',1006,1);
INSERT INTO keywords VALUES(73,'Luthier',1006,1);
INSERT INTO keywords VALUES(74,'Art',1006,1);
INSERT INTO keywords VALUES(75,'Human Vestibular System',660,1);
INSERT INTO keywords VALUES(76,'Human Balance',660,1);
INSERT INTO keywords VALUES(77,'Motion Sickness',660,1);
INSERT INTO keywords VALUES(78,'Smart City',1007,1);
INSERT INTO keywords VALUES(79,'Urban Design',1007,1);
INSERT INTO keywords VALUES(80,'Mathematics',1008,1);
INSERT INTO keywords VALUES(81,'Fuzzy Logic',1008,1);
INSERT INTO keywords VALUES(82,'Computer Technology',1009,1);
INSERT INTO keywords VALUES(83,'Engineering',1009,1);
INSERT INTO keywords VALUES(84,'Difference Engine',1009,1);
DELETE FROM sqlite_sequence;
INSERT INTO sqlite_sequence VALUES('docs',49);
INSERT INTO sqlite_sequence VALUES('authors',52);
INSERT INTO sqlite_sequence VALUES('keywords',84);
COMMIT;
</pre>