Week3 - Restricted and Stored Data / Design

###~~

~~

The questions can come in any order, so make sure you are selecting right option for all questions.

1. Write a query to display all the details of the 'building' whose owner_name is 'Nicholas'.

    select * from building where owner_name = 'Nicholas';

2. Write a query to display the details of all the bills whose 'total_units' greater than 10000, sorted by total_units in descending order.

    select * from bill where total_units>10000 order by total_units desc;

3. Write a query to display the 'rate' of slab whose from_unit is 2001 and to_unit is 4500.

    select rate from slab where from_unit=2001 and to_unit=4500;

4. Write a query to display the details of all the 'bills' with the due_date on '2017-10-01'.Display the records in descending order based on their payable_amount.

    select * from bill where due_date='01-OCT-17' order by payable_amount desc;

5. Write a query to display all the details of all the 'bills' whose payment_date is on the year 2018, sorted by payable_amount in descending order.

    select * from bill where payment_date like '%18' order by payable_amount desc;

6. Write a query to display the owner_name, address and contact_number of the buildings which does not have an email_address. Display the records in ascending order based on their owner_name.

    select owner_name, address, contact_number from building where email_address is null order by owner_name;

7. Write a query to display the entire details of the 'building' whose owner_name starts with the letter 'M', Display the records in ascending order based on their owner_name.

    select * from building where owner_name like 'M%' order by owner_name;

8. Write a query to display the entire details of the building whose building_type_id is 2, sorted by owner_name in ascending order.

    select * from building where building_type_id=2 order by owner_name;

9. Write a query to display all the details of the 'electricity_reading' whose 'total_units' per day is between 500 and 1000.Display the records in ascending order based on their total_units.

    select * from electricity_reading where total_units between 500 and 1000 order by total_units;

10. Write a query to display the details of the 'bill' whose payment is not completed. Display the records in ascending order based on due_date.

    select * from bill where is_payed=0 order by due_date;

11. Write a query to display the meter_id and total_units of electricity_reading whose '13th' hour reading is lesser than the '14th' hour reading, Display the records in descending order based on their total_units.

    select meter_id,total_units from electricity_reading where h13 < h14 order by total_units desc;

12. Write a query to display all the details of the 'meter' whose meter_number starts with 'S' and ends with '6'.

    select * from meter where meter_number like 'S%6';