求助！！sql database相关的问题！求求各位sql大神帮忙看看！非常感谢 2) 查看ER图

Question

求助！！sql database相关的问题！求求各位sql大神帮忙看看！非常感谢 2) 查看ER图(图1)，如果我想知道一个演员演过哪些电影，我可以使用actor_id列从“actor”表映射到“film_actor”表。然后我对电影类别感到好奇(例如。喜剧、恐怖、浪漫)。类别名称作为“名称”字段存储在类别表中。 比方说，我想知道一个演员曾参加过哪些电影类别。我该如何映射？我要链接哪些表，需要连接哪些列？ 3) 扩大问题2，如果我决定我更愿意知道，一个演员出演的所有电影，他们被租借了多少次？提示:实际租金包含在“租金”表中 我该如何映射？我要链接哪些表，需要连接哪些列？ 4) 看了演员出租，我开始好奇我们的顶级客户是谁，就出租的总数而言。 我如何映射它以获得顶级客户的名单？我要链接哪些表，需要连接哪些列？ 图2是英文版的问题

Answer 1

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币。赞助多少都可以。多谢。