📊 SQL Analýza

SELECT
    r.road_code,
    s1.name              AS start_city,
    s2.name              AS end_city,
    r.length_km,
    r.toll_cost_eur_km,
    ROUND(r.length_km * r.toll_cost_eur_km, 2) AS total_cost_eur
FROM road r
JOIN settlement s1 ON s1.settlement_id = r.start_settlement
JOIN settlement s2 ON s2.settlement_id = r.end_settlement
WHERE  r.road_class = 'A'
  AND  r.toll_road  = 1
  AND  r.toll_cost_eur_km IS NOT NULL
GROUP BY r.road_id, r.road_code, s1.name, s2.name,
         r.length_km, r.toll_cost_eur_km
ORDER BY total_cost_eur DESC;

SELECT
    s.name                   AS city,
    co.name                  AS country,
    COUNT(r.road_id)          AS road_count
FROM settlement s
JOIN region  rg ON rg.region_id  = s.region_id
JOIN country co ON co.country_id = rg.country_id
JOIN road    r  ON  r.start_settlement = s.settlement_id
                    OR  r.end_settlement   = s.settlement_id
GROUP BY s.settlement_id, s.name, co.name
ORDER BY road_count ASC;

SELECT
    co.name     AS country,
    r.road_code,
    s1.name     AS start_city,
    s2.name     AS end_city,
    r.length_km,
    r.road_class
FROM road r
JOIN settlement s1 ON s1.settlement_id = r.start_settlement
JOIN settlement s2 ON s2.settlement_id = r.end_settlement
JOIN region  rg   ON rg.region_id  = s1.region_id
JOIN country co   ON co.country_id = rg.country_id
WHERE r.length_km = (
    SELECT MAX(r2.length_km)
    FROM road r2
    JOIN settlement sx ON sx.settlement_id = r2.start_settlement
    JOIN region rx     ON rx.region_id = sx.region_id
    WHERE rx.country_id = co.country_id
)
GROUP BY co.country_id, co.name, r.road_id, r.road_code,
         s1.name, s2.name, r.length_km, r.road_class
ORDER BY r.length_km DESC;