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;
