It is a critique report of 500 words on sql tasks
ASSIGNMENT COVER SHEET Name: Salman Khan ID No.:XXXXXX Unit Code: MIS602 Task 1 CREATE SCHEMA `buildinggo` ; CREATE TABLE `buildinggo`.`building` ( `building_id` INT, `building_name` VARCHAR(45), `building_address` VARCHAR(45) NOT, `built_year` INT, `building_capacity` INT, PRIMARY KEY (`building_id`)); CREATE TABLE `buildinggo`.`owner` ( `owner_id` INT NOT NULL, `owner_fname` VARCHAR(45) NULL, `owner_lname` VARCHAR(45) NULL, `owner_email` VARCHAR(45) NULL, `owner_phone` INT NULL, PRIMARY KEY (`owner_id`)); CREATE TABLE `buildinggo`.`apartment` ( `apartmen_id` INT NOT NULL, `apartment_rent` DECIMAL(10,2) NULL, `total_rooms` INT NULL, `building_id` INT NULL, `owner_id` INT NULL, PRIMARY KEY (`apartmen_id`), INDEX `building_id_idx` (`building_id` ASC) VISIBLE, INDEX `owner_id_idx` (`owner_id` ASC) VISIBLE, CONSTRAINT `building_id` FOREIGN KEY (`building_id`) REFERENCES `buildinggo`.`building` (`building_id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `owner_id` FOREIGN KEY (`owner_id`) REFERENCES `buildinggo`.`owner` (`owner_id`) ON DELETE CASCADE ON UPDATE CASCADE); Task 2: INSERT INTO `buildinggo`.`building` (`building_id`, `building_name`, `building_address`, `built_year`, `building_capacity`) VALUES ('1001', 'Lilly Pilliy', 'Wakefield street', '1995', '5000'); INSERT INTO `buildinggo`.`building` (`building_id`, `building_name`, `building_address`, `built_year`, `building_capacity`) VALUES ('1002', 'Early Settler', 'Flinders Street', '2006', '2000'); INSERT INTO `buildinggo`.`building` (`building_id`, `building_name`, `building_address`, `built_year`, `building_capacity`) VALUES ('1003', 'Horizon Eas', 'Maldives Street', '2018', '5000'); INSERT INTO `buildinggo`.`building` (`building_id`, `building_name`, `building_address`, `built_year`, `building_capacity`) VALUES ('1004', 'Ocean Blue ', 'Wakefield Street', '2020', '15000'); INSERT INTO `buildinggo`.`building` (`building_id`, `building_name`, `building_address`, `built_year`, `building_capacity`) VALUES ('1005', 'reflection tower', 'apple street', '2020', '1000'); INSERT INTO `buildinggo`.`owner` (`owner_id`, `owner_fname`, `owner_lname`, `owner_email`, `owner_phone`) VALUES ('2001', 'Hazel ', 'alex', '
[email protected]', '04023466'); INSERT INTO `buildinggo`.`owner` (`owner_id`, `owner_fname`, `owner_lname`, `owner_email`, `owner_phone`) VALUES ('2002', 'saber', 'khan', '
[email protected]', '04034566'); INSERT INTO `buildinggo`.`owner` (`owner_id`, `owner_fname`, `owner_lname`, `owner_email`, `owner_phone`) VALUES ('2003', 'adam', 'smith', '
[email protected]', '04345567'); INSERT INTO `buildinggo`.`owner` (`owner_id`, `owner_fname`, `owner_lname`, `owner_email`, `owner_phone`) VALUES ('2004', 'Lisa ', 'owen', '
[email protected]', '04078066'); INSERT INTO `buildinggo`.`owner` (`owner_id`, `owner_fname`, `owner_lname`, `owner_email`, `owner_phone`) VALUES ('2005', 'sohail', 'mohammed', '
[email protected]', '04341235'); Task 3 INSERT INTO `buildinggo`.`apartment` (`apartmen_id`, `apartment_rent`, `total_rooms`, `building_id`, `owner_id`) VALUES ('1001', '500', '2', '1001', '2001'); INSERT INTO `buildinggo`.`apartment` (`apartmen_id`, `apartment_rent`, `total_rooms`, `building_id`, `owner_id`) VALUES ('1002', '600', '3', '1001', '2002'); INSERT INTO `buildinggo`.`apartment` (`apartmen_id`, `apartment_rent`, `total_rooms`, `building_id`, `owner_id`) VALUES ('1003', '1000', '2', '1001', '2004'); INSERT INTO `buildinggo`.`apartment` (`apartmen_id`, `apartment_rent`, `total_rooms`, `building_id`, `owner_id`) VALUES ('1004', '389', '1', '1003', '2003'); INSERT INTO `buildinggo`.`apartment` (`apartmen_id`, `apartment_rent`, `total_rooms`, `building_id`, `owner_id`) VALUES ('1005', '400', '1', '1004', '2001'); INSERT INTO `buildinggo`.`apartment` (`apartmen_id`, `apartment_rent`, `total_rooms`, `building_id`, `owner_id`) VALUES ('1006', '590', '2', '1001', '2002'); INSERT INTO `buildinggo`.`apartment` (`apartmen_id`, `apartment_rent`, `total_rooms`, `building_id`, `owner_id`) VALUES ('1007', '345', '2', '1003', '2004'); INSERT INTO `buildinggo`.`apartment` (`apartmen_id`, `apartment_rent`, `total_rooms`, `building_id`, `owner_id`) VALUES ('1008', '789', '3', '1004', '2001'); INSERT INTO `buildinggo`.`apartment` (`apartmen_id`, `apartment_rent`, `total_rooms`, `building_id`, `owner_id`) VALUES ('1009', '600', '3', '1002', '2003'); INSERT INTO `buildinggo`.`apartment` (`apartmen_id`, `apartment_rent`, `total_rooms`, `building_id`, `owner_id`) VALUES ('1010', '500', '2', '1005', '2005'); 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 Pilliy' and building_id=1001; Task 8 SELECT building_id, building_name FROM building where building_capacity>3000; Task 9: update apartment set apartment_rent = apartment_rent*1.02 where building_id=1004; Task 10: select apartmen_id, apartment_rent, total_rooms, building_id 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_fname, owner_lname, COUNT(apartment_id)apartment_count FROM owner JOIN apartment ON apartment.owner_id=owner.owner_id GROUP BY owner_fname, owner_lname ORDER BY apartment_count ASC; Task 16 SET SQL_SAFE_UPDATES = 0; DELETE FROM apartment WHERE owner_id IN ( SELECT owner_id FROM owner WHERE owner_fname='james'); DELETE FROM owner WHERE owner_fname='james'; Task 17 SELECT apartment_id, owner_fname FROM apartment JOIN owner ON apartment.owner_id=owner.owner_id WHERE owner_fname='Hazel '; Task 18: SELECT apartment_id, apartment_rent, total_rooms, building_name, owner_id from apartment JOIN building ON apartment.building_id=building.building_id; Task 19 SELECT apartment_id, apartment_rent, total_rooms, building_name, owner_id from apartment JOIN building ON apartment.building_id=building.building_id where building_name ='Ocean Blue '; Task 20 SELECT building_name, count(apartment_id)apartment_count from apartment right JOIN building ON apartment.building_id=building.building_id group by building_name; Task 21 SELECT owner_fname, owner.owner_id FROM owner LEFT JOIN apartment ON apartment.owner_id=owner.owner_id WHERE(apartment_id IS NULL); Task 22 SELECT building_name, apartment_rent FROM apartment join building ON apartment.building_id=building.building_id WHERE apartment_rent=(SELECT min(apartment_rent) as min_apartment_rent FROM apartment); Task 23 select apartment_id, apartment_rent, owner_fname, owner_lname from apartment join owner ON apartment.owner_id=owner.owner_id WHERE apartment_rent > 600 ORDER BY owner_fname DESC; Task 24 select apartment_id, apartment_rent, total_rooms, building_name, owner.owner_id, owner_fname, owner_lname, owner_email, owner_phone from owner right join (building right join apartment ON apartment.building_id=building.building_id) ON apartment.owner_id=owner.owner_id;