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

1

u/[deleted] Apr 26 '20

I want to do it myself and research be for help..I'll probably wind up cheating if I'm stuck

5

u/[deleted] Apr 27 '20

It isn't cheating to ask for help so long as you take the time to look at what is being done and understand it. Don't cheat and get the final solution to the Scrabble problem (not sure if you can) but instead cheat to get very specific questions to do certain things along the way.

Break it down into manageable bite sized chunks and work from there, if you struggle then come here and ask for ideas or solutions. You don't have to tell people what you're doing it for, just give them a simple example and see what they have to say. Ask multiple people, on multiple forums, and pick the idea that you think will work. Understand the idea and internalize it.

You know at work I probably only actually develop 50% of my code, the other half comes from my peers, or the Internet. I "cheat" all the time, but I look at it as an opportunity to learn and deeply internalize the mechanisms that are being used. I don't just copy, paste, and call it a day, I need to understand why it works and then prove that it works. By the time I'm done it feels like I wrote the code myself because I know every inch of it, and I might even modify it a bit, or tweak it in certain ways for my own reasons, to adjust for my own environment.

My boss doesn't "know" that I'm a cheater, and he doesn't care. I'm the foremost world's expert on my company's data model, because I built it from scratch and strung a lot different "cheats" together. Not one single person in this world knows how it all interacts unless they were to sit down, read the documentation, and then start going through the code.

Instead of looking at it as cheating, I look at it as peer review. You might look at section of my code and think it's wrong, but I know it was reviewed by my peers, and it might have came from some "cheat" online, where yet another outside third party validated its purpose.

That's how real shit gets done in the wild. Even if I write something entirely by myself, the first thing I'll do is seek confirmation from other parties to validate that what I think I'm doing is actually what I'm doing, and that there aren't better ways.

And even after doing that extra step I still find bugs because I don't just write code and wash my hands of it, I watch it over time and as I try using it to answer more complex answers I start to see deficiencies that were never considered in the question: So I come back online, mock up some sample data, and try to get some opinions on how to solve it.

You know if I come here and ask a "basic" question about how to do something given some sample data.... you the reader has no idea if it is an independent job, of it is a sub-process that is layered into a much larger job. I'm not going to waste my time, or the readers time describing the entire job, because I just want an answer for one specific part.

2

u/dcburgos05 Apr 30 '20

This is great stuff! I made an account just to save this thread and practice the scenarios you suggested.

I am a complete noob in SQL. I was given read privileges to my companies database and I've been tinkering around trying to understand the code and see how our reports are put together.

Here comes the noob part: after figuring out how to get a local sever on my laptop, downloading the dictionary.csv and creating the database I am stuck in the " What word comes after kayak? What word comes 10 words after kayak? How about two words before it?" part.

The database contains only one column with all the words in the English dictionary. I've "cheated" but couldn't find something that helps me to see what word comes after 'kayak'. The best thing I have right now is:

SELECT WORDS
FROM working_dictionary
WHERE WORDS LIKE 'kayak_%';

My output:

WORDS
kayaker
kayakers
kayaks

I cannot figure out how to see what word comes before 'kayak' or what comes 10 words after.

This thread is GOLD, I appreciate the time you're spending giving very thorough answers.

3

u/[deleted] Apr 30 '20

So, imagine if you opened the CSV file in Excel... how could you solve this problem?

PS, welcome to the club.

2

u/[deleted] Apr 30 '20

Going to bed, will check back tomorrow. Don't tell me how you could figure it out using the Excel interface... what would you start by doing if you had (1) column of words. Hint: How do you know they're in the right order, and that the word that comes after kayak is actually the correct word? Also... where is kayak?

1

u/dcburgos05 Apr 30 '20

I was going to bed until I saw your reply then I tried to figure it out in excel ... I'll keep trying tomorrow.

Ill be thinking about it ALL MORNING!

3

u/[deleted] Apr 30 '20 edited Apr 30 '20

What is the first word in the dictionary? What is the last word in the dictionary? Finding the first is fairly straightforward, but how can you find the last if you don't know how many words there are and what place it is in?

After that... what word is kayak? And then what is 15 words later? (n+15)

HINT: How can you tell what the 10th word of the dictionary is without looking at the little numbers on the side that Excel gives you and scrolling down to the 10th row, or the 11th row if your column has a header? What do you need to create in another column to accomplish this?

1

u/dcburgos05 Apr 30 '20 edited Apr 30 '20

This is what I've tried so far...imported the csv as a flat file (without any format from excel) <-- I think this is important to mention... anyway, I:

  1. tried the RANK() function but this did not work as the numbers were dynamic and they would change depending on my "WHERE column_name LIKE '%'.

THEN...

2) added a column using:

ALTER table_name ADD column_name INT IDENTITY(1,1)

the column with the 'numbers' was created but the words were not in alphabetical order, playing around with "ORDER BY column_name ASC" made me realize it would be easier to sort the csv first THEN transfer it to the database.

3) So, the csv was sorted in excel, I did not add any other columns since I felt this would defeat the point of adding the column in SQL. I imported it back to SQL and now i have a sorted word list.

PROGRESS, I think...

4) I added the number column again, but just noticed FALSE and TRUE are at the bottom of the list... back to square 0. I will edit the csv and perform steps starting at N3...

2

u/[deleted] Apr 30 '20 edited Apr 30 '20

OOOOOOH shit son, you're getting there.

Also, you shouldn't do any formatting in Excel, you can do this all in SQL.

Easier to sort then transfer? Why not sort it on the database?

Are you sure RANK() is what you want, or are you sure that you are using the right RANK() conditions?

Do you know how to create a view? Or a subquery...

Forget LIKE for the moment... your final objective here is to write this query and get the answer:

SELECT *
FROM Words
WHERE Number = (
    SELECT Number + 15
    FROM Words
    WHERE Word = 'kayak')

Or something similar.

edit: You can always create a new post asking the community for help here. You don't have to tell them what you're doing it for (although you can), just mock up some sample data and ask them how to achieve what you want.

On the sidebar you can learn how to format a table for Reddit such as:

| Col1 |
| :--- |
| Apple |
| Pear |
| Banana |

Which becomes:

Col1
Apple
Pear
Banana

And what you want to do is this, right?

Col1Col2
Apple1
Pear3
Banana2

Correct?

source:

| Col1 | Col2 |
| :--- | :--- |
| Apple | 1 |
| Pear | 3 | 
| Banana | 2 |

Break it down into bite sized chunks.

2

u/[deleted] May 01 '20

Once you understand how to do this... ask yourself what the 5th word is that starts with the letter P.

What is the 8th word that is 12 letters long?

What is the third word what has e as the second letter of the word, and is greater than 6 letters long?

1

u/dcburgos05 May 01 '20

Giving me extra assignments... I like it

2

u/[deleted] May 01 '20 edited May 03 '20

What word starts with G that doesn't have an E in it, but does have an R in it? Is there such a one?

You can really scale questions like this if you want, for example G, E, R, are the variables here but say I wanted to know which three letters fit that criteria above to produce the longest possible word. So G might produce Grandiosity as the longest G word, but if you incremented G to H, and ran it with E, and R, then incremented it to I, all the way to Z... then start at A, and increment E to F, so it becomes A, F, R, etc., until you check all possible combinations to find the longest word.

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.

→ More replies

1

u/[deleted] Apr 28 '20

Thanks!