Week3 - Aggregate Functions / 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 the average 8th hour unit consumption from electricity reading. Give an alias name to the average 8th hour consumption as 'average_8th_hour_consumption'.

    select avg(h8) as average_8th_hour_consumption from electricity_reading;

2. Write a query to display the average total_units of electricity consumption from the electricity_reading table.Give an alias name to the average units as 'average_units_of_electricity'.

    select avg(total_units) as average_units_of_electricity from electricity_reading;

3. Write a query to display the average payable_amount from bill table where payable_amount is greater than 10000. Give an alias name to the average payable amount as 'average_payable_amount'.

    select avg(payable_amount) as average_payable_amount from bill where payable_amount>10000;

4. Write a query to display the average fine_amount of the bills whose payment_date is on the year 2018. Give an alias name to the average fine amount as 'average_fine_amount'.

    select avg(fine_amount) as average_fine_amount from bill where payment_date like '%18';

5. Write a query to display the sum of payable_amount of all the bills from bill table.Give an alias name to the sum of payable amount as 'sum_payable_amount'.

    select sum(payable_amount) as sum_payable_amount from bill;

6. Write a query to display the sum of payable_amount and fine_amount of all the bills from bill table. Give an alias name to the sum of payable amount and fine amount as 'sum_payable_and_fine_amount'.

    select sum(fine_amount+payable_amount) as sum_payable_and_fine_amount from bill; 

7. Write a query to display the sum of 12th hour unit consumption from electricity_reading table. Give an alias name to the sum of 12th hour consumption as 'sum_12th_hour_consumption'.

    select sum(h12) as sum_12th_hour_consumption from electricity_reading;

8. Write a query to display the sum of payable_amount with due_date '2017-10-01' from bill table. Give an alias name to the sum of payable amount as 'sum_payable_amount'.

    select sum(payable_amount) as sum_payable_amount from bill where due_date = '01-OCT-2017';

9. Write a query to display the minimum total_units consumption from electricity_reading table. Give an alias name to the minimum total units as 'min_total_units'.

    select min(total_units) as min_total_units from electricity_reading;

10. Write a query to display the second minimum fine_amount from bill table. Give an alias name to the second minimum fine amount as 'second_min_fine_amount'.

    select min(fine_amount) as second_min_fine_amount from bill where fine_amount not in (select min(fine_amount) from bill);

11. Write a query to display the month and the minimum total_units of electricity consumed on that month from the bill table. Give an alias name to the minimum total_units as 'minimum_units'.

    select month,min(total_units) as minimum_units from bill group by month;

12. Write a query to display the minimum fine_amount present in the bill table. Give an alias name to the minimum fine_amount as 'min_fine_amount'.

    select min(fine_amount) as min_fine_amount from bill;

13. Write a query to display the sum of payable_amount whose payment_date is on the year 2018. Give an alias name to the total payable_amount as 'sum_payable_amount'.

    select sum(payable_amount) as sum_payable_amount from bill where payment_date like '%18';

14. Write a query to display the standard deviation of the fine_amount whose payment_date is on the year 2018 from bill table. Give an alias name to the standard deviation to the fine amount as 'standard_deviation_amount'.

    select stddev(fine_amount) as standard_deviation_amount from bill where payment_date like '%18';

15. Write a query to display the variance of the payable_amount whose payment_date is on the month of 'October'. Give an alias name to the variance as 'variance_payable_amount'.

    select variance(payable_amount) as variance_payable_amount from bill where payment_date like '%OCT%';

16. Write a query to display the second maximum payable_amount from the bill table. Give an alias name to the second maximum payable_amount as 'second_max_payable_amount'.

    select max(payable_amount) as second_max_payable_amount from bill where payable_amount not in (select max(payable_amount) from bill);

17. Write a query to display the number of buildings having an email_address. Give an alias name as 'count_of_buildings_with_email'.

    select count(email_address) as count_of_buildings_with_email from building;

18. Write a query to display the number of bills having fine_amount. Give an alias name as 'count_of_bills_with_fine'.

    select count(fine_amount) as count_of_bills_with_fine from bill;

19. Write a query to display the number of bills in which the bill payment is completed. Give an alias name as 'count_of_bills_with_payment'.

    select count(is_payed) as count_of_bills_with_payment from bill where is_payed!=0;

20. Write a query to display the number of electricity_reading in which the total_units per day is greater than 500. Give an alias name as 'count_total_units_500'.

    select count(total_units) as count_total_units_500 from electricity_reading where total_units >500;