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 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
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
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.
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;
Awsome brother....You are just amazin gbro.....Loved yout teaching style......Hats off to your dedication & commitment.....Thanks a ton .....respect from bangalore,INDIA.
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
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;
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
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.
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
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, 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! 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!
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
9:48 ending with vowels
select distinct city
from station
where right (city,1) in('a','e','i','o','u');
Is better Seloution
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
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
TYSMMMM!!!!!!
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!
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.
1:01:55
select round(long_w,4)
from station
where lat_n=(select max(lat_n)from station
where lat_n
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;
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!
1:04:54
select round(long_w,4) from station
where
lat_n=(select min(lat_n)from station
where lat_n>38.7780);
This is such an amazing video , great explanation , deserves much more appreciation !!!
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 ?
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!
thank you so much, you just activated sql god mode :)
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!
This is a gem 💎
Thank you
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 :)
This is great man, thank you a lot for taking your time for this and sharing it with us
Glad you enjoyed it!
U re not the bro that i deserve but the bro that I need ! !
Really helpful! Thanks!
Thanks so much for sharing approach and solution. Appreciate your efforts .
Thank you so much for sharing the solutions to the SQL questions in Hackerrank!!!!
Sure Victor!
Wow! This is amazing, just heard of this for first time. You got my sub.
Thank you!
Thank you, bro, very clear explanation and l loved the way you teach ... Very thankful for your effort
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 😁
Thank you so much, the Hard problem it's very very hard but you're work so smooth, clever man!
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 😀
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 so much!! You saved me!
Most Welcome David!
thank you so much for your time and effort wish you all the success in your life as this is very kind of you
Thanks for this great work!!!
@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
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 :)
really helpful, thank you for this video !!!
You're so welcome!
Very Resourceful! Thanks a ton!!😊
You are welcome!
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!
Thanks a Lot Sir!
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?
Thank you very much!
thank you bro, very useful video
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
Sir please make a video on Data lemur all questions
Thank you a lot, appreciated it very much
You are welcome Gabriel! Glad that it helped!
God bless you
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!
Thank you so much sir
Thanks a lot sir. ;)
Looking at this video Interviewer will be like you are Hired 😊
youre a god
Nope, just solving sql queries :)
Great video. Thank you so much for explaining so beautifully.
Thanks for your comment! Glad u liked it
Thanks Mate
thank u so much, this is amaizing
Im glad it can help you!
Thank you bro!
You're Welcome Gonza!
that BETWEEEN 🤣 3:01:54
THANKS
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
Thanks!
Thank you!! It is very much appreciated 🙏👏
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 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.
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
Bestt
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!
Weather observation station 5 missing 00:07:15…!
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? 😉
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!
2:24:31 🙄
occuptions problem is not there in the video, please add that too
where is weather station 5 problem?
Has anyone taken the Andela hackerrank test and got similar questions?
Soy de colombia, 1 dia
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! 💎
Why the hell mistakes in every sum like beginner 😑😑😑😑
Half time is wasted like that
Great info but remove your face from the learning exercises. It serves no value and obstructs / clutters the learning experience.
thaxxxx broooooooo👩💻
50:02
select max(months * salary), count( *)
from Employee
where (months * salary) =
(select max(months * salary) from Employee);
1:11:47
select
round((max(lat_n)-min(long_w))+
(max(long_w)-min(lat_n)),4)
from station;