Lab 4.5 – powtórzenie SQL

backback

Klasówka z 20/21

Schemat

Schemat bazy danych: narty.sql

W tabeli trasa są informacje o trasach narciarskich łączących węzły przechowywane w tabeli wezel. Kolory tras odpowiadają ich stopniom trudności: najłatwiejsze są trasy zielone, potem niebieskie, czerwone, a najtrudniejsze są czarne. Przyjmujemy, że początek trasy jest jej najwyższym punktem, a koniec najniższym. Przewyższenie to różnica między początkiem a końcem trasy. Średnie nachylenie to stosunek przewyższenia do długości trasy.

Zadania

  1. Wypisz wszystkie trasy posortowane rosnąco według średniego nachylenia.
  2. Wypisz wszystkie wezły posortowane malejaco według łącznej liczby rozpoczynajacych sie w nich tras zielonych i niebieskich.
  3. Wypisz wszystkie trasy, które dają się bezpośrednio przedłużyć wyłącznie trasami o ostro mniejszym stopniu trudności (lub wcale nie dają się przedłużyć).
  4. Wypisz wszystkie trasy zawierające punkt, powyżej którego jest tyle samo wezłów co poniżej.
  5. Wylicz maksymalne przewyższenie, które można pokonać łącząc kolejno dowolną liczbę tras.

Rozwiązania

  1. Wypisz wszystkie trasy posortowane rosnąco według średniego nachylenia.
SELECT t.id, (ws.wysokosc - wd.wysokosc) / t.dlugosc AS srednie_nachylenie
  FROM trasa t
  JOIN wezel ws
    ON t.skad = ws.id
  JOIN wezel wd
    ON t.dokad = wd.id
ORDER BY srednie_nachylenie;
  1. Wypisz wszystkie wezły posortowane malejaco według łącznej liczby rozpoczynajacych sie w nich tras zielonych i niebieskich.
SELECT w.id, COUNT(t.id) AS liczba_tras
  FROM wezel w
  LEFT JOIN trasa t
    ON w.id = t.skad
   AND t.kolor IN ('zielona', 'niebieska')
GROUP BY w.id
ORDER BY liczba_tras DESC;
  1. Wypisz wszystkie trasy, które dają się bezpośrednio przedłużyć wyłącznie trasami o ostro mniejszym stopniu trudności (lub wcale nie dają się przedłużyć).
WITH stopnie AS (
  SELECT 
      t.*, 
      (CASE t.kolor
       WHEN 'zielona'   THEN 1
       WHEN 'niebieska' THEN 2
       WHEN 'czerwona'  THEN 3
       WHEN 'czarna'    THEN 4
       END) AS stopien  
    FROM trasa t
)
SELECT t1.id
  FROM stopnie t1
  LEFT JOIN stopnie t2
    ON t1.dokad = t2.skad
   AND t1.stopien <= t2.stopien
  WHERE t2.id IS NULL;
  1. Wypisz wszystkie trasy zawierające punkt, powyżej którego jest tyle samo wezłów co poniżej.
SELECT t.id
  FROM trasa t
  JOIN wezel ws
    ON t.skad = ws.id
  JOIN wezel wd
    ON t.dokad = wd.id
  WHERE (SELECT MEDIAN(wysokosc) FROM wezel) BETWEEN wd.wysokosc AND ws.wysokosc;

Bardziej bezpośrednio: znajdujemy przedział, w którym zachodzi warunek i wszystkie trasy przechodzące przez ten przedział:

WITH wezly2 AS (
  SELECT 
      w1.id,
      w1.wysokosc,
      (SELECT COUNT(w2.id) FROM wezel w2 WHERE w2.wysokosc > w1.wysokosc) AS powyzej,
      (SELECT COUNT(w2.id) FROM wezel w2 WHERE w2.wysokosc < w1.wysokosc) AS ponizej
    FROM wezel w1
),
ogr_gorne AS (
  SELECT MIN(w.wysokosc) wysokosc 
    FROM wezly2 w
    WHERE w.powyzej + 1 = w.ponizej
),
ogr_dolne AS (
  SELECT MAX(w.wysokosc) wysokosc
    FROM wezly2 w
    WHERE w.powyzej = w.ponizej + 1
)
SELECT t.id
  FROM trasa t
  JOIN wezel ws
    ON t.skad = ws.id
  JOIN wezel wd
    ON t.dokad = wd.id
  WHERE ws.wysokosc < (SELECT wysokosc FROM ogr_gorne g) AND ws.wysokosc > (SELECT wysokosc FROM ogr_dolne d)
     OR wd.wysokosc < (SELECT wysokosc FROM ogr_gorne g) AND ws.wysokosc > (SELECT wysokosc FROM ogr_dolne d);

Pierwsze jest oczywiście lepsze, ale drugie łatwiej zrozumieć, czemu działa.

  1. Wylicz maksymalne przewyższenie, które można pokonać łącząc kolejno dowolną liczbę tras.
WITH osiagalne(skad, dokad) AS (
  SELECT t.skad, t.dokad
    FROM trasa t
  UNION ALL
  SELECT o.skad, t.dokad
    FROM osiagalne o
    JOIN trasa t
      ON o.dokad = t.skad
)
SELECT MAX(ws.wysokosc - wd.wysokosc) przewyzszenie
  FROM osiagalne o
  JOIN wezel ws
    ON o.skad = ws.id
  JOIN wezel wd
    ON o.dokad = wd.id;

backback