ALL Hackerrank SQL Solutions in ONE Video! | Easy Medium Hard Problems
ฝัง
- เผยแพร่เมื่อ 8 ก.ค. 2024
- Why this video is 7.5 hours long? Because Im solving ALL of the SQL Problems on Hackerrank in this video. This is the only video you will need to improve your Coding Skills in SQL.
____________________________________________
SUBSCRIBE!
Do you want to understand how to solve SQL Problems in every detail? That's what my channel is about. When I was younger I thought I could never program because it looked way too difficult. The truth is that it takes time but with some patience anybody can do it! Follow me along and get better!
____________________________________________
SUPPORT MY CHANNEL
🙌The best way to support my channel right now is to give me a Super Thanks. You can do that by clicking thanks next to the title of the video. It is much appreciated!
____________________________________________
💾GitHub: github.com/CuriosityLeonardo?...
✋Add me on LinkedIn to get in contact: / markus-friemann-221b3814b
____________________________________________
Timestamps:
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)
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)
thank you!
Is there any way to solve it again
This is such an amazing video , great explanation , deserves much more appreciation !!!
Thank you so much, the Hard problem it's very very hard but you're work so smooth, clever man!
Thank you, bro, very clear explanation and l loved the way you teach ... Very thankful for your effort
this video should have 1M+ views! thanks a bunch for your effort, really helpful for sql learners
Thanks thats very kind of you! Spread the word and we will get 1M Views! :)
bạn đang học ở đâu vậy, có thể giúp mình học sql không
This is a gem 💎
Thank you
This is amazing, appreciate all the detailed explanation of the problems and their solution. This is super helpful in learning and practicing!
Thank you! It took a ton of time to record all of this so Im glad you like it!
thank you so much, you just activated sql god mode :)
Thanks so much for sharing approach and solution. Appreciate your efforts .
Thank you so much. I appreciate your work.
Keep up the Work with SQL . I am gonna be your true follower.
💯
Thanks Ahsanul, glad that I can help you!
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.
Thanks so much for your kind words! No I will not remove it, keep going, you are amazing!
You are really a SQL genius. Thank you for your video. You are much better than the SQL teacher that I paid for.
Thanks! Im glad you could get value out of my content!
Thanks for this great work!!!
Awsome brother....You are just amazin gbro.....Loved yout teaching style......Hats off to your dedication & commitment.....Thanks a ton .....respect from bangalore,INDIA.
Thank you! Love my viewers from India! Keep learning :)
Really helpful! Thanks!
Wow! This is amazing, just heard of this for first time. You got my sub.
Thank you!
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
Thank you for adding your solution and helping us learn! Cheers
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.
Thanks a Lot Sir!
1:11:47
select
round((max(lat_n)-min(long_w))+
(max(long_w)-min(lat_n)),4)
from station;
9:48 ending with vowels
select distinct city
from station
where right (city,1) in('a','e','i','o','u');
Is better Seloution
Very Resourceful! Thanks a ton!!😊
You are welcome!
Thank you so much for sharing the solutions to the SQL questions in Hackerrank!!!!
Sure Victor!
Thank you very much!
thank you so much......god bless you....i was looking these Kind of explanation all over the TH-cam
You're welcome. Glad I could help you out!
thank you bro, very useful video
This is great man, thank you a lot for taking your time for this and sharing it with us
Glad you enjoyed it!
1:04:54
select round(long_w,4) from station
where
lat_n=(select min(lat_n)from station
where lat_n>38.7780);
thankyou so much for these amazing videos. hope to learn more on SQL
you are most welcome! You will surely learn more, just keep trying :)
Thanks a lot sir. ;)
Thank you so much!! You saved me!
Most Welcome David!
Thank you so much sir
1:01:55
select round(long_w,4)
from station
where lat_n=(select max(lat_n)from station
where lat_n
Thanks Mate
Awesome video and thank you for putting the timestamp for each problem in the comments
Thanks for appreciating it! Timestamps make sense, I dont think anyone would want to watch the whole thing at once 😁
Thanks man appreciate it keep up the good work! looking forward more videos
Thanks! I kind of ran out of SQL Challenges to solve after this video, but Im trying to come up with different ideas
@@thecodingmentor7701 any idea about job hunting lol?
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
Hey! Thanks for enriching my post with your great addition!
Hello Sir,
I am from India and I love your work and your quires explanation is so amazing .😊😊😊
Thanks Kumar! Im glad it can help you and its nice to see that so many people from India like to watch my explanations 😀
really helpful, thank you for this video !!!
You're so welcome!
Thank you bro!
You're Welcome Gonza!
Thank you a lot, appreciated it very much
You are welcome Gabriel! Glad that it helped!
Great video. Thank you so much for explaining so beautifully.
Thanks for your comment! Glad u liked it
thank u so much, this is amaizing
Im glad it can help you!
50:02
select max(months * salary), count( *)
from Employee
where (months * salary) =
(select max(months * salary) from Employee);
THANKS
God bless you
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');
Hey Ashraf, that's a great Solution. Thank you for sharing!
youre a god
Nope, just solving sql queries :)
Sir please make a video on Data lemur all questions
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;
Wow what a great resource! I don't see the "Occupations" problem though. Have you done a video for that one?
You are right, its not there yet, I try to make it soon!
Thank you!
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;
this doesn't work in Mysql
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.
Bestt
Looking at this video Interviewer will be like you are Hired 😊
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?
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!
@@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 TH-cam 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
@4:23:40 the problem description doesn’t say about start date. It says about the end dates being consecutive.
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.
@@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
can you post hierarchy query like finding manager, employe hierarchy. thank you for all help!
hey dbatexas, what question are you talking about?
Do you have solution for Advertising Sytem Net Seller Report?
And Transaction Monitoring System Report
Are these from the hackerrank certifications? I don't recall, but I also have videos on the hackerrank certifications. You should check them out
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
Hi Alex, thanks for your comment! Can you give me a timestamp for your question? 😉
are we gonna get the same set of 58 questions while giving a shot for hackerank certification?
No the hackerrank certifications have their own questions. You can also find videos about them in my channel!
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
Thanks for sharing!
occuptions problem is not there in the video, please add that too
Has anyone taken the Andela hackerrank test and got similar questions?
that BETWEEEN 🤣 3:01:54
2:25:57 new companies, can't understand this solution at all, can you please explain it? thanks in advance
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
Weather observation station 5 missing 00:07:15…!
Soy de colombia, 1 dia
where is weather station 5 problem?
2:24:31 🙄
is there only 58 questions for sql on hackerrak?
Yes there are 58 SQL Challenges on Hackerrank. There are also other programming languages and certifications which include different querstions
Weather Observation Station 5 is missing
Indeed!
you forgot Occupations (medium)
You are right! I uploaded it now :)
Voice is not clear. Else content is good.
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!
bro that's gold dust.
Thanks Abid! 💎
Great info but remove your face from the learning exercises. It serves no value and obstructs / clutters the learning experience.
thaxxxx broooooooo👩💻
Thanks!
Thank you!! It is very much appreciated 🙏👏
50:02
select max(months * salary), count( *)
from Employee
where (months * salary) =
(select max(months * salary) from Employee);
this is what i thought but it does not pass the test case , are you having the same problem also ?