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);
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.
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!
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
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 ❤
Hey thanks Chandoo for another amazing content. Appreciate your teaching method to explain theory and ability to deliver the practical use of it with examples that certainly clears the confusion if any.
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.
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! ❤❤
Good find. I have updated the webpage with correct query. Please use this. with low_rentals as (select i.film_id, count(*) from rental r join inventory i on i.inventory_id = r.inventory_id group by i.film_id having count(*)
@@chandoo_ This will unfortunately not show us films that were not rented at all. We would have to go with your suggestion from the video and start the groupping from film - inventory tables instead of inventory - rental tables.
Both of the queries won't show the "never" rented films. For that you need a left-anti-join on films to rentals (to see which films have null rental record).
@@chandoo_ I think each rental operation have unique rental_id so we have to count how many rental_ids generated for each inventory id, then to film_id. I have tried the folowing query, please justify it. SELECT f.title, i.film_id, count(r.rental_id) rental_count FROM sakila.inventory i join rental r on r.inventory_id=i.inventory_id join film f on f.film_id=i.film_id group by i.film_id having rental_count
@@chandoo_please justify this for 'low rentals query' SELECT f.title, i.film_id, count(r.rental_id) rental_count FROM sakila.inventory i join rental r on r.inventory_id=i.inventory_id join film f on f.film_id=i.film_id group by i.film_id having rental_count
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 !
Thank you for the excellent and straightforward yet professional explanation. I appreciate your valuable efforts and looking forward to more videos from you. 🙏
# H1) How many distinct last names are present in the data ? select count(distinct last_name) as last_names from customer; # H2) What is the busiest day in the rentals date ? select date(rental_date) as Date,count(rental_date) as rentals from rental group by date(rental_date) order by rentals desc limit 1; # H3) How much rentals we make on each day ? select date(r.rental_date) as Date , sum(p.amount) 'Amount Produced' from rental r join payment p on p.rental_id=r.rental_id group by Date order by Date asc; # H4) What are the three top earning days so far? select date(r.rental_date) as Date , sum(p.amount) 'Amount Produced' from rental r join payment p on p.rental_id=r.rental_id group by Date order by SUM(amount) desc LIMIT 3;
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?
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
hi, while executing the 1st question I have faced 2 issues: 1. while writing [select * from film f] the f is not working. Means at the second line i.e.[where f.rental_rate
Hi Chandoo, can you share these concepts using postgresql please because i have learnt postgresql but not mysql, there are queries which are similar in both but such things aren't there in postgresql
Thank you, Chandoo garu, for the very helpful learning I am taking from your channel. #How much money and rentals we make for Store 1 by day? select date(r.rental_date) as Rental_Date, st.store_id, count(*) as Total_Rentals, date(p.payment_date) as Payment_Date, sum(p.amount) as Total_Payments from rental r join staff s on s.staff_id = r.staff_id join store st on st.store_id = s.store_id join payment p on p.rental_id = r.rental_id where st.store_id = 1 group by date(r.rental_date), date(p.payment_date); I guess this is the right analysis of the data requested?
#What are the three top earning days so far for Store 1? select date(p.payment_date) as Payment_Date, sum(p.amount) as Total_Payments from rental r join staff s on s.staff_id = r.staff_id join store st on st.store_id = s.store_id join payment p on p.rental_id = r.rental_id where st.store_id = 1 group by date(p.payment_date) order by Total_Payments desc limit 3; Ans: Payment_Date Total_Payments 2005-08-02 1456.57 2005-07-31 1454.70 2005-08-01 1425.63 This is the result I got while reading data from Rental table prespective.
#Three top earning days, if reading data from Payment table SELECT DATE(p.payment_date) AS Payment_Day, SUM(p.amount) AS Total_Payments FROM PAYMENT p where p.staff_id = 1 GROUP BY DATE(p.payment_date) ORDER BY Total_Payments DESC LIMIT 3; Ans: Payment_Day Total_Payments 2005-08-01 1442.55 2005-07-30 1437.86 2005-07-27 1403.71 I got the above answer while calcuating the Top 3 earning days from the Payment table and checking for the staff_id = 1. My question is, as we know there are only 2 store ids and 2 staff ids, what is the reason for the difference in the answers in between the above 2 comment posts. Am I missing some logic in my Analysis of pulling up the data? Guidance will be welcomed. Thank you!
Query#9 Sir its a good thing U used CTE method, I will need to understand the concept of it bcox its still not clear to me so I did it in a simplest way. Here's the query; SELECT f.title, COUNT(r.rental_id) AS rental_count FROM film f LEFT JOIN inventory i ON f.film_id = i.film_id LEFT JOIN rental r ON i.inventory_id = r.inventory_id GROUP BY f.title HAVING COUNT(r.rental_id) < 1;
Hello Chandoo, thanks for these tutorials, but my com is showing compatibility error, it is saying I try workbench 6.3, but it's not available on mysql website, any help?
#7) select a.title from film a join film_category b on a.film_id = b.film_id join category c on b.category_id = c.category_id where c.name = "Sci-Fi" order by a.title;
In 90% of data analyst work, you don't need either of those. I cover CTE & Joins in the video. Subquery is a similar concept and can be easily learnt. I've never written a sproc in my life. So as of now no plans to expand the SQL section. If in future, I make a course, I will add these topics to it.
Great catch. I will update the webpage with correct query soon. But this one should work. The condition reverts to less than 5 life time rentals. with low_rentals as (select i.film_id, count(*) from rental r join inventory i on i.inventory_id = r.inventory_id group by i.film_id having count(*)
Query no. 6: What is the busiest day? A simple solution is to add an ORDER BY as follows: SELECT date(r.rental_date), count(*) rentals FROM sakila.rental r group by date(r.rental_date) order by rentals desc ; The answer is: 2005-07-31 was the busiest day with 679 rentals.
Halo Chandoo, please interrogate my opinion on query #7. this is how I did it, following your thought process but going my own way: # 7) All Sci-fi films in our catalogue select c.category_id, c.name, fc.film_id, f.title from category c join film_category fc on fc.category_id=c.category_id join film f on f.film_id=fc.film_id having category_id='14';
H2 : select p.staff_id, sum(p.amount) as "total money made", count(p.rental_id) as "total rental count" from payment p join store s on s.manager_staff_id = p.staff_id group by p.staff_id having p.staff_id = 1; H2_Bonus : select p.staff_id, p.amount as "Total money made", p.rental_id as "Total rental count", date(p.payment_date) from payment p join store s on s.manager_staff_id = p.staff_id having p.staff_id = 1 order by p.amount desc limit 3; Hoping those are correct. Lol
H2 solution: select s.store_id, date(r.rental_date) 'rental date', count(r.rental_id) 'total rentals', sum(p.amount) 'total revenue' from rental r join payment p on p.rental_id = r.rental_id join staff st on st.staff_id = p.staff_id join store s on s.store_id = st.store_id where s.store_id = 1 group by date(r.rental_date) order by date(r.rental_date); I hope this is one of the appropriate methods to answer this query.
Hello Chandoo, while working on join function it showed a Error Code:1046, saying No database selected and to select the DB by try double clicking on the schemas. I tried for a long but still was not working....Until i googled it and used the "USE" function. Could you help me understand where did I go wrong and when do we have to apply this "USE" function.
You need to double click on the word "sakila" in the schemas to use it. Alternatively, you can also write "Use sakila;" as the first line of your SQL file so all the subsequent queries run against sakila.
@@chandoo_ I opened the workbench again today to work on homework questions....same error is coming up, i am unable to use Mysql despite double clicking & using USE function.
Query for the HW 2 select date(rental_date) d, count(*) as rental_count from sakila.rental r group by date(rental_date) order by rental_count desc limit 1;
# What are the three top earning days so far? SELECT date(p.payment_date) AS 'Payment Date', COUNT(*) AS 'Total Rental', SUM(p.amount) AS Amount FROM payment p GROUP BY date(p.payment_date) ORDER BY Amount DESC LIMIT 3; Can you suggest improvements for this query?
Select f.film,f.title,f.release_year,c.name,c.category_id from film f join film_category fc using(film_id) join category c using(category_id) where c.category_id=14
H1) How many distinct last names we have in the data? select distinct last_name,c.customer_id,count(last_name) from rental r join customer c on c.customer_id = r.customer_id group by c.customer_id; (or) SELECT COUNT(DISTINCT last_name) AS last_names FROM customer;
QUESTION #9 9) Which movies should we discontinue from our catalogue (less than 2 lifetime rentals) -- less than or equal to 1 as shown in the video. I wanted to get the film title as well ... The below query returns 0 rows because the minimum number of rentals for the result set is 2 SELECT inventory_id, COUNT(inventory_id) FROM rental r JOIN film f ON r.inventory_id=f.film_id GROUP BY inventory_id HAVING COUNT(*)
@@chandoo_ Thanks for the reply. The following query returns the correct/expected output. SELECT f.film_id, i.inventory_id, title, release_year, COUNT(rental_id) AS rentals FROM inventory i JOIN film f ON i.film_id = f.film_id JOIN rental r USING (inventory_id) GROUP BY r.inventory_id HAVING COUNT(rental_id)
Solution using subquery: SELECT inventory_id, title FROM inventory i JOIN film f ON i.film_id = f.film_id JOIN rental r USING (inventory_id) WHERE inventory_id IN (SELECT inventory_id FROM rental GROUP BY inventory_id HAVING COUNT(*)
Dear Professor @chandoo # H1) How many distinct last names we have in the data? SELECT COUNT(DISTINCT c.last_name) AS 'Distinct last name' FROM rental r JOIN customer c ON r.customer_id = c.customer_id; Output: 599
HW 2 Select date(p.payment_date) as date, sum(p.amount) as total_sales, count(*) as total_daily_rentals From payment p Where p.rental_id in ( select r.rental_id From rental r Where r.inventory_id in ( Select i.inventory_id From inventory i Where i.store_id = 1) ) group by date(p.payment_date);
hi everyone to practice these queries we need a sakila data set right where it is provided . please anyone can answer my question if you already know because it may take time for chandu sir reply. documentation related to sakila is there in description but i am not geting where is the actual data set .please anyone can help me ?
Refer to video #1 of this (here - th-cam.com/video/Iceaqdy7mEs/w-d-xo.html ) for instructions on how to install MySQL with Sakila. If you already have MySQL but not Sakila, download the installation scripts from official MySQL page here (in the Example databases section) - dev.mysql.com/doc/index-other.html
Bonus Homework answer- Q-Which day is the busiest day? SELECT DATE(rental_date) AS d, COUNT(*) AS rental_count FROM rental GROUP BY DATE(rental_date) ORDER BY rental_count DESC LIMIT 1;
-- H1) How many distinct last names we have in the data? select count(distinct last_name) from customer; -- H2) How much money and rentals we make for Store 1 by day? with cte as (select date(r.rental_date) , r.rental_id, r.staff_id from rental r where r.staff_id = 1 group by 1,2 ) select date(p.payment_date) 'rental day', count(*) rental , sum(amount) from cte c join payment p on p.rental_id = c.rental_id group by 1 order by 3 desc; -- H-2 What are the three top earning days so far? select date(payment_date) 'rental day', count(*) rental , sum(amount) earnings, row_number() over(order by sum(amount) desc) rankings from payment group by 1 order by 3 desc limit 3;
There is a github page with Sakila ports for various db servers. See here - github.com/ozzymcduff/sakila-sample-database-ports/tree/master/sql-server-sakila-db
-- all sci-fi films in our catalgoe SELECT title FROM FILM WHERE film_id in ( SELECT film_id FROM film_category WHERE category_id IN (SELECT category_id FROM category WHERE name = 'Sci-Fi') );
How much money and rentals we make for Store 1 by day? SELECT date(r.rental_date) 'rental_date' , count(r.rental_id) 'sold_qty' , sum(p.amount) 'amount' FROM sakila.rental r join sakila.payment p on p.rental_id = r.rental_id where r.staff_id = 1 group by date(r.rental_date); is it correct ? if not correct this annyone?
I was eagerly waiting for this video. Thank you sir for providing such good content at zero cost ❤.
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);
Good one.
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.
😮😮😮
Great!
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!
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
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.
You are so good at writing SQL queries.
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 ❤
Hey thanks Chandoo for another amazing content. Appreciate your teaching method to explain theory and ability to deliver the practical use of it with examples that certainly clears the confusion if any.
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.
Chandoo means Quality Content
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!
❤❤
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
Good find. I have updated the webpage with correct query. Please use this.
with low_rentals as
(select i.film_id, count(*)
from rental r
join inventory i on i.inventory_id = r.inventory_id
group by i.film_id
having count(*)
@@chandoo_ This will unfortunately not show us films that were not rented at all. We would have to go with your suggestion from the video and start the groupping from film - inventory tables instead of inventory - rental tables.
Both of the queries won't show the "never" rented films. For that you need a left-anti-join on films to rentals (to see which films have null rental record).
@@chandoo_ I think each rental operation have unique rental_id so we have to count how many rental_ids generated for each inventory id, then to film_id.
I have tried the folowing query, please justify it.
SELECT f.title, i.film_id, count(r.rental_id) rental_count
FROM sakila.inventory i
join rental r on r.inventory_id=i.inventory_id join film f on f.film_id=i.film_id
group by i.film_id having rental_count
@@chandoo_please justify this for 'low rentals query'
SELECT f.title, i.film_id, count(r.rental_id) rental_count
FROM sakila.inventory i
join rental r on r.inventory_id=i.inventory_id join film f on f.film_id=i.film_id
group by i.film_id having rental_count
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 !
Thank you for the excellent and straightforward yet professional explanation. I appreciate your valuable efforts and looking forward to more videos from you. 🙏
Thank you Anna... I really loved the way you are explaining.... Waiting for the next video...
# H1) How many distinct last names are present in the data ?
select count(distinct last_name) as last_names from customer;
# H2) What is the busiest day in the rentals date ?
select date(rental_date) as Date,count(rental_date) as rentals from rental group by date(rental_date) order by rentals desc limit 1;
# H3) How much rentals we make on each day ?
select date(r.rental_date) as Date , sum(p.amount) 'Amount Produced' from rental r
join payment p on p.rental_id=r.rental_id
group by Date
order by Date asc;
# H4) What are the three top earning days so far?
select date(r.rental_date) as Date , sum(p.amount) 'Amount Produced' from rental r
join payment p on p.rental_id=r.rental_id
group by Date
order by SUM(amount) desc LIMIT 3;
thankyou so much sir g 💗💗
This is really helpful for beginners, we want to see some example advanced sql quires. Thanks Chandoo.!
Hi. It is wonderful video. Please let me know will you further cover SQL or it is completed
watched this when it dropped yesterday, back here today to practice. wish me luck🍀
Thank you Chandu anna for making such nice content.
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?
THANK YOU CHANDOO. YOU'RE THE BEST ❤❤
Good tutorial on PQ. I am still learning.
Nice video! Thanks! I would for sure purchase a course by you maybe on Udemy platform, totally worth it
Very very useful, super thanks 🙏
Sir, in your Telugu channel too there is an in-depth video on sql right. Does that covers all concepts what you explained here
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
hi, while executing the 1st question I have faced 2 issues:
1. while writing [select * from film f] the f is not working. Means at the second line i.e.[where f.rental_rate
"Whenever I visit places of worship, I always pray for you, Chandoo. Thank Y🤗U !
For sharing this with us; it means the world to us."
thank you this is helpful
Thank you for what you do Sir 🙏
Hi Chandoo, can you share these concepts using postgresql please because i have learnt postgresql but not mysql, there are queries which are similar in both but such things aren't there in postgresql
Thanks for the video @Chandoo, i clicked the Sample data and completed queries files, but couldn't download it, pls need your help.
Thank you, Chandoo garu, for the very helpful learning I am taking from your channel.
#How much money and rentals we make for Store 1 by day?
select date(r.rental_date) as Rental_Date, st.store_id, count(*) as Total_Rentals,
date(p.payment_date) as Payment_Date, sum(p.amount) as Total_Payments
from rental r
join staff s on s.staff_id = r.staff_id
join store st on st.store_id = s.store_id
join payment p on p.rental_id = r.rental_id
where st.store_id = 1
group by date(r.rental_date), date(p.payment_date);
I guess this is the right analysis of the data requested?
#What are the three top earning days so far for Store 1?
select date(p.payment_date) as Payment_Date, sum(p.amount) as Total_Payments
from rental r
join staff s on s.staff_id = r.staff_id
join store st on st.store_id = s.store_id
join payment p on p.rental_id = r.rental_id
where st.store_id = 1
group by date(p.payment_date)
order by Total_Payments desc
limit 3;
Ans:
Payment_Date Total_Payments
2005-08-02 1456.57
2005-07-31 1454.70
2005-08-01 1425.63
This is the result I got while reading data from Rental table prespective.
#Three top earning days, if reading data from Payment table
SELECT
DATE(p.payment_date) AS Payment_Day,
SUM(p.amount) AS Total_Payments
FROM
PAYMENT p
where p.staff_id = 1
GROUP BY
DATE(p.payment_date)
ORDER BY
Total_Payments DESC
LIMIT 3;
Ans:
Payment_Day Total_Payments
2005-08-01 1442.55
2005-07-30 1437.86
2005-07-27 1403.71
I got the above answer while calcuating the Top 3 earning days from the Payment table and checking for the staff_id = 1.
My question is, as we know there are only 2 store ids and 2 staff ids, what is the reason for the difference in the answers in between the above 2 comment posts.
Am I missing some logic in my Analysis of pulling up the data?
Guidance will be welcomed. Thank you!
Thank you… it is awesome
How many distinct last names do we have in the data?
599 rows
Query#9
Sir its a good thing U used CTE method, I will need to understand the concept of it bcox its still not clear to me so I did it in a simplest way. Here's the query;
SELECT
f.title,
COUNT(r.rental_id) AS rental_count
FROM
film f
LEFT JOIN inventory i ON f.film_id = i.film_id
LEFT JOIN rental r ON i.inventory_id = r.inventory_id
GROUP BY
f.title
HAVING
COUNT(r.rental_id) < 1;
You're the bestestttttttttttttttttttttttttttttttttttttttttttt
Hello Chandoo, thanks for these tutorials, but my com is showing compatibility error, it is saying I try workbench 6.3, but it's not available on mysql website, any help?
@chandoo sir how to answer for the homework problems in notebook or in any option to answer those
Awesome My SQL Queries explanation with examples...💥👌👍
Hi sir I am a finance background student. can I learn this data analysis without code and the benefits of learning,please tell
Good was waiting
SIR JI PLEASE COMPLETE DATA ANALYIST COURSE
select count(*) from customer where active=1;
Please suggest good books for learning sql
table can also be called as Entity, So that we can understand it as Table Relation Diagram.
Thank you
count of active customers = 584. select count(active) from customer where active=1;
Thank you Sir.
H1 : select count(distinct last_name) as 'Number of distinct last names' from customer;
#7) select a.title from film a
join film_category b on a.film_id = b.film_id
join category c on b.category_id = c.category_id
where c.name = "Sci-Fi"
order by a.title;
Hi Chandu bro ❤ Sub queries and stored procedure gurinchi detailed Video chesthaaraa
In 90% of data analyst work, you don't need either of those. I cover CTE & Joins in the video. Subquery is a similar concept and can be easily learnt. I've never written a sproc in my life. So as of now no plans to expand the SQL section. If in future, I make a course, I will add these topics to it.
Great
Please recheck the 9th query. A single film has multiple inventory_id’s.
Great catch. I will update the webpage with correct query soon. But this one should work. The condition reverts to less than 5 life time rentals.
with low_rentals as
(select i.film_id, count(*)
from rental r
join inventory i on i.inventory_id = r.inventory_id
group by i.film_id
having count(*)
#H1) How many distinct last names we have in the data?
select distinct count(c.last_name) from customer c;
Query no. 6: What is the busiest day?
A simple solution is to add an ORDER BY as follows:
SELECT date(r.rental_date), count(*) rentals FROM sakila.rental r
group by date(r.rental_date)
order by rentals desc
;
The answer is: 2005-07-31 was the busiest day with 679 rentals.
I will press LIKE first
Then watch the content
Halo Chandoo, please interrogate my opinion on query #7. this is how I did it, following your thought process but going my own way:
# 7) All Sci-fi films in our catalogue
select c.category_id, c.name, fc.film_id, f.title
from category c
join film_category fc on fc.category_id=c.category_id
join film f on f.film_id=fc.film_id
having category_id='14';
For counting distinct last name Select count(distinct(lastname))) from customer
I didn't got this databass during installation.. would you provide this database
H2 :
select p.staff_id, sum(p.amount) as "total money made", count(p.rental_id) as "total rental count" from payment p join store s on s.manager_staff_id = p.staff_id group by p.staff_id having p.staff_id = 1;
H2_Bonus :
select p.staff_id, p.amount as "Total money made", p.rental_id as "Total rental count", date(p.payment_date) from payment p join store s on s.manager_staff_id = p.staff_id having p.staff_id = 1 order by p.amount desc limit 3;
Hoping those are correct. Lol
please start power bi also sir
Sir how to take the sakila db into my work bench ?
H2 solution:
select s.store_id, date(r.rental_date) 'rental date', count(r.rental_id) 'total rentals', sum(p.amount) 'total revenue'
from rental r
join payment p on p.rental_id = r.rental_id
join staff st on st.staff_id = p.staff_id
join store s on s.store_id = st.store_id
where s.store_id = 1
group by date(r.rental_date)
order by date(r.rental_date);
I hope this is one of the appropriate methods to answer this query.
I did the same thing!!
Hello Chandoo Sir, where can I get the CSV file of Sakila ?
He demonstrated it in the first video of this series. Down below..
th-cam.com/video/Iceaqdy7mEs/w-d-xo.html
I have tried to do so many attempts but I couldn't how to do please upload the vedio of how to add the resources to view the Page
SELECT count(distinct c.last_name) FROM sakila.customer c;
What is the difference between data visualization and
Data analysis
21:48 Distinct last_name is 31.
Hello Chandoo, while working on join function it showed a Error Code:1046, saying No database selected and to select the DB by try double clicking on the schemas. I tried for a long but still was not working....Until i googled it and used the "USE" function. Could you help me understand where did I go wrong and when do we have to apply this "USE" function.
You need to double click on the word "sakila" in the schemas to use it. Alternatively, you can also write "Use sakila;" as the first line of your SQL file so all the subsequent queries run against sakila.
@@chandoo_ I opened the workbench again today to work on homework questions....same error is coming up, i am unable to use Mysql despite double clicking & using USE function.
Please search up online with the error code. There could be other reasons.
How to connect with Shakila database please guide me
Hii sir how to add the users to view the Pages in Power Bi
Query for the HW 2
select date(rental_date) d, count(*) as rental_count
from sakila.rental r
group by date(rental_date)
order by rental_count desc
limit 1;
# What are the three top earning days so far?
SELECT date(p.payment_date) AS 'Payment Date', COUNT(*) AS 'Total Rental', SUM(p.amount) AS Amount
FROM payment p
GROUP BY date(p.payment_date)
ORDER BY Amount DESC
LIMIT 3;
Can you suggest improvements for this query?
Select f.film,f.title,f.release_year,c.name,c.category_id from film f join film_category fc using(film_id) join category c using(category_id) where c.category_id=14
can you share the data for practice
H1) How many distinct last names we have in the data?
select distinct last_name,c.customer_id,count(last_name)
from rental r
join customer c on c.customer_id = r.customer_id
group by c.customer_id;
(or)
SELECT COUNT(DISTINCT last_name) AS last_names
FROM customer;
Sir for me i meet errors in some of these quires. and i copy as yours but I dont know whats wrong
Please make more Telugu videos
QUESTION #9
9) Which movies should we discontinue from our catalogue (less than 2 lifetime rentals) -- less than or equal to 1 as shown in the video.
I wanted to get the film title as well ...
The below query returns 0 rows because the minimum number of rentals for the result set is 2
SELECT
inventory_id, COUNT(inventory_id)
FROM
rental r
JOIN film f ON r.inventory_id=f.film_id
GROUP BY inventory_id
HAVING COUNT(*)
Note: COUNT(inventory_id) or COUNT(*) gives the same result. Also, film_id and inventory_id are same if you compare the outputted columns.
You are joining wrong columns. Even if the values are same, you should join inventory_ids on both sides.
@@chandoo_ Thanks for the reply. The following query returns the correct/expected output.
SELECT
f.film_id,
i.inventory_id,
title,
release_year,
COUNT(rental_id) AS rentals
FROM
inventory i
JOIN
film f ON i.film_id = f.film_id
JOIN
rental r USING (inventory_id)
GROUP BY r.inventory_id
HAVING COUNT(rental_id)
Solution using subquery:
SELECT
inventory_id, title
FROM
inventory i
JOIN
film f ON i.film_id = f.film_id
JOIN
rental r USING (inventory_id)
WHERE
inventory_id IN (SELECT
inventory_id
FROM
rental
GROUP BY inventory_id
HAVING COUNT(*)
Dear Professor @chandoo
# H1) How many distinct last names we have in the data?
SELECT COUNT(DISTINCT c.last_name) AS 'Distinct last name'
FROM rental r
JOIN customer c ON r.customer_id = c.customer_id;
Output: 599
Thanks for all you are doing🎉
Select active, count(*)
from customers
group by active;
homework 2 answer
select date(rental_date),count(*) from rental
group by date(rental_date)
order by count(*)desc;
HW 2
Select date(p.payment_date) as date, sum(p.amount) as total_sales, count(*) as total_daily_rentals
From payment p
Where p.rental_id in (
select r.rental_id From rental r
Where r.inventory_id in (
Select i.inventory_id From inventory i
Where i.store_id = 1)
)
group by date(p.payment_date);
Unable to solve the h2 problem, can you please help me out
where to download sample database
hi everyone to practice these queries we need a sakila data set right where it is provided . please anyone can answer my question if you already know because it may take time for chandu sir reply. documentation related to sakila is there in description but i am not geting where is the actual data set .please anyone can help me ?
Refer to video #1 of this (here - th-cam.com/video/Iceaqdy7mEs/w-d-xo.html ) for instructions on how to install MySQL with Sakila. If you already have MySQL but not Sakila, download the installation scripts from official MySQL page here (in the Example databases section) - dev.mysql.com/doc/index-other.html
select date(rental_date),count(*) as counto from rental group by date(rental_date) order by counto desc;
Bonus Homework answer-
Q-Which day is the busiest day?
SELECT DATE(rental_date) AS d, COUNT(*) AS rental_count
FROM rental
GROUP BY DATE(rental_date)
ORDER BY rental_count DESC
LIMIT 1;
-- H1) How many distinct last names we have in the data?
select count(distinct last_name)
from customer;
-- H2) How much money and rentals we make for Store 1 by day?
with cte as
(select date(r.rental_date) , r.rental_id, r.staff_id
from rental r
where r.staff_id = 1
group by 1,2
)
select date(p.payment_date) 'rental day', count(*) rental , sum(amount)
from cte c
join payment p on p.rental_id = c.rental_id
group by 1
order by 3 desc;
-- H-2 What are the three top earning days so far?
select date(payment_date) 'rental day', count(*) rental , sum(amount) earnings,
row_number() over(order by sum(amount) desc) rankings
from payment
group by 1
order by 3 desc
limit 3;
How to schemas and table import
Select * from customers
Order by customers
Limit 3;
I am using SQL server , how to get the sakila dataset
There is a github page with Sakila ports for various db servers. See here - github.com/ozzymcduff/sakila-sample-database-ports/tree/master/sql-server-sakila-db
How to download/import sakila in my workbench? am not finding any link to download
same
-- all sci-fi films in our catalgoe
SELECT title FROM FILM WHERE film_id in (
SELECT film_id FROM film_category WHERE category_id IN (SELECT category_id FROM category WHERE name = 'Sci-Fi') );
Select count(last_name) from customers.
How much money and rentals we make for Store 1 by day?
SELECT date(r.rental_date) 'rental_date' , count(r.rental_id) 'sold_qty' , sum(p.amount) 'amount' FROM sakila.rental r
join sakila.payment p on p.rental_id = r.rental_id
where r.staff_id = 1
group by date(r.rental_date);
is it correct ? if not correct this annyone?