Q.1 [20 pts, 5 pts each] Given the following relational schema:
Store(sid, sname, country, zipcode)
Cloth(cid, type, color, size, gender )
Sells(sid, cid, price, sdate)
sid is a FOREIGN KEY referencing Store
cid is a FOREIGN KEY referencing Cloth
Translate the following Relational Algebra expressions into SQL queries:
(a) sname ( country= “Turkey” (Store) Sells size= “XL” (Cloth) )
(b) max(price) (gender=“female” (Cloth) sdate=“08/10/2017” (Sells))
(c) sname, zipcode ( country=“France” (Store) price<100(Sells) type=“jeans” gender=“female”(Cloth) )
sname, zipcode ( country=“France” (Store) price<100(Sells) type=“jeans” gender=“male”(Cloth) )
(d) cid count(*) as quantity_sold ( country=`Germany` (Store) Sells type=`suit` (Cloth) )
Q.2 [80 pts, 10 pts each] Given the following relational schema:
Brand(brand-name, owner-company-name, country)
Model(model-id, brand-name, model-name, sold-amount, price, engine-id, tax-level) brand-name is a FOREIGN KEY referencing Brand
engine-id is a FOREIGN KEY referencing Engine tax-level is a FOREIGN KEY referencing Tax
Engine(engine-id, horse-power, fuel-type)
Tax(tax-level, tax-cost)
Notice that a company may own several brands (e.g. Toyota Motor Corp. owns Lexus and Toyota). A brand may have different car models with the same model-name (based on different engine properties).
For each of the following queries, give an expression in SQL.
(a) Provide the list of brand-name and owner-company-name of the car brands which are from Germany and have models with a horse-power more than 170 and the corresponding model price is less than 8000.
(b) Provide the list of brand-name and owner-company-name of the car brands which are from Germany and do not have any models with a fuel-type of diesel.
(c) Provide the list of brand-name of the car brands which are from Germany and have some models with diesel fuel-type while they also have some models with a horse-power more than 300.
(d) Provide the list of brand-name and model-name of the car models that have at least five versions with different engine-id’s.
(e) Provide the list of brand-name and model-name of the car models that have at least five versions with different engine-id’s with a tax-cost between 150 and 300.
(f) Give the average price of the car models of French brands, with 300 horse-power.
(g) Provide the list of brand-name of German car brands such that the average price of their models sold more than 1000 times is higher than the average price of models of every French brand.
(h) Provide owner-company-name and brand-name of French brand that has the least selling (sold-amount) model with a fuel-type of gasoline.
