SQL Project | SQL Case Study to SOLVE and PRACTICE SQL Queries | 20+ SQL Problems

Подписаться 305 тыс.

Просмотров 113 тыс.

50% 1

In this video let us work on an SQL Case study in which we shall download a dataset from Kaggle and then upload it to a PostgreSQL database using Python. We shall then analyze the data and solve 20+ SQL queries. You can term this SQL practice exercise as an SQL project or an SQL case study. The aim is to provide you with a dataset to analyze data and then practice basic to intermediate SQL problems.
The dataset we use is the famous paintings dataset taken from Kaggle. We use a simple Python script to upload data from CSV files to PostgreSQL database tables. We then use SQL queries to answer over 20 problems related to the famous paintings dataset.
Download the project material from my blog:
techtfq.com/blog/sql-project-...
Kaggle dataset link:
www.kaggle.com/datasets/mexwe...
Timestamp:
00:00 Intro
00:50 Download Kaggle Dataset
01:27 Load data to Database using Python
12:29 Analysing Kaggle Dataset
16:01 Solving SQL Query no 1
21:27 Solving SQL Query no 2
27:46 Solving SQL Query no 3
Thank you for watching!
Watch more videos:
🔴 My Recommended courses 👇
✅Complete Data Analytics Bootcamp:
codebasics.io/bootcamps/data-...
✅ Learn Power BI:
codebasics.io/courses/power-b...
✅ Learn complete SQL:
learnsql.com/?ref=thoufiqmoha...
techtfq.graphy.com/courses/Re...
✅ Practice SQL Queries:
www.stratascratch.com/?via=te...
✅ Learn Python:
techtfq.graphy.com/courses/Py...
🔴 WATCH MORE VIDEOS HERE 👇
✅ SQL Tutorial - Basic concepts:
• SQL Tutorial - Basic c...
✅ SQL Tutorial - Intermediate concepts:
• SQL Tutorial - Interme...
✅ SQL Tutorial - Advance concepts:
• SQL Tutorial - Advance...
✅ Practice Solving Basic SQL Queries:
• Practice Solving BASIC...
✅ Practice Solving Intermediate SQL Queries:
• Practice Solving INTER...
✅ Practice Solving Complex SQL Queries:
• Practice Solving COMPL...
✅ Data Analytics Career guidance:
• Data Analytics career ...
✅ SQL Course, SQL Training Platform Recommendations:
• SQL Course / Training
✅ Python Tutorial:
• Python Tutorial
✅ Git and GitHub Tutorial:
• Git and GitHub
✅ Data Analytics Projects:
• Data Analytics Projects
THANK YOU,
Thoufiq | techTFQ

Опубликовано:

20 июн 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 183

@frankedwin1183 4 месяца назад

This guy is so smart ... I'm dicovering incredible tricks in SQL. Amazing.

@Pravin33unique95 5 месяцев назад

Its great for freshers who wants to improve their problem solving skills

@mohitnemade5320 5 месяцев назад

You are really perfect mentor for postgray sql, Thanks a lot 😍👍👍

@leonmadara3903 5 месяцев назад

This guy is amazing!!! The stuff I have learnt from you is infinite, God bless!!! And please, make more video!!! Make it longer, tackle all the questions, we are hear to learn!!! SIMPLY AMAZING!!! THANKS

@techTFQ 5 месяцев назад

Thank you so much :) Much appreciated.

@muraliprasad621 5 месяцев назад

Excellent and very informative, learned a lot of new things in SQL and python from this video. Thanks.

@hamantjagwan07 5 месяцев назад

I always consider you as my primary SQL mentor. 🙏😊 Could you please provide us the answer key for the questions mentioned in the video? I would like to solve those questions

@techTFQ 5 месяцев назад

Thank you ☺️ The solutions are already provided in my blog. You can find the link in video description

@Sai-nk1vd 2 месяца назад

This is pretty straight forward, precise and crisp. Good content explained in a great way. So helpful. Thank you.

@debacle-stylings7505 3 дня назад

i never leave comments on youtube but have to say that your videos are by far the best ive found ( after extensive searching )in regards to learning data analysis thank you very much!

@adnanhashmio 5 месяцев назад

🎉🎉 this is a blessing 🙌

@debapriyabehera7146 5 месяцев назад

Waiting for this kind project 😊

@nikhilbhute07 5 месяцев назад

You are Genius Man.🙌

@maghy_kethychannel 5 месяцев назад

superb affords. really hats of to you.please start a playlist videos for data engineers.which will help lots of techies

@fathimafarahna2633 5 месяцев назад

Much awaited😍As always, best of the best 👍

@techTFQ 5 месяцев назад

Thank you :)

@okechukwuezinne6236 Месяц назад

I enjoyed the video cos you teach so well. Thanks, man.

@nilabja2974 Месяц назад

Amazing query has learnt from you. Thanks

@yashwanthrasamala5933 5 месяцев назад

Wait for more such kind of videos

@techTFQ 5 месяцев назад

will look into it. thank you

@user-dn1my6kh4b 5 месяцев назад

Thank you for this one. I hope I'm not asking too much as I would like you to do another one covering the remaining questions.

@gazart8557 5 месяцев назад

Yes sir plz make more. Plz make some interviews specific ones

@samkhan4214 5 месяцев назад

Very interesting video. Please make some more such case study videos. Thanks in advanced

@Lokeshna83 5 месяцев назад

Hi Thoufiq, Thank you for all your vedios. Is there any possibility that you can provide training personally by taking the charges? It would be highly helpful.

@moinuddinmohd6943 4 месяца назад

Your videos always inspires to dig more into subject. Can i get the detailed SQL sessions from basics to advance and also with real time scenarios.

@shaikmubeen5093 5 месяцев назад

You are the best sir

@yaminirajyalakshmiyamini8171 5 месяцев назад

Wonderful content 👏👏👏

@KartikSir_ 5 месяцев назад

Great video! I really appreciate the part about Python since I always thought that it was really hard. However, you showed that with a few lines of code, you can achieve a lot even without being a programmer or something. Very useful! I have one question, maybe too about the solutions: Do we need any type of grant permission to run df.to_sql()?

@cloudpavan 3 месяца назад

Excellent tutor,

@F_A_R_man 5 месяцев назад

Thanks a lot. You are amazing as always. Great content! 🥰

@techTFQ 5 месяцев назад

Your welcome 🙏

@abisheks.p6223 5 месяцев назад

Thanks a lot. Your content is amazing Sir. Hope to learn lot of things from you Sir. A small request, can you please post videos on performance tuning in SQL.

@vinayakyerekar762 5 месяцев назад

Finally wait is over

@parthtandel5848 5 месяцев назад

Nicely explained sir. One question - What if we just want to add/append the data in the existing table using python?

@kanchanshirnath6556 4 дня назад

Awesome... please make such more videos

@nishtha_pd 5 месяцев назад

All set to do this.. 😊

@dibyabhanumohanta698 4 месяца назад

Very nice content!

@sravankumar1767 5 месяцев назад

Superb explanation 👌 👏 👍

@techTFQ 5 месяцев назад

Thank you 🙂

@bhongosinxo6248 5 месяцев назад

Thank you for another nice video.

@techTFQ 5 месяцев назад

Thank you too!

@Mrlegacy1_ 5 месяцев назад

And this video is so so superb!💝😍

@techTFQ 5 месяцев назад

Thank you

@sameer9368 5 месяцев назад

Awesome

@bhanuprakashneelam8250 5 месяцев назад

Thank you in advance

@PraveenKumar-ic1to 4 месяца назад

My sql mentor😊😊😊😊😊

@neerajanaidu2558 4 месяца назад

Hii... instead of Python can we use SSIS to load data into the respective tables.?? Kindly respond PLZ. Thank you for the efforts and the information. I learnt so much from your videos.

@johnypl21 5 месяцев назад

good video

@PukiPukiPo 5 месяцев назад

Great video! I really appreciate the part about Python since I always thought that it is really hard. However, you showed that with a few lines of code, you can achieve a lot even without being a programmer or something. Very useful! I have one question, maybe too about the solutions: What is 'exists (select 1)'? And why in 'count(1)' is there '1'? Does it equal to '*'? Thank you!

@techTFQ 5 месяцев назад

Glad you enjoyed it! thank you! as for select 1 in EXISTS: it can be anything. Select 1 or any other field or value. When we use EXISTS clause, what you mention after SELECT in INNER query does not matter since SQL is only interested in the no of records returned from inner query. as for count(1) its almost similar to count(*).

@wasiaamir8125 5 месяцев назад

1.Exists returns nothing so here we use 1 or anything else 2.count(* / 1/ anything) except column name will give you all rows if it has any null values row it also count but if you use count(column name) it only gives you the none nullable rows count

@rahulpattnaik4118 5 месяцев назад

Hey Thoufiq, Thank You. I have a question, you have made a video for practicing sql from different website can you please help us with python, where we can practice python specifically for Data Analysis

@lokeshbobbala1765 5 месяцев назад

Great video. Is there chance for you to make video on pyspark as well?

@sam-uw3gf 5 месяцев назад

The data can be imported in Mysql easily than writing code of this much but I came to know about this way 👍👍

@geraldineorieoma7169 3 месяца назад

Teach me. I know the data wizard way

@chinyereobi5456 3 месяца назад

Thank you for this tutorial. Please what connector can i use for MySqQL

@valenciawalker6498 Месяц назад

Thank you

@mohsinmanzoor2483 5 месяцев назад

Can we use groupby in question 10. How it is different from day in (‘sunday’,’monday’)

@sarunlorteerapong335 5 месяцев назад

Thanks!

@techTFQ 5 месяцев назад

Thank you ❤️

@rajujannapreddy7355 Месяц назад

Hi Thoufiq! I am using MQLWork bench instead of Postgresql. Is the process the same for connecting to the database?

@industryrule-4080 5 месяцев назад

In problem 15, you finished with a subquery and where rank = 1 statement. Would a simple limit 1 have worked the same?

@AnalysisWithVinay 4 месяца назад

Thanks

@muhammadshahin3667 5 месяцев назад

I would like to see a similar video for MongoDB

@satyajitbiswal6162 5 месяцев назад

Waiting

@Sabiha-le7cj 5 месяцев назад

Woow

@davidwayne9864 4 месяца назад

In mysql server management studio, can i just save the files as excel files and then import them in that way. When i downloaded the cv files, they opened in excel anyway.

@dolandtramp5555 4 месяца назад

hi thoufiq, great video. I just wanted to point out that in the third query the desired output should be country:USA and city: New York, Washington. The country USA has most no of museums and the cities New York and Washington (in USA) have the most no of museums. Other cities ie London and Paris should not be included in the output.

@ckarkhan 4 месяца назад

Exactly! That's my confusion too. I assumed that we need to show the cities with max museums in countries with the max museums. Maybe it's the phrasing of the question that's throwing me off. Thoufiq....would you care to explain please..?

@divyajeetsingh5049 5 месяцев назад

Sir when join two CTEs, why do we need to use CROSS JOIN? Why can't we simply use a comma? Like SELECT * FROM t1,t2

@sivakumarisadineni3193 4 месяца назад

facing issues as no module as pandas , could you make a video on python installation and configurations

@muzeebdua 5 месяцев назад

💥💥💥

@jeanmarieabengzoa2600 4 месяца назад

I have one question, are you create the tables before?

@Ankit-rv2my 4 месяца назад

Hello Thoufiq , will the same line of code work for sql server too as I am working on it

@anudeepreddy5559 5 месяцев назад

❤ 💯🔥

@user-ki3ip1bq5o 5 месяцев назад

What if in case, I need to upload files from an s3 or from source other than local machine. how can I upload it to DB using python?

@anjalikumar9193 4 месяца назад

sir I want to add this as a project in my resume , however, I am new to it , Could you briefly tell me how to mention (as in what key points to mention)?

@kemjhonponce3881 5 месяцев назад

Hi, please grant my request. Could you please make a video about sql triggers or dcl?

@mohammedshahil4898 5 месяцев назад

🤩🤩🤩

@mohammedvahid5099 5 месяцев назад

Thnk u so much toufique please one migration project scenario from python to snowflake how it's behavs and how it wil done pls make an video

@techTFQ 5 месяцев назад

I have already done one project using python and snowflake. may be check that out. linke below: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-GHtX0QXfi6g.html

@AnalysisWithVinay 5 месяцев назад

Can we use LIMIT clause in Q no. 15 instead of Rank function ?

@user-bv2qd5wt1q 3 месяца назад

Hi! Thank you for great video! Is the procedure is same for uploading the csv from Python to MySQL? (I have zero knowledge of Phyton:)

@BlackSouL24 Месяц назад

No , db_url method will be different

@agohachukwuemeka8709 Месяц назад

PLEASE MAKE MORE

@akkshheyagarwaal7629 5 месяцев назад

I never even heard about exists. Can query 1 be done in another way?

@KartikSir_ 5 месяцев назад

Hi, Postgres did not allow my query to run, is there any database permission issue, I tried to run the same Python script and also created a "painting" database, with no error from the Python script but unable to see any database changes.

@tanveersingh4217 5 месяцев назад

Tried problem 10 using CTE, more complicated maybe : ) with cte as( select * from museum_hours where day in ('Sunday', 'Monday') ), cte2 as( select museum_id, string_agg(day, ', ') as days_comb from cte group by museum_id ) select museum.name, museum.city from cte2 left join museum on cte2.museum_id = museum.museum_id where cte2.days_comb = 'Sunday, Monday';

@kanwalhemant 4 месяца назад

How is this? WITH cte AS ( SELECT museum_id, count(day) AS both FROM museum_hours WHERE day in ('Sunday','Monday') GROUP BY museum_id ) SELECT name, city FROM museum M JOIN cte C ON C.museum_id = M.museum_id WHERE both = 2

@kenadams1521 5 месяцев назад

Hi Thoufiq, I tried different way, but could not find altrenative to to_timestamp() function for mysql, is there any?

@satindersingh2562 5 месяцев назад

May Allah also make me master in Sql like u....❤

@ishtyaqe 5 месяцев назад

Aameen

@dobijr7900 4 месяца назад

@techTFQ pls how should i approach' to_timestamp' in MSSQL

@ankitavishwakarma4851 3 месяца назад

Can we do this with SQL server with same python script?

@-TechLearning 2 месяца назад

Bro, I am not able to connect MySQL to Python and load the data into my database table. can you please help me? I have tried different methods but am still unable to make this happen.

@Mju98 3 месяца назад

Hello sir I need to import a table which has 4 lakh rows. While importing it in a big query sandbox Im getting more errors unable to import it. Any solution for this. It's urgent pls( asked in a interview assignment)

@mousaalzahrani1873 5 месяцев назад

why do we need to send the datasets to sql any more while we can use pandas to read them ?!

@KishanSingh-tk6sg 2 месяца назад

Hi you are going to start SQL batch where will i get link for that

@sakshirana2877 4 месяца назад

Hi If you could share the PowerBI project on Information Security.

@labib8705 2 месяца назад

what is the alternative to to_timestamp in MS Sql , in ms sql its not running

@ujjwalsaxena3722 3 месяца назад

Can you please help me with converting time from varchar to time datatype in MSSQL server. thank you

@akkshheyagarwaal7629 5 месяцев назад

At 34:15 you could have also used GROUP BY Country instead of doing all distinct and string agg for that.

@techTFQ 5 месяцев назад

there are always multiple ways to solve a problem in SQL. However I am not sure what you exactly mean but I am sure there are other solutions too

@akkshheyagarwaal7629 5 месяцев назад

34:06 since the countries were getting repeated, you could have only used GROUP BY Country instead of using another string agg.

@user-hh1nv3pm1k 5 месяцев назад

Want to learn sql from scratch from you.Are there any classes conducted by you? could you please show the path?

@premkistories 5 месяцев назад

Following as I would be interested

@oluseyeoyeyemisunday4890 5 месяцев назад

He has a sql class. You can check his blog. I took his class last year and it really improved my sql skills

@akkshheyagarwaal7629 5 месяцев назад

But how does PostgreSQL know what script is run in Python?

@shubhamborade1112 2 месяца назад

how can we show this as a project like writing a report or what?

@learningislife2934 5 месяцев назад

Sir please create sql course for college students We don't much more money Please create udemy course Thanks sir❤

@user-xt6lq3lx6y 4 месяца назад

hello,I m trying to load the musuem_hours dataset on databricks however it not getting loaded with correct schema .Can someone please help .I have checked the file locally its downloaded with correct schema .Not sure what is wrong

@rajeshkumar-lo7te 5 месяцев назад

can you please explain on how to do it in mysql ?

@vikramamin2853 5 месяцев назад

Hi everyone. This is with regards to Q.15. I am unable to convert the string data type of open and close to time format. Can someone help me out. I am using Mysql. I tried using alter table and modify column , but it did not work