SQLize Online / PHPize Online

A A A
Share   Donate   Blog   Popular
Copy Format Clear
-- HUE-Zettel 4, Aufgabe 1.1 CREATE TABLE Konto ( KontoID INTEGER PRIMARY KEY, Name TEXT NOT NULL, EMail VARCHAR(50) NOT NULL UNIQUE, Punkte INTEGER NOT NULL, CHECK(Punkte BETWEEN 0 AND 999) ); CREATE TABLE Eintrag ( PostID INTEGER PRIMARY KEY, Autor TEXT NOT NULL REFERENCES Konto ON DELETE CASCADE, Datum DATE NOT NULL, Anonym BOOLEAN NOT NULL ); CREATE TABLE Kommentar ( KontoID INTEGER NOT NULL REFERENCES Konto ON DELETE CASCADE, Zeit TIMESTAMP NOT NULL, Text TEXT NOT NULL, PostID INTEGER NOT NULL REFERENCES Eintrag ON DELETE CASCADE, PRIMARY KEY (KontoID, Zeit, PostID), CHECK(LENGTH(Text) <= 1024) ); -- HUE-Zettel 4, Aufgabe 1.2 INSERT INTO Konto VALUES (1000, "Viola F.", "viola@mail.com", 50), (1001, "Natan S.", "natan@mail.com", 10), (1002, "Oscar H.", "oscar@mail.com", 30), (1003, "Klara W.", "klara@mail.com", 70); INSERT INTO Eintrag VALUES (10, 1002, "2005-11-13", 1), (11, 1000, "2005-11-17", 0), (12, 1001, "2005-11-21", 0), (13, 1002, "2005-11-25", 1), (14, 1002, "2005-11-13", 0); INSERT INTO Kommentar VALUES (1000, "2005-11-13 11:45:00.000", "A?", 10), (1001, "2005-11-13 12:15:00.000", "A!", 10), (1002, "2005-11-17 11:15:00.000", "B?", 11), (1003, "2005-11-25 12:45:00.000", "C!", 13), (1004, "2005-11-13 11:15:00.000", "D.", 14); CREATE VIEW OLDEST_POSTS AS ( SELECT PostID FROM Eintrag WHERE Datum = (SELECT min(Datum) FROM Eintrag) ); CREATE VIEW COMMENTS_OF_OLDEST_POSTS AS ( SELECT * FROM Kommentar NATURAL JOIN OLDEST_POSTS ); CREATE VIEW POST_TIME_PAIR AS ( SELECT PostID, min(Zeit) as Zeit FROM COMMENTS_OF_OLDEST_POSTS GROUP BY PostID ); SELECT PostID, Zeit, Text, LENGTH(Text) FROM ( X NATURAL JOIN POST_TIME_PAIR ); -- SELECT PostID, Zeit, Text, LENGTH(Text) FROM -- X WHERE Zeit IN (SELECT min(Zeit) FROM X GROUP BY PostID) -- ;
Stuck with a problem? Need help? Ask professionals on our Telegram channel!
Join the channel
Your quersion is posted.
Join our Telegram channel and get answers.
Send
Copy