This advanced SQL exercise is part of the book: Advanced SQL – Practical Techniques and Use-Cases, which includes 100 day-to-day SQL challenges (and solutions), need-to-know advanced features, and key concepts.
A real estate investment firm manages two office buildings. Each building has 2 floors, and each floor has 4 rooms. The firm uses the following table to manage the rent rates for each building, office, and floor.
The ChargeType column represents the billing type, the EffectiveYear column represents the billing year, and the ChargeRate column represents the billing rate.
CREATE TABLE rates (ChargeType varchar(25), EffectiveYear int, ChargeRate int) GO
The rate is calculated as a combination of 3 parameters.
For example, in the year 2016 the cost of Office2 on floor 2 in BuildingB is calculated as follows:
(1) Office cost – 2000 ILS, (2) Floor cost – 2000 ILS, (3) Building cost – 4000 ILS. In total – a rent of 8,000 ILS.
