Answer to Question #171446 in Databases | SQL | Oracle | MS Access for dan

Question

Given the schema:

Pizzas (

pizza TEXT,

PRI KEY (pizza)

);

Restaurants (

rname TEXT,

area TEXT not null,

PRI KEY (rname)

);

Customers (

cname TEXT,

area TEXT not null,

PRI KEY (cname)

);

Sells (

rname TEXT,

pizza TEXT,

price INTEGER not null,

PRI KEY (rname,pizza),

FK (rname) REF Restaurants (rname),

FK (pizza) REF Pizzas (pizza)

);

Likes (

cname TEXT,

pizza TEXT,

PRI KEY (cname,pizza),

FK (cname) REF Customers (cname),

FK (pizza) REF Pizzas (pizza)

);

A customer C is happy if for each pizza P that C likes, there are at least two restaurants, which are located in the same area as C, that sells P.

E.g. if Alice likes only pizzas P1 and P2, then Alice will be happy if

(1) restaurants RA and RB both sell P1;

(2) restaurants RC and RD both sell P2; and

(3) restaurants RA, RB, RC and RD are all located in the same area as Alice.

It doesn't matter whether {RA,RB} and {RC,RD} are the same or different set of restaurants.

Find all happy customers(cname). Exclude customers who do not like any pizza.

Answer 1

select cname
from restaurants as r, customers as c
where r.rname =c.cname;