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 owner name and the length of the owner's name of the buildings. Display the records in ascending order based on owner name. Give an alias as name_length to the length of the owner names.
Use the inbuilt function length().
select owner_name,length(owner_name) as name_length from building order by owner_name;
2. Write a query to display all the owner_name and concatenate contact_number and email with a hyphen(-) separated . Display the records in descending order based on owner_name . Give an alias name as 'contact_details'.
select owner_name,contact_number||'-'||email_address as contact_details from building order by owner_name desc;
3. Write a query to display the first 3 characters of each meter number. Give an alias name for the meter number as 'meter_code'.Display the records sorted by meter_code in descending order.
select substr(meter_number,0,3) as meter_code from meter order by meter_code desc;
4. Write a query to display the first 3 characters of the owner_name and the contact_number of all buildings in ascending order based on the owner name of the building. Give an alias to the first 3 characters as 'name_code'.
select substr(owner_name,0,3) as name_code,contact_number from building order by owner_name;
5. Write a query to display the owner_name and mail id in which the string should end before the '@' symbol. Display the details of the buildings which are having the email address and give an alias name as 'email_name' and display it in ascending order based on owner name.
select owner_name, substr(email_address,0,instr(email_address,'@')-1) as email_name from building where email_address is not null order by owner_name;
6. Write a query to display the name of all building types that ends with letter 'l'. Display the records in ascending order based on name.
select name from building_type where name like '%l' order by name;
7. Write a query to display all the details of the buildings whose owner name contains 'di' in ascending order based on the owner name of the building.
select * from building where owner_name like '%di%' order by owner_name;
8. Write a query to display the meter_number and updated_meter_number(which is obtained by left padding meter_number with ‘*’ and making it 10 character length field.) . Give an alias name as 'updated_meter_number'. Display the records in ascending order based on meter_number.
select meter_number, lpad(meter_number,10,'*') as updated_meter_number from meter order by meter_number;
9. Write a query to display the owner_name and modified_number(which is obtained by right padding contact_number with ‘#’ and making it 17 character length field.) Give an alias name for the contact_number as'modified_number'. Display the records in ascending order based on owner name.
select owner_name, rpad(contact_number,17,'#') as modified_number from building order by owner_name;
10. Write a query to display all the details of bills whose payment_date is on 2nd day of the month and also display the records in descending order based on the total_units.
select * from bill where payment_date like '02%' order by total_units desc;
11. Write a query to display the count of bills which has completed payment without fine on the month of October 2017. Give an alias name as 'bills_paid_withoutfine'.
select count(id) as bills_paid_withoutfine from bill where fine_amount is null and payment_date like '%OCT%17';
12. Write a query to display the month and sum of fine_amount collected on the month between July to October. Give an alias name as 'monthly_fine_amount' also display the records in ascending order based on month.
select month,sum(fine_amount) as monthly_fine_amount from bill group by month having month >=7 and month <=10 order by month;
