CREATE TABLE Raumschiff (Schiffsnummer integer PRIMARY KEY, Schiffsname varchar (200), MaxPersonen integer, Linie integer,
FOREIGN KEY (Linie) REFERENCES Linie (Liniennummer) );

CREATE TABLE Linie (Liniennummer integer PRIMARY KEY, Linienname varchar(200) );

CREATE TABLE Planet (Planetnummer integer PRIMARY KEY, Name varchar(200));

CREATE TABLE bedient (Linie integer, Planet integer, Haltestelle integer,
FOREIGN KEY (Linie) REFERENCES Linie (Liniennummer),
FOREIGN KEY (Planet) REFERENCES Planet (Planetnummer));

CREATE TABLE Pilot (Personalnummer integer PRIMARY KEY, Vorname varchar(200), Nachname varchar(200));

CREATE TABLE darfFahren (Pilot integer, Schiff integer,
FOREIGN KEY (Pilot) REFERENCES Pilot (Personalnummer),
FOREIGN KEY (Schiff) REFERENCES Raumschiff (Schiffsnummer) );

CREATE TABLE Streckenkunde (Pilot integer, Linie integer,
FOREIGN KEY (Pilot) REFERENCES Pilot (Personalnummer),
FOREIGN KEY (Linie) REFERENCES Linie (Linienummer) );


INSERT INTO Linie (Liniennummer, Linienname) VALUES (1, 'Mars-Shuttle');
INSERT INTO Linie (Liniennummer, Linienname) VALUES (2, 'Hot Line');
INSERT INTO Linie (Liniennummer, Linienname) VALUES (3, 'Cold Line');
INSERT INTO Linie (Liniennummer, Linienname) VALUES (4, 'Gravity Line');

INSERT INTO Planet (Planetnummer, Name) VALUES (1, 'Merkur');
INSERT INTO Planet (Planetnummer, Name) VALUES (2, 'Venus');
INSERT INTO Planet (Planetnummer, Name) VALUES (3, 'Erde');
INSERT INTO Planet (Planetnummer, Name) VALUES (4, 'Mars');
INSERT INTO Planet (Planetnummer, Name) VALUES (5, 'Jupiter');
INSERT INTO Planet (Planetnummer, Name) VALUES (6, 'Saturn');
INSERT INTO Planet (Planetnummer, Name) VALUES (7, 'Uranus');
INSERT INTO Planet (Planetnummer, Name) VALUES (8, 'Neptun');

INSERT INTO bedient (Linie, Planet, Haltestelle) VALUES (1, 3, 1);
INSERT INTO bedient (Linie, Planet, Haltestelle) VALUES (1, 4, 2);
INSERT INTO bedient (Linie, Planet, Haltestelle) VALUES (2, 1, 1);
INSERT INTO bedient (Linie, Planet, Haltestelle) VALUES (2, 2, 2);
INSERT INTO bedient (Linie, Planet, Haltestelle) VALUES (2, 3, 3);
INSERT INTO bedient (Linie, Planet, Haltestelle) VALUES (2, 4, 4);
INSERT INTO bedient (Linie, Planet, Haltestelle) VALUES (3, 5, 1);
INSERT INTO bedient (Linie, Planet, Haltestelle) VALUES (3, 6, 2);
INSERT INTO bedient (Linie, Planet, Haltestelle) VALUES (3, 7, 3);
INSERT INTO bedient (Linie, Planet, Haltestelle) VALUES (3, 8, 4);
INSERT INTO bedient (Linie, Planet, Haltestelle) VALUES (4, 3, 1);
INSERT INTO bedient (Linie, Planet, Haltestelle) VALUES (4, 4, 2);
INSERT INTO bedient (Linie, Planet, Haltestelle) VALUES (4, 5, 2);

INSERT INTO Raumschiff (Schiffsnummer, Schiffsname, MaxPersonen, Linie) VALUES (1, 'Enterprise', 800, 4);
INSERT INTO Raumschiff (Schiffsnummer, Schiffsname, MaxPersonen, Linie) VALUES (2, 'Titanic', 1200, 1);
INSERT INTO Raumschiff (Schiffsnummer, Schiffsname, MaxPersonen, Linie) VALUES (3, 'Horus', 750, 4);
INSERT INTO Raumschiff (Schiffsnummer, Schiffsname, MaxPersonen, Linie) VALUES (4, 'Phi', 625, 2);
INSERT INTO Raumschiff (Schiffsnummer, Schiffsname, MaxPersonen, Linie) VALUES (5, 'Chi', 670, 2);
INSERT INTO Raumschiff (Schiffsnummer, Schiffsname, MaxPersonen, Linie) VALUES (6, 'Gamestar', 900, 3);
INSERT INTO Raumschiff (Schiffsnummer, Schiffsname, MaxPersonen, Linie) VALUES (7, 'Curie', 810, 4);

INSERT INTO Pilot (Personalnummer, Vorname, Nachname) VALUES (13, 'Alice', 'Amann');
INSERT INTO Pilot (Personalnummer, Vorname, Nachname) VALUES (37, 'Bob', 'Bernig');
INSERT INTO Pilot (Personalnummer, Vorname, Nachname) VALUES (42, 'Claire', 'Clepto');
INSERT INTO Pilot (Personalnummer, Vorname, Nachname) VALUES (7, 'Bob', 'Baumeister');
INSERT INTO Pilot (Personalnummer, Vorname, Nachname) VALUES (9, 'Ash', 'Ketchum');
INSERT INTO Pilot (Personalnummer, Vorname, Nachname) VALUES (10, 'Pika', 'Chu');
INSERT INTO Pilot (Personalnummer, Vorname, Nachname) VALUES (100, 'Hans-Ulrich', 'Wyneken');

INSERT INTO darfFahren (Pilot, Schiff) VALUES (13, 4);
INSERT INTO darfFahren (Pilot, Schiff) VALUES (13, 2);
INSERT INTO darfFahren (Pilot, Schiff) VALUES (37, 4);
INSERT INTO darfFahren (Pilot, Schiff) VALUES (37, 5);
INSERT INTO darfFahren (Pilot, Schiff) VALUES (37, 7);
INSERT INTO darfFahren (Pilot, Schiff) VALUES (37, 2);
INSERT INTO darfFahren (Pilot, Schiff) VALUES (42, 4);
INSERT INTO darfFahren (Pilot, Schiff) VALUES (42, 3);
INSERT INTO darfFahren (Pilot, Schiff) VALUES (7, 6);
INSERT INTO darfFahren (Pilot, Schiff) VALUES (9, 6);
INSERT INTO darfFahren (Pilot, Schiff) VALUES (9, 4);
INSERT INTO darfFahren (Pilot, Schiff) VALUES (9, 5);
INSERT INTO darfFahren (Pilot, Schiff) VALUES (9, 3);
INSERT INTO darfFahren (Pilot, Schiff) VALUES (10, 1);
INSERT INTO darfFahren (Pilot, Schiff) VALUES (100, 1);
INSERT INTO darfFahren (Pilot, Schiff) VALUES (100, 2);
INSERT INTO darfFahren (Pilot, Schiff) VALUES (100, 3);
INSERT INTO darfFahren (Pilot, Schiff) VALUES (100, 4);
INSERT INTO darfFahren (Pilot, Schiff) VALUES (100, 5);
INSERT INTO darfFahren (Pilot, Schiff) VALUES (100, 6);
INSERT INTO darfFahren (Pilot, Schiff) VALUES (100, 7);

INSERT INTO Streckenkunde (Pilot, Linie) VALUES (13, 2);
INSERT INTO Streckenkunde (Pilot, Linie) VALUES (13, 1);
INSERT INTO Streckenkunde (Pilot, Linie) VALUES (42, 1);
INSERT INTO Streckenkunde (Pilot, Linie) VALUES (42, 2);
INSERT INTO Streckenkunde (Pilot, Linie) VALUES (37, 1);
INSERT INTO Streckenkunde (Pilot, Linie) VALUES (37, 2);
INSERT INTO Streckenkunde (Pilot, Linie) VALUES (37, 4);
INSERT INTO Streckenkunde (Pilot, Linie) VALUES (7, 3);
INSERT INTO Streckenkunde (Pilot, Linie) VALUES (9, 4);
INSERT INTO Streckenkunde (Pilot, Linie) VALUES (10, 4);
INSERT INTO Streckenkunde (Pilot, Linie) VALUES (100, 1);
INSERT INTO Streckenkunde (Pilot, Linie) VALUES (100, 2);
INSERT INTO Streckenkunde (Pilot, Linie) VALUES (100, 3);
INSERT INTO Streckenkunde (Pilot, Linie) VALUES (100, 4);

-- a) Vor- und Nachnamen der Pilotin mit der Personalnummer 42.
SELECT Vorname, Nachname FROM Pilot WHERE Personalnummer = 42;

-- b) Personalnummern aller Piloten mit dem Vornamen Bob.
SELECT Personalnummer FROM Pilot WHERE Vorname = 'Bob';

-- c) Maximale Personenzahl des Raumschiffs mit dem Namen „Chi“.
SELECT MaxPersonen FROM Raumschiff WHERE Schiffsname = 'Chi';

-- d) Namen aller Raumschiffe mit dem Namen der Linie, die sie jeweils fahren.
SELECT Schiffsname, Linienname FROM Raumschiff, Linie WHERE Linie = Liniennummer;

-- e) Nachnamen aller Piloten mit den Nummern aller Linien, auf denen sie jeweils Streckenkunde haben.
SELECT Nachname, Linie FROM Pilot, Streckenkunde WHERE Personalnummer = Pilot;

-- f) Personalnummern aller Piloten, die Raumschiffe mit mindestens 800 Plätzen fahren dürfen.
SELECT Pilot FROM darfFahren, Raumschiff WHERE Schiff = Schiffsnummer AND maxPersonen >= 800;

-- g) Name der Linie, die das Raumschiff „Curie“ bedient.
SELECT Linienname FROM Linie, Raumschiff WHERE Liniennummer = Linie AND Schiffsname = 'Curie';

-- h) Name des Planeten, den Linie 3 als drittes bedient.
SELECT Name FROM Planet, bedient WHERE Planetnummer = Planet AND Linie = 3 AND Haltestelle = 3;

-- i) Maximale Personenzahl aller Raumschiffe, die Planet 4 anfahren.
SELECT maxPersonen FROM Raumschiff, Linie, bedient WHERE Raumschiff.Linie = Liniennummer AND Liniennummer = bedient.Linie AND Planet = 4;
-- j) Personalnummern aller Piloten, die ein Raumschiff fahren dürfen, das Linie 4 bedient, sowie den Namen des jeweiligen Raumschiffs.
SELECT Personalnummer, Schiffsname FROM Pilot, darfFahren, Raumschiff WHERE Personalnummer = Pilot AND Schiff = Schiffsnummer AND Linie = 4;

-- k) Vornamen alle Piloten, die auf der Linie mit dem Namen „Cold Line“ Streckenkunde haben.
SELECT Vorname FROM Pilot, Streckenkunde, Linie WHERE Personalnummer = Pilot AND Linie = Liniennummer AND Linienname = 'Cold Line';

-- l) Nachnamen aller Piloten mit den Namen aller Raumschiffe, die eine Strecke bedienen, für die die jeweiligen Piloten jeweils Streckenkunde haben.
SELECT Nachname, Schiffsname FROM Pilot, Streckenkunde, Linie, Raumschiff WHERE Personalnummer = Pilot AND Streckenkunde.Linie = Liniennummer AND Liniennummer = Raumschiff.Linie;

-- m) Vor- und Nachnamen aller Piloten, die Streckenkunde auf einer Strecke haben, die den Planet mit dem Namen Mars bedient, sowie den Namen dieser Strecke.
SELECT Vorname, Nachname, Linienname FROM Pilot, Streckenkunde, Linie, bedient, Planet WHERE Personalnummer = Pilot AND Streckenkunde.Linie = Liniennummer AND Liniennummer = bedient.Linie AND Planet = Planetnummer AND Planet.Name = 'Mars';
