selects.sql

-- a) Запрос с использованием соединения таблиц через WHERE
SELECT
    l.listing_id,
    l.title,
    l.price,
    u.first_name,
    u.last_name,
    d.deal_name
FROM listing l, user u, deal_type d
WHERE l.author_id = u.user_id
  AND l.deal_type_id = d.deal_type_id;



-- b) Запрос с использованием INNER JOIN
SELECT
    l.listing_id,
    l.title,
    l.price,
    u.first_name,
    u.last_name,
    d.deal_name
FROM listing l
INNER JOIN user u
    ON l.author_id = u.user_id
INNER JOIN deal_type d
    ON l.deal_type_id = d.deal_type_id;
    


-- c) Запрос с использованием CASE
SELECT
    user_id,
    first_name,
    last_name,
    rating,
    CASE
        WHEN rating < 3 THEN 'Низкий рейтинг'
        WHEN rating BETWEEN 3 AND 4.5
            THEN 'Надежный пользователь'
        WHEN rating > 4.5
            THEN 'Проверенный пользователь'
        ELSE 'Нет рейтинга'
    END AS user_status
FROM user;



-- d) Запрос с использованием GROUP BY и HAVING
SELECT
    d.deal_name,
    COUNT(l.listing_id) AS listings_count
FROM listing l
INNER JOIN deal_type d
    ON l.deal_type_id = d.deal_type_id
GROUP BY d.deal_name
HAVING COUNT(l.listing_id) >= 1;


-- e) Запрос с использованием LEFT JOIN
SELECT
    l.title AS 'Объявление',
    p.file_url AS 'Фотография'
FROM listing l
LEFT JOIN photo p
    ON l.listing_id = p.listing_id;



-- f) Запрос с использованием вложенного подзапроса 
SELECT
    l.title AS 'Объявление',
    d.deal_name AS 'Тип сделки',
    l.price AS 'Цена'

FROM listing l
INNER JOIN deal_type d
    ON l.deal_type_id = d.deal_type_id

WHERE l.price < (
    SELECT AVG(l2.price)
    FROM listing l2
    WHERE l2.deal_type_id = l.deal_type_id
);



-- g) Создание представления (VIEW)
CREATE VIEW active_listing_view AS

SELECT
    l.title AS 'Заголовок',
    l.price AS 'Цена',
    CONCAT(u.first_name, ' ', u.last_name) AS 'Имя владельца',
    c.city_name AS 'Город'
FROM listing l
INNER JOIN user u
    ON l.author_id = u.user_id
INNER JOIN object_real_estate o
    ON l.property_id = o.property_id
INNER JOIN address a
    ON o.property_id = a.property_id
INNER JOIN city c
    ON a.city_id = c.city_id
WHERE l.status = 'ACTIVE';

SELECT * FROM active_listing_view;