I'm a beginner and found these videos amazing. Everything is just what you need, no frills, no lengthy talking, right to the point, If I can follow, everybody can. Real examples of how to solve queries, proper use of screen space, with a strong support from his blog where you can delve into more if still hungry of it. All for free, I so much appreciate this work, first class.
This feedback made my day.. thank you Stefano 🙏🏼 Your feedback is so precise that I feel I am able to deliver what I wished to deliver.. Thanks again and I am so happy you are able to follow this video 🙏🏼☺️
Thank You so much techTFQ. Your videos are sooo soo good. After learning window function by myself, the concept wasnt clear for me. It was after watching your video on window function I got a idea of how it works. Last month I had interview and questions were from SQL only. They asked different queries based on window functions and joins. The result came within a week and I am joining the company as Research Executive.
Amazing to hear this Sudha 👌 Thank you for sharing your success story.. I am really happy to have contributed to your learning .. Wishing you all the very best with your new job 💐
TFQ, it’s like you were reading my mind! This is exactly where I’ve been stuck, not only finding reliable datasets that are interesting, but not knowing how to properly load them into Postgres so I can play. I really want to thank you so much for your time and efforts. I’ve only seen your intro, but you can bet I’ll be returning to see how you’re importing this dataset and connecting it to Postgres. It seems like that would be an easy thing to do, but I’ve tried and it isn’t. You will be helping so many people with this video. And your explanations are better than most instructors I’ve seen. You have many talents, but you’re an excellent teacher. Thank you again, so glad I found you here on TH-cam! 🙏🏻😀
Hi Laura , Thank you for such an amazing feedback🙏🏼 Taking your time to appreciate my work, I feel so happy to read each n every word you said. Am so glad you have liked my content and I am able to help you with your learning ☺️.. Thanks again 🙏🏼
@@techTFQ If I am able to make a career change as I hope to, it will be in no small part due to your efforts. I’m very grateful for your expertise and your ability to explain tricky concepts and break them down easily for us. Not everyone can do this; you have a gift 🙏🏻!
@@techTFQ PS I’m also going to try and solve all of your questions and I’ll be visiting your blog for more on that. Hopefully I won’t need any of your hints but still being new to SQL, I’m not so sure on this. All the time and effort you put into this is really appreciated. I hope next year at this time you have over 100K subscribers because you deserve it!
Wowww amazing.. 100k would be great.. I’ll remember this comment if that happens by next year 😃 And yes try out solving all the queries .. after that hopefully you can come up with some queries of your own.. good luck 👍
I have to mention the mistake from your blog. You gave a solution query In your official blog. Problem NO. 10 you write a query for fetch all female count and male count with out removing duplicate records. So it return more number of users rather then the actual user count. Please verify all queries once. Your tutorial is very helpful thank you lot.❤
Thoufiq, I've been writing SQL for many years now but watching these and other videos by you takes me to a different level. What I've learned is to break the problem into smaller parts and solve them individually. Also, there is a huge boost in my confidence in SQL interviews and the elevated quality of my code is noticed by the interviewers. All this is thanks to you. If I may request something, please make some videos on use of the QUALIFY clause in window functions. Also a video on the use of CUBE and ROLLUP features would be awesome! Is that asking for too much? Now, although I can read up on all these topics, if I watch your video, I will be able to internalize it even better. You are the best. Keep it up!
I cannot overstate how helpful this was. I was at my wit's end trying to figure out how to resolve the rogue ' without editing the source file. Thank you!
Very nice set of questions. Query 3, at 26:12, If in interviews it is not allowed to use pivot functions then we can do it using CASE WHEN with medals as (select nr.region as country, (case when medal = 'Gold' then 1 else 0 end) as Gold, (case when medal = 'Silver' then 1 else 0 end) as Silver, (case when medal = 'Bronze' then 1 else 0 end) as Bronze from olympics_history oh join olympics_history_noc_regions nr on nr.noc = oh.noc where medal 'NA') select country, sum(medals.Gold) as gold, sum(medals.Silver) as silver, sum(medals.Bronze) as bronze from medals group by country order by gold desc, silver desc, bronze desc;
You are right i think also check my answer plz with cta1 as (select country, count(*) as Gold from OLYMPICS_HISTORY oh join OLYMPICS_HISTORY_NOC_REGIONS nr on oh.noc=nr.noc WHERE medal='Gold' GROUP BY country) with cta2 as (select country, count(*) as Silver from OLYMPICS_HISTORY oh join OLYMPICS_HISTORY_NOC_REGIONS nr on oh.noc=nr.noc WHERE medal='Silver' GROUP BY country) with cta3 as (select country, count(*) as Bronze from OLYMPICS_HISTORY oh join OLYMPICS_HISTORY_NOC_REGIONS nr on oh.noc=nr.noc WHERE medal='Bronze' GROUP BY country) select country,gold,silver,bronze from cta1,cta2,cta3 where cta1.country=cta2.country and cta2.country=cta3.country;
also select nr.region as country, sum(case when medal = 'Gold' then 1 else 0 end) as Gold, sum(case when medal = 'Silver' then 1 else 0 end) as Silver, sum(case when medal = 'Bronze' then 1 else 0 end) as Bronze from olympics_history oh join olympics_history_noc_regions nr on nr.noc = oh.noc where medal 'NA'
The last query explanation was just mind-blowing the best part of your explanation is dividing the problem into small parts and solving it step by step
You're the best man..main part is you explain how to write queries by dividing the question and even the format part you follow..excellent quality. Thanks for guiding so many of us through your valuable content ❤️🙏..it would be great if you do more videos of query optimization and tuning in stored procedures your way of teaching would make it simple to understand them.
You are such an inspiration . the way you are helping me understand clearly what want is just amazing. Your efforts are seen and appreciated as well. i tried to solve question no 5 in my way please look once with cte as ( select games , noc from OLYMPICS_HISTORY group by games ,noc ) select noc ,count( noc) from cte group by noc having count(noc)=(select count( distinct games) from OLYMPICS_HISTORY ); ;
Hello God Gifted sir👑, I think you is one of the TH-camrs for best content to teaching SQL Your video is really amazing and we alll students shares your all videos to our college and teachers also refer your video.... Please keep it up sir and make more videos for related SQL.... please please ..
Thoufiq first of all the video is very insightful, and meticulous I reallly learnt a lot of new concepts hands down your tutorials on SQL are one of the I have ever come across. Please upload more such videos in future as well.
Your sql query brings lots of clarification to the approach of solving. It would e of great help if you could take a step more further considering the real time scenarios where we have large number of tables inter related and we have to write queries out of those table. The main issue we face in such cases is the approach to join the certain set of tables and in using the window functions as well.If you can guide for such cases.
Thank you Aditya 🙏🏼 Prior to this video, I had already posted another video where I covered solving complex SQL queries where I used window functions and JOINS.. perhaps you can check that video out.. link below: th-cam.com/video/FNYdBLwZ6cE/w-d-xo.html
Hello Thoufiq, I have been watching your videos and they are really helpful in understanding complex concepts. There is alternative solution to query 1: select * from (select sport, count(games) count from ( select * from ( select sport, games from olympics_history where season='Summer' ) group by sport, games) group by sport) x where x.count = (select count(distinct games) from olympics_history where season='Summer');
Similarly for Query 4, at 40:17, with medals as (select oh.games as games, nr.region as country, (case when medal = 'Gold' then 1 else 0 end) as Gold, (case when medal = 'Silver' then 1 else 0 end) as Silver, (case when medal = 'Bronze' then 1 else 0 end) as Bronze from olympics_history oh join olympics_history_noc_regions nr on nr.noc = oh.noc where medal 'NA'), county_wise as (select games, country, sum(medals.Gold) as gold, sum(medals.Silver) as silver, sum(medals.Bronze) as bronze from medals group by games, country order by games, gold desc, silver desc, bronze desc) select distinct(games), concat(first_value(country) over(partition by games order by gold desc), ' - ', first_value(gold) over(partition by games order by gold desc)) as Max_gold, concat(first_value(country) over(partition by games order by silver desc), ' - ', first_value(silver) over(partition by games order by silver desc)) as Max_silver, concat(first_value(country) over(partition by games order by bronze desc), ' - ', first_value(bronze) over(partition by games order by bronze desc)) as Max_bronze from county_wise order by games;
WOW...(7,611) views, (402) likes 0 (Dislike) achived in 16 days (28-10-21 to 13-11-21); thats Awsome appreciation for your knowledge and skills brother.....Thanks a ton....
You made the first query very complicated. The simple solution is as follows: SELECT Sport FROM olympics_history GROUP BY Sport HAVING COUNT(DISTINCT Year) = (SELECT COUNT(DISTINCT Year) FROM olympics_history where Season='Summer');
You are such an inspiration and my role model . the way you are helping everyone understand clearly what they want is just amazing. Your efforts are seen and appreciated as well. Seriously so so so proud ❤️
Sir plz make a full-fledge course of 5-6 hours just like freecodecamp videos on SQL queries which covers from basic beginners to Advance Love from 21 yrs old boy congrats in Advance for 100k subs
Thank you so much. It's entertaining to watch and learn. This is the training video I've been looking for. I'm currently working with Oracle SQL Developer and can only insert partial data. It would be more fun if I could insert the entire data set.
@@techTFQ Thank you for responding. I fixed it. I accidentally checked the limit box, which is why only partial data were inserted. Importing a dataset was a lot easier than I expected on SQL developers. There's no need to write the entire Create statement. Thank you for the video once more. Continue making videos and motivating us. Happy New Year!
God bless you brother, really loved your content so clear to the point, I am beginner concepts are getting clear , if you add more videos will learn and to become mastery in it
Looks like 51 unique olympics games are in the database but 52 olympic games by location are listed because the 1956 were held in two cities.The 1956 Summer Olympics, officially known as the Games of the XVI Olympiad, were an international multi-sport event held in Melbourne, Victoria, Australia, from 22 November to 8 December 1956, with the exception of the equestrian events, which were held in Stockholm, Sweden, in June 1956.
Hello sir, I am your big fan, and your videos helps me alot, For that Jazakallahu Khaira, Sir in your blog there's a slight problem, the expected output you showed is wrong somewhere I think, some of that I noticed and you can check the 18th problems expected output. Thankyou sir
Hello sir, I have learned sql recently but didn't how to apply this skill and practice of the project , but your videos helped me to understand more about SQL and about the work of data analyst. I have a question ,can we show this project in LinkedIn ? if yes then how?
Amazing video! I'm a frontend guy, watching your videos help me widen my skills in an excellent way. Thanks! I have a question regarding the performance. I don't know too much about the performance of the partition by statement. In the last task, the partition by was used several times in the concat fns (5-6 times). What do you think regarding the performance in a production ready environment? Would it scale well by the time if total number of records grow?
Hi, thank you 🙏🏼 Glad you find these videos useful.. As for performance, window functions are pretty good even if we use partition by.. they should not cause any performance bottlenecks but then it also depends on how you write your sql query. As for my query in this video, we had around 270k+ records and still the queries with multiple partition by hardly took a few milliseconds to run.. Even with much more data I believe this won’t cause any performance issues but again it all depends on how you write your sql queries and how you create indexes on your tables and few other factors like table statistics etc.
Thank you so much for these question sets , I have just completed these questions sets , What do you recommend which type of questions should we do now ?
Q6 query: select Sport, count(distinct(Games)) as count from athletes where season = 'summer' group by Sport having count= (select count(distinct(Games)) as count from athletes where season = 'summer')
Hi I've been watching your videos for a while and i love them. They're very helpful! But I wanted to let you know that I think there may be somehing wrong with your crosstab function (or maybe it's not reading the data correctly). For example, your response to question 18 shows that Paraguay has 17 bronze medals. They actually have 17 silver medals. I think the function my be classifying data into the wrong columns. I found that simply pivoting the medal data from rows to columns in MySQL did the trick! Just wanted to let you know. Have a great day!
Hi Maya , Thank you for liking my contents 🙏🏼 And thanks for highlighting this difference.. I am actually surprised,, I had tested it for a few countries data.. let me check this out tomorrow and correct it if needed.. Thanks again 🙏🏼
@@techTFQ no problem!! You’ve helped me so much in developing my SQL skills. I really love and appreciate your videos! If you could do more of these types of videos where you display advanced SQL techniques using data on kaggle, that would be awesome. Thanks again and have a great day!
Hii Taufiq, Big fan, learner and admirer of your work here!! I'm one of the most benefitted person from your videos. I just wanted to inform, in query 12, as you have taken 'group by name and team' and because data is not so clean ...hence it has missed the name of athlete 'Birgit Fischer-Schmidt' from Germany who is among top 5 athletes by total number of medels won. her team is shown as 'Germay' as some places and 'East Germany ' at some places. you could have get even accurate results by grouping on id and name.
qus -14 WITH CTE AS ( select n.region as countries,a.medal,count(*) as total from athlete a join noc_regions n on n.NOC=a.NOC where a.medal 'NA' group by n.region,a.medal ) SELECT countries , sum(case when medal='gold' then total end) as gold , sum(case when medal='silver' then total end) as silver , sum(case when medal='bronze ' then total end) as bronze from cte group by countries order by gold desc ,silver desc ,bronze desc
When I import our table data, I was an encounter problems which has ERROR: invalid input syntax for type integer: it shows me this error; "1;A Dijiang;M;24;180;80;China;CHN;1992 Summer;1992;Summer;Barcelona;Basketball;Basketball Men's Basketball;NA" CONTEXT: COPY olympics_history, line 2, column id: "1;A Dijiang;M;24;180;80;China;CHN;1992 Summer;1992;Summer;Barcelona;Basketball;Basketball Men's Bask..." at the beginning part 07:48 Could you help me please?
Thank you so much for this. I just have 1 doubt. In the question number 3, Can't we just use below query directly instead of with clause? Please do clarify my doubt. Select game,count(n.region) from history h join noc n on h.noc=n.noc group by game
You cannot because the output is wrong.. because history table has data for each athlete so there would be multiple athletes from each country corresponding to each olympic games so the counts would be way to more.. For example, in 1896 games, 12 countries participated but from your query you would get 380 (which is the total athletes and not country)
19:45 i dont understand why this code isnt working on my system, im using MS SQL, using azure Data studio, im getting this error Msg 8120, Level 16, State 1, Line 8 Column 'T2.Sport' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
10:59 when I was importing the athlete_event table either the connection has been lost or import process is finished without importing a single row. (I can import all 230 rows of noc_regions table.) Sir how can I resolve this problem?
alternate way to solve query 14 , select noc, count( case when medal="gold" then 1 else null end ) as gold, count( case when medal="silver" then 1 else null end ) as silver, count( case when medal="bronze" then 1 else null end ) as bronze from olympics_history where medal'na' group by noc order by gold desc,silver desc,bronze desc; please let me know if there is any mistake.
Hello, I'm a newly subscriber and I have been looking for channel like this for quite sometime. Thank you. For Query 2, I can just use limit 5 if ranking is not needed? select name, team, count(medal) as total_medals from olympics_history where medal = 'Gold' group by 1, 2 order by 3 desc limit 5
Thank you for liking my contents 🙏🏼 Limit 5 will just fetch the first 5 records but if you look at the data there can be multiple athletes who won 9 gold medals or 8 gold medals so you need to fetch all of them.. That is why ranking is required
I'm a beginner and found these videos amazing.
Everything is just what you need, no frills, no lengthy talking, right to the point, If I can follow, everybody can.
Real examples of how to solve queries, proper use of screen space, with a strong support from his blog where you can delve into more if still hungry of it.
All for free, I so much appreciate this work, first class.
This feedback made my day..
thank you Stefano 🙏🏼
Your feedback is so precise that I feel I am able to deliver what I wished to deliver..
Thanks again and I am so happy you are able to follow this video 🙏🏼☺️
You are beginner and you need short and real examples >> How examples in shorts ? Don't Lecture If you Don't Have Common sense.. He is doing Perfect..
You are awesome Taufiq bro @@techTFQ
Thank You so much techTFQ. Your videos are sooo soo good. After learning window function by myself, the concept wasnt clear for me. It was after watching your video on window function I got a idea of how it works. Last month I had interview and questions were from SQL only. They asked different queries based on window functions and joins. The result came within a week and I am joining the company as Research Executive.
Amazing to hear this Sudha 👌
Thank you for sharing your success story.. I am really happy to have contributed to your learning ..
Wishing you all the very best with your new job 💐
Kudos
TFQ, it’s like you were reading my mind! This is exactly where I’ve been stuck, not only finding reliable datasets that are interesting, but not knowing how to properly load them into Postgres so I can play. I really want to thank you so much for your time and efforts. I’ve only seen your intro, but you can bet I’ll be returning to see how you’re importing this dataset and connecting it to Postgres. It seems like that would be an easy thing to do, but I’ve tried and it isn’t. You will be helping so many people with this video. And your explanations are better than most instructors I’ve seen. You have many talents, but you’re an excellent teacher. Thank you again, so glad I found you here on TH-cam! 🙏🏻😀
Hi Laura ,
Thank you for such an amazing feedback🙏🏼
Taking your time to appreciate my work, I feel so happy to read each n every word you said.
Am so glad you have liked my content and I am able to help you with your learning ☺️..
Thanks again 🙏🏼
@@techTFQ If I am able to make a career change as I hope to, it will be in no small part due to your efforts. I’m very grateful for your expertise and your ability to explain tricky concepts and break them down easily for us. Not everyone can do this; you have a gift 🙏🏻!
@@techTFQ PS I’m also going to try and solve all of your questions and I’ll be visiting your blog for more on that. Hopefully I won’t need any of your hints but still being new to SQL, I’m not so sure on this. All the time and effort you put into this is really appreciated. I hope next year at this time you have over 100K subscribers because you deserve it!
That’s a very big complement Laura .. You’ve made my day by such kind words..
I truly wish you get your dream job ..
thank you once again 🙏🏼
Wowww amazing.. 100k would be great.. I’ll remember this comment if that happens by next year 😃
And yes try out solving all the queries .. after that hopefully you can come up with some queries of your own..
good luck 👍
I have to mention the mistake from your blog. You gave a solution query In your official blog. Problem NO. 10 you write a query for fetch all female count and male count with out removing duplicate records. So it return more number of users rather then the actual user count. Please verify all queries once. Your tutorial is very helpful thank you lot.❤
Sir I saw all the sql videos it was really helpful. You are one of the best faculty and make concepts understand so easily.
Thank you so much Deepak 🙏🏼
Am so glad you have liked my videos 🙂
And thank you for taking time to leave your feedback 🙏🏼
Thoufiq, I've been writing SQL for many years now but watching these and other videos by you takes me to a different level. What I've learned is to break the problem into smaller parts and solve them individually. Also, there is a huge boost in my confidence in SQL interviews and the elevated quality of my code is noticed by the interviewers. All this is thanks to you. If I may request something, please make some videos on use of the QUALIFY clause in window functions. Also a video on the use of CUBE and ROLLUP features would be awesome! Is that asking for too much? Now, although I can read up on all these topics, if I watch your video, I will be able to internalize it even better. You are the best. Keep it up!
I cannot overstate how helpful this was. I was at my wit's end trying to figure out how to resolve the rogue ' without editing the source file. Thank you!
You made my day and that of many others’ learning SQL by sharing this amazing video! Please keep posting such valuable materials 👍
Thank you Enrique 🙏🏼
Am so glad to hear you benefited from this video 🙂
How do you explain everything so effortlessly! Best SQL resource on the internet!
yur are the best teacher of sql, I am a big fan of u
Thank you Minal :)
great toufiq, your teaching and explanations are top ...you deserve more ..
It feels like whatever I am looking for now a days I can find it here. Thankyou so much for this video 🙏🏻
Your welcome Swati 🙏🏼
Very nice set of questions.
Query 3, at 26:12, If in interviews it is not allowed to use pivot functions then we can do it using CASE WHEN
with medals as
(select nr.region as country,
(case when medal = 'Gold' then 1 else 0 end) as Gold,
(case when medal = 'Silver' then 1 else 0 end) as Silver,
(case when medal = 'Bronze' then 1 else 0 end) as Bronze
from olympics_history oh
join olympics_history_noc_regions nr on nr.noc = oh.noc
where medal 'NA')
select country, sum(medals.Gold) as gold, sum(medals.Silver) as silver, sum(medals.Bronze) as bronze
from medals
group by country
order by gold desc, silver desc, bronze desc;
thanks, Manish i found this easy
You are right i think
also check my answer plz
with cta1 as
(select country, count(*) as Gold from OLYMPICS_HISTORY oh join OLYMPICS_HISTORY_NOC_REGIONS nr on oh.noc=nr.noc WHERE medal='Gold' GROUP BY country)
with cta2 as
(select country, count(*) as Silver from OLYMPICS_HISTORY oh join OLYMPICS_HISTORY_NOC_REGIONS nr on oh.noc=nr.noc WHERE medal='Silver' GROUP BY country)
with cta3 as
(select country, count(*) as Bronze from OLYMPICS_HISTORY oh join OLYMPICS_HISTORY_NOC_REGIONS nr on oh.noc=nr.noc WHERE medal='Bronze' GROUP BY country)
select country,gold,silver,bronze
from cta1,cta2,cta3 where cta1.country=cta2.country and cta2.country=cta3.country;
also
select nr.region as country,
sum(case when medal = 'Gold' then 1 else 0 end) as Gold,
sum(case when medal = 'Silver' then 1 else 0 end) as Silver,
sum(case when medal = 'Bronze' then 1 else 0 end) as Bronze
from olympics_history oh
join olympics_history_noc_regions nr on nr.noc = oh.noc
where medal 'NA'
This is the kind of video been looking for ages
The last query explanation was just mind-blowing the best part of your explanation is dividing the problem into small parts and solving it step by step
You are like god ( teacher ) for me .🙏
Your are the best! Thanks a lot for this class, now I know all the data sources and how to practice!
Your videos are actually giving me practical training on sql , very well explained with logics and prgani
You're the best man..main part is you explain how to write queries by dividing the question and even the format part you follow..excellent quality. Thanks for guiding so many of us through your valuable content ❤️🙏..it would be great if you do more videos of query optimization and tuning in stored procedures your way of teaching would make it simple to understand them.
Yours are the best videos that I have ever found on you tube …. Keep going Toufiq
Glad you like them Shobha!
You are doing Amazing job, keep on doing the good work!!
Thank you Jamil 🙏🏼
Glad you liked it..
I have learned so much from your series. Thank you so much sir !!
Thank you :)
So glad to hear this..
Thank you very much Sir, for this tutorial. Got more clearity and new perspectives to approach a problem.
You are such an inspiration . the way you are helping me understand clearly what
want is just amazing. Your efforts are seen and appreciated as well.
i tried to solve question no 5 in my way please look once
with cte as (
select games , noc from OLYMPICS_HISTORY group by games ,noc )
select noc ,count( noc) from cte group by noc having count(noc)=(select count( distinct games) from OLYMPICS_HISTORY );
;
awesome👍👏👍👍👍👍👍👍👍👍👍👍
Thank you Ganesh :)
Hello God Gifted sir👑,
I think you is one of the TH-camrs for best content to teaching SQL
Your video is really amazing and we alll students shares your all videos to our college and teachers also refer your video....
Please keep it up sir and make more videos for related SQL.... please please ..
Thoufiq first of all the video is very insightful, and meticulous I reallly learnt a lot of new concepts hands down your tutorials on SQL are one of the I have ever come across. Please upload more such videos in future as well.
Thank you Agrim :)
I am glad to know you have benefited from these videos :)
Your sql query brings lots of clarification to the approach of solving. It would e of great help if you could take a step more further considering the real time scenarios where we have large number of tables inter related and we have to write queries out of those table. The main issue we face in such cases is the approach to join the certain set of tables and in using the window functions as well.If you can guide for such cases.
Thank you Aditya 🙏🏼
Prior to this video, I had already posted another video where I covered solving complex SQL queries where I used window functions and JOINS.. perhaps you can check that video out.. link below:
th-cam.com/video/FNYdBLwZ6cE/w-d-xo.html
really sir, u are doing a great job..please keep helping us!!!!
really very very usefull
Thank you :) Glad you liked it Pooja
This has actually helped me a lot in understanding the SQL
Thanks and keep posting such videos.
Hello Thoufiq, I have been watching your videos and they are really helpful in understanding complex concepts. There is alternative solution to query 1:
select * from
(select sport, count(games) count
from (
select * from (
select sport, games from olympics_history where season='Summer'
)
group by sport, games)
group by sport) x
where x.count = (select count(distinct games) from olympics_history where season='Summer');
Similarly for Query 4, at 40:17,
with medals as
(select oh.games as games, nr.region as country,
(case when medal = 'Gold' then 1 else 0 end) as Gold,
(case when medal = 'Silver' then 1 else 0 end) as Silver,
(case when medal = 'Bronze' then 1 else 0 end) as Bronze
from olympics_history oh
join olympics_history_noc_regions nr on nr.noc = oh.noc
where medal 'NA'),
county_wise as
(select games, country, sum(medals.Gold) as gold, sum(medals.Silver) as silver, sum(medals.Bronze) as bronze
from medals
group by games, country
order by games, gold desc, silver desc, bronze desc)
select distinct(games),
concat(first_value(country) over(partition by games order by gold desc),
' - ', first_value(gold) over(partition by games order by gold desc)) as Max_gold,
concat(first_value(country) over(partition by games order by silver desc),
' - ', first_value(silver) over(partition by games order by silver desc)) as Max_silver,
concat(first_value(country) over(partition by games order by bronze desc),
' - ', first_value(bronze) over(partition by games order by bronze desc)) as Max_bronze
from county_wise
order by games;
WOW...(7,611) views, (402) likes 0 (Dislike) achived in 16 days (28-10-21 to 13-11-21); thats Awsome appreciation for your knowledge and skills brother.....Thanks a ton....
Thank you Shaik :)
I dint realize this like and dislike count until i saw this comment :)
Glad this video is liked by so many.
Man! I’ve been looking for something like this, thanks man
No problem buddy!
glad this helped
Really appreciate your work. ❤❤❤
Bro your channel is Amazing I am officially a fan now 😀😀😀
You made the first query very complicated. The simple solution is as follows:
SELECT Sport
FROM olympics_history
GROUP BY Sport
HAVING COUNT(DISTINCT Year) = (SELECT COUNT(DISTINCT Year) FROM olympics_history where Season='Summer');
Simple and elegant
Thank you... thank you....for sharing and posting.to world
Your welcome bro 🙏🏼
Glad this helped
great work .It is really very much helpful for beginners. Please do more videos for us.
Thank you and will do
Just came back to your channel. Very happy to see you with many more subscribers. More wishes young man. God bless 🙏
Thank you so much Girija :)
Amazing...thanks a lot for sharing knowledge.. It means so much for learners lik me... Pls continue and post more content about sql for data analyst
Thank you Jayanth 🙏🏼
Glad to see your comment ☺️
Today subscribed your channel and really amazing explanation , I would recommend this video to my friends , thanks toufiq
Awesome buddy, glad you liked it
You are such an inspiration and my role model . the way you are helping everyone understand clearly what they want is just amazing. Your efforts are seen and appreciated as well. Seriously so so so proud ❤️
Thank you Cheeb for the support always :)
Happy to receive your feedback 😍
@@techTFQ how did you find the script to run on the database
Very nice practicing videos
Thank you 🙏🏼
Sir plz make a full-fledge course of 5-6 hours just like freecodecamp videos on SQL queries
which covers
from basic beginners to Advance
Love from 21 yrs old boy
congrats in Advance for 100k subs
Haha thanks Aman ☺️☺️
100k is far away but thanks for the wishes 🤩
As for one long video, never thought about it but I’ll start considering it now
thats what i call quality content
Thanks Shiv 🙏🏼
Thanks for such a great videos....
Your welcome Dhananjai :)
glad this helped..
Thank you so much Thoufiq. I learnt a lot from your channel
Your welcome Shaik 🙏🏼
Glad you liked it..
I learned a lot from your SQL videos, please upload more videos about SQL and Python in the future. Thank you so much.
Thank you, will do!
Thank you so much TFQ! This goes a long way for me! Always learning new strategies to tackle SQL queries.👍🏻
Thank you Ibrahim 🙏
Great content !!!!
Thank you Amit
Proud of you mate...God bless all your efforts and time to give out authentic and precise content👌🏻👌🏻👌🏻🙏
Thank you 😘
@ Taufiq sir where is your vlog link ? Can't find it
Thank you so much. It's entertaining to watch and learn. This is the training video I've been looking for.
I'm currently working with Oracle SQL Developer and can only insert partial data. It would be more fun if I could insert the entire data set.
Your welcome Amit 🙏🏼
I am glad you liked it..
Do you get any error when importing all the data ?
@@techTFQ Thank you for responding. I fixed it. I accidentally checked the limit box, which is why only partial data were inserted. Importing a dataset was a lot easier than I expected on SQL developers. There's no need to write the entire Create statement. Thank you for the video once more. Continue making videos and motivating us. Happy New Year!
Great to hear Amit 👌
Thank you and happy new year to you too 🎊🎉
You are Great Dear
Thank you 🙏🏼
Glad you like the content 🙂
Man! I love your work
God bless you brother, really loved your content so clear to the point, I am beginner concepts are getting clear , if you add more videos will learn and to become mastery in it
Great and Thanks!!! Make more video like this for sql practice.
Glad you liked it and sure will do
Earned my subscription 😎
It indeed became quite complex by the end, but also very simple at the same time thanks to your reasoning
Trust me guys, this series have worth more than 'n'thousand price for those who want master in sql.
-Thankyou so much for your effort @techTFQ💯
Thanks. Hope you can release more videos like this in the future ^^
I plan to Giang
great explanation
Thank you 🙏🏼
thanks alot, very well explained
Glad it was helpful!
Great video sir
Thank you 🙏🏼
Thank you ,for this amazing tutorial
Welcome:)
we need more video sir love from 🇲🇾
I want to solve some queries in SQL but I don't find anywhere correctly.Thanks for the video by letting this know.
Your superstar man ! thank you
Sir Salute. Plz plz make case study on sql by taking different datasets and also do data cleaning using sql and use advanced sq; queries. Plz sir
Great video
thank you Gabriel :)
subscribed to your channel, please give us more real-world SQL projects
good stuff! Thanks
Your explanation is awasome AND plz make video on Root cause ANALYSIS
Thank you Mushtak 🙏🏼
Looks like 51 unique olympics games are in the database but 52 olympic games by location are listed because the 1956 were held in two cities.The 1956 Summer Olympics, officially known as the Games of the XVI Olympiad, were an international multi-sport event held in Melbourne, Victoria, Australia, from 22 November to 8 December 1956, with the exception of the equestrian events, which were held in Stockholm, Sweden, in June 1956.
So you really need to undetstand the data as rightly advised!!
Hello sir, I am your big fan, and your videos helps me alot, For that Jazakallahu Khaira,
Sir in your blog there's a slight problem, the expected output you showed is wrong somewhere I think, some of that I noticed and you can check the 18th problems expected output.
Thankyou sir
Hello sir, I have learned sql recently but didn't how to apply this skill and practice of the project , but your videos helped me to understand more about SQL and about the work of data analyst. I have a question ,can we show this project in LinkedIn ? if yes then how?
Thanks bhai..
Your welcome bro
Amazing video! I'm a frontend guy, watching your videos help me widen my skills in an excellent way. Thanks! I have a question regarding the performance. I don't know too much about the performance of the partition by statement. In the last task, the partition by was used several times in the concat fns (5-6 times). What do you think regarding the performance in a production ready environment? Would it scale well by the time if total number of records grow?
Hi, thank you 🙏🏼
Glad you find these videos useful..
As for performance, window functions are pretty good even if we use partition by.. they should not cause any performance bottlenecks but then it also depends on how you write your sql query.
As for my query in this video, we had around 270k+ records and still the queries with multiple partition by hardly took a few milliseconds to run..
Even with much more data I believe this won’t cause any performance issues but again it all depends on how you write your sql queries and how you create indexes on your tables and few other factors like table statistics etc.
@@techTFQ thanks for the detailed answer 👍
Your welcome bro 🙏🏼
Good
Thank you 🙏🏼
Thanks!!! Make more video like this for sql practice.Also could you make a EDA video for python? You explain things very nicely.
Thank you Rishi 🙏🏼
Yeah for EDA , I will plan to do it..
25:00 instead of using this weird method, use R and use the slice_max function. slice_max(on_your_col, n = 5)
Thank you ☺️
Your welcome 🙏🏼
Thank you brother.
Your welcome buddy
Thank you so much for these question sets , I have just completed these questions sets , What do you recommend which type of questions should we do now ?
Thank you sir
Hi brother your tutorials are the best.. can you please make a course alongwith problems so we can learn in-depth?
Thank you Subhabrata 🙏 and I will make a complete SQL tutorial in a couple of months time.
1st query
select count(distinct games) as co , sport
from events
where events.Season = 'summer'
group by sport
order by co
2nd query
select top 5 count(medal) as "count medal" , name
from events
where events.medal ='gold'
group by name
order by "count medal" desc
Q6 query:
select Sport, count(distinct(Games)) as count from athletes
where season = 'summer'
group by Sport
having count= (select count(distinct(Games)) as count from athletes
where season = 'summer')
Hi I've been watching your videos for a while and i love them. They're very helpful! But I wanted to let you know that I think there may be somehing wrong with your crosstab function (or maybe it's not reading the data correctly). For example, your response to question 18 shows that Paraguay has 17 bronze medals. They actually have 17 silver medals. I think the function my be classifying data into the wrong columns. I found that simply pivoting the medal data from rows to columns in MySQL did the trick! Just wanted to let you know. Have a great day!
Hi Maya ,
Thank you for liking my contents 🙏🏼
And thanks for highlighting this difference.. I am actually surprised,, I had tested it for a few countries data.. let me check this out tomorrow and correct it if needed..
Thanks again 🙏🏼
@@techTFQ no problem!! You’ve helped me so much in developing my SQL skills. I really love and appreciate your videos! If you could do more of these types of videos where you display advanced SQL techniques using data on kaggle, that would be awesome. Thanks again and have a great day!
Am glad to hear this Maya..
Yes I plan to cover more such videos in the near future..
Have a good day to you too..🙂🙏🏼
Hii Taufiq, Big fan, learner and admirer of your work here!! I'm one of the most benefitted person from your videos.
I just wanted to inform, in query 12, as you have taken 'group by name and team' and because data is not so clean ...hence
it has missed the name of athlete 'Birgit Fischer-Schmidt' from Germany who is among top 5 athletes by total number of medels won. her team is shown as 'Germay' as some places and 'East Germany ' at some places. you could have get even accurate results by grouping on id and name.
qus -14
WITH CTE AS
( select n.region as countries,a.medal,count(*) as total from athlete a
join noc_regions n
on n.NOC=a.NOC
where a.medal 'NA'
group by n.region,a.medal )
SELECT countries ,
sum(case when medal='gold' then total end) as gold ,
sum(case when medal='silver' then total end) as silver ,
sum(case when medal='bronze ' then total end) as bronze
from cte
group by countries
order by gold desc ,silver desc ,bronze desc
When I import our table data, I was an encounter problems which has ERROR: invalid input syntax for type integer: it shows me this error;
"1;A Dijiang;M;24;180;80;China;CHN;1992 Summer;1992;Summer;Barcelona;Basketball;Basketball Men's Basketball;NA"
CONTEXT: COPY olympics_history, line 2, column id: "1;A Dijiang;M;24;180;80;China;CHN;1992 Summer;1992;Summer;Barcelona;Basketball;Basketball Men's Bask..."
at the beginning part 07:48 Could you help me please?
Thank you so much for this. I just have 1 doubt. In the question number 3, Can't we just use below query directly instead of with clause? Please do clarify my doubt. Select game,count(n.region) from history h
join noc n on h.noc=n.noc
group by game
You cannot because the output is wrong.. because history table has data for each athlete so there would be multiple athletes from each country corresponding to each olympic games so the counts would be way to more..
For example, in 1896 games, 12 countries participated but from your query you would get 380 (which is the total athletes and not country)
@@techTFQ Ahh Okay. Got it. Thank you 👏
Your welcome 🙏🏼
19:45 i dont understand why this code isnt working on my system, im using MS SQL, using azure Data studio, im getting this error
Msg 8120, Level 16, State 1, Line 8
Column 'T2.Sport' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
pls upload more videos related to sql
noted, will do
10:59 when I was importing the athlete_event table either the connection has been lost or import process is finished without importing a single row.
(I can import all 230 rows of noc_regions table.)
Sir how can I resolve this problem?
Thank you sir.
How do I load this same datasets on mysql?
Could you help ?
Well sir,..🎉Why dont you continue this type of content...?
alternate way to solve query 14 ,
select noc, count(
case
when medal="gold" then 1
else null
end ) as gold,
count(
case
when medal="silver" then 1
else null
end ) as silver,
count(
case
when medal="bronze" then 1
else null
end ) as bronze
from olympics_history where medal'na' group by noc order by gold desc,silver desc,bronze desc;
please let me know if there is any mistake.
Hello, I'm a newly subscriber and I have been looking for channel like this for quite sometime. Thank you.
For Query 2, I can just use limit 5 if ranking is not needed?
select name, team, count(medal) as total_medals
from olympics_history
where medal = 'Gold'
group by 1, 2
order by 3 desc
limit 5
Thank you for liking my contents 🙏🏼
Limit 5 will just fetch the first 5 records but if you look at the data there can be multiple athletes who won 9 gold medals or 8 gold medals so you need to fetch all of them..
That is why ranking is required
@@techTFQ looking forward to seeing your solution to other queries