Question

Write a SQL query to display films in inventory that have never been rented for all stores. Include inventory ID, film ID, film name, store ID, and store location in the result set

Answer 1

Answer:

SELECT inventory_id, film_id, title, store_id, address_id

FROM Inventory.Inventory

JOIN Business.store ON Inventory.store_id = store.store_id

JOIN Business.rental ON rental.film_id = Inventory.film_id

JOIN film_text ON Inventory.film_id = film_text.film_id

WHERE COUNT(rental_id) = 0;

Explanation:

The JOIN clause is also used to query tables from two databases. The Inventory and the Business database are joined in the inventory and store table respectively. The Query also joins the inventory table and the film_text table to get the title or name of the film rented where the rental_id count is equal to zero.