Query with subquery

Asked

Viewed 165 times

0

tabelas do banco de dados

I need to answer this question: 4) Find the name and address of the customers who rented all the films from the video store.

I don’t have a way to test the query, but I will do something like this?

SELECT C.nome, C.endereço from CLIENTES C
WHERE EXISTS (
  SELECT * FROM filmes F
  WHERE EXISTS (
    SELECT * from locaçoes L
    where F.id = L.filme and C.id = L.cliente
  )
)
sql

  • The question is not very clear: do you want to list the data of customers who have already rented absolutely ALL the films from the video store, that is, that there is no lack of film to be rented? If so, the query would have to be quite different from this.

1 answer

1


To list the customers who have already rented absolutely all the movies from the rental company catalog, you can use (among others) the following query:

SELECT C.nome, C.endereço FROM CLIENTES C
WHERE (SELECT COUNT(DISTINCT Filme) FROM Locações WHERE cliente = C.id) 
      = (SELECT COUNT(1) FROM Filmes)

What this query does is count the number of rentals, without considering rented movies more than once (COUNT(DISTINCT Filme)), and list only those that count equals the total amount of movies from the video store.

  • 1

    I had tried so, but forgot the distinct on Count, in case the same customer had rented the film 2 times or more, grateful for the solution.

Browser other questions tagged sql

You are not signed in. Login or sign up in order to post.