Create database Reale_Estate;
use Reale_Estate;


Create Table House (
hid int,
aquareFeet double,
address varchar(30),
dattBuilt date,
zip varchar(5), 
constraint house_PK Primary Key (hid)  )  type = innodb;

Create Table roomType (
rid varchar (2),
roomDescription varchar(30),
Constraint roomType primary key (RID))
Type = innodb;

Create Table Room (
rid int auto_increment ,
hid int,
nameID varchar(2),
roomWidth double,
roomLength double,
roomHeight double,
constraint room_PK Primary Key (rid),
constraint room_FK2 foreign key (nameID) references roomType(rid),
constraint room_FK1 foreign key (hid) references House(hid) 
) type = innodb;





--  This code inserts the data into the tables 

insert into roomType(rid, roomDescription) values  ('LR', 'Living Room');
insert into roomType(rid, roomDescription) values  ('K',  'Kitchen');
insert into roomType(rid, roomDescription) values  ('BR', 'Bedroom Room');
insert into roomType(rid, roomDescription) values  ('BT', 'Bath Room');
insert into roomType(rid, roomDescription) values  ('DN', 'Den');
insert into roomType(rid, roomDescription) values  ('BM', 'Basement');
insert into roomType(rid, roomDescription) values  ('DR', 'Dining Room');

insert into House values (1, 2500, '321 East A Street', '1960-07-21', '14321');
insert into House values (2, 1600, '1234 South Street', '1903-07-21', '15321');
insert into House values (3, 5000, '555 North Street', '1980-07-21', '14521'); 
insert into House values (4, 5000, '321 West A Street', '1960-07-21', '14321');

insert into room (hid, nameID, roomWidth, roomLength, roomHeight)  values (1, 'LR',15, 18, 20);
insert into room (hid, nameID, roomWidth, roomLength, roomHeight)  values (1, 'K',12, 18, 20);
insert into room (hid, nameID, roomWidth, roomLength, roomHeight)  values (1, 'DR',20, 18, 20);
insert into room (hid, nameID, roomWidth, roomLength, roomHeight)  values (1, 'BR',10, 12, 15);
insert into room (hid, nameID, roomWidth, roomLength, roomHeight)  values (1, 'BR',10, 12, 15);
insert into room (hid, nameID, roomWidth, roomLength, roomHeight)  values (1, 'BR',18, 20, 15);
insert into room (hid, nameID, roomWidth, roomLength, roomHeight)  values (1, 'BT',10, 9, 15);
insert into room (hid, nameID, roomWidth, roomLength, roomHeight)  values (1, 'BT',8, 6, 20);


insert into room (hid, nameID, roomWidth, roomLength, roomHeight)  values (2, 'LR',10, 10, 15);
insert into room (hid, nameID, roomWidth, roomLength, roomHeight)  values (2, 'K',12, 12, 15);
insert into room (hid, nameID, roomWidth, roomLength, roomHeight)  values (2, 'DR',12, 15, 15);
insert into room (hid, nameID, roomWidth, roomLength, roomHeight)  values (2, 'BR',10, 10, 15);
insert into room (hid, nameID, roomWidth, roomLength, roomHeight)  values (2, 'BR',10, 10, 15);
insert into room (hid, nameID, roomWidth, roomLength, roomHeight)  values (2, 'BR',12, 12, 15);
insert into room (hid, nameID, roomWidth, roomLength, roomHeight)  values (2, 'BT',10, 9, 15);


insert into room (hid, nameID, roomWidth, roomLength, roomHeight)  values (3, 'LR',20, 30, 20);
insert into room (hid, nameID, roomWidth, roomLength, roomHeight)  values (3, 'K',20, 20, 20);
insert into room (hid, nameID, roomWidth, roomLength, roomHeight)  values (3, 'DR',20, 30, 20);
insert into room (hid, nameID, roomWidth, roomLength, roomHeight)  values (3, 'BR',10, 15, 15);
insert into room (hid, nameID, roomWidth, roomLength, roomHeight)  values (3, 'BR',10, 15, 15);
insert into room (hid, nameID, roomWidth, roomLength, roomHeight)  values (3, 'BR',10, 15, 15);
insert into room (hid, nameID, roomWidth, roomLength, roomHeight)  values (3, 'BR',20, 20, 15);
insert into room (hid, nameID, roomWidth, roomLength, roomHeight)  values (3, 'BT',10, 9, 15);
insert into room (hid, nameID, roomWidth, roomLength, roomHeight)  values (3, 'BT',10, 9, 15);
insert into room (hid, nameID, roomWidth, roomLength, roomHeight)  values (3, 'BT',10, 9, 15);
insert into room (hid, nameID, roomWidth, roomLength, roomHeight)  values (3, 'BT',8, 6, 20);
insert into room (hid, nameID, roomWidth, roomLength, roomHeight)  values (3, 'BM',20, 40, 12);

insert into room (hid, nameID, roomWidth, roomLength, roomHeight)  values (4, 'LR',20, 30, 20);
insert into room (hid, nameID, roomWidth, roomLength, roomHeight)  values (4, 'K',20, 20, 20);
insert into room (hid, nameID, roomWidth, roomLength, roomHeight)  values (4, 'DR',20, 30, 20);
insert into room (hid, nameID, roomWidth, roomLength, roomHeight)  values (4, 'BR',10, 15, 15);
insert into room (hid, nameID, roomWidth, roomLength, roomHeight)  values (4, 'BR',10, 15, 15);
insert into room (hid, nameID, roomWidth, roomLength, roomHeight)  values (4, 'BR',10, 15, 15);
insert into room (hid, nameID, roomWidth, roomLength, roomHeight)  values (4, 'BR',20, 20, 15);
insert into room (hid, nameID, roomWidth, roomLength, roomHeight)  values (4, 'BT',10, 9, 15);
insert into room (hid, nameID, roomWidth, roomLength, roomHeight)  values (4, 'BT',10, 9, 15);
insert into room (hid, nameID, roomWidth, roomLength, roomHeight)  values (4, 'BT',10, 9, 15);
insert into room (hid, nameID, roomWidth, roomLength, roomHeight)  values (4, 'BT',8, 6, 20);
insert into room (hid, nameID, roomWidth, roomLength, roomHeight)  values (4, 'DN',8, 6, 20);
insert into room (hid, nameID, roomWidth, roomLength, roomHeight)  values (4, 'BM',20, 60, 12);


-- This Query illustrates that a database administrator may construct a query that associates two tables
-- In this example the Room and roomtype tables are joined to list all of the rooms and there 
-- associated descriptions

select Room.rid, roomType.roomDescription, Room.roomWidth, Room.roomLength, Room.roomheight
from Room, roomType
where Room.nameID = roomType.rid; 


--  This query also illustrates the concepts of joining the attributes of two tables to produce a result set
--  In this illustration House instances in the house table are joined with their associatied room instances
--  in the room table.   

Select House.address, House.dattBuilt, Room.nameID
From Room, House
where House.hid = Room.hid;

--
Select House.address, House.dattBuilt, Room.nameID
From Room, House
where House.hid = Room.hid
;


--  This Query illustrates how a databse administrator could select  a property and information on that property 
--  based on a type of room that they are serching for.  Here the search is for a property that has a Den

select h.address, h.dattBuilt as 'Date Build', t.roomDescription as Description, r.roomWidth as Width, r.roomLength as 'Room Length',
r.roomHeight as height 
from House h, roomType t, Room r
where t.rid = 'DN' and t.rid = r.nameID and r.hid = h.hid;

-- -- This Query illustrates that a database administrator may construct a query to list all houses in the system
--  Along with selected attributes of the houses associated 

select distinct h.hid, h.address, h.dattBuilt as 'Date Build', t.roomDescription as Description,
                r.roomWidth as Width, r.roomLength as 'Room Length'
from House h, roomType t, Room r
where r.hid = h.hid and t.rid = r.nameID ;

-- This Query illustrates that a database administrator may sort information, the defualt order is Ascending Order
select distinct h.hid, h.address, h.dattBuilt as 'Date Build', t.roomDescription as Description,
                r.roomWidth as Width, r.roomLength as 'Room Length'
from House h, roomType t, Room r
where r.hid = h.hid and t.rid = r.nameID 
order by h.hid;

-- This Query illustrates that a database administrator may sort information i Ascesnding or Decending Order 
select distinct h.hid, h.address, h.dattBuilt as 'Date Build', t.roomDescription as Description, 
                r.roomWidth as Width, r.roomLength as 'Room Length'
from House h, roomType t, Room r
where r.hid = h.hid and t.rid = r.nameID 
order by h.hid DESC;


-- This Query illustrates that a database administrator may sort information by multiple keys.  Here the resturn 
--  set is first sorted by HID in Descending Order and then sorted within HID by RID in Ascending Order

select distinct h.hid, h.address, h.dattBuilt as 'Date Build', t.roomDescription as Description,
                r.roomWidth as Width, r.roomLength as 'Room Length'
from House h, roomType t, Room r
where r.hid = h.hid and t.rid = r.nameID 
order by h.hid DESC, t.rid ASC;


