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.
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);
38:25 Hello chandoo, You are very kind hearted person. That you provide us high quality content. I am studying data analytics from paid course, but I am learning from here. 🙏 There is a silly mistake .. In question 9 solution, ... one film_id is present in many inventory_id... so you calculated it in terms of inventory_id ... maybe this film is popular but often rented from some other inventory_id ... if we calculate it respect of film... solution will be- with low_rentals as (Select i.film_id, count(*) as count from rental r Join inventory i on r.inventory_id = i.inventory_id Group by film_id Having count(*)
yes I agree, below is my solution: # 12) Which movies should we discontinue(less than 5 lifetime rentals) with low_rentals as (select i.film_id, r.inventory_id, count(*) from rental r join inventory i on r.inventory_id = i.inventory_id group by r.inventory_id having count(*)
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
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!
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.
# 7 : I wish you mentioned how to get rid of the null values, but I figured it out, thanks Chandoo for your great effort. This is my solution before checking yours: select fc.category_id, fc.film_id, f.title, c.name,c.category_id from film_category fc left join category c on c.name like 'Sci-Fi' and c.category_id = fc.category_id right join film f on fc.film_id = f.film_id where c.name is not null and c.category_id is not null order by name desc ;
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.
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?
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
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 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! ❤❤
Thank you for the excellent and straightforward yet professional explanation. I appreciate your valuable efforts and looking forward to more videos from you. 🙏
24:39 For this Home work we have to use ORDER BY to find the solution. select date(rental_date) as Date, count(*) as rental_count from rental group by date(rental_date) order by rental_count desc limit 1; :)
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 !
14:35 Solution for Home work; [select count(*) AS total_customers from customer;] I also tried with what is the active, inactive and total customers. Here is a solution: [select count(*) as total_customers, sum(case when active=1 then 1 else 0 end) as active_customers, sum(case when active=0 then 1 else 0 end) as inactive_customers from customer;] I hope you find it useful :)
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 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
22:03 Home Work 1). How many distinct last names we have in the data? Solution: select count(distinct c.last_name) as Distinct_Last_Name from rental r join customer c on c.customer_id=r.customer_id;
Here's how I wrote my #7 Query: "select title, c.name, release_year, rental_rate, length, rating from film f join film_category fc on f.film_id = fc.film_id join category c on c.category_id = fc.category_id where c.name='Sci-Fi';" I guess my brain works sequentially as the above flow is how I understood what I was supposed to do. I chose different tables because I wanted the viewer to have a holistic view of the sci-fi movies in our catalog.
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
Hii I got stuck at the point of imputing a password and Username on the app. Can i get assistance. Is there a special format to imputing the password. Thank you
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?
Sir in question no. 8 you are selecting many columns but grouping them by one column. Yes your queer run . But in my SQL it is throwing error. Like selected column are not in group by
having is used only when you are grouping data and accepts the grouped columns or aggregates for criteria. For ex: we can use having to see which products have total sales > 2mn (here the group by would on product, the sum(sales) > 2mn would in having). where is used to filter the data the query sees. Usually where runs first then query is computed then if there is grouping then it is applied and then if there is having that is used to further reduce the values you see in output.
#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;
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!
# 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;
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(*)
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.
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
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
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.
Used CTE to do question 7: # 10) All sci-fi films in our catalogue with f_category as (select c.film_id, c.category_id, f.title from film_category c join film f on f.film_id = c.film_id ) select f.film_id, f.title, c.name from f_category f join category c on f.category_id = c.category_id where c.name = "Sci-Fi";
I was eagerly waiting for this video. Thank you sir for providing such good content at zero cost ❤.
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!
Nice
SELECT date(rental_date), count(*) FROM sakila.rental
group by date(rental_date)
order by count(*) desc
@@vasanthkotimisetti1255 @giladyair7007 can we use max function here?
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.
38:25
Hello chandoo,
You are very kind hearted person. That you provide us high quality content. I am studying data analytics from paid course, but I am learning from here. 🙏
There is a silly mistake .. In question 9 solution, ... one film_id is present in many inventory_id... so you calculated it in terms of inventory_id ... maybe this film is popular but often rented from some other inventory_id ... if we calculate it respect of film... solution will be-
with low_rentals as
(Select i.film_id, count(*) as count from rental r
Join inventory i on r.inventory_id = i.inventory_id
Group by film_id
Having count(*)
yes I agree, below is my solution:
# 12) Which movies should we discontinue(less than 5 lifetime rentals)
with low_rentals as
(select i.film_id, r.inventory_id, count(*)
from rental r
join inventory i
on r.inventory_id = i.inventory_id
group by r.inventory_id
having count(*)
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.
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!
You are so good at writing SQL queries.
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.
# 7 : I wish you mentioned how to get rid of the null values, but I figured it out, thanks Chandoo for your great effort.
This is my solution before checking yours:
select fc.category_id, fc.film_id, f.title, c.name,c.category_id
from film_category fc
left join category c
on c.name like 'Sci-Fi'
and c.category_id = fc.category_id
right join film f
on fc.film_id = f.film_id
where c.name is not null and c.category_id is not null order by name desc ;
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.
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?
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
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 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!
❤❤
Thank you Anna... I really loved the way you are explaining.... Waiting for the next video...
Thank you for the excellent and straightforward yet professional explanation. I appreciate your valuable efforts and looking forward to more videos from you. 🙏
This is really helpful for beginners, we want to see some example advanced sql quires. Thanks Chandoo.!
watched this when it dropped yesterday, back here today to practice. wish me luck🍀
Chandoo means Quality Content
24:39 For this Home work we have to use ORDER BY to find the solution.
select date(rental_date) as Date, count(*) as rental_count
from rental
group by date(rental_date)
order by rental_count desc
limit 1;
:)
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 !
Select date(rental_date), count(*) as rental_count
from rental
group by date(rental_date)
order by rental_count desc
limit 1;
Am I Correct?
Hi. It is wonderful video. Please let me know will you further cover SQL or it is completed
14:35 Solution for Home work;
[select count(*) AS total_customers
from customer;]
I also tried with what is the active, inactive and total customers.
Here is a solution:
[select
count(*) as total_customers,
sum(case when active=1 then 1 else 0 end) as active_customers,
sum(case when active=0 then 1 else 0 end) as inactive_customers
from customer;]
I hope you find it useful :)
Thank you Chandu anna for making such nice content.
How many distinct last names do we have in the data?
599 rows
THANK YOU CHANDOO. YOU'RE THE BEST ❤❤
Good tutorial on PQ. I am still learning.
Thank you for what you do Sir 🙏
Very very useful, super thanks 🙏
You're the bestestttttttttttttttttttttttttttttttttttttttttttt
can a mandatory joining class be created for the dataset?
@chandoo sir how to answer for the homework problems in notebook or in any option to answer those
select count(*) from customer where active=1;
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
Thank you… it is awesome
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
Sir, in your Telugu channel too there is an in-depth video on sql right. Does that covers all concepts what you explained here
thankyou so much sir g 💗💗
22:03 Home Work 1). How many distinct last names we have in the data?
Solution:
select count(distinct c.last_name) as Distinct_Last_Name
from rental r
join customer c on c.customer_id=r.customer_id;
SIR JI PLEASE COMPLETE DATA ANALYIST COURSE
Sir how to take the sakila db into my work bench ?
Nice video! Thanks! I would for sure purchase a course by you maybe on Udemy platform, totally worth it
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 this is helpful
Please suggest good books for learning sql
Hii sir how to add the users to view the Pages in Power Bi
Here's how I wrote my #7 Query:
"select title, c.name, release_year, rental_rate, length, rating from film f
join film_category fc on f.film_id = fc.film_id
join category c on c.category_id = fc.category_id
where c.name='Sci-Fi';"
I guess my brain works sequentially as the above flow is how I understood what I was supposed to do. I chose different tables because I wanted the viewer to have a holistic view of the sci-fi movies in our catalog.
How to connect with Shakila database please guide me
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
I encountered the same thing. I always had to write sakila.table name. No matter if it was selected. Consequently, I had issues with the CTE.
Awesome My SQL Queries explanation with examples...💥👌👍
Hii I got stuck at the point of imputing a password and Username on the app. Can i get assistance. Is there a special format to imputing the password. Thank you
I didn't got this databass during installation.. would you provide this database
can you share the data for practice
"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."
How to schemas and table import
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;
21:48 Distinct last_name is 31.
What is the difference between data visualization and
Data analysis
Good was waiting
sir i am downloaded my SQL benchmark and reverse engineer data from select schemes to reverse engineer is not click not coming any option
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?
Sir in question no. 8 you are selecting many columns but grouping them by one column. Yes your queer run . But in my SQL it is throwing error. Like selected column are not in group by
Hi sir I am a finance background student. can I learn this data analysis without code and the benefits of learning,please tell
Question:
What is the difference between the “where” keyword and the “having” keyword?
Thanks.
having is used only when you are grouping data and accepts the grouped columns or aggregates for criteria. For ex: we can use having to see which products have total sales > 2mn (here the group by would on product, the sum(sales) > 2mn would in having).
where is used to filter the data the query sees.
Usually where runs first then query is computed then if there is grouping then it is applied and then if there is having that is used to further reduce the values you see in output.
please start power bi also sir
Thanks for all you are doing🎉
Unable to solve the h2 problem, can you please help me out
How to import all tables in sql
Thank you
Sir for me i meet errors in some of these quires. and i copy as yours but I dont know whats wrong
table can also be called as Entity, So that we can understand it as Table Relation Diagram.
Thank you Sir.
where to download sample database
For counting distinct last name Select count(distinct(lastname))) from customer
i couldnt get data set,where it is?
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
#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;
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!
count of active customers = 584. select count(active) from customer where active=1;
I will press LIKE first
Then watch the content
# 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;
H1 : select count(distinct last_name) as 'Number of distinct last names' from customer;
14:38 In my system no. of active customer is 584.
i am not able to download Sakila Data base it's showing error (permission denied)
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(*)
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.
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
folks... i couldn't find the video link of data analysis glossary terms .... can any1 direct me plz...
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
#H1) How many distinct last names we have in the data?
select distinct count(c.last_name) from customer c;
How to download/import sakila in my workbench? am not finding any link to download
same
same
Unable to get dataset
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.
Please make more Telugu videos
Used CTE to do question 7:
# 10) All sci-fi films in our catalogue
with f_category as
(select c.film_id, c.category_id, f.title
from film_category c
join film f
on f.film_id = c.film_id
)
select f.film_id, f.title, c.name
from f_category f
join category c
on f.category_id = c.category_id
where c.name = "Sci-Fi";
Great
link for the dataset
It is in the video description or here - chandoo.org/dac
views truggers r they use in real life?
Select * from customers
Order by customers
Limit 3;