Help with Sakila

hoobajue · #1 (**permalink**) 01-10-10, 14:34

Hello everyone!

I have an assignment which uses the default database sakila.
Here is what I have to do:

List the names of the films whose titles start with W rented by
customers whose last name starts with A and the number of times each
such film was rented (by a customer whose last name starts with A).

I'm working on it and I have hit a wall and here is what I have come up with so far:

Code:

select  film.title, customer.last_name , count(customer.last_name)  from film, customer,
rental, inventory where film.title like 'W%' and film.film_id =
inventory.film_id and rental.inventory_id = inventory.inventory_id and
rental.customer_id = customer.customer_id and customer.last_name like 'A%' group by
film.title, customer.last_name;

If anyone can help me see what I am doing wrong I would really appreciate it!
Thank you so much for your help!
Mike.

r937 · #2 (**permalink**) 01-10-10, 19:43

the first thing you need to do is learn how to write joins

have a look at this --

Code:

  FROM film
INNER
  JOIN inventory
    ON inventory.film_id = film.film_id
INNER
  JOIN rental
    ON rental.inventory_id = inventory.inventory_id
INNER
  JOIN customer
    ON customer.customer_id = rental.customer_id  
   AND customer.last_name LIKE  'A&#37;'
 WHERE film.title LIKE  'W%'

please notice how the sequence in which the tables are joined make sense logically

you start with films which begin with W

then you find all the inventory for those films, then all the rentals for those inventories, then all the rental customers who start with A

at each step along the way, it's very clear as to which table is being added, and on what column

notice how the ON clauses mention the column from the table being joined first, and then equate that column to a column of a table previously mentioned

this style makes queries very easy to understand, and to debug

hoobajue · #3 (**permalink**) 01-10-10, 20:03

Thank you that does make sense and it is much easier to read. I was getting so confused trying to read my own code.

Code:

SELECT film.title, customer.last_name
FROM film
INNER 
JOIN inventory ON inventory.film_id = film.film_id
INNER 
JOIN rental ON rental.inventory_id = inventory.inventory_id
INNER 
JOIN customer ON customer.customer_id = rental.customer_id
AND customer.last_name LIKE  'A&#37;'
WHERE film.title LIKE  'W%'

Does this query look like it would accomplish this task (minus the counting part), it does to me:

Quote:

List the names of the films whose titles start with W rented by
customers whose last name starts with A and the number of times each
such film was rented (by a customer whose last name starts with A).

Now how in the world do I count how many times the film was rented?

Thank you for your help!

r937 · #4 (**permalink**) 01-10-10, 20:40

Quote:

Originally Posted by hoobajue

Does this query look like it would accomplish this task (minus the counting part), it does to me:

hmm.... what happened when you tested it?

Quote:

Originally Posted by hoobajue

Now how in the world do I count how many times the film was rented?

hint: COUNT(*) and GROUP BY

hoobajue · #5 (**permalink**) 01-11-10, 09:54

It's a large database so I'm not sure if the results I'm getting are correct.

r937 · #6 (**permalink**) 01-11-10, 10:16

Quote:

Originally Posted by hoobajue

It's a large database so I'm not sure if the results I'm getting are correct.

then break it down into steps

first, use a simple query pull out all the films that start with W, and grab their ids

then run a query against the inventory table, pulling out the inventory_ids, using WHERE film_id IN ( list of film ids from first query )

then run a query against the rentals table, pulling out the customer ids, using WHERE inventory_id IN ( list of inventory ids from previous query )

finally, pull all the customers based on these rental ids, sort them by name, and compare the list of those customers whose name starts with A

this process will allow you to trust the JOIN query's result

Internet Services

Web Development

Digital Marketing

Consumer Tech