Question

consider the following schema:

Product(pid, name, type, manufacturer, price)

key: pid

Buys(cid, pid)

key: cid and pid together

Customer(cid, cname, age, gender)

key: cid

1) write the following query in relational algebra; find the names of all customers who have purchased all products manufactured by sears.

2)write the following in sql: find the names of all the customers who have not purchased the most expensive product.

3)write the following query in sql: find the best selling products (in terms of the total sales price) per gender.

4) write the following query in sql: find the type of products with the highest difference in the total sales between males and females customers.

Answer 1

1.

SELECT c.cname from customer c, product p, buys b where p.pid = b.pid AND p.cid = c.cid AND p.manufacturer = "Sears";

2.

SELECT c.cname from customer c, product p, buys b where p.pid = b.pid AND p.cid = c.cid AND p.price NOT IN (select MAX(Price) from product);

3.

SELECT p.name from

(SELECT p.name, SUM(p.price) as Price FROM customer c, product p, buys b where p.pid = b.pid AND p.cid = c.cid GROUP BY c.gender Order BY SUM(p.price) desc) where Price IN

(SELECT MAX(p.price) from

(SELECT p.name, SUM(p.price) as Price FROM customer c, product p, buys b where p.pid = b.pid AND p.cid = c.cid GROUP BY c.gender Order BY SUM(p.price) desc));