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

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

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

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

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

14 июн 2024

Ссылка:

Скачать:

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

Добавить в:

Мой плейлист

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

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

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

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

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

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

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

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

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

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

@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

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

Amazing query has learnt from you. Thanks

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

You are Genius Man.🙌

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

Waiting for this kind project 😊

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

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

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

🎉🎉 this is a blessing 🙌

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

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

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

Excellent tutor,

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

Wonderful content 👏👏👏

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

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

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

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

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

You are the best sir

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

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

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

Very nice content!

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

Much awaited😍As always, best of the best 👍

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

Thank you :)

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

All set to do this.. 😊

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

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

Wait for more such kind of videos

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

will look into it. thank you

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

Awesome

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

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

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

Your welcome 🙏

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

Thank you in advance

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

Superb explanation 👌 👏 👍

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

Thank you 🙂

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

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

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

good video

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

Thank you for another nice video.

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

Thank you too!

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

Thank you

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

Thanks

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

@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 ❤️

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

And this video is so so superb!💝😍

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

Thank you

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

My sql mentor😊😊😊😊😊

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

Woow

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

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

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

@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

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

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

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

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

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

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

@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

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

PLEASE MAKE MORE

@muzeebdua 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?

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

I would like to see a similar video for MongoDB

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

🤩🤩🤩

@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

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

Waiting

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

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

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

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

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

@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

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

❤ 💯🔥

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

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

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

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

@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

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

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

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

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

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

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

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

🤝🤝🤝

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

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

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

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

@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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

@@techTFQ okay sir, thanks a lot!

@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

@malaksoni3027 15 дней назад

sale price means discounted price from regular price thank you

@malaksoni3027 15 дней назад

there is no sell price its sale so it is discounted

@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

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

@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

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

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

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

Aameen

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

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

@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

@tonye_jimgeorge 5 дней назад

I am really having a hard time connecting to the postgres with python, the code is refusing to work, frustrated

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

How load data into database

@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 Месяц назад

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