Movies Management system
Set-1
1. Count the members who have gold cards.
Ans: select count(ccd.customer_id) as Count from
customer_card_details ccd join library_card_master lcm on
ccd.card_id=lcm.card_id where
lcm.description='gold';
2. Display the name of member who issued movie and the count of the movies issued and display 0 for the member
who has not issued any movie.
Ans: select cm.customer_name, ifnull(count(cid.movie_id), 0) as Count from
customer_master cm left join customer_issue_details cid on
cm.customer_id=cid.customer_id
group by cm.customer_id;
3. Display the name of the person starting with letter 'r' and category is 'comedy'
Ans: select distinct cm.customer_name from
customer_master cm join customer_issue_details cid join movies_master mm on
cm.customer_id=cid.customer_id and cid.movie_id=mm.movie_id
where cm.customer_name like 'R%' and mm.movie_category='COMEDY';
4. Display id, name & total rent of customers for movie issued
Ans: select cm.customer_id, cm.customer_name, count(mm.movie_id)*mm.rent_cost from
customer_master cm join customer_issue_details cid join movies_master mm on
cm.customer_id=cid.customer_id and cid.movie_id=mm.movie_id
group by cm.customer_id;
5. Display id,name,card id,amount in $(amount/54.42) up to 0 decimals
Ans: select cm.customer_id,cm.customer_name,lcm.card_id,round(lcm.amount/54.42) as $_Amount from
customer_master cm join customer_card_details ccd join library_card_master lcm on
cm.customer_id=ccd.customer_id and ccd.card_id=lcm.card_id
group by cm.customer_id;
6. Display id, name of customers who don’t have library card but still have issued the movie
Ans: select distinct cid.customer_id, cm.customer_name
from customer_issue_details cid join customer_master cm
on cm.customer_id=cid.customer_id
where cid.customer_id not in
(select customer_id from customer_card_details);
7. Display the no. of customers with first letter 'r' and have paid fine, i.e. actual return date is greater than return date
Ans: select cm.customer_id, count(distinct cm.customer_id)
from customer_master cm join customer_issue_details cid on
cm.customer_id=cid.customer_id
where cid.actual_date_return>cid.return_date and cm.customer_name like 'R%'
group by cm.customer_id;
8. Display customer name, customer id who have issued max and min no. of movies issued
Ans: select cm.customer_name, cm.customer_id
from customer_master cm join customer_issue_details cid
on cm.customer_id =cid.customer_id
group by cm.customer_id having
count(cid.movie_id) in (select max(t.top) as Count_Movie from (select count(movie_id) as top from
customer_master cm join customer_issue_details cid
on cm.customer_id =cid.customer_id group by cm.customer_id) as t)
or
count(cid.movie_id) in (select min(b.bottom) as Count_Movie from (select count(movie_id) as bottom from
customer_master cm join customer_issue_details cid
on cm.customer_id =cid.customer_id group by cm.customer_id) as b);
9. Display id, name, mobile num and description of all customers. If mobile num is not available then display address
as alias contact, for those who doesn't have library cards display null as description.
Ans: select cm.customer_id, cm.customer_name, cm.contact_no,
cast(coalesce(cm.contact_no, cm.contact_add) as char) as contact_alias,
ifnull(lcm.description,null) as description
from
customer_master cm left join customer_card_details ccd
on cm.customer_id=ccd.customer_id
left join library_card_master lcm
on ccd.card_id=lcm.card_id;
10. Display customer details and movie id for those who issued same movie more than one time OR Dislay customer details who watched same movie more than once.
Ans: select cm.customer_id, cm.customer_name, cid.movie_id, count(cid.movie_id) as Movie_Count
from customer_master cm join customer_issue_details cid
on cm.customer_id = cid.customer_id
group by cid.movie_id, cm.customer_id
having count(movie_id)>1;
11. Display customer information those who has library cards
Ans: select cm.customer_id, cm.customer_name from
customer_card_details ccd join customer_master cm join library_card_master lcm
on cm.customer_id=ccd.customer_id and ccd.card_id=lcm.card_id;
12. Display the members who watch the movie but doesnt have card
Ans: select cm.customer_id, cm.customer_name from
customer_master cm join customer_issue_details cid
on cm.customer_id=cid.customer_id
where cm.customer_id not in
(select customer_id from customer_card_details);
13. Display Sr no as 2 digits of issue id,emp id,movie watched,video id and sort by Sr no.
Ans: select substring(cid.issue_id,4) as Sr_No, cm.customer_name,
cm.customer_id, cid.issue_id as video_id, mm.movie_id, mm.movie_name
from customer_issue_details cid join customer_master cm join movies_master mm
on cm.customer_id=cid.customer_id and cid.movie_id=mm.movie_id
order by Sr_no;
14. Display total revenue spent on videos by each customer
Ans: select cm.customer_id, cm.customer_name,
ifnull((count(cid.issue_id)*mm.rent_cost),0) from
customer_master cm left join customer_issue_details cid
on cid.customer_id=cm.customer_id
left join movies_master mm
on mm.movie_id = cid.movie_id
group by cm.customer_id
order by cm.customer_id;
15. Display customer name in perfect order i.e. 1st letter in ucase remaining lcase
Ans: select concat(ucase(substring(customer_name,1,1)),lcase(substring(customer_name,2)))
from customer_master;
16. Count how many times a movie issued and arrange them in desc order and display 0 for the movie not issued
Ans: select ifnull(count(cid.movie_id),0) as Count from
movies_master mm left join customer_issue_details cid on
mm.movie_id=cid.movie_id
group by cid.movie_id
order by Count desc;
17. Display cus id and cus name and address as if phone num presents display phone num otherwise address.
Ans: select customer_id, customer_name,
cast((coalesce(contact_no, contact_add)) as char) as Address
from customer_master;
18. Display number of customers registered in 2012 year and provided contact num use NO_OF_CUSTOMERS as alias
name.
Ans: Select count(customer_id) as No_of_Customers from
customer_master where
date_of_registration like '%2012%' and
contact_no is not null;
19. Display customer id, customer name, year of registration, library card id, card issue date alias name
registered_year for year of registration.
Ans: select cm.customer_id, cm.customer_name, year(date_of_registration) as Registered_Year, ccd.card_id,
ccd.issue_date from customer_master cm join customer_card_details ccd on
cm.customer_id=ccd.customer_id;
20. Display movie name and num of times movie issued to customers. In case of no movie issued to customers display
0.Use alias name as NO_OF_TIMES
Ans: select mm.movie_name, ifnull(count(cid.movie_id),0) as Count from
movies_master mm left join customer_issue_details cid on
mm.movie_id=cid.movie_id
group by cid.movie_id;
21. Display customer id and customer name ,number of times movie issued to customer in comedy movie category. Display only customers who has issude more than once
Ans: select cm.customer_id, cm.customer_name, count(mm.movie_id) as Count from
customer_master cm join movies_master mm join customer_issue_details cid on
cm.customer_id=cid.customer_id and mm.movie_id=cid.movie_id
where mm.movie_category='comedy'
group by cm.customer_id having Count>1;
22. Display customerid and total rent paid by them.Use alias name as total_cost.
Ans: select cid.customer_id, count(mm.movie_id)*mm.rent_cost as total_cost from
customer_issue_details cid join movies_master mm on
cid.movie_id=mm.movie_id
group by cid.customer_id;
23. Display customer id, customer name, contact no, num of movies issued to customer based on category and
category. Display the customer who has issued for more than one movie from that category. Display phone num
as "+91-987- 654-3210".
Ans: select cm.customer_id, cm.customer_name, count(mm.movie_id) as Count, cast(
concat('+91','-',substring(cm.contact_no,1,3),'-',substring(cm.contact_no,4,3),'-',substring(cm.contact_no,7)) as
char) as Phone_Number
from Customer_Master cm join Customer_Issue_Details cid join Movies_Master mm on
cm.customer_id=cid.customer_id and cid.movie_id=mm.movie_id
group by cm.customer_id, mm.movie_category
having Count>1;
Set-2
1).count the members who has DES1 cards
select count(customer_id) DES1_holder
from customer_card_details
inner join
library_card_master
on customer_card_details.card_id=library_card_master.card_id
where description='des1';
2)display the name of member who issued movie and the count of the
movies issued and display 0 for the member who have not issued any
movie
select customer_name,count(issue_id)
from
customer_master
left outer join
customer_issue_details
on customer_master.customer_id=customer_issue_details.customer_id
group by customer_master.customer_id;
3)display the name of the person starting with letter 'r' and category
is 'comedy'
Select distinct m.customer_name from customer_master m,customer_issue_details i,movies_master mi
where mi.movie_category='comedy'and mi.movie_id=i.movie_id and i.customer_id=m.customer_id
and m.customer_name like 'R%' ;
4)display id,name & total rent of customers for movie issued
select cm.customer_id,customer_name,sum(rent_cost)
from
customer_master cm
inner join
customer_issue_details cid
on cid.customer_id=cm.customer_id
inner join
movies_master mm
on
mm.movie_id=cid.movie_id
group by cid.customer_id;
5)display id,name,card id,amount in $(amount/54.42) upto 0 decimals
select cm.customer_id,customer_name,ccd.card_id,round(amount/54.42,0) amount
from customer_master cm
inner join
customer_card_details ccd
on cm.customer_id=ccd.customer_id
inner join
library_card_master lcm
on
ccd.card_id=lcm.card_id;
6)display id,name of customers who dont have library card but still
have issued the movie
select customer_id,customer_name
from customer_master
where
customer_id in
(select customer_id
from customer_issue_details
where customer_id not in
( select customer_id from customer_card_details));
7)display the no.of customers with first letter 'r' and have paid fine
i.e actual return date is greater than return date
select count(distinct customer_name) NO_CUST
from
customer_master cm
inner join
customer_issue_details cid
on cm.customer_id=cid.customer_id
where customer_name like 'r%'
and actual_date_return>return_date;
8)display customer name,customer id who have issued max and min no.of
movies
select customer_name,cm.customer_id
from customer_master cm
inner join
customer_issue_details cid
on cm.customer_id=cid.customer_id
group by customer_id
having count(issue_id) in (
select max(issue)
from(
select customer_id,count(issue_id) issue
from customer_issue_details
group by customer_id)t)
union
select customer_name,cm.customer_id
from customer_master cm
inner join
customer_issue_details cid
on cm.customer_id=cid.customer_id
group by customer_id
having count(issue_id) in (
select min(issue)
from(
select customer_id,count(issue_id) issue
from customer_issue_details
group by customer_id)t);
9)display id,name,mobile num and description of all customers.if
mobile num is not available then display address as alias contact,for
those who does't have library cards display null as description
select cm.customer_id,customer_name,coalesce(
contact_no,contact_add) CONTACT,coalesce(
description,null) DESCRIPTION
from customer_master cm
left outer join
customer_card_details ci
on
cm.customer_id=ci.customer_id
left outer join
library_card_master lc
on
ci.card_id=lc.card_id;
10)display customer details and movie id for those who issued same movie more than one time
select cm.*,movie_id
from customer_master cm
inner join
(
select distinct customer_id,movie_id from customer_issue_details group by movie_id,customer_id having count(issue_id)>1)t
on cm.customer_id=t.customer_id;
11)display customer information those who has library cards
select cm.*
from customer_master cm
inner join
customer_card_details ccd
on cm.customer_id=ccd.customer_id;
12)display the members who watch the movie but doesnt have card
select customer_name
from customer_master
where
customer_id in
(select customer_id
from customer_issue_details
where customer_id not in
( select customer_id from customer_card_details));
13)display sr no as 2 digits of issue id,emp id,movie watched, by sr no
select concat(substr(issue_id,3,2),substr(customer_id
,4,2),movie_name) SR_NO
from customer_issue_details cid
inner join
movies_master mm
on mm.movie_id=cid.movie_id
order by SR_NO;
14)display total revenue spent on videos by each customer
select customer_id,sum(rent_cost)
from customer_issue_details cid
inner join
movies_master mm
on
cid.movie_id=mm.movie_id
group by
customer_id;
15)display customer name in perfect order
i.e 1st letter in ucase remaining lcase
select concat(ucase(substr(customer_name,1,1)),lcase(substr(customer_name,2))) name
from
customer_master;
16).count how many times a movie issued and arrange them in desc order
and display 0 for the movie not issued
select movies_master.movie_id,count(issue_id) COUNT
from customer_issue_details cid
right outer join
movies_master
on
movies_master.movie_id=cid.movie_id
group by movies_master.movie_id
order by count desc;
17)waq to display cus id and cus name and address as if phone num
presents display phone num otherwise address.
select customer_id,customer_name,coalesce(
contact_no,contact_add) contact
from customer_master;
18).waq that num of customers registered in 2012 year and provided contact num
use NO_OF_CUSTOMERS as alias name
select count(customer_id) no_of_customers
from
customer_master
where year(date_of_registration)='2012'
and
contact_no is not null;
19)display customer id, cus name,year of registration,library card id,card issue date
alias name registered_year for year of registration
select cm.customer_id,customer_name,year(date_of_registration) registered_year,
card_id,issue_date
from
customer_master cm
inner join
customer_card_details ccd
on
cm.customer_id=ccd.customer_id;
20)waq to display movie name and num of times movie issued to
customers..in case of no movie issued
to customers display 0.. use alias name as NO_OF_TIMES
select movie_name,count(issue_id) NO_OF_TIMES from customer_issue_details cid
right outer join
movies_master mm
on mm.movie_id=cid.movie_id
group by mm.movie_id;
21)waq to display customer id and customer name ,num of times movie issued to customer in comedy movie category
display only customers who has issude more than once
select cm.customer_id,customer_name,count(issue_id) count
from
customer_master cm
inner join
customer_issue_details cid
on
cm.customer_id=cid.customer_id
inner join
movies_master mm
on
mm.movie_id=cid.movie_id
where movie_category='comedy'
group by
cm.customer_id,mm.movie_id
having count>1;
22)waq to display customerid and total rent paid by them.
use alias name as total_cost
select customer_id,sum(rent_cost) total_cost
from customer_issue_details cid
inner join
movies_master mm
on
cid.movie_id=mm.movie_id
group by
customer_id;
23)waq to display customerid,cusname,contactno,num of movies issued to customer based on category and category
display the customer who has issude for more than one movie from that caregory.
display phone num as "+91-987-654-3210".
select cm.customer_id,customer_name,concat('+91-',substr(contact_no,1,3),'-',substr(contact_no,4,3),'-',substr(contact_no,-4)) contact,
count(issue_id) count,movie_category
from
customer_master cm
inner join
customer_issue_details cid
on
cm.customer_id=cid.customer_id
inner join
movies_master mm
on
mm.movie_id=cid.movie_id
group by
cm.customer_id,movie_category
having count>1;
Thanks for post your comment, it is very useful. I have a website and want to share with you. you can referenced. Have a good time.
ReplyDeleteash vs evil dead hulu
the revenant solarmovie
padman full movie watch online 123movies
inside out full movie online free