[ Date Index ] [ Thread Index ] [ <= Previous by date / thread ] [ Next by date / thread => ]
Dear all, I have been following this excellent video tutorial for learning SQL: https://www.youtube.com/playlist?list=PLi01XoE8jYojRqM4qGBF1U90Ee1Ecb5tt The tutorial series uses PostgreSQL with pgAdmin. In this particular video, https://youtu.be/PkJKzR_sClM https://github.com/socratica/data/blob/master/earthquake.csv the presenter explains how to count the number of earthquakes recorded in the data caused by natural circumstances with this query: > SELECT COUNT(*) FROM earthquake WHERE cause = 'earthquake'; Her database returns 22942 as might be expected. When I run this query, I get 0... :/ I have listed below some details about how I imported the data and which database system I am using. Help would be most appreciated, as I am learning SQL not only for fun but also for my upcoming A Level exam, and I would like to know why there is this discrepancy between my database and the tutorial. My database is a locally hosted installation of MariaDB 10.3.22, on Fedora 31. My SQL client is mycli 1.21.1. I created the table with this: > create table earthquakes ( > -> earthquake_id integer NOT NULL, > -> occured_on datetime, > -> latitude numeric(10,5), > -> longitude numeric(10,5), > -> depth numeric(10,2), > -> magnitude numeric, > -> calculation_method text(512), > -> network_id text(512), > -> place text (512), > -> cause text (512), > -> CONSTRAINT earthquake_pkey PRIMARY KEY (earthquake_ID)); And imported the data like this: > LOAD DATA local infile '/tmp/earthquake.csv' into table earthquakes fields > terminated by ',' enclosed by '"' lines terminated by '\n'ignore 1 rows; Please ignore the inconsistent capitalisation! :D All the other queries explained in the series so far have worked, for instance: > SELECT MIN(occured_on), MAX(occured_on) FROM earthquakes; +---------------------+---------------------+ | MIN(occured_on) | MAX(occured_on) | +---------------------+---------------------+ | 1969-01-01 09:07:06 | 2018-12-31 02:35:38 | +---------------------+---------------------+ There are minor differences here and there, for instance I imported the magnitude as an integer rather than a decimal number. However, I cannot explain the refusal of my database to query anything useful with the earthquakes field, despite this field working fine ordinarily: > SELECT occured_on, magnitude, place, cause FROM earthquakes ORDER BY magnitude > DESC LIMIT 3; +---------------------+-----------+----------------------------------------+------------+ | occured_on | magnitude | place | cause | +---------------------+-----------+----------------------------------------+------------+ | 2005-03-28 16:09:37 | 9 | northern Sumatra, Indonesia | earthquake | | 2004-12-26 00:58:53 | 9 | off the west coast of northern Sumatra | earthquake | | 2010-02-27 06:34:12 | 9 | offshore Bio-Bio, Chile | earthquake | +---------------------+-----------+----------------------------------------+------------+ I look forward to seeing what everyone can come up with. Best wishes and many thanks, Sebastian Freenode: 'seabass' -- The Mailing List for the Devon & Cornwall LUG https://mailman.dcglug.org.uk/listinfo/list FAQ: http://www.dcglug.org.uk/listfaq