Consider the following schema that represents houses for sale and customers who are looking to buy:



Customer(Id, Name, Address)

Preference(CustId, Feature)

Agent(Id, AgentName)

House(Address, OwnerId, AgentId)

Amenity(Address, Feature)

Preference is a relation that lists all features requested by the customers (one tuple per customer/feature; e.g., 123, ’5BR’, 123,’2BATH’, 432,’pool’), and Amenity is a relation that lists all features of each house (one tuple per house/feature).
A customer is interested in buying a house if the set of all features speci?ed by the customer is a subset of the amenities the house has. A tuple in the House relation states who is the owner and who is the real estate agent listing the house. Write the following queries in SQL:

a. Find all customers who are interested in every house listed with the agent with Id 007.
b. Using the previous query as a view, retrieve a set of tuples of the form feature, number of customers, where each tuple in the result shows a feature and the number of customers who want this feature such that
– Only the customers who are interested in every house listed with Agent 007 are considered.
– The number of customers interested in feature is greater than three. (If this number is not greater than three, the corresponding tuple feature, number of customers is not added to the result.)



SELECT        C.Id

FROM         Customer C

WHERE       NOT EXISTS (

                            (SELECT     P.Feature

                             FROM      Preference P

                             WHERE    P.CustId = C.Id)

                            EXCEPT

                            (SELECT    A.Feature

                             FROM     Amenity A, House H

                             WHERE   A.Address = H.Address

                                            AND H.AgentId = ’007’) )



CREATE VIEW     ClientOf007(CustId) AS

SELECT               C.Id

FROM                 Customer C

WHERE               NOT EXISTS (

                                    (SELECT      P.Feature

                                     FROM       Preference P

                                     WHERE     P.CustId = C.Id)

                                    EXCEPT

                                    (SELECT     A.Feature

                                     FROM       Amenity A, House H

                                     WHERE     A.Address = H.Address

                                                      AND H.AgentId = ’007’ )



SELECT       P.Feature, COUNT(*)

FROM         Preference P, ClientOf007 C

WHERE       P.CustId = C.CustId

GROUP BY P.Feature

HAVING     COUNT(*) > 3