Exam Preparation 2

CREATE DATABASE real_estates;

-- 01. Table Design

-- CITIES
CREATE TABLE cities
(
	id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(60) NOT NULL UNIQUE
);

-- property_types
CREATE TABLE property_types
(
	id INT AUTO_INCREMENT,
    type VARCHAR(40) NOT NULL UNIQUE,
    description TEXT,
    PRIMARY KEY(id)
);

-- PROPERTIES
CREATE TABLE properties
(
	id INT AUTO_INCREMENT PRIMARY KEY,
    address VARCHAR(80) NOT NULL UNIQUE,
    price DECIMAL(19, 2) NOT NULL,
    area DECIMAL (19, 2),
    property_type_id INT,
    city_id INT,
    FOREIGN KEY(property_type_id) REFERENCES property_types(id),
    FOREIGN KEY(city_id) REFERENCES cities(id)
);

-- AGENTS
CREATE TABLE agents
(
	id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(40) NOT NULL,
    last_name VARCHAR(40) NOT NULL,
    phone VARCHAR(20) NOT NULL UNIQUE,
    email VARCHAR(50) NOT NULL UNIQUE,
    city_id INT,
    FOREIGN KEY (city_id) REFERENCES cities(id)
);

-- BUYERS
CREATE TABLE buyers
(
	id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(40) NOT NULL,
    last_name VARCHAR(40) NOT NULL,
    phone VARCHAR(20) NOT NULL UNIQUE,
    email VARCHAR(50) NOT NULL UNIQUE,
	city_id INT,
    FOREIGN KEY (city_id) REFERENCES cities(id)
);

-- property_offers
CREATE TABLE property_offers
(
	property_id INT NOT NULL,
    agent_id INT NOT NULL,
    price DECIMAL(19, 2) NOT NULL,
    offer_datetime DATETIME,
    CONSTRAINT `fk_offers_with_properties` FOREIGN KEY(property_id) REFERENCES properties(id),
    CONSTRAINT `fk_offers_with_agents` FOREIGN KEY(agent_id) REFERENCES agents(id)
);

-- property_transactions
CREATE TABLE property_transactions
(
	id INT AUTO_INCREMENT PRIMARY KEY,
    property_id INT NOT NULL,
    buyer_id INT NOT NULL,
    transaction_date DATE,
    bank_name VARCHAR(30),
    iban VARCHAR(40) UNIQUE,
    is_successful BOOLEAN,
    FOREIGN KEY (property_id) REFERENCES properties (id),
    FOREIGN KEY (buyer_id) REFERENCES buyers (id)
);

-- 02.	Insert
INSERT INTO property_transactions (property_id, buyer_id, transaction_date, bank_name, iban, is_successful)
SELECT
	po.agent_id + DAY (po.offer_datetime),
    po.agent_id + MONTH (po.offer_datetime),
    DATE(po.offer_datetime),
    CONCAT('Bank ', po.agent_id),
    CONCAT('BG', po.price, po.agent_id),
    true
FROM property_offers po
WHERE po.agent_id <= 2;

-- 03. Update
UPDATE properties prop
SET prop.price = prop.price - 50000
WHERE prop.price >= 800000;


-- 04.	Delete
DELETE FROM property_transactions trans
WHERE trans.is_successful = 0;

-- 05.	Agents
SELECT * FROM agents
ORDER BY city_id DESC, phone DESC;

-- 06.	Offers from 2021
SELECT * FROM property_offers po
WHERE YEAR(po.offer_datetime) = 2021
ORDER BY price ASC
LIMIT 10;

-- 07.	Properties without offers
SELECT SUBSTRING(pr.address, 1, 6) AS agent_name, CHAR_LENGTH(pr.address) * 5430 AS price FROM properties pr
LEFT JOIN property_offers po ON pr.id = po.property_id
WHERE po.agent_id IS NULL
ORDER BY agent_name DESC, price DESC;

-- 08.	Best Banks
SELECT bank_name, COUNT(`iban`) AS count FROM property_transactions
GROUP BY bank_name
HAVING count >= 9
ORDER BY count DESC, bank_name  ASC;

-- 09.	Size of the area
SELECT address, area,
(CASE
	WHEN area <= 100 THEN 'small'
    WHEN area <= 200 THEN 'medium'
    WHEN area <= 500 THEN 'large'
    ELSE 'extra large'
END) AS size
FROM properties
ORDER BY area ASC, address DESC

-- 10.	Offers count in a city
-- брой на офертите за дадения град
DELIMITER //
CREATE FUNCTION udf_offers_from_city_name (cityName VARCHAR(50))
	RETURNS INT
    DETERMINISTIC
BEGIN
	DECLARE offers_count INT;
    SET offers_count := (
		SELECT COUNT(*) FROM property_offers po
        JOIN properties p on po.property_id = p.id
        JOIN cities c on c.id = p.city_id
        WHERE cityName = c.name
    );
    RETURN offers_count;
END;

-- 11.	Special Offer
DELIMITER //
CREATE PROCEDURE udp_special_offer (first_name VARCHAR(50))
BEGIN
	UPDATE property_offers po
    JOIN agents a ON a.id = po.agent_id
    SET po.price = po.price * 0.9
    WHERE a.first_name = first_name;
END;