求助！！sql database相关的问题！求求各位sql大神帮忙看看！非常感谢 2) 查看ER图
2) To query the categories of an actor had been in, the main tables are actor, film and category. file_actor adn fil_category are link tables between film and actor, film and category respectively. To find out the fil categories that an actor had been in, we need to join all the 5 tables. Multiple films an actor had been in may fall into the same category thus we need select distinct categories for each actor. Below is the T-SQL query which needs to replace the place holder <interesting_actor_id> with the actual actual ID If we query by actor's first_name and last_name, we need to tweak the WHERE clause a little bit but it's a pretty simple change.
SELECT DISTINCT category.name
FROM sch_film.actor
INNER JOIN sch_film.film_actor
ON actor.actor_id = film_actor.actor_id
INNER JOIN sch_film.film
ON film_actor.film_id = film.film_id
INNER JOIN sch_film.film_category
ON film.film_id = film_category.film_id
INNER JOIN sch_film.category
ON film_category.category_id = category.category_id
WHERE actor.actor_id = <interesting_actor_id>
3) To query the number of times that an actor's movies had been rented, the main tables are actor, film and rental, and the link tables are film_actor and inventory. Or we can simply say the 5 tables without distinguish them explicitly. But Ipersonally prefer to identify the roles of entities so that we can understand the entity relationship clearly.
We then need to GROUP BY film and query the COUNT of rentals. Again, we need to have WHERE clasue for he actor we are interested in. Below is the T-SQL script.
SELECT film.title,
COUNT(rental.rentl_id) AS rental_times
FROM sch_film.actor
INNER JOIN sch_film.film_actor
ON actor.actor_id = film_actor.actor_id
INNER JOIN sch_film.film
ON film_actor.film_id = film.film_id
INNER JOIN sch_film.inventory
ON film.film_id = inventory.film_id
INNER JOIN sch_film.rental
ON inventory.inventory_id= rental.inventory_id
WHERE actor.actor_id = <interesting_actor_id>
GROUP BY film.title
ORDER BY film.title
4) To get the top customers about number of rentals, we need add customer table. We do not have to worry about which actors are involved. We mainly are interested in the number of rentals. We even do not care too much about what films are rented. So we can focus on tables customer and rental. Notice the schema names are different. We have to specify the schema names in query.
Below is the T-SQL script. There is a place holder <top_number_interested> and we need to replace it with a specific number. If we simply want to see all the records, we can remove TOP <top_number_interested> from the query.
SELECT TOP <top_number_interested>
customer.first_name AS customer_first_name,
customer.last_name AS customer_last_name,
COUNT(*) AS total_number_of_rentals
FROM sch_customer.customer
INNER JOIN sch_film.rental
ON customer.customer_id = rental.customer_id
GROUP BY customer.first_name, customer.last_name
ORDER BY total_number_of_rentals DESC
Let me know if you have any questions or issues.
Cheers!
附注：求赞助积分和C币。加入CSDN将近20年了。最近几年忙小孩没登录。刚才搜索到一本电子书想下载，需要20积分/C币。赞助多少都可以。多谢。
Would you like to answer in English or Chinese? I am in meeting now and will be able to answer later. Thanks.
;