Practice Writing SQL Queries using Real Dataset(Practice Complex SQL Queries)

In this video, let's learn to write SQL Queries using a real dataset downloaded from the internet. We shall download Olympics dataset from Kaggle and then write SQL Queries to fetch different information from this Olympics data.

Understanding the underlying data stored across different tables will make it much easier to write SQL queries. And that is why we will use real data (real dataset) to write SQL queries in this video.

We start this video by downloading the Olympics history dataset from kaggle website. We then load this dataset into a PostgreSQL database. Meaning we store this data into database tables. Once the data is stored in tables, we then write SQL queries to fetch different information about the history of Olympic games.

I have come up with 20 different SQL queries which we can write on this Olympics dataset. I have posted all these queries in my blog. However, in this video, I will solve only 4 of the SQL queries which I feel are the most complex. If I tried to cover all the 20 SQL queries, then the video would have been many hours long.

The 20 SQL Queries should give you very good understanding of how to write different types of queries and how to think different when solving a SQL problem.

Timestamp:
00:00 Intro
02:41 Download dataset from Kaggle
04:22 Load Kaggle dataset to PostgreSQL database
12:25 Query 1 - Write SQL query to identify the sport which was played in all summer Olympic games.
21:21 Query 2 - Write SQL query to fetch the top 5 athletes who won the most Olympics gold medal.
26:12 Query 3 - Write SQL query to display the total gold, silver and bronze medal won by each country in Olympics.
40:17 Query 4 - Write SQL query to fetch the country which won the most gold, most silver and most bronze medal during each Olympic game.

🔴 WATCH MORE VIDEOS HERE 👇

✅ SQL Tutorial - Basic concepts:

✅ SQL Tutorial - Intermediate concepts:

✅ SQL Tutorial - Advance concepts:

✅ Practice Solving Basic SQL Queries:

✅ Practice Solving Intermediate SQL Queries:

✅ Practice Solving Complex SQL Queries:

✅ Data Analytics Career guidance:

✅ SQL Course, SQL Training Platform Recommendations:

✅ Python Tutorial:

THANK YOU,
Thoufiq

[StefanoVerugi]

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.

[sudhap]

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.

[nareshsachin]

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.❤

[deepakts]

Sir I saw all the sql videos it was really helpful. You are one of the best faculty and make concepts understand so easily.

[enriquembomio]

You made my day and that of many others’ learning SQL by sharing this amazing video! Please keep posting such valuable materials 👍

[laurak]

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 YouTube! 🙏🏻😀

[bayrambayramov]

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');

[kswinny]

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!

[aapbeete]

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');

[ckarkhan]

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!

[iitian]

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.

[swatisinha]

It feels like whatever I am looking for now a days I can find it here. Thankyou so much for this video 🙏🏻

[manishgaurav]

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;

[bharathkumar.m]

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.

[sachinjagtap]

Your videos are actually giving me practical training on sql, very well explained with logics and prgani

[AhmerJamil]

You are doing Amazing job, keep on doing the good work!!

[tushardaund]

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 );
;

[hieutruong]

I have learned so much from your series. Thank you so much sir !!

[jeevankumarthadikonda]

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

[minalgupta]

yur are the best teacher of sql, I am a big fan of u