ALL Hackerrank SQL Solutions in ONE Video! | Easy Medium Hard Problems

[@alexmyers3716]

00:00:00 Intro 00:00:29 Revising the Select Query I (Easy) 00:02:00 Revising the Select Query II (Easy) 00:02:57 Select All (Easy) 00:03:14 Select By ID (Easy) 00:03:37 Japanese Cities' Attributes (Easy) 00:04:08 Japanese Cities' Names (Easy) 00:04:40 Weather Observation Station 1 (Easy) 00:05:05 Weather Observation Station 3 (Easy) 00:07:15 Weather Observation Station 4 (Easy) 00:08:03 Weather Observation Station 6 (Easy) 00:08:53 Weather Observation Station 7 (Easy) 00:09:59 Weather Observation Station 8 (Easy) 00:12:24 Weather Observation Station 9 (Easy) 00:13:32 Weather Observation Station 10 (Easy) 00:14:41 Weather Observation Station 11 (Easy) 00:16:46 Weather Observation Station 12 (Easy) 00:19:14 Higher Than 75 Marks (Easy) 00:21:20 Employee Names (Easy) 00:21:55 Employee Salaries (Easy) 00:23:01 Type of Triangle (Easy) 00:31:29 The PADS (Medium) 00:40:56 Revising Aggregations - The Count Function (Easy) 00:41:42 Revising Aggregations - The Sum Function (Easy) 00:43:29 Revising Aggregations - Averages (Easy) 00:44:02 Average Population (Easy) 00:44:52 Japan Population (Easy) 00:45:18 Population Density Difference (Easy) 00:45:52 Top Earners (Easy) 00:50:01 Weather Observation Station 2 (Easy) 00:53:01 Weather Observation Station 13 (Easy) 00:54:50 Weather Observation Station 14 (Easy) 00:56:06 Weather Observation Station 15 (Easy) 01:02:18 Weather Observation Station 16 (Easy) 01:03:27 Weather Observation Station 17 (Easy) 01:07:16 Weather Observation Station 18 (Easy) 01:12:31 Weather Observation Station 19 (Easy) 01:20:50 Population Census (Easy) 01:23:26 African Cities (Easy) 01:24:27 Average Population of Each Continent (Easy) 01:26:48 The Blunder (Easy) 01:34:06 Weather Observation Station 5 (Easy) 01:41:04 Draw The Triangle 1 (Easy) 01:47:38 Draw The Triangle 2 (Easy) 01:49:19 The PADS (Medium) 02:01:22 Binary Tree Nodes (Medium) 02:24:39 New Companies (Medium) 02:44:00 Weather Observation Station 20 (Medium) 02:53:10 The Report (Medium) 03:07:44 Top Competitors (Medium) 03:27:03 Ollivander's Inventory (Medium) 03:48:14 Challenges (Medium) 04:05:32 Contest Leaderboard (Medium) 04:22:56 SQL Project Planning (Medium) 04:44:49 Placements (Medium) 05:11:08 Symmetric Pairs (Medium) 05:27:39 Print Prime Numbers (Medium) 05:47:21 Interviews (Hard) 06:39:06 15 Days of Learning SQL (Hard)

[@werka_z]

thank you!

[@ronakvadher9977]

Is there any way to solve it again

[@thanhvyho2195]

this video should have 1M+ views! thanks a bunch for your effort, really helpful for sql learners

[@thecodingmentor7701]

Thanks thats very kind of you! Spread the word and we will get 1M Views! :)

[@trihua5296]

bạn đang học ở đâu vậy, có thể giúp mình học sql không

[@aditijaitly2844]

This is such an amazing video , great explanation , deserves much more appreciation !!!

[@anonyousperson1672]

Thank you so much for your tutorial! It's been a life saver for me. Sharing my solution to Binary Treed Nodes 02:01:22 which may be slightly easier SELECT N, CASE WHEN P IS NULL THEN 'Root' WHEN N IN (SELECT P FROM BST) THEN 'Inner' ELSE 'Leaf' END FROM BST ORDER BY 1

[@thecodingmentor7701]

Thank you for adding your solution and helping us learn! Cheers

[@Amber-rh8dh]

TYSMMMM!!!!!!

[@dataguy3553]

Thank You So Very Much, Your efforts have helped me a lot. Please never remove this video, haha. I truly wish The Best for you in Life. Stay Blessed.

[@thecodingmentor7701]

Thanks so much for your kind words! No I will not remove it, keep going, you are amazing!

[@ashrafabdo5461]

9:48 ending with vowels select distinct city from station where right (city,1) in('a','e','i','o','u'); Is better Seloution

[@bigshot_07]

my approach : For odd number median -> quants = 0.5 i.e. 1st and last number / 2. so in this case, we are taking min(LAT_N) + max(LAT_N)/2 (do in calculator) ans: 85.03... round(85.03..,4) where 4 means round to 4 decimal places. then - 1.13999455 Wolla! weather station 20.

[@gazanfersayed4870]

Hey! This video has been such a life saver. Thank you!! Also, for the last challenge i.e. 06:39:06 15 days of learning SQL, you can skip the @count_hacker variable by just doing the count in the @consistent_hacker variable while joining it with the max_hacker CTE. Like this: declare @consistent_hackers table ( submission_date date, hacker_id int ); declare @sub_date date; declare @remaining_date date; insert into @consistent_hackers select submission_date, hacker_id from submissions where submission_date = '2016-03-01'; set @sub_date = '2016-03-01'; set @remaining_date = '2016-03-01'; while @sub_date

[@thecodingmentor7701]

Hey! Thanks for enriching my post with your great addition!

[@Victor-yn6lv]

You are really a SQL genius. Thank you for your video. You are much better than the SQL teacher that I paid for.

[@thecodingmentor7701]

Thanks! Im glad you could get value out of my content!

[@terran008]

This is great man, thank you a lot for taking your time for this and sharing it with us

[@thecodingmentor7701]

Glad you enjoyed it!

[@sw8tanya]

This is amazing, appreciate all the detailed explanation of the problems and their solution. This is super helpful in learning and practicing!

[@thecodingmentor7701]

Thank you! It took a ton of time to record all of this so Im glad you like it!

[@davestorm6718]

For Binary Tree Nodes, try this instead: SELECT N, CASE WHEN P IS NULL THEN 'Root' WHEN N IN (SELECT P FROM BST) THEN 'Inner' ELSE 'Leaf' END FROM BST ORDER BY N;

[@karangupta_DE]

thank you so much, you just activated sql god mode :)

[@shaikusman536]

Awsome brother....You are just amazin gbro.....Loved yout teaching style......Hats off to your dedication & commitment.....Thanks a ton .....respect from bangalore,INDIA.

[@thecodingmentor7701]

Thank you! Love my viewers from India! Keep learning :)

[@Ajjayy4444]

U re not the bro that i deserve but the bro that I need ! !

[@mennahtamally2821]

thank you so much for your time and effort wish you all the success in your life as this is very kind of you

[@modalediamond5280]

Wow! This is amazing, just heard of this for first time. You got my sub.

[@thecodingmentor7701]

Thank you!

[@SeanzySmooth]

Awesome video and thank you for putting the timestamp for each problem in the comments

[@thecodingmentor7701]

Thanks for appreciating it! Timestamps make sense, I dont think anyone would want to watch the whole thing at once 😁

[@DataVisionary]

Thank you so much. I appreciate your work. Keep up the Work with SQL . I am gonna be your true follower. 💯

[@thecodingmentor7701]

Thanks Ahsanul, glad that I can help you!

[@sudeep246]

thank you so much......god bless you....i was looking these Kind of explanation all over the KZbin

[@thecodingmentor7701]

You're welcome. Glad I could help you out!

[@trihua5296]

Thank you so much, the Hard problem it's very very hard but you're work so smooth, clever man!

[@Victor-yn6lv]

Thank you so much for sharing the solutions to the SQL questions in Hackerrank!!!!

[@thecodingmentor7701]

Sure Victor!

[@AmanBkumarvlogs3375]

Hello Sir, I am from India and I love your work and your quires explanation is so amazing .😊😊😊

[@thecodingmentor7701]

Thanks Kumar! Im glad it can help you and its nice to see that so many people from India like to watch my explanations 😀

[@madhuhanumantu8080]

Thanks so much for sharing approach and solution. Appreciate your efforts .

[@carolrockk]

Really helpful! Thanks!

[@gayathrivelmurugan6148]

Thank you, bro, very clear explanation and l loved the way you teach ... Very thankful for your effort

[@unuboldenkhsaruul8682]

Thanks man appreciate it keep up the good work! looking forward more videos

[@thecodingmentor7701]

Thanks! I kind of ran out of SQL Challenges to solve after this video, but Im trying to come up with different ideas

[@unuboldenkhsaruul8682]

@@thecodingmentor7701 any idea about job hunting lol?

[@ashrafabdo5461]

10:43 select distinct city from station where right (city,1) in('a','e','i','o','u') and left(city,1) in('a','e','i','o','u');

[@thecodingmentor7701]

Hey Ashraf, that's a great Solution. Thank you for sharing!

[@victor_peral]

This is a gem 💎 Thank you

[@aakiffpanjwani1089]

really helpful, thank you for this video !!!

[@thecodingmentor7701]

You're so welcome!

[@Livefree103]

thankyou so much for these amazing videos. hope to learn more on SQL

[@thecodingmentor7701]

you are most welcome! You will surely learn more, just keep trying :)

[@davidfuentes7806]

Thank you so much!! You saved me!

[@thecodingmentor7701]

Most Welcome David!

[@ComputationalArt]

Good Job Man!, but You forgot "Weather Observation Station 5", the solution: SELECT CITY, LENGTH(CITY) FROM STATION ORDER BY LENGTH(CITY), CITY LIMIT 1; SELECT CITY, LENGTH(CITY) FROM STATION ORDER BY LENGTH(CITY) DESC, CITY LIMIT 1;

[@Shivam-nr1si]

this doesn't work in Mysql

[@sidharthadaggubati438]

@4:23:40 the problem description doesn’t say about start date. It says about the end dates being consecutive.

[@thecodingmentor7701]

Hi Sidharta, you are right, it doesnt say about start date. However if the end dates are consecutive, that also means that the end date of one record is the start date of the next record.

[@sidharthadaggubati438]

@@thecodingmentor7701 you're right. I forgot that the gap between start and end dates is exactly one for each row. Is there any alternative solution you can think of for this problem? I have started with lag/lead to find out the "days difference" between two tasks and thought of ranking them somehow. Any thoughts? Thanks for your reply. Your solutions are awesome

[@ashrafabdo5461]

50:02 select max(months * salary), count( *) from Employee where (months * salary) = (select max(months * salary) from Employee);

[@gabrielmoffa]

Thank you a lot, appreciated it very much

[@thecodingmentor7701]

You are welcome Gabriel! Glad that it helped!

[@ashrafabdo5461]

1:11:47 select round((max(lat_n)-min(long_w))+ (max(long_w)-min(lat_n)),4) from station;

[@ashrafabdo5461]

1:01:55 select round(long_w,4) from station where lat_n=(select max(lat_n)from station where lat_n

[@ianpropst-campbell6028]

Wow what a great resource! I don't see the "Occupations" problem though. Have you done a video for that one?

[@thecodingmentor7701]

You are right, its not there yet, I try to make it soon!

[@ianpropst-campbell6028]

Thank you!

[@quillerj2412]

Thank you very much!

[@paakwesiblankson7884]

Thanks for this great work!!!

[@himanikumar7979]

Very Resourceful! Thanks a ton!!😊

[@thecodingmentor7701]

You are welcome!

[@a2011q]

thank you bro, very useful video

[@chaitanyagawade9663]

Thanks a Lot Sir!

[@karthikdnd]

I don't understand how this logic worked in the project planning medium question because, for task IDs 10, 15, and 11, the start dates are listed in the end date column.

[@Sam_tuunes]

Sir please make a video on Data lemur all questions

[@mickyman753]

Is the questions showed in the video are in order we should solve them , like next question would be upgrade or follow up question previous to it? Also I have placements in few weeks ,so I wanted to prepare Sql enough to clear my onine assesment and interview , so are these 58 questions enough to prepare me?

[@thecodingmentor7701]

Hi Mayank, no they are not in a specific order. Of course its good to first solve easier and then harder questions. These questions will prepare you well for the interview. The only addition is to look up Data Modelling like Entity Relationship Model. These questions are not covering that, good Luck on the interview!

[@mickyman753]

@@thecodingmentor7701 will do and thanks for the amazing content and if you can , please make a cheat sheet kinda video for SQL for frequently asked patterns for SQL queries questions, I searched a lot on KZbin and Google ,but didn't find anything good , given that almost every IT fresher job aspirant prepares SQL and there's such less worthy content is surprising, so if you can it would be a huge help to me and other future learners too , in the meantime I'll try to complete your video and practice

[@nikitasinha8181]

Thank you so much sir

[@raktimmazumdar5794]

Great video. Thank you so much for explaining so beautifully.

[@thecodingmentor7701]

Thanks for your comment! Glad u liked it

[@ajtam05]

I'm a bit confused on --> "If more than one student created the same number of challenges AND the count is less than the maximum number of challenges created, then exclude those students from the result." 1. Shouldn't the WHERE clause be AND (versus OR)??? 2. Technically, shouldn't --> tnc.num_challenges >= MAX (versus = MAX)??? WHERE tcc.count_challenges

[@thecodingmentor7701]

Hi Alex, thanks for your comment! Can you give me a timestamp for your question? 😉

[@mayurijoshiofficial]

Do you have solution for Advertising Sytem Net Seller Report? And Transaction Monitoring System Report

[@thecodingmentor7701]

Are these from the hackerrank certifications? I don't recall, but I also have videos on the hackerrank certifications. You should check them out

[@melanibruce3755]

thank u so much, this is amaizing

[@thecodingmentor7701]

Im glad it can help you!

[@dbatexas]

can you post hierarchy query like finding manager, employe hierarchy. thank you for all help!

[@thecodingmentor7701]

hey dbatexas, what question are you talking about?

[@rayyanamir8560]

Thanks a lot sir. ;)

[@Manjunath_pai]

Looking at this video Interviewer will be like you are Hired 😊

[@AnkitSharma-ds9mo]

are we gonna get the same set of 58 questions while giving a shot for hackerank certification?

[@thecodingmentor7701]

No the hackerrank certifications have their own questions. You can also find videos about them in my channel!

[@Gonza2432]

Thank you bro!

[@thecodingmentor7701]

You're Welcome Gonza!

[@GM-xz4xc]

God bless you

[@diftq8191]

youre a god

[@thecodingmentor7701]

Nope, just solving sql queries :)

[@Sandra-b4c1r]

Hi, In Weather Observation Station 8 I did something like that: SELECT DISTINCT City FROM Station WHERE LOWER(LEFT(City, 1)) IN ('a', 'e', 'i', 'o', 'u') AND LOWER(RIGHT(City, 1)) IN ('a', 'e', 'i', 'o', 'u') ; Maybe it would be helpfull for somebody

[@thecodingmentor7701]

Thanks for sharing!

[@manaskumar8532]

occuptions problem is not there in the video, please add that too

[@AnandKumar-dc2bf]

Thanks Mate

[@hackerutkarsh]

2:25:57 new companies, can't understand this solution at all, can you please explain it? thanks in advance

[@thecodingmentor7701]

Hi utkasrsh, in this problem you basically just want to COUNT something (in this case managers). In order to count it, you have to make a couple of joins. Thats really all there is

[@abhay-zw3yw]

THANKS

[@adithya1577]

where is weather station 5 problem?

[@funmitoadeyemi4647]

Has anyone taken the Andela hackerrank test and got similar questions?

[@anuchauhan5048]

Weather observation station 5 missing 00:07:15…!

[@Nothing-hf8uj]

that BETWEEEN 🤣 3:01:54

[@Abhishek-tv8px]

is there only 58 questions for sql on hackerrak?

[@thecodingmentor7701]

Yes there are 58 SQL Challenges on Hackerrank. There are also other programming languages and certifications which include different querstions

[@bhumikalalchandani321]

Bestt

[@lakshgaming696]

Weather Observation Station 5 is missing

[@thecodingmentor7701]

Indeed!

[@santiagobernaltinjaca5360]

Soy de colombia, 1 dia

[@trading_buddy]

2:24:31 🙄

[@koogymysta7029]

you forgot Occupations (medium)

[@thecodingmentor7701]

You are right! I uploaded it now :)

[@IITian_Vlogs7]

Voice is not clear. Else content is good.

[@thecodingmentor7701]

Thanks! I got an extra microphone in some parts of the video. In the beginning I recorded with the computer mic which was not great. Glad you still liked it!

[@Anonymous____________A721]

Why the hell mistakes in every sum like beginner 😑😑😑😑 Half time is wasted like that

[@abidhasan5583]

bro that's gold dust.

[@thecodingmentor7701]

Thanks Abid! 💎

[@oldcmputer]

Great info but remove your face from the learning exercises. It serves no value and obstructs / clutters the learning experience.

[@fusionx4945]

thaxxxx broooooooo👩‍💻

[@989894945]

Thanks!

[@thecodingmentor7701]

Thank you!! It is very much appreciated 🙏👏

[@ashrafabdo5461]

1:04:54 select round(long_w,4) from station where lat_n=(select min(lat_n)from station where lat_n>38.7780);

[@ashrafabdo5461]

50:02 select max(months * salary), count( *) from Employee where (months * salary) = (select max(months * salary) from Employee);

[@aryakaagnihotri3210]

this is what i thought but it does not pass the test case , are you having the same problem also ?