SQLize | PHPize | SQLtest

--Aufgabe 1 CREATE TABLE Bahnstation ( Name VARCHAR(30) NOT NULL CHECK (LENGTH(Name) >= 3), Barrierefrei BOOLEAN NULL, Unterirdisch BOOLEAN NULL, Toiletten INTEGER CHECK (Toiletten >= 0), PRIMARY KEY (Name) ); CREATE TABLE Straßenbahn ( Linie INTEGER NOT NULL CHECK (Linie >= 1), Farbe VARCHAR(10) NOT NULL UNIQUE, Strecke VARCHAR(255) NULL, PRIMARY KEY (Linie) ); CREATE TABLE Fahrzeug ( Fahrzeug CHAR(8) NOT NULL CHECK ( Fahrzeug LIKE '^FN-[a-zA-Z0-9]{2}-[a-zA-Z0-9]{2}$' AND (POSITION('Q' IN Fahrzeug) = 0 AND POSITION('R' IN Fahrzeug) = 0) ), Linie INTEGER NULL, PRIMARY KEY (Fahrzeug), FOREIGN KEY (Linie) REFERENCES Straßenbahn(Linie) ON DELETE SET NULL ); CREATE TABLE Fahrplan ( Fahrzeug CHAR(8) NOT NULL, Minute INTEGER NOT NULL, Richtung VARCHAR(30) NOT NULL, Station VARCHAR(30) NOT NULL, PRIMARY KEY (Fahrzeug, Minute), FOREIGN KEY (Fahrzeug) REFERENCES Fahrzeug(Fahrzeug), FOREIGN KEY (Richtung) REFERENCES Bahnstation(Name) ON UPDATE CASCADE ON DELETE CASCADE, FOREIGN KEY (Station) REFERENCES Bahnstation(Name) ON DELETE CASCADE ); --Aufgabe 2 --a SELECT * FROM Bahnstation WHERE Unterirdisch = TRUE AND Toiletten >= 2; --b SELECT F.Station FROM Fahrplan F JOIN Fahrzeug Fa ON Fa.Fahrzeug = F.Fahrzeug JOIN Straßenbahn S ON S.Linie = Fa.Linie WHERE S.Linie = 4 OR S.Farbe = 'GELB'; --c SELECT Fa.Linie, COUNT(DISTINCT F.Station) AS 'Anzahl Stationen' FROM Fahrplan F JOIN Fahrzeug Fa ON Fa.Fahrzeug = F.Fahrzeug GROUP BY Fa.Linie; --d SELECT F.Station FROM Fahrplan F JOIN Fahrzeug Fa ON F.Fahrzeug = Fa.Fahrzeug GROUP BY F.Station HAVING COUNT(DISTINCT Fa.Linie) = (SELECT COUNT(*) FROM Straßenbahn); --e WITH Station_Bedienungen AS ( SELECT Station, COUNT(*) AS Bedienungen FROM Fahrplan GROUP BY Station ), Durchschnitt AS ( SELECT AVG(Bedienungen) AS Durchschnittsbedienungen FROM Station_Bedienungen ) SELECT sb.Station, sb.Bedienungen FROM Station_Bedienungen sb WHERE sb.Bedienungen > (SELECT Durchschnittsbedienungen FROM Durchschnitt) ORDER BY sb.Station DESC; --Aufgabe 3 --a DELETE FROM Straßenbahn WHERE Strecke LIKE '%Jahnplatz%' AND Strecke LIKE '%Nordpark%'; --b UPDATE Bahnstation SET Toiletten = Toiletten + 2 WHERE Unterirdisch = TRUE; --c ALTER TABLE Fahrzeug ADD Baujahr INTEGER; --d ALTER TABLE Fahrzeug DROP COLUMN Baujahr; --e ALTER TABLE Fahrplan ADD CHECK (Minute >= 0 AND Minute <= 59); --f DROP TABLE Bahnstation; --Aufgabe 4 SELECT f1.Station AS Start, f1.Minute AS Abfahrt, fa.Linie, f1.Richtung AS Endstation, f2.Station AS Ziel, f2.Minute AS Ankunft FROM Fahrplan f1 JOIN Fahrplan f2 ON f1.Fahrzeug = f2.Fahrzeug JOIN Fahrzeug fa ON f1.Fahrzeug = fa.Fahrzeug WHERE f1.Minute >= 00 AND f2.Minute > f1.Minute AND f2.Minute < 59 AND f2.Station = f1.Richtung ORDER BY f2.Minute ASC;