Lets Play coding database. I can see so many forums people are doing. (1 Viewer)

ashill07

New member
Local time
Today, 10:16
Joined
Apr 3, 2021
Messages
17
Inquiries:
1. Find the actor who has acted in the greatest number of movies.
2. Find an actor who has acted in the greatest number of films in excess of an hour.
3. Find the number of movies in Japanese.
4. Find all actors who have played in at least three Spanish-language movies.
5. Find the number of comedy in French.
6. Find all actors who have played in more than one movie genre.
7. Find the customer who has the most loans in the database.
8. For the client found in the previous section, find his favorite movie category.
9. View all countries with customers who have at least 5 rentals in their account.
10. Display the e-mail address of the most active employee.
11. Find the three language versions of the least-rented movies. View their total number of rentals.
12. Find the highest payment among all customers from Bydgoszcz.
13. Do French customers prefer films in French?
14. What is the most popular name among actors, customers and staff? Is it the same name?
15. Calculate the total amount of rentals of the most active Polish customer.
 

Attachments

  • dvd-rental-sample-database-diagram.png
    dvd-rental-sample-database-diagram.png
    127.2 KB · Views: 233

ashill07

New member
Local time
Today, 10:16
Joined
Apr 3, 2021
Messages
17
1. select a.first_name, a.last_name, fa.actor_id, count(*) as film_number from film_actor fa join actor a on fa.actor_id=a.actor_id join film f on f.film_id=fa.film_id group by fa.actor_id, a.first_name, a.last_name order by film_number desc limit 1;
 

ashill07

New member
Local time
Today, 10:16
Joined
Apr 3, 2021
Messages
17
2. select a.first_name, a.last_name, fa.actor_id, count(*) as film_number from film_actor fa join actor a on fa.actor_id=a.actor_id join film f on f.film_id=fa.film_id group by fa.actor_id, a.first_name, a.last_name, f.length having f.length>60 order by film_number desc limit 1;
 

ashill07

New member
Local time
Today, 10:16
Joined
Apr 3, 2021
Messages
17
3.select count(*) from film f join language l on f.language_id=l.language_id where l.name='Japanese' limit 5;
 

ashill07

New member
Local time
Today, 10:16
Joined
Apr 3, 2021
Messages
17
4. select a.first_name, a.last_name, fa.actor_id, count(*) as film_number from film_actor fa join actor a on fa.actor_id=a.actor_id join film f on f.film_id=fa.film_id join language l on l.language_id=f.language_id where l.name='English' group by fa.actor_id, a.first_name, a.last_name having count(*)>2;
 

ashill07

New member
Local time
Today, 10:16
Joined
Apr 3, 2021
Messages
17
5.select count(*) from category as c join film_category as fc on c.category_id=fc.category_id join film as f on f.film_id=fc.film_id join language as l on l.language_id=f.language_id where l.name='English' and c.name='Comedy';
 

ashill07

New member
Local time
Today, 10:16
Joined
Apr 3, 2021
Messages
17
6.select a.first_name, a.last_name, c.name from category as c join film_category as fc on c.category_id=fc.category_id join film as f on f.film_id=fc.film_id join language as l on l.language_id=f.language_id join film_actor as fa on fa.film_id=f.film_id join actor as a on a.actor_id=fa.actor_id group by a.actor_id, c.name having count(c.name)>1;
 

ashill07

New member
Local time
Today, 10:16
Joined
Apr 3, 2021
Messages
17
7.select c.first_name, c.last_name, count(*) from rental r join customer c on c.customer_id = r.customer_id group by r.customer_id, c.first_name, c.last_name order by count(*) desc limit 1;
 

ashill07

New member
Local time
Today, 10:16
Joined
Apr 3, 2021
Messages
17
8.select c.first_name, c.last_name, ca.name as category, count(*) from rental r join customer c on c.customer_id = r.customer_id join inventory i on i.inventory_id=r.inventory_id join film f on f.film_id=i.film_id join film_category fc on fc.film_id=f.film_id join category ca on ca.category_id=fc.category_id where c.first_name='Eleanor' group by ca.name, r.customer_id, c.first_name, c.last_name order by count(*) desc;
 

Users who are viewing this thread

Top Bottom