Advanced SQL for Data Analysis - 10 Powerful Examples in 40 minutes 🕗

Learn ADVANCED SQL with 10 PRACTICAL QUERIES in this video.

You will be learning:

How to use WHERE clause in SQL
Work with AND / OR / LIKE / IN operators in SQL
Getting DISTINCT values with SQL
Grouping data with GROUP BY
Using SQL JOINS
Creating Common Table Expressions (CTE)
Business Analysis and Decision Making with SQL

For the purpose of this video, we will be using the Sakila sample database of MySQL. I explain the process of installing MySQL and the sample databases in the previous video of this series here -

The queries we will be developing are:

# 1) All films with PG-13 films with rental rate of 2.99 or lower

# 2) All films that have deleted scenes

# 3) All active customers

# 4) Names of customers who rented a movie on 26th July 2005

# 5) Distinct names of customers who rented a movie on 26th July 2005

# 6) How many rentals we do on each day?

# 7) All Sci-fi films in our catalogue

# 8) Customers and how many movies they rented from us so far?

# 9) Which movies should we discontinue from our catalogue (less than 2 lifetime rentals)

# 10) Which movies are not returned yet?

# H1) How many distinct last names we have in the data?
# H2) How much money and rentals we make for Store 1 by day?
# What are the three top earning days so far?

~

⏱ VIDEO TOPICS:
===============
0:00 - Introduction to Advanced SQL
0:48 - How to understand the structure of any dataset? (ERD)
4:30 - Primary vs. Foreign Key
7:30 - Let's write SQL queries
7:50 - Query 1
11:32 - Query 2
13:37 - Query 3
14:28 - Homework
14:43 - Query 4
19:32 - Bonus Query
20:34 - Query 5
21:43 - Homework
22:04 - Query 6
24:42 - Query 7
29:15 - Query 8
32:10 - Query 9
35:36 - How to use CTEs (Common Table Expressions)
38:37 - Query 10
42:06 - Homework
42:45 - Limitations of SQL

📁 Sample data and completed queries:
=================================

For all sample files, completed queries and homework solutions, visit the page below:

💥 ADVANCED DATA COURSES BY CHANDOO
======================================

If you want to learn advanced Excel and / or Power BI, consider my paid courses. These in-depth courses cover the entire spectrum of Excel, Power BI, Power Query, Power Pivot and Dashboard reporting in one place with plenty of real-world examples, portfolio projects and completion certificate.

Check out these links:

~

#sql #database #chandoo #freedataanalystcourse #fdac

[subhadeepmitra]

I was eagerly waiting for this video. Thank you sir for providing such good content at zero cost ❤.

[giladyair]

HW2:
with Rentals_s1 as
(select date(r.rental_date) 'Rental Day', r.rental_id, r.staff_id
from rental r
where r.staff_id = 1
group by date(r.rental_date), r.rental_id
)

select date(pa.payment_date) 'Rental day', count(*) 'Rental Count', sum(amount) 'Total Payment'
from Rentals_s1 rs1
join payment pa on pa.rental_id = rs1.rental_id
group by date(pa.payment_date)
order by sum(amount) desc

This was my first time experimenting with SQL, and the lesson was really easy to understand and enjoyable. Thank you so much for your contribution to the community.

[godismyway]

How the hell in your 15 yeras on youtube, you don't have least 1M Subscribers is beyond me. I personaly admir how fast, skilled and good teahcer you are but I beieieve to get the most out of your effort, you should jump in to Technical fileds like WEB Technologies from easy ones like HTML CSS JS and framworks REACT & Angular, and some Backend Technolgies like Node.jS Express Phyton with some Database design courses like NoSQL(MongoDB) and relational the one you are teaching in this video. It is time to evalute our progress. This is from your day one subscriber. Keep it up!

[namrataghosh]

Select count(distinct c.last_name) as Distinct_Last_Name from customer c; (Answer -599)

Select date(rental_date), count(*) as rental_count
from rental
group by date(rental_date)
order by rental_count
limit 1; ( Answer - 2005-07-31 /Count -679)


Thank you Chandoo for this amazing tutorials and so easily explained !

[ousmanesidibe]

I'm start seeing Chandoo as the better version of me, now how am i gonna achieved this? in the mean time thank you for all knowledge shared.

[dailyuploads]

Your teaching method is great. I really appreciate your work and content. Personally i give you 5 stars ⭐⭐⭐⭐⭐ rating. Thanks again continue. Love from pakistan

[abhisheksharma]

Top tier stuff Chandoo, as always. A big admirer of your work.

I resolved the question#7 with one less join ;

select f.film_id, f.title from film f
join film_category fc on fc.film_id = f.film_id
where f.film_id IN (select film_id from film_category where category_id=14);

[remuslupinhp]

Hey Chandoo, this is super good practice. I have been learning SQL from other sources also because I want to be able to absorb the maximum out of your videos, so if possible, please cover Left, Right, and outer joins also as we have only done inner joins till now in this video... Looking forward to your upcoming videos, I am not able to focus on anything else waiting for your videos and then when they come, I have to watch and practice along instantly. Keep up the great work... I know a little bit of Excel, Power BI and SQL so this is where everything comes together.

Thanks a lot!
❤❤

[muhammadsadiq]

You are so good at writing SQL queries.

[bhaweshjoshi]

First of all a big thank you for providing such kind of content .
But i have a humble request please dont stop this series at any cost also don't make it paid or for only members in the end .
This playlist is the only one which i am following from my heart so that i can understand and work well with data .

Once again thank you for all of this ❤

[wasifsaleem]

Hi. It is wonderful video. Please let me know will you further cover SQL or it is completed

[rashidkhan]

In query no.9 you should filter out film_id instead of inventory_id because each film has multiple copies and each copy has its own inventory_id

[SheikImranMohamedAribu]

Dear Chandoo, thank you for the knowledge, I am so grateful for this video, I have learnt a lot. Please teach us more of the queries using "WITH" function and more of using the subqueries. Please enlighten us on these more.

[prasad]

Thank you for the excellent and straightforward yet professional explanation. I appreciate your valuable efforts and looking forward to more videos from you. 🙏

[ArifKhan-qojw]

This is really helpful for beginners, we want to see some example advanced sql quires. Thanks Chandoo.!

[HaileysHomes-ixyu]

watched this when it dropped yesterday, back here today to practice. wish me luck🍀

[stytricks]

Sir, in your Telugu channel too there is an in-depth video on sql right. Does that covers all concepts what you explained here

[harishsree]

Thank you Anna... I really loved the way you are explaining.... Waiting for the next video...

[seanetho]

Not sure if I missed something but for Q8) 31:55, shouldn't we be counting rental ID not customer ID? My thoughts would be that one customer could rent more than one movie (one customer ID could have more than one rental ID) and therefore, counting rental ID would give a better understanding of how many movies each customer rented?

[ravi__negi__]

thank u so muchc channdo sir, , , and my humble request that if u add your perosnal experince diffultes u face in sql in these video aslo then veru helpful