0

Problem link: https://cs50.harvard.edu/sql/2023/psets/3/meteorites/

As the problem mentioned, any empty values in meteorites.csv are represented by NULL in the meteorites table.

When I run the sqlite3 on the terminal, I can see the NULL value shown on the table

sqlite> select * from meteorites where year is not NULL limit 50;
+-----+-------------------+-------------------+------------+-----------+------+--------+---------+
| id  |       name        |       class       |    mass    | discovery | year |  lat   |  long   |
+-----+-------------------+-------------------+------------+-----------+------+--------+---------+
| 291 | Nogata            | L6                | 472.0      | Fell      | 860  | 33.73  | 130.75  |
| 292 | Narni             | Stone-uncl        | NULL       | Fell      | 920  | 42.52  | 12.52   |
| 293 | Elbogen           | Iron, IID         | 107000.0   | Fell      | 1399 | 50.18  | 12.73   |
| 294 | Rivolta de Bassi  | Stone-uncl        | 103.3      | Fell      | 1490 | 45.48  | 9.52    |
| 295 | Ensisheim         | LL6               | 127000.0   | Fell      | 1491 | 47.87  | 7.35    |
| 296 | Valdinoce         | Stone-uncl        | NULL       | Fell      | 1495 | 44.07  | 12.1    |
| 297 | Oliva-Gandia      | Stone-uncl        | NULL       | Fell      | 1519 | 39.0   | -0.03   |
| 298 | Campo del Cielo   | Iron, IAB-MG      | 50000000.0 | Found     | 1575 | -27.47 | -60.58  |
| 299 | Castrovillari     | Stone-uncl        | 15000.0    | Fell      | 1583 | 39.8   | 16.2    |
| 300 | Morito            | Iron, IIIAB       | 10100000.0 | Found     | 1600 | 27.05  | -105.43 |
| 301 | Jalandhar         | Iron              | 1967.0     | Fell      | 1621 | 31.0   | 75.0    |
| 302 | Stretchleigh      | Stone-uncl        | 10400.0    | Fell      | 1623 | 50.38  | -3.95   |
| 303 | Hatford           | Stone-uncl        | 29000.0    | Fell      | 1628 | 51.65  | -1.52   |
| 304 | Minamino          | L                 | 1040.0     | Fell      | 1632 | 35.08  | 136.93  |
| 305 | Sagan             | Stone-uncl        | NULL       | Fell      | 1636 | 51.53  | 14.88   |
| 306 | Mount Vaisi       | Stone-uncl        | 17000.0    | Fell      | 1637 | 44.08  | 6.87    |
| 307 | Stolzenau         | Stone-uncl        | NULL       | Fell      | 1647 | 52.53  | 9.05    |
| 308 | Fünen             | Stone-uncl        | NULL       | Fell      | 1654 | 55.33  | 10.33   |
| 309 | Novy-Ergi         | Stone-uncl        | NULL       | Fell      | 1662 | 58.55  | 31.33   |
| 310 | Vago              | H6                | 40.0       | Fell      | 1668 | 45.42  | 11.13   |
| 311 | Ortenau           | Stone-uncl        | 4500.0     | Fell      | 1671 | 48.5   | 8.0     |
| 312 | Sasagase          | H                 | 695.0      | Fell      | 1688 | 34.72  | 137.78  |
| 313 | Barcelona (stone) | OC                | NULL       | Fell      | 1704 | 41.37  | 2.17    |
| 314 | Schellin          | L                 | 7000.0     | Fell      | 1715 | 53.35  | 15.05   |
| 315 | Siratik           | Iron, IIAB        | 1700.0     | Found     | 1716 | 14.0   | -11.0   |
| 316 | Ploschkovitz      | L5                | 39.0       | Fell      | 1723 | 50.53  | 14.12   |
| 317 | Steinbach         | Iron, IVA-an      | 98000.0    | Found     | 1724 | 50.5   | 12.5    |
| 318 | Rasgrad           | Stone-uncl        | 24700.0    | Fell      | 1740 | 43.5   | 26.53   |
| 319 | Ogi               | H6                | 14360.0    | Fell      | 1741 | 33.28  | 130.2   |
| 320 | Krasnojarsk       | Pallasite, PMG-an | 700000.0   | Found     | 1749 | 54.9   | 91.8    |
| 321 | Nicorps           | Stone-uncl        | NULL       | Fell      | 1750 | 49.03  | -1.43   |
| 322 | Hraschina         | Iron, IID         | 49000.0    | Fell      | 1751 | 46.1   | 16.33   |
| 323 | Luponnas          | H3-5              | 14000.0    | Fell      | 1753 | 46.22  | 5.0     |
| 324 | Tabor             | H5                | 7540.0     | Fell      | 1753 | 49.4   | 14.65   |
| 325 | Albareto          | L/LL4             | 2000.0     | Fell      | 1766 | 44.65  | 11.02   |
| 326 | Lucé              | L6                | 3500.0     | Fell      | 1768 | 47.85  | 0.48    |
| 327 | Mauerkirchen      | L6                | 19000.0    | Fell      | 1768 | 48.18  | 13.13   |
| 328 | Aire-sur-la-Lys   | Unknown           | NULL       | Fell      | 1769 | 50.67  | 2.33    |
| 329 | Sena              | H4                | 4000.0     | Fell      | 1773 | 41.72  | -0.05   |
| 330 | Owrucz            | OC                | NULL       | Fell      | 1775 | 51.33  | 28.83   |
| 331 | Rodach            | Stone-uncl        | 2900.0     | Fell      | 1775 | 50.35  | 10.8    |
| 332 | Toluca            | Iron, IAB-sLL     | 3000000.0  | Found     | 1776 | 19.57  | -99.57  |
| 333 | Pettiswood        | Stone-uncl        | NULL       | Fell      | 1779 | 53.53  | -7.33   |
| 334 | Longchang         | Iron, IVA-an      | 158500.0   | Found     | 1781 | 29.3   | 105.3   |
| 335 | Bendegó           | Iron, IC          | 5360000.0  | Found     | 1784 | -10.12 | -39.2   |
| 336 | Sierra Blanca     | Iron, IAB?        | 405.0      | Found     | 1784 | 27.15  | -104.9  |
| 337 | Eichstädt         | H5                | 3000.0     | Fell      | 1785 | 48.9   | 11.22   |
| 338 | Kharkov           | L6                | 1500.0     | Fell      | 1787 | 50.63  | 35.08   |
| 339 | Barbotan          | H5                | 6400.0     | Fell      | 1790 | 43.95  | -0.05   |
| 340 | Castel Berardenga | Stone-uncl        | NULL       | Fell      | 1791 | 43.35  | 11.5    |
+-----+-------------------+-------------------+------------+-----------+------+--------+---------+

However check50 says NULL values are not present in meteorites table. Below is the result generated by check50

:) import.sql exists
:) import.sql runs without error
:) import.sql creates a table named "meteorites" 
:) import.sql creates a table named "meteorites" with all prescribed columns
:( no empty values from CSV are present in "meteorites" table
    NULL values are not present in "meteorites" table
:) all decimal values in "meteorites" table are rounded to two places
:) no meteorites of type "relict" found in "meteorites" table
:| "meteorites" table properly sorts elements and assigns IDs
    can't check until a frown turns upside down

I checked the whole csv file and database and I am pretty sure that all empty values are converted to NULL.

This is the unprocessed data stored in meteorites_temp, empty values are represented by '' (empty space)

+----------------------+------+----------+---------------+------+-----------+------+-----------+----------+
|         name         |  id  | nametype |     class     | mass | discovery | year |    lat    |   long   |
+----------------------+------+----------+---------------+------+-----------+------+-----------+----------+
| Asarco Mexicana      | 2344 | Valid    | Iron, IIIAB   |      | Found     |      |           |          |
| Aus                  | 4902 | Valid    | L             | 30.2 | Found     |      | -26.66667 | 16.25    |
| Benares (b)          | 5012 | Valid    | Iron          |      | Found     |      | 25.33333  | 83       |
| Cacilandia           | 5191 | Valid    | H6            |      | Found     |      |           |          |
| California           | 5199 | Valid    | Iron, IAB-sLL |      | Found     |      |           |          |
| Cochabamba           | 5389 | Valid    | CM2           | 85   | Found     |      |           |          |
| Cook                 | 5420 | Valid    | Unknown       |      | Found     |      |           |          |
| Czestochowa Rakow I  | 5501 | Valid    | Iron          |      | Found     |      | 50.8      | 19.11667 |
| Czestochowa Rakow II | 5502 | Valid    | Iron          |      | Found     |      | 50.8      | 19.11667 |
| Dhofar 1073          | 6880 | Valid    | CV3           | 2.72 | Found     |      | 19.33302  | 54.78217 |
+----------------------+------+----------+---------------+------+-----------+------+-----------+----------+

Below is part of my SQL code for schema and update from empty cell to NULL value:

(As the question mentioned, mass, year, lat, and long columns have empty values in the CSV.)

CREATE TABLE IF NOT EXISTS meteorites_temp (
    name TEXT NOT NULL,
    id INTEGER,
    nametype TEXT NOT NULL,
    class TEXT NOT NULL,
    mass REAL DEFAULT NULL,
    discovery TEXT CHECK (discovery IN ('Fell', 'Found')),
    year YEAR DEFAULT NULL,
    lat REAL DEFAULT NULL,
    long REAL DEFAULT NULL,
    PRIMARY KEY(id)
);

UPDATE meteorites_temp
    SET year = NULL
    WHERE year = '';

UPDATE meteorites_temp
    SET lat = NULL
    WHERE lat = '';

UPDATE meteorites_temp
    SET long = NULL
    WHERE long = '';

UPDATE meteorites_temp
    SET mass = NULL
    WHERE mass = '';

Thanks in advance for any help!

5
  • Did you set nullvalue to NULL in sqlite3 (as with .nullvalue "NULL")? If not, then it seems likely that NULL is a string literal in the database, not an "empty" column. Commented Oct 5, 2023 at 15:53
  • @DinoCoderSaurus I set nullvalue to NULL but still got an error. The problem is I don't know why check50 did not detect my NULL value.
    – yzrtt22
    Commented Oct 5, 2023 at 20:13
  • Sorry, I should have said "Did you set nullvalue to NULL in sqlite3 when the results were selected from meteorites table". This should work, assuming the import is between the CREATE and the UPDATES. Commented Oct 6, 2023 at 2:57
  • @DinoCoderSaurus thanks for your message. I've updated my code to correct some mistakes and as you can see from my SQL code and results I am pretty sure that I've already converted all empty values ('') to NULL and it runs perfectly on sqlite3 on my computer. I think the problem comes from check50 which didn't detect my NULL value and pops up the errors.
    – yzrtt22
    Commented Oct 9, 2023 at 23:18
  • The meteorites_temp data looks ok in the edit. The meteorites data still shows the word NULL. If check50 sees the word NULL it will not pass. Commented Oct 10, 2023 at 9:35

2 Answers 2

0

Don't set any defaults in your temp table. That will end up changing what you are inserting via the .csv command.

2
  • If WHERE is NULL create any results then you have NULL. Something is wrong with CHECK?
    – Mikkey200
    Commented Oct 15, 2023 at 8:59
  • I think there are a BUG in the CHECK
    – Mikkey200
    Commented Oct 16, 2023 at 7:57
0

I found the solution.

Its is important that you following excatly the steps written in ADVICE and USAGE.

You also have to create your database and the table with the cat import.sql | sqlite3 meteorites.db command then the UPDATE of the "" to NULL works.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .