hey Mariska,

I’m not 100% sure what’s the original task based on your description but there is a missing comparsion operator for sure in your dataset:

SELECT *
FROM flight_delays
WHERE month IN (3,5)
AND dayofweek IN (6,7)
AND ((airtime > 200 AND airtime < 220) OR (airtime > 250 AND airtime < 270));

I suppose this is what you meant. And this query will just run! : )

Hope this helps!
Tomi

hey Justin,

thanks for the question and no worries, that’s a quite typical data cleaning issue. : )
It can be done in bash or Python (I prefer bash.)

In this very particular case this will work:
sed "s/, / /g"

But of course, you’ll have to check this for all lines.
Although it’s a good enough assumption that in comments they’ll use spaces after commas… Anyways, it would get much easier if you could save the file separated by ; or tabs.

Tomi

hey Sriram,

it’s a bash command that selects the right columns, removes the lines with NA values and then pushes the whole thing into a new file called sql_ready.csv.
You don’t have to worry about that for now.
But if you want to learn more about it, I recommend my Bash articles, here:
https://data36.com/learn-data-analytics-bash-scratch/

Tomi

hey Zalan,

that means that you have extracted the same file multiple time (hence 2007.csv.5 and not 2007.csv.

You just have to remove the all the 2007.csv files with the rm 2007.csv* command — then rerun the dtrx line and you’ll be fine.

I am gettin this warning after trying to unzip the csv file:
dtrx: WARNING: extracting /home/tallizali/2007.csv.bz2 to 2007.csv.5

Do you have any idea what should I do differently?

Thanks a lot in advance!

Zalan

hi Simeon,

thank you so much for drawing my attention to this!
I reached out to their webmaster (and hopefully they will restore the dataset.)

But until then you can use this temporary link instead:
http://46.101.230.157/sql_tutorial/2007.csv.bz2

Cheers,
Tomi

well stupid me forgot to attach my failed code:

select * from flight_delays
where month in (3,5)
and dayofweek in (6,7)
and (airtime > 200 and airtime 250 and airtime < 270);

Then adding the NOT function at the end.

So let’s say I’d like to filter the flight data of
>> Month 3 and 5 wher
>> day of week is only 6 and 7
>> airtime only 200or function – so it failed. The only workaround I managed is to include this at the end:

and month NOT in (1,2,4,6,7,8,9,10,11,12);

So.. despite receiving the result I wanted, I still feel like without the NOT function there should be another solution with just smartly using the and / or function and parentheses.. Would you show me how you’d do this prompt?

Can you explain this line cat 2007.csv |cut -d',' -f1,2,3,4,5,7,10,11,14,15,16,17,18,19 | grep -v ',NA' > sql_ready.csv actually How it works?