buildinggo

task 1:
CREATE DATABASE buildingo;

USE buildingo;

CREATE TABLE building(
building_id INT,
building_name VARCHAR(50),
building_address VARCHAR(100),
built_year INT,
building_capacity INT,
PRIMARY KEY(building_id)
);

CREATE TABLE owner(
owner_id INT,
owner_fname VARCHAR(20),
owner_lname VARCHAR(20),
owner_email VARCHAR(20),
owner_phone VARCHAR(20),
PRIMARY KEY(owner_id)
);

CREATE TABLE apartment(
apartment_id INT,
total_room INT,
apartment_rent DECIMAL(65,3),
building_id INT,
owner_id INT,
FOREIGN KEY (building_id) REFERENCES building(building_id),
FOREIGN KEY (owner_id) REFERENCES owner(owner_id),
PRIMARY KEY(apartment_id)
);

task 2:
INSERT INTO owner
VALUES
("2001","Hazel","Alex","hazel@gmail.com","04023466"),
("2002","Saber","Khan","khan@gmail.com","04034566"),
("2003","Adam","Smith","adam@gmail.com","04345567"),
("2004","Lisa","Owen","owen@gmail.com","04078066"),
("2005","Chris","Emmanuel","chris@gmail.com","04096544")
;

INSERT INTO building
VALUES
("1001","Lilly Pilli","Wakefield street","1995","5000"),
("1002","Early Settler","Flinders Street","2006","2000"),
("1003","Horizon East","Maldives Street","2018","5000"),
("1004","Ocean Blue","Wakefield Street","2020","15000"),
("1005","Mountain Dew","Whitefeild","2021","12500")
;

task 3:
INSERT INTO apartment (apartment_id, apartment_rent, total_room, building_id, owner_id)
VALUES
("1001", "500", "2", "1001", "2001"),
("1002", "600", "3", "1001", "2002"),
("1003", "1000", "2", "1001", "2004"),
("1004", "389", "1", "1003", "2003"),
("1005", "400", "1", "1004", "2001"),
("1006", "590", "2", "1001", "2002"),
("1007", "345", "2", "1003", "2004"),
("1008", "789", "3", "1004", "2001")
;

task 4:
SELECT * FROM building;

task 5:
SELECT building_name FROM building;

task 6:
SELECT building_name, building_capacity FROM building;

task 7:
UPDATE building SET building_capacity="2000" WHERE building_name="Lilly Pilli";

task 8:
SELECT building_id, building_name FROM building WHERE building_capacity>3000;

task 9:
UPDATE apartment SET apartment_rent=apartment_rent+(apartment_rent*0.02) WHERE building_id="1004";

task 10:
SELECT * FROM apartment WHERE owner_id="2003";

task 11:
SELECT DISTINCT building_address FROM building;

task 12:
SELECT building_name, built_year FROM building WHERE built_year="2001";

task 13:
SELECT building_name, building_capacity FROM building WHERE building_capacity BETWEEN 1000 AND 2000 ORDER BY building_capacity DESC;

task 14:
SELECT COUNT(apartment_id) FROM apartment;

task 15:
SELECT owner_id, COUNT(apartment_id) AS "Number of Apartment" FROM apartment GROUP BY owner_id;

task 16:
DELETE FROM owner WHERE owner_fname IN(SELECT owner.owner_fname HAVING owner_fname="James");

task 17:
SELECT apartment_id FROM apartment WHERE owner_id IN(SELECT owner_id FROM owner WHERE owner_fname LIKE '%Hazel%');

task 18:
SELECT apartment.apartment_id, apartment.total_room, apartment.apartment_rent, apartment.building_id, building.building_name, apartment.owner_id FROM apartment INNER JOIN building WHERE building.building_id=apartment.building_id;

task 19:
SELECT * FROM apartment WHERE building_id IN(SELECT building_id FROM building WHERE building_name="Ocean Blue");

task 20:
SELECT building.building_name, COUNT(apartment.apartment_id) AS "Number of Apartments" FROM apartment LEFT JOIN building ON apartment.apartment_id=building.building_id GROUP BY apartment.building_id HAVING COUNT(apartment.apartment_id)>5;

task 21:
SELECT owner_id FROM owner WHERE owner_id NOT IN(SELECT owner_id FROM apartment);

task 22:
SELECT building_name FROM building WHERE building_id IN(SELECT building_id FROM apartment WHERE apartment_rent IN(SELECT MIN(apartment_rent) FROM apartment));

task 23:
SELECT apartment.apartment_id, apartment.apartment_rent, concat(owner.owner_fname," ",owner.owner_lname) AS "owner name" FROM apartment INNER JOIN owner ON owner.owner_id = apartment.owner_id WHERE apartment_rent>600 AND apartment.owner_id=owner.owner_id ORDER BY owner_fname DESC;

task 24:
SELECT apartment_id, apartment.apartment_rent, building.building_name, owner.* FROM apartment INNER JOIN building ON apartment.building_id=building.building_id INNER JOIN owner ON apartment.owner_id=owner.owner_id;