Below are the tables I have created I have also inserted data in all the tables:

CREATE DATABASE DVD_Transaction

CREATE TABLE DVD
(
 DVD_No INTEGER IDENTITY(1,1) PRIMARY KEY,
 DVD_Name VARCHAR(50) UNIQUE NOT NULL
)

INSERT INTO DVD (DVD_Name)
VALUES ('School of Rock')
INSERT INTO DVD (DVD_Name)
VALUES ('Rock Dog 1')
INSERT INTO DVD (DVD_Name)
VALUES ('Rock Dog 2')
INSERT INTO DVD (DVD_Name)
VALUES ('Coco')
INSERT INTO DVD (DVD_Name)
VALUES ('Luca')

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

INSERT INTO Customer (Customer_Name)
VALUES ('Bill Gates')
INSERT INTO Customer (Customer_Name)
VALUES ('Larry Ellison')
INSERT INTO Customer (Customer_Name)
VALUES ('Steve Jobs')
INSERT INTO Customer (Customer_Name)
VALUES ('Jeff Bezos')
INSERT INTO Customer (Customer_Name)
VALUES ('Elon Musk')
INSERT INTO Customer (Customer_Name)
VALUES ('David Gilmour')

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

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

Then I ran the following query to see which Customer purchased which DVD product:

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

It shows the following the result where:

1. Bill Gates purchased 5 DVD
2. Larry Ellison purchased 4 DVD
3. Steve Jobs purchased 3 DVD
4. Jeff Bezos purchased 2 DVD
5. Elon Musk purchased 1 DVD

I successfully ran a query to show a customer that has purchased all DVD:

SELECT DISTINCT Customer_Name 
FROM Customer, DVD_Purchase
WHERE Customer.Customer_No = DVD_Purchase.Customer_No
AND Customer.Customer_No IN
(SELECT Customer_No
 FROM DVD_Purchase
 GROUP BY Customer_No
 HAVING COUNT(DISTINCT DVD_No) = (SELECT COUNT(*) FROM DVD)
)

The query result shows that Bill Gates is the customer who has purchased all DVD

Now, I tried to run a query to show a DVD that has been purchased by all customers.

SELECT DISTINCT DVD_Name 
FROM DVD, DVD_Purchase
WHERE DVD.DVD_No = DVD_Purchase.DVD_No
AND DVD.DVD_No IN
(SELECT DVD_No
 FROM DVD_Purchase
 GROUP BY DVD_No
 HAVING COUNT(DISTINCT Customer_No) = (SELECT COUNT(*) FROM Customer)
)

Now I am getting blank result.

I am still not able to figure out why I am getting blank result.

What mistake am I possibly making here ?

Any recommended solution would be highly appreciated.

CodePudding user response：

Your last query does not return results because this sub-query does not return results:

SELECT DVD_No
 FROM DVD_Purchase
 GROUP BY DVD_No
 HAVING COUNT(DISTINCT Customer_No) = (SELECT COUNT(*) FROM Customer)

You have 6 customers (result from: SELECT COUNT(*) FROM Customer)

And the number of DVD_Purchase is:

SELECT DVD_No, COUNT(DISTINCT Customer_No)
 FROM DVD_Purchase
 GROUP BY DVD_No

result:

There is no DVD that is being purchased 6 times...

CodePudding user response：

I have finally solved the problem:

All I had to add in the sub-query was:

(SELECT DVD_No
 FROM DVD_Purchase
 GROUP BY DVD_No
 HAVING COUNT(DISTINCT Customer_No) = 
 (SELECT COUNT (*) FROM Customer
  WHERE Customer.Customer_No IN 
  (SELECT Customer_No FROM DVD_Purchase)
 )
)