Consider a database with three relation instance shown below. The schema describ

Question

Consider a database with three relation instance shown below. The schema describes the database containing information about the cars. The drivers relation includes information about the id, name, rating and age of the driver. The reserves relation provides information about charges being charged by the drivers for reserving cars. The cars relation provides the information about its id, name and color. Domain of each field is listed after the field name.

drivers(did:integer, dname:string, rating:integer, age:integer)

reserves(did:integer, cid:integer, charges:integer)

cars(cid:integer, cname:string, color:string)

Give the SQL queries for the following based on the given schema.

a) Find the names of all drivers who reserved a red and a green car.

b) Find the names of all drivers who reserved a non-blue car.

c) Find the average age of drivers for each rating.

d) Find the names of the drivers who drives the Skoda cars.

e) Find the names of the cars which was not reserved by any driver.

f) Find the ids of the drivers who reserved the cars for charges more than the charges of any other car.

g) Find the names of the cars which was reserved by the drivers who have same rating.

h) Find the age of youngest drivers with rating less than 5.

i) Find the names of drivers who reserves the same car that Boris reserved.

j) Retrieve pairs of did’s such that drivers with first did charges more than driver with second did for the same car.

Explanation / Answer

a) select d.name from drivers as d join reserves as r on d.did=r.did join cars as c on r.cid=c.cid where c.color in ("red","green") group by d.name having count(distinct c.color) = 2;

The query joins all three relations and selects names of those drivers who have reserved both red and green car.

b)select distinct d.name from drivers as d join reserves as r on d.did=r.did join cars as c on r.cid=c.cid where c.color!="blue";

The query joins all three relations and selects names of those drivers who have reserved atleast 1 non-blue car.

c) select avg(age) from drivers group by rating;

Returns the average age of drivers for each reating.

d) select distinct d.name from drivers as d join reserves as r on d.did=r.did join cars as c on r.cid=c.cid where c.cname="Skoda";

Joins all the 3 relations and returns names of those drivers who have reserved atleast 1 skoda car.

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.