Question

-- Define the database

CREATE TABLE Product (
ProdName VARCHAR2(10) NOT NULL,
Price INTEGER NOT NULL,
CONSTRAINT PRODUCT_PK PRIMARY KEY (ProdName)
);

CREATE TABLE Customer (
ID INTEGER NOT NULL,
ProdName VARCHAR2(10),
Rating INTEGER,
CustName VARCHAR2(10),
CONSTRAINT CUSTOMER_PK PRIMARY KEY (ID)
);

CREATE TABLE Buys (
ID INTEGER NOT NULL,
ProdName VARCHAR2(10) NOT NULL,
Quantity INTEGER NOT NULL,
CONSTRAINT BUYS_PK PRIMARY KEY (ID, ProdName)
);

ALTER TABLE Buys ADD CONSTRAINT PRODUCT_BUYS_FK
FOREIGN KEY (ProdName)
REFERENCES Product (ProdName)
NOT DEFERRABLE;

ALTER TABLE Customer ADD CONSTRAINT PRODUCT_CUSTOMER_FK
FOREIGN KEY (ProdName)
REFERENCES Product (ProdName)
NOT DEFERRABLE;

ALTER TABLE Buys ADD CONSTRAINT CUSTOMER_BUYS_FK
FOREIGN KEY (ID)
REFERENCES Customer (ID)
NOT DEFERRABLE;

-- Populate the database

INSERT INTO Product (ProdName, Price) VALUES ('Apple', 18);
INSERT INTO Product (ProdName, Price) VALUES ('Raisin', 60);
INSERT INTO Product (ProdName, Price) VALUES ('Aspirin', 86);
INSERT INTO Product (ProdName, Price) VALUES ('Coconut', 34);
INSERT INTO Product (ProdName, Price) VALUES ('Zucchini', 119);
INSERT INTO Product (ProdName, Price) VALUES ('Null', 158);
INSERT INTO Product (ProdName, Price) VALUES ('Pear', 21);
INSERT INTO Product (ProdName, Price) VALUES ('Grape', 8);
INSERT INTO Product (ProdName, Price) VALUES ('Tuna', 147);
INSERT INTO Product (ProdName, Price) VALUES ('Banana', 50);
INSERT INTO Product (ProdName, Price) VALUES ('Avocado', 17);
INSERT INTO Product (ProdName, Price) VALUES ('Squash', 89);
INSERT INTO Product (ProdName, Price) VALUES ('Salmon', 109);

INSERT INTO Customer (ID, ProdName, Rating, CustName) VALUES (347213304, 'Apple', 8, 'Max');
INSERT INTO Customer (ID, ProdName, Rating, CustName) VALUES (646244225, 'Aspirin', 5, 'Raiden');
INSERT INTO Customer (ID, ProdName, Rating, CustName) VALUES (917892713, 'Zucchini', 2, 'Max');
INSERT INTO Customer (ID, ProdName, Rating, CustName) VALUES (965487978, 'Zucchini', 2, 'Ben');
INSERT INTO Customer (ID, ProdName, Rating, CustName) VALUES (972302754, Null, 7, 'Will');
INSERT INTO Customer (ID, ProdName, Rating, CustName) VALUES (929240561, 'Raisin', 2, 'Max');
INSERT INTO Customer (ID, ProdName, Rating, CustName) VALUES (123456789, 'Null', 10, 'Indigo');
INSERT INTO Customer (ID, ProdName, Rating, CustName) VALUES (646691459, 'Banana', 0, 'Thor');
INSERT INTO Customer (ID, ProdName, Rating, CustName) VALUES (937821659, 'Apple', 1, 'Zavier');
INSERT INTO Customer (ID, ProdName, Rating, CustName) VALUES (645391459, 'Coconut', 8, 'Elsa');
INSERT INTO Customer (ID, ProdName, Rating, CustName) VALUES (277821659, 'Apple', 10, 'Anna');
INSERT INTO Customer (ID, ProdName, Rating, CustName) VALUES (283757492, Null, 2, 'Apple');
INSERT INTO Customer (ID, ProdName, Rating, CustName) VALUES (277821660, 'Tuna', 3, 'Olaf');
INSERT INTO Customer (ID, ProdName, Rating, CustName) VALUES (929240562, 'Salmon', 4, 'Anna');
INSERT INTO Customer (ID, ProdName, Rating, CustName) VALUES (283757493, 'Avocado', Null, 'Avocado');

INSERT INTO Buys (ID, ProdName, Quantity) VALUES (347213304, 'Apple', 83);
INSERT INTO Buys (ID, ProdName, Quantity) VALUES (347213304, 'Grape', 1);
INSERT INTO Buys (ID, ProdName, Quantity) VALUES (917892713, 'Coconut', 21);
INSERT INTO Buys (ID, ProdName, Quantity) VALUES (965487978, 'Raisin', 79);
INSERT INTO Buys (ID, ProdName, Quantity) VALUES (972302754, 'Zucchini', 6);
INSERT INTO Buys (ID, ProdName, Quantity) VALUES (929240561, 'Coconut', 47);
INSERT INTO Buys (ID, ProdName, Quantity) VALUES (123456789, 'Apple', 81);
INSERT INTO Buys (ID, ProdName, Quantity) VALUES (646691459, 'Zucchini', 98);
INSERT INTO Buys (ID, ProdName, Quantity) VALUES (937821659, 'Aspirin', 78);
INSERT INTO Buys (ID, ProdName, Quantity) VALUES (937821659, 'Apple', 85);
INSERT INTO Buys (ID, ProdName, Quantity) VALUES (277821659, 'Aspirin', 93);
INSERT INTO Buys (ID, ProdName, Quantity) VALUES (283757492, 'Apple', 19);
INSERT INTO Buys (ID, ProdName, Quantity) VALUES (277821660, 'Null', 82);
INSERT INTO Buys (ID, ProdName, Quantity) VALUES (929240562, 'Pear', 86);
INSERT INTO Buys (ID, ProdName, Quantity) VALUES (972302754, 'Coconut', 24);
INSERT INTO Buys (ID, ProdName, Quantity) VALUES (646691459, 'Banana', 42);
INSERT INTO Buys (ID, ProdName, Quantity) VALUES (937821659, 'Grape', 90);
INSERT INTO Buys (ID, ProdName, Quantity) VALUES (917892713, 'Banana', 18);
INSERT INTO Buys (ID, ProdName, Quantity) VALUES (123456789, 'Zucchini', 12);
INSERT INTO Buys (ID, ProdName, Quantity) VALUES (123456789, 'Banana', 30);
INSERT INTO Buys (ID, ProdName, Quantity) VALUES (646691459, 'Grape', 98);
INSERT INTO Buys (ID, ProdName, Quantity) VALUES (277821660, 'Avocado', 0);
INSERT INTO Buys (ID, ProdName, Quantity) VALUES (277821660, 'Coconut', 41);
INSERT INTO Buys (ID, ProdName, Quantity) VALUES (929240562, 'Coconut', 10);
INSERT INTO Buys (ID, ProdName, Quantity) VALUES (283757493, 'Avocado', 90);
INSERT INTO Buys (ID, ProdName, Quantity) VALUES (123456789, 'Grape', 52);
INSERT INTO Buys (ID, ProdName, Quantity) VALUES (965487978, 'Zucchini', 19);
INSERT INTO Buys (ID, ProdName, Quantity) VALUES (283757492, 'Grape', 18);
INSERT INTO Buys (ID, ProdName, Quantity) VALUES (646691459, 'Pear', 80);
INSERT INTO Buys (ID, ProdName, Quantity) VALUES (123456789, 'Null', 88);

1. Produce table Answer1(CustName) that lists all the names of the customers who bought both products that cost more than 100 each (inclusive) and products that cost less than 20 each (inclusive).

2. Produce table Answer2(ID1, ID2, CustName) that lists two different customers that have the same CustName but different ratings for their favorite products.

3. Produce table Answer3(Quantity) that list all the values of Quantity for products bought by Customers with CustName 'Thor'. Do not remove duplicates from the answer.

Answer 1

Query 1:

SELECT DISTINCT(c1.CustName) FROM Customer c1, Buys b1, Product p1 WHERE c1.ID = b1.ID AND p1.ProdName = b1.ProdName
AND c1.ID In (SELECT DISTINCT(b.ID) FROM Buys b, Product p WHERE b.ProdName = p.ProdName AND p.Price >= 100) AND p1.Price <= 20;

Result:

Query 2:

SELECT c1.ID, c2.ID, c1.CustName FROM Customer c1, Customer c2
WHERE c1.CustName = c2.CustName AND c1.ID < c2.ID AND c1.Rating != c2.Rating;

Result:

Query 3:

SELECT b.Quantity FROM customer c, buys b WHERE c.ID = b.ID and c.CustName = 'Thor';

Result: