11 - Arbeitsblatt Busunternehmen

use busunternehmen;

-- 1. Geben Sie alle Fahrer aus, die nach Hamburg fahren
select fr.Name
from fahrer fr
         inner join fahrt f on f.PersonalNr = fr.PersonalNr
where f.Reiseziel = 'Hamburg';

-- 2. Geben Sie die FahrtNR und das Datum aus, an denen der „Mercedes Benz O 303“
--    unterwegs ist
select FahrtNr, Datum
from bus b
         inner join fahrt f on f.Kennzeichen = b.Kennzeichen
where b.Bustyp = 'Mercedes Benz O 303';

-- 3. Welche Busse starten in Darmstadt und haben mehr als 30 Sitzplätze?
select *
from fahrt f
         inner join bus b on b.Kennzeichen = f.Kennzeichen
where Reisestart = 'Darmstadt'
  and b.Sitzplaetze > 30;

-- 4. Wie heißt der Busfahrer, dessen Telefonnummer mit „061“ beginnt und in München
--    startet?
select fr.*
from fahrer fr
         inner join fahrt f on f.PersonalNr = fr.PersonalNr
where Telefon like '061%'
  and f.Reisestart = 'München';

-- 5. Welcher Stammkunde hat 5 Plätze bei der Fahrt mit der FahrtNr 5 gebucht?
select k.Name, k.Nachname
from fahrt f
         inner join bucht b on f.FahrtNr = b.FahrtNr
         inner join kunde k on b.KundenNr = k.KundenNr
where f.FahrtNr = 5;

-- 6. Gib alle Stammkunden und deren Reiseziele aus.
select k.Name, k.Nachname, f.Reiseziel
from kunde k
         inner join bucht b on k.KundenNr = b.KundenNr
         inner join fahrt f on b.FahrtNr = f.FahrtNr
where Stammkunde = 1;

-- 7. Welche Kunden fahren am 18.06.2012?
select k.Name, k.Nachname
from fahrt f
         inner join bucht b on f.FahrtNr = b.FahrtNr
         inner join kunde k on b.KundenNr = k.KundenNr
where Datum = '2012-06-18';

-- 8. Welche Kunden fahren nach Dortmund?
select k.Name, k.Nachname
from kunde k
         inner join bucht b on k.KundenNr = b.KundenNr
         inner join fahrt f on b.FahrtNr = f.FahrtNr
where f.Reiseziel = 'Dortmund';

-- 9. Geben Sie die Fahrer aus mit den Bustypen die Sie fahren.
select f.Vorname, f.Name, b.Bustyp, f.PersonalNr
from fahrer f
         inner join fahrt fr on fr.PersonalNr = f.PersonalNr
         inner join bus b on b.Kennzeichen = fr.Kennzeichen;

-- 10. Mit welchem Bustyp fahren welche Kunden länger als 300 Minuten und haben weniger
--     als 3 Plätze gebucht?
select Bustyp, k.Nachname, k.Name
from kunde k
         inner join bucht bu on k.KundenNr = bu.KundenNr
         inner join fahrt f on bu.FahrtNr = f.FahrtNr
         inner join bus b on b.Kennzeichen = f.Kennzeichen
where f.Dauer > 300
  and bu.GebuchtePlaetze < 3;

-- 11. Für welche Fahrten haben sich noch keine Kunden angemeldet.
select f.FahrtNr
from fahrt f
         left join bucht b on f.FahrtNr = b.FahrtNr
where b.FahrtNr is null;

-- 12. Geben Sie alle Fahrer aus die keine Fahrten haben.
select f.Vorname, f.Name
from fahrer f
         left join fahrt fr on fr.PersonalNr = f.PersonalNr
where fr.FahrtNr is null;

-- 13. Geben Sie alle Kunden aus mit ihren Reisezielen aus, auch diejenigen, die noch keine
--     Reise gebucht haben.
select k.Name, k.Nachname, f.Reiseziel
from kunde k
         left join bucht b on k.KundenNr = b.KundenNr
         left join fahrt f on b.FahrtNr = f.FahrtNr;