Lets Play coding database. I can see so many forums people are doing.

[ashill07]

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.

 

[ashill07]

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]

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]

3.select count(*) from film f join language l on f.language_id=l.language_id where l.name='Japanese' limit 5;

 

[ashill07]

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]

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]

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]

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]

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;

 

[Isaac]

Fun stuff!

 

[ashill07]

Fun stuff!

yes. will you join?