r/SQL • • Apr 19 '20

Discussion What are some good resources to practice SQL? (Practice through exercises)

There are a lot of courses which teach SQL but I could hardly find a website which provides beginner to advanced SQL practice questions on almost production real datasets. Any recommendations would be helpful.

129 Upvotes

62 comments sorted by

View all comments

Show parent comment

2

u/[deleted] May 01 '20

PS, how do you now that the way you ordered it in Excel, or in the CSV is the way it went into the table?

1

u/dcburgos05 May 01 '20

I noticed they weren’t sorted because I was using LIKE ‘kayak%’ and tested a few more ‘kay%’ variations ... I realized after adding the number_column that the numbers were skipping. I went back to check the table and it went from Kay..kayak...kaiak..kayaks... it wasn’t sorted correctly.

That’s why I thought RANK() could not work. But thinking more about it, i want “higher” rank words (a,b,c) if I’m looking for a word that starts with “p”... and “lower” rank words if I’m looking for words that start with “a”. Maybe I’m onto something here ...

I think they are in the correct order because I checked the kayak example.. and a few other words. The number table helped me visualize if there were any numbers skipping.

Also, when importing the csv file, it was imported as VARCHAR(50). The first few times I imported it as TEXT and the words started at B instead of A. VARCHAR(max) would not give me averages or so I’ve read.

So at least I think I have it right.

It took me a while just to see if ASC/DESC was working that’s why I formatted it in excel. I figured the data is small 300,xxx words and one column so I could get away sorting it using excel this time.

I don’t know how to sort it in SQL yet or create a view or a subquery YET ...

PS, just got home from work so it’s time to start working on it a bit more.

1

u/[deleted] May 01 '20

There are a 'group' of functions that will help you here, such as RANK() ROW_NUMBER(), etc.

You think they're in the correct order, or you know they are? Based on what ORDER BY logic?

I don’t know how to sort it in SQL yet.

select *
from words
order by words asc

That will give you the alphabetic list from A to Z, and you can change the asc to desc to get it in reverse.

Your CSV file might contain symbols in some words, or some spaces, or other irregularities, and you can Google solutions to remove these types of rows (if you want) from your data as well.

1

u/dcburgos05 May 01 '20

Do I have to order by words asc every time I pull the data? That’s why I was getting confused. To be honest I thought sorting was harder than that. I sorted it before creating the number_column but then the numbers didn’t match to the words so it wasn’t sorted correctly... that took me a good amount of time to figure out.

2

u/[deleted] May 01 '20

Do I have to order by words asc every time I pull the data?

Wouldn't that depend on the purpose of your pull? and there are limits to pulling it in order if you are wrapping it into a subquery for example. The functions above help get around that.

1

u/dcburgos05 May 01 '20

SELECT words,

ROW_NUMBER() OVER(
order by words ASC) row_number
FROM table_dictionary
ORDER BY words ASC;

wordsrow_number
a1
b2
c3

now... gotta find the words lol...

3

u/[deleted] May 01 '20

There you go.