Description
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:
-
sname ( country= “Turkey” (Store) Sells size= “XL” (Cloth) )
-
max(price) (gender=“female” (Cloth) sdate=“08/10/2017” (Sells))
-
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) )
-
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.
-
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.
-
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.
-
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.
-
Provide the list of brand-name and model-name of the car models that have at least five versions with different engine-id’s.
-
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.
-
Give the average price of the car models of French brands, with 300 horse-power.
-
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.
-
Provide owner-company-name and brand-name of French brand that has the least selling (sold-amount) model with a fuel-type of gasoline.