SQL query to display names of customers who have purchased all the DVD

Question

These are the tables I have created and inserted the values accordingly:

CREATE TABLE Customer
(Customer_No INTEGER IDENTITY (1,1) PRIMARY KEY,
 Customer_Name VARCHAR(30) NOT NULL
)

CREATE TABLE DVD
(DVD_No INTEGER IDENTITY (1,1) PRIMARY KEY,
 DVD_Name VARCHAR(30)
)

CREATE TABLE DVD_Purchase
(DVD_Purchase_No INTEGER IDENTITY (1,1) PRIMARY KEY,
 DVD_No INTEGER NOT NULL,
 Customer_No INTEGER NOT NULL
 )

INSERT INTO Customer (Customer_Name)
VALUES('Daman')
INSERT INTO Customer (Customer_Name)
VALUES('Saif')
INSERT INTO Customer (Customer_Name)
VALUES('Gurung')
INSERT INTO Customer (Customer_Name)
VALUES('Upendra')

INSERT INTO DVD (DVD_Name)
VALUES('Bleach')
INSERT INTO DVD (DVD_Name)
VALUES('Gintama')
INSERT INTO DVD (DVD_Name)
VALUES('Tokyo Ghoul')
INSERT INTO DVD (DVD_Name)
VALUES('Death Note')

INSERT INTO DVD_Purchase (DVD_No,Customer_No)
VALUES (4,1)
INSERT INTO DVD_Purchase (DVD_No,Customer_No)
VALUES (1,2)
INSERT INTO DVD_Purchase (DVD_No,Customer_No)
VALUES (1,3)
INSERT INTO DVD_Purchase (DVD_No,Customer_No)
VALUES (2,3)
INSERT INTO DVD_Purchase (DVD_No,Customer_No)
VALUES (3,3)
INSERT INTO DVD_Purchase (DVD_No,Customer_No)
VALUES (4,3)
INSERT INTO DVD_Purchase (DVD_No,Customer_No)
VALUES (1,4)
INSERT INTO DVD_Purchase (DVD_No,Customer_No)
VALUES (2,4)

In order to display names of customers who have purchased all the DVD, this is the query that I have tried out:

SELECT Customer_Name
FROM Customer
WHERE 
EXISTS (SELECT Customer_Name,DVD_Name
FROM Customer,DVD,DVD_Purchase
WHERE Customer.Customer_No = DVD_Purchase.Customer_No AND DVD.DVD_No = DVD_Purchase.DVD_No)

But unfortunately, after executing this query it is displaying all the customer names. But I want to display a specific customer name who have purchased all the DVD.

Can someone provide me the correct query ?

Answer 1

You can try something like:

select c.Customer_Name
from Customer c
join (  select Customer_No
        from DVD_Purchase
        group by Customer_No
        having count(distinct DVD_No) = (select count(*) from DVD)
        ) d on c.Customer_No = d.Customer_No

Explaination: first we need to know the number of DVDs available.

select count(*) 
from DVD

Then we need to get the number of distinct DVDs purchased by the customers, and to compare it with the total we got previously.

select Customer_No
from DVD_Purchase
group by Customer_No
having count(distinct DVD_No) = <number of DVDs available>

And finally, we just need to join with customers table to retrieve the names.

Answer 2

As a complement to irimias solution which is a form of relational division, another variant is to use exists and a double negation. In all essence what one wants to achieve is:

FORALL x:p(x)

This is however not supported in SQL, but you can use the equivalence:

FORALL x:p(x) <=> NOT EXISTS x:NOT p(x)

In your case that would be: customers, where it does not exist a DVD such that the customer did not purchase it:

select c.Customer_Name
from Customer c
where not exists (
    select 1 from dvd d
    where not exists (
        select 1 from DVD_Purchase dp
        where dp.DVD_NO = d.DVD_NO
          and dp.Customer_No = c.Customer_No
    )
);

In my experience most people find it easier to understand irimias solution, so you should probably stick with that. It might, however, be of some interest to see other solutions.

As an exercise, for a consistent database (there's no referential integrity declared in op's example) these two queries return the same result except in one special situation, what situation is that?

Answer 3

declare @No_dvd as int
select @No_dvd = count(*) from dvd

select *
from customer AS c
    cross apply(select count(distinct dvd_no) as cd_dvd 
                 from DVD_Purchase AS DP 
                 where c.Customer_No = dp.Customer_No) AS ca
where
    ca.cd_dvd = @no_dvd -- (select count(*) from dvd)

output for it:

Customer_No Customer_Name   cd_dvd
3   Gurung  4

The idea behind it is to count the number of dvd per customer and match it with all the dvds.

Answer 4

Alternatively, you could try with window functions, as below:

1. Find out the number of each dvd bought by each customer
2. Sum it up, to get the total number of distinct dvd's each customer bought

3. Check if the total matches with the total distinct rows in the dvd table

   select customer_no, customer_name, sum(rownum) count_distinct_dvds
   from
       (
             select d.customer_no
                  , c.customer_name
                  , d.dvd_no
                  , count(*) over(partition by d.customer_no, d.dvd_no) nr_dvds_bought
                  , row_number() over(partition by d.customer_no, d.dvd_no order by d.customer_no, d.dvd_no) rownum
             from Customer c 
                        join DVD_Purchase d on c.Customer_No = d.customer_no 
          ) x
    group by customer_no, customer_name
    having sum(rownum) = (select count(*) from dvd) 
    order by customer_no, customer_name
   

Answer 5

;with CTE_test1 (customer_name, customer_no,ppo)
as (
select 
customer_name,
p.customer_no
, count(p.dvd_no) [ppo]
from dvd_purchase p
  inner join (select count(*)[test], dvd_no from dvd group by dvd_no) d on d.dvd_no = p.dvd_no
  inner join customer c on c.customer_no = p.customer_no
group by  p.customer_no ,c.customer_name)

select  customer_name from CTE_test1  where ppo = ( select count(*) from dvd)