Answer Happy

5. MPhone MAge . . MID . • ● MBD • Question 5 MSalary Manager HO (MFutName LName qo MBonus (0) FName Residesin For each building, building Manages 64008 Building BNoFloors Locatedin Apartment ANDBedrooms An apartment rental company has created a database to keep track of its buildings and apartments: . 000 For each apartment, we keep track of a unique apartment number and the number of bedrooms in the apartment . For each manager, we keep track of a unique manager ID, multiple phone numbers full name composed of first and last name, his birthday, the salary, and the optional bonuses. For each inspector, we keep track of a unique inspector ID, and the inspector's name. Each building has multiple apartments, and each apartment is located in one building. Each building is inspected by many inspectors, and many inspectors can inspect multiple buildings. The next and last inspections dates are saved. A manager manages one or many buildings, but a building has only one manager. A manager resides only in one building, but a building may not have any manager residing in it. DateNet Inspects Datelast 0-40 InsName we keep track of a unique building ID and the number of Floors in the
Answer the following: a) Write the query that displays the building id, building nr of floors, and its manager's first and last name for all the buildings (5 p) b) State what is wrong with the query (2p). Correct it (5p) Display the manager's first name, last name, salary, birthday and the nr of buildings that he/she manages for all managers with a salary less than 55.000 Select m.mfname, m.mlname, m.msalary, m.bdate From building b, manager m Where b.bmanagerid-m.managerid and m.salary <55000 Group by m.mfname; c) Write the query to display the inspector id and name for all inspectors whose next inspection is scheduled after 1-Jan-2016. Do not display the same information more than once. (5p) d) Write a managerial query using nested queries + sql code of your own for the g. schema. (5p)