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

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

[@frankedwin1183]

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

[@don33unique95]

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

[@mohitnemade5320]

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

[@muraliprasad621]

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

[@Sai-nk1vd]

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

[@leonmadara3903]

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]

Thank you so much :) Much appreciated.

[@maghy_kethychannel]

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

[@adnanhashmio]

🎉🎉 this is a blessing 🙌

[@nikhilbhute07]

You are Genius Man.🙌

[@debapriyabehera7146]

Waiting for this kind project 😊

[@user-dn1my6kh4b]

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.

[@nilabja2974]

Amazing query has learnt from you. Thanks

[@hamantjagwan07]

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]

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

[@okechukwuezinne6236]

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

[@gazart8557]

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

[@moinuddinmohd6943]

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.

[@KartikSir_]

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()?

[@Lokeshna83]

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.

[@abisheks.p6223]

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.

[@samkhan4214]

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

[@yaminirajyalakshmiyamini8171]

Wonderful content 👏👏👏

[@cloudpavan]

Excellent tutor,

[@shaikmubeen5093]

You are the best sir

[@fathimafarahna2633]

Much awaited😍As always, best of the best 👍

[@techTFQ]

Thank you :)

[@parthtandel5848]

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

[@F_A_R_man]

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

[@techTFQ]

Your welcome 🙏

[@nishtha_pd]

All set to do this.. 😊

[@yashwanthrasamala5933]

Wait for more such kind of videos

[@techTFQ]

will look into it. thank you

[@dibyabhanumohanta698]

Very nice content!

[@vinayakyerekar762]

Finally wait is over

[@neerajanaidu2558]

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.

[@bhanuprakashneelam8250]

Thank you in advance

[@sameer9368]

Awesome

[@rahulpattnaik4118]

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

[@chinyereobi5456]

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

[@PukiPukiPo]

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]

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]

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

[@sravankumar1767]

Superb explanation 👌 👏 👍

[@techTFQ]

Thank you 🙂

[@valenciawalker6498]

Thank you

[@bhongosinxo6248]

Thank you for another nice video.

[@techTFQ]

Thank you too!

[@lokeshbobbala1765]

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

[@Mrlegacy1_]

And this video is so so superb!💝😍

[@techTFQ]

Thank you

[@mohsinmanzoor2483]

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

[@sam-uw3gf]

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

[@geraldineorieoma7169]

Teach me. I know the data wizard way

[@AnalysisWithVinay]

Thanks

[@PraveenKumar-ic1to]

My sql mentor😊😊😊😊😊

[@johnypl21]

good video

[@sarunlorteerapong335]

Thanks!

[@techTFQ]

Thank you ❤️

[@ravitejak9414]

Excellent Bro 👏

[@techTFQ]

thank you

[@dolandtramp5555]

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]

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

[@davidwayne9864]

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.

[@industryrule-4080]

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

[@sivakumarisadineni3193]

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

[@muhammadshahin3667]

I would like to see a similar video for MongoDB

[@Ankit-rv2my]

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

[@anjalikumar9193]

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

[@satyajitbiswal6162]

Waiting

[@rajujannapreddy7355]

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

[@jeanmarieabengzoa2600]

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

[@user-ki3ip1bq5o]

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?

[@mohammedvahid5099]

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]

I have already done one project using python and snowflake. may be check that out. linke below: kzbin.info/www/bejne/fXnXiWOHjcucbMk

[@kemjhonponce3881]

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

[@anudeepreddy5559]

❤ 💯🔥

[@user-bv2qd5wt1q]

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

[@Sabiha-le7cj]

Woow

[@muzeebdua]

💥💥💥

[@divyajeetsingh5049]

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

[@mohammedshahil4898]

🤩🤩🤩

[@agohachukwuemeka8709]

PLEASE MAKE MORE

[@AnalysisWithVinay]

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

[@KartikSir_]

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.

[@akkshheyagarwaal7629]

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

[@kenadams1521]

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

[@dobijr7900]

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

[@tanveersingh4217]

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]

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

[@sakshirana2877]

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

[@ankitavishwakarma4851]

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

[@user-hh1nv3pm1k]

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

[@premkistories]

Following as I would be interested

[@oluseyeoyeyemisunday4890]

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

[@KishanSingh-tk6sg]

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

[@hyderali1252]

🤝🤝🤝

[@mousaalzahrani1873]

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

[@akkshheyagarwaal7629]

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

[@techTFQ]

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]

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

[@Mrlegacy1_]

Please sir if I may ask, can I also use this method to import data into my MSS Server, or other RDBMS?

[@techTFQ]

Yes you can but the python package which support MSSQL may be different hence need to google that

[@Mrlegacy1_]

@@techTFQ okay sir, thanks a lot!

[@rajeshkumar-lo7te]

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

[@ujjwalsaxena3722]

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

[@satindersingh2562]

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

[@ishtyaqe]

Aameen

[@akkshheyagarwaal7629]

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

[@shubhamborade1112]

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

[@Mju98]

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)

[@anuprauthan1895]

sir can you explain Q-14 query, please

[@labib8705]

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

[@user-xt6lq3lx6y]

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

[@-TechLearning]

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.

[@abdullahfaizal7739]

Assalamu alaikum warah. Taufiq, can you please do an SQL video on time series analysis?

[@techTFQ]

Walaikum salaam , sure, let me consider it.

[@abdullahfaizal7739]

Thanks. Waiting 😊.

[@learningislife2934]

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

[@user-rm1pb9ys7d]

Hi Toufiq, I am trying to connect to the PostgreSQL using the python script you have provided but I am getting the Syntax error at the initial step itself. Please help me if possible. Thanks in advance

[@BlackSouL24]

Before doing his method, create virtual environment and then install pandas, sqlalchemy, pyscopg2 and then follow him

[@kirandarshanooj9716]

I am unable to see any CSV file in the Document path

[@LearnwithNaviOfficial]

How i upload this database to SQL workbench

[@vikramamin2853]

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

[@shreytyagi5580]

i'm facimg a issue, im not able to connect to my postgre server, can somebody tell the possible solution for this problem?

[@insidehead]

Could u share python code for SQL server connection.. i tried but not working.