I have tried several times to answer the following question with Oracle 12: write the SQL statement that displays movie titles that were never rented by customers?

Here is the schema I need to follow to answer the question: here is the schema

SELECT titre 
FROM film INNER JOIN copie ON film.id_film = copie.id_film
          INNER JOIN location ON copie.id_copie = location.id_copie
          INNER JOIN client ON client.id_clt = location.id_client
WHERE id_loc IS NULL;

when i made this request, it gave me no result. i strongly believe that there is something missing in my request but i don't know what. Could someone please help me?

CodePudding user response：

write the SQL statement that displays movie titles that were never rented by customers?

I don't see that info in ERD you posted, but - I presume you do know which table stores information about movies being rented. In that case, one option is to use the MINUS set operator; something like this:

select movie_id from all_movies
minus
select movie_id from movies_being_rented;

CodePudding user response：

Since this is a homework question:

You can INNER JOIN the copie to location on their primary and foreign key relationship and put that into a sub-query.

Then, you can either:

1. Take the film table and LEFT OUTER JOIN it to that sub-query and then GROUP BY the film's primary key and find the films HAVING a COUNT of zero rows in the joined sub-query and return the title (using an aggregation function or adding title to the GROUP BY clause); or
2. Find, for each film, WHERE there NOT EXISTS a matching row in that sub-query.