Microsoft SQL for Beginners Views Query

1. Create a view named ‘building_details’ to display all the details of building.

create view building_details as
select * from building;

2. Create a view named ‘building_owners’ to display all the owners of the building.

create view building_owners as
select owner_name from building;

3. Create a view named ‘owner_details’ to display the owner name and contact number of the buildings having length of the owner name greater than 15.

create view owner_details as 
select owner_name,contact_number from building
where len(owner_name)>15

4. Create a view named ‘bill_details’ to display the details of the bill for which the payment has been completed without fine amount.

create view bill_details as 
select * from bill
where is_payed = 1 and fine_amount is NULL;

5. Create a view named ‘electricity_reading_details’ to display all the details of the electricity having total units per day greater than 500 units.

create view electricity_reading_details as
select * from electricity_reading
where total_units > 500;

6. Create a view named ‘meter_details’ to display the details of the buildings whose meter number starts with ‘SG‘ and ends with ‘2‘.

create view meter_details as
select * from building
where id in(select building_id from meter 
where meter_number like 'SG%' and meter_number like '%2');

7. Create a view named ‘home_buildings’ to display all the unique building_type name having connection type as ‘Home’.

create view home_buildings as
select  name from building_type
where connection_type_id in(select id from electricity_connection_type
where connection_name = 'HOME');

8. Create a view named ‘all_payable_amount’ to display the meter number and its corresponding payable amount in the bill.

create view all_payable_amount as 
select meter_number,payable_amount from bill b
cross join meter m  where m.id = b.meter_id;

9. Create a view named ‘daily_readings’ to display the readings for every hour(from h1 to h24) along with the meter number .

CREATE view dialy_readings as
select E.h1,
       E.h2,
       E.h3,
       E.h4,
       E.h5,
       E.h6,
       E.h7,
       E.h8,
       E.h9,
       E.h10,
       E.h11,
       E.h12,
       E.h13,
       E.h14,
       E.h15,
       E.h16,
       E.h17,
       E.h18,
       E.h19,
       E.h20,
       E.h21,
       E.h22,
       E.h23,
       E.h24,
       M.meter_number
from electricity_reading E
CROSS join meter M  where M.id = E.meter_id;

10. Create a view named ‘total_unit_spent_by_commercial’ to display the sum of the total_units spent by the connection type ‘Commercial’.

Note: specify column name for column 1 as ‘total’

create view total_unit_spent_by_commercial as
select  sum(total_units) as 'total' from bill
where meter_id in(select id from meter
where building_id in(select id from building
where building_type_id in(select id from building_type
where connection_type_id in(select id from electricity_connection_type
where connection_name = 'COMMERCIAL'))));

Happy Learning – If you require any further information, feel free to contact me.