The questions can come in any order, so make sure you are selecting right option for all questions.
1. Write a query to display bill id, meter_id, month, year, total_units, payable_amount and due_date of all the bills. Display the due_date in the format like June 17, 2003. Give an alias name as 'formatted_due_date'. Display all the records sorted in ascending order based on bill id. Note: 1. Display bill id, meter_id, month, year, total_units, payable_amount and due_date from bill table. 2. Specify the alias name without any mistake. 3. Use TO_CHAR to specify the column and format of the date. 4. Order by is mandatory. 5. Compare your executed result with the sample output given below.
select id, meter_id, month, year, total_units, payable_amount, to_char(due_date, 'Month DD, YYYY') as formatted_due_date from bill order by id;
2. Write a query to display owner_name and email_address of all the buildings. If email_address is NULL, then display the email_address as 'NA'. Give an alias name as email. Display all the records sorted in ascending order based on building id. Note: 1. Display all the owner_name and email_address from building table. 2. Specify the alias name without any mistake. 3. Order by is mandatory. 4. Use NVL function
select owner_name, nvl(email_address, 'NA') as email from building order by id;
3. Write a query to display owner_name and contact_details of all the buildings. Display email_address as contact_details. If email_address is NULL, then display the contact_number as contact_details. Give an alias name as contact_details. Display all the records sorted in ascending order based on building id. Note: 1. Display all the owner_name and contact_details from building table. 2. Specify the alias name without any mistake. 3. Order by is mandatory. 4. Use NVL2 function to display the contact_details.
select owner_name, nvl(email_address,contact_number) as contact_details from building order by id;
4. Write a query to display id, meter_id, day and reading_details of all readings from electricity_reading table. If the reading at 11th hour and 12th hour reading is equal, then display reading_details as 'NULL', else display 11th hour reading as reading_details. Give an alias name as 'reading_details'. Display all the records sorted in ascending order based on electricity_reading id. Note: 1. Display only the id, meter_id, day and reading_details from electricity_reading table. (11th & 12thhour reading will be h11 and h12 column). 2. Specify the alias name without any mistake. 3. Order by is mandatory 4. Use NVL IF function.
select id,meter_id,day, case when h11=h12 then null else h11 end as reading_details from electricity_reading order by id;
5. Write a query to display the id, meter_id, day and reading_details of all readings from the electricity_reading table. If the reading at 1st hour is NULL, then display 2nd hour reading as reading_details, else if 2nd hour reading is also NULL, then display 3rd hour reading as reading_details and so on. Give an alias name as 'reading_details'. Display all the records sorted in ascending order based on electricity_reading id. Note: 1. Display only the id, meter_id, day and reading_details from electricity_reading table. (1st , 2nd & 3rd hour reading will be h1, h2 and h3 column). 2. Specify the alias name without any mistake. 3. Order by is mandatory 4. Use Coalesce function.
select id,meter_id,day, coalesce(h1,h2,h3,h4,h5,h6,h7,h8,h9,h10,h11,h12,h13,h14,h15,h16,h17,h18,h19,h20,h21,h22,h23,h24) as reading_details from electricity_reading order by id;
6. Write a query to display the bill id, meter_id, month, year, total_units, payable_amount and due_date of all the bills. Display the payable_amount in the format like '$100000.00'. Give an alias name as 'payable_amt'. Display all the records sorted in ascending order based on bill id. Note: 1. Display only id, meter_id, month, year, total_units, payable_amount and due_date from the bill table. 2. Specify the alias name without any mistake. 3. Order by is mandatory 4. Use To_char function. 5. Compare your executed result with the sample output given below.
select id,meter_id,month,year,total_units,to_char(payable_amount, '$99999999.99') as payable_amt,due_date from bill order by id;
7. Write a query to display bill id, meter_id, month, year, due_date, total_units, is_payed and fine_details of the bills whose payable_amount is greater than 500000. If the fine_amount field is NULL, then display 'No Fine', else display 'Fine Amount is $fine_amount' as fine_details. Display the records sorted in ascending order based on bill id. Note: 1. Display only the id, meter_id, month, year, due_date, total_units, is_payed and fine_details from bill table. 2. Specify alias name without any mistake. 3. Use Case statement 4. Order by is mandatory 5. Compare your executed result with the sample output given below.
select id,meter_id,month,year,due_date,total_units,is_payed, case when fine_amount is null then 'No Fine' else 'Fine Amount is'||to_char(fine_amount, '$99999999') end as fine_details from bill where payable_amount > 500000 order by id;
8. Write a query to display bill id, meter_id, month, year, due_date, total_units, is_payed and fine_details of the bills whose payable_amount is greater than 500000. If the fine_amount field is NULL, then display 0 as fine_details, else display the fine_amount as 'fine_details'. Give an alias name as 'fine_details'. Display the records sorted in ascending order based on bill id. Note: 1. Display only the id, meter_id, month, year, due_date, total_units, is_payed and fine_details from bill table. (payable amount > 500000). 2. Specify the alias name without any mistake. 3. Order by is mandatory 4. Use NVL function. 5. Compare your executed result with the sample output given below.
select id,meter_id,month,year,due_date,total_units,is_payed, case when fine_amount is null then 0 else fine_amount end as fine_details from bill where payable_amount > 500000 order by id;
9. Write a query to display the bill id, meter_id, month, year, due_date, total_units and paid_details of the bills whose due_date is on the year 2017. If the bill is paid, then display paid_details as 'Bill Paid', else display 'Bill not Paid'. Give an alias name as 'paid_details'. Display the records sorted in ascending order based on bill id. Note: 1. Display only the id, meter_id, month, year, due_date, total_units, is_payed and fine_details from bill table. (due_date year must be 2017). 2. Specify the alias name without any mistake. 3. Order by is mandatory 4. Use DECODE function.
select id,meter_id,month,year,due_date,total_units, decode(is_payed, 1, 'Bill Paid', 'Bill Not Paid') as paid_details from bill where due_date like '%17' order by id;
10. Write a query to display the due_date and payable_amount that are all not paid in between '2017-09-10' to '2017-10-10'. Note: 1. Display only the due_date and payable_amount which are not paid between these dates from bill table. 2. Specify condition on the the payment_date column present in the bill table. 3. Compare your executed result with the sample output given below.
select due_date, payable_amount from bill where payment_date not between '10-SEP-17' and '10-OCT-17';
11. Write a query to display the sum of fine_amount which charged between '2017-10-01' and '2017-11-01'. Give an alias name for fine amount as 'total_fine'. Note: 1. Display only the sum of fine_amount from bill table. 2. Specify the condition and retrieve the correct rows to compute the total fine_amount.(condition need to be specified on due_date column). 2. Specify the alias name without any mistake.
select sum(fine_amount) as total_fine from bill where due_date between '01-OCT-17' and '01-NOV-17';
12. Write a query to display day and the sum of total_units of electricity consumed on each day between the dates '2018-05-06' and '2018-05-07' from the electricity_reading table. Give alias name for sum of total_units as total_unit_perday and also display the records in ascending order based on day. Note: 1. Display only the day and sum of total_units of electricity consumed between the given dates from the electricity_reading table. 2. Specify the alias name without any mistake. 3.Specify the condition and retrieve the correct rows in the result(condition need to be specified on payment_date column). 4. Order by is mandatory. 5. Group by day to get the day wise sum of total_units.
select day,sum(total_units) as total_unit_perday from electricity_reading where day between '06-MAY-18' and '07-MAY-18' group by day order by day;
13. Write a query to display the due_date, payment_date, difference betweeen the due_date and the payment_date of the bill in days and payable_amount of the bills whose due date is exceeding the payment_date. Give an alias name for difference betweeen the due_date and the payment_date as 'date_difference'. Note: 1. Display only the below 4 columns, i. due_date ii. payment_date iii. difference between due_date and payment_date of the bill in days iv. payable_amount with the condition that the due_date > payment_date from bill table. 2. Specify the alias name without any mistake. 3. Compare your executed result with the sample output given below.
select due_date, payment_date, due_date - payment_date AS date_difference, payable_amount from bill where due_date > payment_date;
14. Write a query to display the month and average of fine_amount collected on every month between July to December. Give alias as 'average_monthly_fine' for average fine_amount and also display in ascending order by month. Note: 1. Display only the month and average of fine_amount collected between the given months. 2. Specify the alias name without any mistake. 3. Order by is mandatory. 4. Group by month to get the month wise average fine_amount.
select month,avg(fine_amount) as average_monthly_fine from bill group by month having month between 7 and 12 order by month;
15. Write a query to display the day, dayname and average of total_units of electricity consumption. Give alias as 'average_consumption_daywise' for average of total_units and dayname as 'day_name'. Display the records in ascending order based on dayname. Note: 1. Display only the day, dayname and average of total_units from electricity_reading table. 2. Specify the alias names without any mistake. 3. Order by is mandatory. 4. Group by day to get the day wise average total_units.
select day, to_char(day,'Day') as day_name, avg(total_units) as average_consumption_daywise from electricity_reading group by day order by day_name;