SQL Project | SQL Case Study to SOLVE and PRACTICE SQL Queries

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

Рет қаралды 107,299

Күн бұрын

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

Пікірлер: 178

@hamantjagwan07 4 ай бұрын

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 4 ай бұрын

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

@don33unique95 4 ай бұрын

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

@frankedwin1183 4 ай бұрын

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

@leonmadara3903 4 ай бұрын

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 4 ай бұрын

Thank you so much :) Much appreciated.

@mohitnemade5320 4 ай бұрын

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

@maghy_kethychannel 4 ай бұрын

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

@muraliprasad621 4 ай бұрын

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

@Lokeshna83 4 ай бұрын

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.

@Sai-nk1vd 2 ай бұрын

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

@debapriyabehera7146 4 ай бұрын

Waiting for this kind project 😊

@adnanhashmio 4 ай бұрын

🎉🎉 this is a blessing 🙌

@fathimafarahna2633 4 ай бұрын

Much awaited😍As always, best of the best 👍

@techTFQ 4 ай бұрын

Thank you :)

@nikhilbhute07 4 ай бұрын

You are Genius Man.🙌

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

@KartikSir_ 4 ай бұрын

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

@user-dn1my6kh4b 4 ай бұрын

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.

@yaminirajyalakshmiyamini8171 4 ай бұрын

Wonderful content 👏👏👏

@nilabja2974 23 күн бұрын

Amazing query has learnt from you. Thanks

@gazart8557 4 ай бұрын

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

@samkhan4214 4 ай бұрын

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

@shaikmubeen5093 4 ай бұрын

You are the best sir

@cloudpavan 3 ай бұрын

Excellent tutor,

@parthtandel5848 4 ай бұрын

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

@abisheks.p6223 4 ай бұрын

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.

@okechukwuezinne6236 Ай бұрын

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

@yashwanthrasamala5933 4 ай бұрын

Wait for more such kind of videos

@techTFQ 4 ай бұрын

will look into it. thank you

@dibyabhanumohanta698 4 ай бұрын

Very nice content!

@nishtha_pd 4 ай бұрын

All set to do this.. 😊

@F_A_R_man 4 ай бұрын

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

@techTFQ 4 ай бұрын

Your welcome 🙏

@bhanuprakashneelam8250 4 ай бұрын

Thank you in advance

@sameer9368 4 ай бұрын

Awesome

@vinayakyerekar762 4 ай бұрын

Finally wait is over

@rahulpattnaik4118 4 ай бұрын

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

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

@valenciawalker6498 22 күн бұрын

Thank you

@lokeshbobbala1765 4 ай бұрын

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

@PukiPukiPo 4 ай бұрын

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 4 ай бұрын

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 4 ай бұрын

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

@chinyereobi5456 3 ай бұрын

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

@mohsinmanzoor2483 4 ай бұрын

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

@bhongosinxo6248 4 ай бұрын

Thank you for another nice video.

@techTFQ 4 ай бұрын

Thank you too!

@muzeebdua 4 ай бұрын

💥💥💥

@industryrule-4080 4 ай бұрын

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

@sravankumar1767 4 ай бұрын

Superb explanation 👌 👏 👍

@techTFQ 4 ай бұрын

Thank you 🙂

@johnypl21 4 ай бұрын

good video

@Sabiha-le7cj 4 ай бұрын

Woow

@AnalysisWithVinay 4 ай бұрын

Thanks

@sarunlorteerapong335 4 ай бұрын

Thanks!

@techTFQ 4 ай бұрын

Thank you ❤️

@sam-uw3gf 4 ай бұрын

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

@mohammedshahil4898 4 ай бұрын

🤩🤩🤩

@satyajitbiswal6162 4 ай бұрын

Waiting

@sivakumarisadineni3193 4 ай бұрын

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

@PraveenKumar-ic1to 4 ай бұрын

My sql mentor😊😊😊😊😊

@Mrlegacy1_ 4 ай бұрын

And this video is so so superb!💝😍

@techTFQ 4 ай бұрын

Thank you

@rajujannapreddy7355 20 күн бұрын

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

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

@kemjhonponce3881 4 ай бұрын

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

@muhammadshahin3667 4 ай бұрын

I would like to see a similar video for MongoDB

@user-bv2qd5wt1q 2 ай бұрын

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 29 күн бұрын

No , db_url method will be different

@anudeepreddy5559 4 ай бұрын

❤ 💯🔥

@anjalikumar9193 3 ай бұрын

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

@mohammedvahid5099 4 ай бұрын

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 4 ай бұрын

I have already done one project using python and snowflake. may be check that out. linke below: kzfaq.info/get/bejne/fa6ki5OCvczNZps.html

@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 3 ай бұрын

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 4 ай бұрын

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

@user-ki3ip1bq5o 4 ай бұрын

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?

@AnalysisWithVinay 4 ай бұрын

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

@Ankit-rv2my 3 ай бұрын

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

@jeanmarieabengzoa2600 3 ай бұрын

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

@akkshheyagarwaal7629 4 ай бұрын

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

@user-hh1nv3pm1k 4 ай бұрын

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

@premkistories 4 ай бұрын

Following as I would be interested

@oluseyeoyeyemisunday4890 4 ай бұрын

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

@KartikSir_ 4 ай бұрын

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.

@sakshirana2877 3 ай бұрын

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

@tanveersingh4217 4 ай бұрын

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 3 ай бұрын

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

@agohachukwuemeka8709 Ай бұрын

PLEASE MAKE MORE

@akkshheyagarwaal7629 4 ай бұрын

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

@techTFQ 4 ай бұрын

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 4 ай бұрын

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

@hyderali1252 4 ай бұрын

🤝🤝🤝

@dobijr7900 3 ай бұрын

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

@malaksoni3027 5 күн бұрын

sale price means discounted price from regular price thank you

@malaksoni3027 5 күн бұрын

there is no sell price its sale so it is discounted

@abdullahfaizal7739 4 ай бұрын

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

@techTFQ 4 ай бұрын

Walaikum salaam , sure, let me consider it.

@abdullahfaizal7739 4 ай бұрын

Thanks. Waiting 😊.

@Mrlegacy1_ 4 ай бұрын

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

@techTFQ 4 ай бұрын

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

@Mrlegacy1_ 4 ай бұрын

@@techTFQ okay sir, thanks a lot!

@rajeshkumar-lo7te 4 ай бұрын

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

@ujjwalsaxena3722 2 ай бұрын

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

@kenadams1521 4 ай бұрын

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

@satindersingh2562 4 ай бұрын

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

@ishtyaqe 4 ай бұрын

Aameen

@ankitavishwakarma4851 2 ай бұрын

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

@anuprauthan1895 4 ай бұрын

sir can you explain Q-14 query, please

@KishanSingh-tk6sg 2 ай бұрын

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

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

@akkshheyagarwaal7629 4 ай бұрын

I'm not sure if query 3 solution is right. How is London and Paris grouped in the same country (USA)? They should have had their own countries as a separate row.

@techTFQ 4 ай бұрын

You have not understood the problem statement, Please read again

@ravitejak9414 4 ай бұрын

Excellent Bro 👏

@techTFQ 4 ай бұрын

thank you

@learningislife2934 4 ай бұрын

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

@user-rm1pb9ys7d 3 ай бұрын

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 29 күн бұрын

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

@payalbhardwaj5815 4 ай бұрын

Hi toufiq...can we use Max function on duration column

@techTFQ 4 ай бұрын

yes you can but then you may not be able to fetch other fields in same query

@mousaalzahrani1873 4 ай бұрын

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

@labib8705 2 ай бұрын

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

@akkshheyagarwaal7629 4 ай бұрын

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

@user-xt6lq3lx6y 3 ай бұрын

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.

@shubhamborade1112 2 ай бұрын

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

@vikramamin2853 4 ай бұрын

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