hajosema1zh.sql

-- 1. Keressük meg azokat a hajókat, amelyeknek a maximális konténerkapacitása meghaladja a 15-öt, és amelyek "It_Cat" kikötőbe tartanak.

SELECT DISTINCT h.nev,
  h.max_kontener_dbszam, u.erkezesi_kikoto
FROM hajo.s_hajo h JOIN hajo.s_ut u
ON h.hajo_id = u.hajo
WHERE h.max_kontener_dbszam > 15
  AND u.erkezesi_kikoto = 'It_Cat';

-- 2. Határozzuk meg, hogy hány megrendelés van folyamatban (azaz még nem szállították ki őket).

SELECT COUNT(*) AS teljesitetlen_szallitas
FROM (
  SELECT erkezesi_ido FROM hajo.s_ut
  WHERE erkezesi_ido IS NULL
);

-- 3. Keressük meg azokat a hajókat, amelyeknek a maximális terhelhetősége meghaladja a 25 tonnát, és amelyeknek az indulási ideje 2021. április 6. után van.

SELECT h.nev, TO_CHAR(u.indulasi_ido, 'DS')
FROM hajo.s_hajo h JOIN hajo.s_ut u
ON h.hajo_id = u.hajo
WHERE h.max_sulyterheles > 25
  AND TO_CHAR(
    u.indulasi_ido, 'DS'
  ) > '2021.04.06';

-- 4. Jelenítsük meg a hajókat a maximális terhelhetőségük alapján csökkenő sorrendben.

SELECT nev, max_sulyterheles FROM hajo.s_hajo
ORDER BY max_sulyterheles DESC;

-- 5. Határozzuk meg, hogy egy adott kikötőbe hány hajó érkezett 2021 júniusában.

SELECT k.kikoto_id, COUNT(*) AS beerkezett_hajo
FROM hajo.s_ut u JOIN hajo.s_kikoto k
ON u.erkezesi_kikoto = k.kikoto_id
WHERE TO_CHAR(u.erkezesi_ido, 'YYYY.MM') = '2021.06'
GROUP BY k.kikoto_id;

-- 6. Keressük meg azokat a megrendeléseket, amelyeket Melnik Nikolay ügyfél adott le, és amelyeknek az összértéke meghaladja a 500 000 eurót.

SELECT u.vezeteknev || ' ' || u.keresztnev AS megrendelo,
       m.fizetett_osszeg
FROM hajo.s_ugyfel u JOIN hajo.s_megrendeles m
ON u.ugyfel_id = m.ugyfel
WHERE u.vezeteknev = 'Melnik'
  AND u.keresztnev = 'Nikolay'
  AND m.fizetett_osszeg > 500000;

-- 7. Keressük meg azokat a hajókat, amelyek több konténert szállítottak összesen 2021-ben, mint a hajók átlagos konténer szállítási mennyiségének a tízezerszerese.

-- átlagos konténer szállítási mennyiség * 10000:
SELECT AVG(rakomanysuly) * 10000 FROM hajo.s_szallit sz
JOIN hajo.s_hozzarendel h USING(megrendeles);

-- 2021-ben szállított konténerek darabszáma összeadva és csoportosítva hajónként
SELECT h.nev, SUM(h.kontener) FROM hajo.s_hajo h
  JOIN hajo.s_ut u ON h.hajo_id = u.hajo
  JOIN hajo.s_szallit sz ON u.ut_id = sz.ut
  JOIN hajo.s_hozzarendel h USING(megrendeles)
WHERE TO_CHAR(u.indulasi_ido, 'YYYY') = '2021'
GROUP BY h.nev;

-- végleges:
SELECT h.nev, SUM(h.kontener) AS szallitott_kontener_db
FROM hajo.s_hajo h
  JOIN hajo.s_ut u ON h.hajo_id = u.hajo
  JOIN hajo.s_szallit sz ON u.ut_id = sz.ut
  JOIN hajo.s_hozzarendel h USING(megrendeles)
WHERE TO_CHAR(u.indulasi_ido, 'YYYY') = '2021'
GROUP BY h.nev
  HAVING SUM(h.kontener) > (
    SELECT AVG(rakomanysuly) * 10000 FROM hajo.s_szallit sz
    JOIN hajo.s_hozzarendel h USING(megrendeles);
  );


-- 8. Számítsuk ki, hogy egy adott hajó átlagosan mennyi időt tölt egy út során.

SELECT h.nev, ROUND(AVG(
  (u.erkezesi_ido - u.indulasi_ido) * 24
), 2) AS vizen_toltott_atlag_nap
FROM hajo.s_hajo h
JOIN hajo.s_ut u ON h.hajo_id = u.hajo
GROUP BY h.nev;

-- 9. Keressük meg azokat a kikötőket, amelyekből nem indult hajó 2021. január 1. és 2021. március 31. között.

SELECT DISTINCT k.kikoto_id
FROM hajo.s_kikoto k
LEFT JOIN hajo.s_ut u
ON k.kikoto_id = u.indulasi_kikoto
WHERE u.indulasi_ido IS NULL;

-- 10. Határozzuk meg, hogy melyik hajó szállította a legtöbb konténert 2023-ban.

SELECT h.nev FROM hajo.s_hajo h
  JOIN hajo.s_ut u ON h.hajo_id = u.hajo
  JOIN hajo.s_szallit sz ON u.ut_id = sz.ut
  JOIN hajo.s_hozzarendel h USING(megrendeles)
GROUP BY h.nev
ORDER BY SUM(h.kontener) DESC
FETCH FIRST ROW ONLY;



-- RÉGI ZH (bshit)

-- 1. feladat:

SELECT DISTINCT u.vezeteknev || ' ' || u.keresztnev AS nev
FROM hajo.s_ugyfel u
JOIN hajo.s_helyseg h
ON u.helyseg = h.helyseg_id
WHERE h.orszag = 'Olaszország';

-- 2. feladat: ?????

SELECT h.helyseg_id, h.orszag, h.helysegnev
FROM hajo.s_helyseg h JOIN hajo.s_ugyfel u
ON h.helyseg_id = u.helyseg
ORDER BY orszag, helysegnev;

-- 3. feladat

SELECT u.ut_id, sz.kontener FROM hajo.s_ut u
JOIN hajo.s_szallit sz ON u.ut_id = sz.ut
WHERE u.indulasi_kikoto = 'It_Cat'
  AND sz.kontener >= 1
ORDER BY sz.kontener
FETCH FIRST 4 ROW ONLY;


-- 2022-es zh:

-- 1. feladat

SELECT megrendeles, kontener,
       ROUND(rakomanysuly, 2) AS rakomanysuly
FROM hajo.s_hozzarendel
ORDER BY rakomanysuly;

-- 2. feladat

SELECT * FROM hajo.s_kikoto
WHERE leiras LIKE '%kikötőméret: kicsi%mobil daruk%'
  OR leiras LIKE '%mobil daruk%kikötőméret: kicsi%';

-- 3. feladat

SELECT ut_id, hajo, indulasi_kikoto, erkezesi_kikoto,
       TO_CHAR(indulasi_ido, 'YYYY.MM.DD HH:MI:SS') AS indulasi_ido,
       TO_CHAR(erkezesi_ido, 'YYYY.MM.DD HH:MI:SS') AS erkezesi_ido
FROM hajo.s_ut
WHERE TO_CHAR(indulasi_ido, 'SS') <> 0
ORDER BY indulasi_ido;

-- 4. feladat

SELECT ht.nev, COUNT(*) AS db
FROM hajo.s_hajo_tipus ht
JOIN hajo.s_hajo h
  ON ht.hajo_tipus_id = h.hajo_tipus
WHERE h.max_sulyterheles > 500
GROUP BY ht.nev;

-- 5. feladat

SELECT TO_CHAR(megrendeles_datuma, 'YYYY.MM') AS datum,
       COUNT(*) AS db
FROM hajo.s_megrendeles
GROUP BY TO_CHAR(megrendeles_datuma, 'YYYY.MM')
HAVING COUNT(*) >= 6;

-- 6. feladat

SELECT u.vezeteknev || ' ' || u.keresztnev AS nev,
       u.telefon
FROM hajo.s_ugyfel u
JOIN hajo.s_helyseg h
  ON u.helyseg = h.helyseg_id
WHERE orszag = 'Szíria';

-- 7. feladat

SELECT ht.nev, MIN(h.netto_suly) AS legkisebb_suly
FROM hajo.s_hajo_tipus ht JOIN hajo.s_hajo h
ON ht.hajo_tipus_id = h.hajo_tipus
GROUP BY ht.nev;

-- 8. feladat

SELECT o.orszag, h.helysegnev
FROM hajo.s_orszag o
JOIN hajo.s_helyseg h
  ON o.orszag = h.orszag
JOIN hajo.s_kikoto k
  ON h.helyseg_id = k.helyseg
WHERE o.foldresz = 'Ázsia'
ORDER BY o.orszag, h.helysegnev;

-- 9. feladat

SELECT h.nev, h.hajo_id, u.indulasi_kikoto,
       u.erkezesi_kikoto, u.indulasi_ido
FROM hajo.s_hajo h JOIN hajo.s_ut u
  ON h.hajo_id = u.hajo
ORDER BY u.indulasi_ido DESC;

-- 10. feladat

SELECT u.erkezesi_kikoto, h.helysegnev, orszag
FROM hajo.s_ut u
JOIN hajo.s_kikoto k
  ON u.indulasi_kikoto = k.kikoto_id
JOIN hajo.s_helyseg h
  ON k.helyseg = h.helyseg_id
JOIN hajo.s_orszag USING(orszag)
WHERE k.kikoto_id = 'It_Cat'
ORDER BY u.indulasi_ido;


-- ZH mittomén mikori

-- 1. feladat

SELECT hr.megrendeles, hr.kontener,
       ROUND(hr.rakomanysuly, 2) AS suly
FROM hajo.s_hozzarendel hr
WHERE hr.rakomanysuly BETWEEN 7 AND 14
ORDER BY rakomanysuly DESC;

-- 2. feladat

SELECT vezeteknev || ', ' || keresztnev AS nev
FROM hajo.s_ugyfel
WHERE helyseg IS NULL
  AND keresztnev LIKE '_____';

-- 3. feladat

SELECT TO_CHAR(
    megrendeles_datuma, 'YYYY.MM.DD HH:MI:SS'
  ) AS megrendeles_datuma,
  indulasi_kikoto, erkezesi_kikoto,
  fizetett_osszeg
FROM hajo.s_megrendeles
WHERE TO_CHAR(
  megrendeles_datuma, 'YYYY.MM'
) IN ('2021.02', '2021.04')
ORDER BY fizetett_osszeg;

-- 4. feladat

SELECT foldresz,
       SUM(terulet) AS ossz_terulet
FROM hajo.s_orszag
WHERE foldresz IS NOT NULL
GROUP BY foldresz
ORDER BY SUM(terulet) DESC;

-- 5. feladat

SELECT u.ugyfel_id,
       SUM(m.fizetett_osszeg) AS ossz_rendeles
FROM hajo.s_ugyfel u
JOIN hajo.s_megrendeles m
  ON u.ugyfel_id = m.ugyfel
GROUP BY u.ugyfel_id;

-- 6. feladat

SELECT DISTINCT ht.nev
FROM hajo.s_hajo_tipus ht
JOIN hajo.s_hajo h
  ON ht.hajo_tipus_id = h.hajo_tipus;

-- 7. feladat

SELECT TO_CHAR(
    megrendeles_datuma, 'YYYY.MM'
  ) AS megrendeles_datuma,
  COUNT(*) AS leadott_megrendelesek
FROM hajo.s_megrendeles m
JOIN hajo.s_kikoto k
  ON m.erkezesi_kikoto = k.kikoto_id
WHERE k.leiras LIKE '%mobil daruk%'
GROUP BY TO_CHAR(megrendeles_datuma, 'YYYY.MM');

-- 8. feladat

SELECT DISTINCT megrendeles
FROM hajo.s_hajo h
JOIN hajo.s_ut u
  ON h.hajo_id = u.hajo
JOIN hajo.s_szallit sz
  ON u.ut_id = sz.ut
JOIN hajo.s_hozzarendel hr
  USING(megrendeles)
WHERE h.nev = 'Asterix'
  AND u.erkezesi_kikoto = 'It_Cat';

-- 9. feladat

SELECT m.megrendeles_id, TO_CHAR(
         m.megrendeles_datuma, 'DS HH:MI:SS'
       ) AS megrendeles_ideje,
       m.indulasi_kikoto, m.erkezesi_kikoto,
       u.vezeteknev || ' ' || u.keresztnev AS nev
FROM hajo.s_megrendeles m
JOIN hajo.s_ugyfel u
  ON m.ugyfel = u.ugyfel_id;

-- 10. feladat

SELECT u.vezeteknev || ' ' || u.keresztnev AS nev,
       TO_CHAR(szul_dat, 'DS') AS szul_dat,
       FLOOR((sysdate - szul_dat) / 365) AS eletkor
FROM hajo.s_helyseg h
JOIN hajo.s_ugyfel u
  ON h.helyseg_id = u.helyseg
WHERE h.orszag = 'Olaszország'
ORDER BY eletkor DESC
FETCH FIRST ROW WITH TIES;