Question

create table Buildings ( integer, varchar (100), varchar (100), varchar (2), 2 id unswid 4 name 5 campus primary key (id) 6 8 9 create table Rooms ( integer, varchar (100), varchar (100), integer references Buildings (id), integer, id 1 unswid name building capacity primary key (id) 3 4 5 6 ; 17 8 create table Staff ( integer, integer re ferences Rooms (id), varchar (100) , id :0 1 22 3 office phone primary key (id) ) ;

If I have this schema, how can I solve the following problem by using MySQL? Please help me, thanks a lot.

Answer 1

Find the id and name of the rooms with maximum capacity for each building at campus "K"

select r.id , r.name from Rooms r,
(select r.building building, max(r.capacity) max_cap from Rooms r, Buildings b where r.building = b.id and b.campus = 'K' group by r.building) mcb
where r.building = mcb.building and r.capacity = mcp.max_cap;

The inner query returns the id and capacity of the maximum capacity building in campus "K" by joining the Rooms and Buildings tables on field building id. The outer query returns the id and name of the rooms with the maximum capacity present in each building.