Week5 - Set Operators / 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 details of the buildings whose owner name is Nicholas and 'Muzzammil'. Display the records sorted in ascending order based on the owner name and then by id.

select id,owner_name,address,building_type_id,contact_number,email_address from building where owner_name = 'Nicholas'
union
select id,owner_name,address,building_type_id,contact_number,email_address from building where owner_name = 'Muzzammil'
order by owner_name,id;

2. Write a query to display the number of bills whose payment date is in the month of october and number of bills whose payment date is in the month of december. Give an alias name to the number of bills as 'no_of_bills'.

select count(id) no_of_bills from bill where payment_date like '%OCT%'
union
select count(id) no_of_bills from bill where payment_date like '%DEC%'

3. Write a query to display the electricity readings for the meters with meter number 'SG923564' and 'SG288942' in descending order based on the total units consumed per day.
Note: Use 'UNION ALL'

select id,meter_id, day, h1,h2,h3,h4,h5,h6,h7,h8,h9,h10,h11,h12,h13,h14,h15,h16,h17,h18,h19,h20,h21,h22,h23,h24,total_units
from electricity_reading
where meter_id in (select id from meter where meter_number = 'SG923564')
union all
select id,meter_id, day, h1,h2,h3,h4,h5,h6,h7,h8,h9,h10,h11,h12,h13,h14,h15,h16,h17,h18,h19,h20,h21,h22,h23,h24,total_units
from electricity_reading
where meter_id in (select id from meter where meter_number = 'SG288942')
ORDER BY total_units desc

4. Write a query to display the meter_id,due_date, total_units and payable_amount for the bills whose meter number contains a number '2' and for the bills whose meter number contains a number '9'. Display only the common records in descending order based on payable_amount.

select b.meter_id,b.due_date,b.total_units,b.payable_amount from bill b,meter m where m.id = b.meter_id and m.meter_number like '%2%'
intersect
select b.meter_id,b.due_date,b.total_units,b.payable_amount from bill b,meter m where m.id = b.meter_id and m.meter_number like '%9%'
order by payable_amount desc

5. Write a query to display all the details of the buildings, whose building type name contains a letter 'e' but do not contain a letter 'a'. Display the records in descending order based on their owner_name. Note: Use 'Minus'

(select id,owner_name,address,building_type_id,contact_number,email_address from building where building_type_id in (select id from building_type where name like '%e%'))
minus
(select id,owner_name,address,building_type_id,contact_number,email_address from building where building_type_id in (select id from building_type where name like '%a%'))
order by owner_name desc