SQLize Online / PHPize Online  /  SQLtest Online

CREATE TABLE besitzer ( besitzer_id integer PRIMARY KEY, name varchar(20), vorname varchar(20), strasse varchar(50), hausnr smallint, plz integer ); CREATE TABLE hunde ( hunde_id integer PRIMARY KEY, hunde_name varchar(20), rasse varchar(40), geschlecht char, besitzer_id integer REFERENCES besitzer(besitzer_id), CHECK (geschlecht = 'w' OR geschlecht = 'm') ); CREATE TABLE trainer ( trainer_id integer PRIMARY KEY, name varchar(20), vorname varchar(20) ); CREATE TABLE zertifikat ( zertifikat_id integer PRIMARY KEY, bezeichnung varchar(100) ); CREATE TABLE kurs ( kurs_nr integer PRIMARY KEY, kursname varchar(100), trainer_id integer REFERENCES trainer(trainer_id), zertifikat_id integer REFERENCES zertifikat(zertifikat_id) ); CREATE TABLE termin ( termin_nr integer PRIMARY KEY, datum date null, uhr time, kurs_nr integer REFERENCES kurs(kurs_nr), hunde_id integer REFERENCES hunde(hunde_id) ); CREATE TABLE hunde_zertifikat ( hunde_id integer REFERENCES hunde(hunde_id), zertifikat_id integer REFERENCES zertifikat(zertifikat_id), note numeric(8,1), PRIMARY KEY (hunde_id, zertifikat_id) ); CREATE TABLE rudel ( rudel_nr integer PRIMARY KEY, rudelname varchar(40) ); CREATE TABLE rudel_mitglied ( rudel_nr integer REFERENCES rudel(rudel_nr), mitglied_id integer REFERENCES hunde(hunde_id) ); CREATE TABLE hunderennen ( zeitraum varchar(20) PRIMARY KEY, rennname varchar(40) ); CREATE TABLE rennerergebnis ( zeitraum varchar(20) REFERENCES hunderennen(zeitraum), rudel_nr integer REFERENCES rudel(rudel_nr), endzeit time, PRIMARY KEY (zeitraum, rudel_nr) ); INSERT INTO besitzer VALUES (1, 'Holland', 'Tom', 'Alicenstr', 9, 35392); INSERT INTO besitzer VALUES (2, 'Cena', 'John', 'Breitenstr', 17, 35398); INSERT INTO besitzer VALUES (3, 'Downey', 'Robert', 'Schillerstr', 11, 35390); INSERT INTO hunde VALUES (1, 'Molly', 'Husky', 'w', 1); INSERT INTO hunde VALUES (2, 'Arthur', 'Shiba Inu', 'm', 2); INSERT INTO hunde VALUES (3, 'Petra', 'Dogge', 'm', 2); INSERT INTO hunde VALUES (4, 'Bonty', 'Dogge', 'w', 1); INSERT INTO hunde VALUES (5, 'Winter', 'Husky', 'm', 3); INSERT INTO hunde VALUES (6, 'Summer', 'Dogge', 'w', 3); INSERT INTO hunde VALUES (7, 'Autumn', 'Shiba Inu', 'm', 1); INSERT INTO hunde VALUES (8, 'Star', 'Dogge', 'w', 3); INSERT INTO hunde VALUES (9, 'Lily', 'Shiba Inu', 'w', 2); INSERT INTO trainer VALUES (1, 'Ackerman', 'Levi'); INSERT INTO trainer VALUES (2, 'D. Luffy', 'Monkey'); INSERT INTO trainer VALUES (3, 'Jaeger', 'Eren'); INSERT INTO trainer VALUES (4, 'Arlert', 'Armin'); INSERT INTO zertifikat VALUES (1, 'Bachelor of Blanket'); INSERT INTO zertifikat VALUES (2, 'Bachelor of Clicks'); INSERT INTO zertifikat VALUES (3, 'Master of Poison'); INSERT INTO zertifikat VALUES (4, 'Master of Tricks'); INSERT INTO zertifikat VALUES (5, 'Bachelor of Fetch'); INSERT INTO kurs VALUES (1, 'Grundkurs Deckentraining', 1, 1); INSERT INTO kurs VALUES (2, 'Clickertraining', 1, 2); INSERT INTO kurs VALUES (3, 'Anti-Giftkoeder-Training', 2, 3); INSERT INTO kurs VALUES (4, 'Hundetricks', 3, 4); INSERT INTO kurs VALUES (5, 'Apportieren lernen', 2, 5); INSERT INTO termin VALUES (1, '2024-01-31', now(), 1, 1); INSERT INTO termin VALUES (2, '2023-01-06', now(), 3, 4); INSERT INTO termin VALUES (3, '2023-08-24', now(), 5, 5); INSERT INTO termin VALUES (4, '2023-07-01', now(), 2, 3); INSERT INTO termin VALUES (5, '2023-07-03', now(), 2, 6); INSERT INTO hunde_zertifikat VALUES (1, 2, 3.5); INSERT INTO hunde_zertifikat VALUES (2, 3, 1.2); INSERT INTO hunde_zertifikat VALUES (2, 4, 2.0); INSERT INTO hunde_zertifikat VALUES (4, 1, 4.3); INSERT INTO hunde_zertifikat VALUES (2, 5, 3.3); INSERT INTO hunde_zertifikat VALUES (4, 5, 1.7); INSERT INTO rudel VALUES (1, 'At Least We Tried'); INSERT INTO rudel VALUES (2, 'Born Pro'); INSERT INTO rudel VALUES (3, 'Legends Never Die'); INSERT INTO rudel_mitglied VALUES (1, 1); INSERT INTO rudel_mitglied VALUES (2, 2); INSERT INTO rudel_mitglied VALUES (1, 9); INSERT INTO rudel_mitglied VALUES (3, 5); INSERT INTO rudel_mitglied VALUES (3, 4); INSERT INTO rudel_mitglied VALUES (2, 6); INSERT INTO rudel_mitglied VALUES (1, 7); INSERT INTO rudel_mitglied VALUES (2, 3); INSERT INTO rudel_mitglied VALUES (3, 8); INSERT INTO hunderennen VALUES ('20/21', 'Winter Solstice Race'); INSERT INTO hunderennen VALUES ('21/22', 'Winter Wonderland Competition'); INSERT INTO hunderennen VALUES ('22/23', 'Blizzard Snow Race'); INSERT INTO rennerergebnis VALUES ('20/21', 1, '00:05:30'); INSERT INTO rennerergebnis VALUES ('20/21', 2, '00:04:25'); INSERT INTO rennerergebnis VALUES ('20/21', 3, '00:03:55'); INSERT INTO rennerergebnis VALUES ('21/22', 1, '00:07:01'); INSERT INTO rennerergebnis VALUES ('21/22', 2, '00:06:40'); INSERT INTO rennerergebnis VALUES ('21/22', 3, '00:08:11'); INSERT INTO rennerergebnis VALUES ('22/23', 1, '00:08:09'); INSERT INTO rennerergebnis VALUES ('22/23', 2, '00:08:50'); INSERT INTO rennerergebnis VALUES ('22/23', 3, '00:09:06'); SELECT * FROM besitzer; SELECT * FROM hunde; SELECT * FROM trainer; SELECT * FROM zertifikat; SELECT * FROM kurs; SELECT * FROM termin; SELECT * FROM hunde_zertifikat; SELECT * FROM rudel; SELECT * FROM rudel_mitglied; SELECT * FROM hunderennen; SELECT * FROM rennerergebnis; /* 1. Zu welchem Kurs sind durchschnittlich die meisten Doggen angemeldet? */ SELECT k.kurs_nr, k.kursname, COUNT(*) AS anz_teilnehmer FROM kurs k INNER JOIN termin t ON t.kurs_nr = k.kurs_nr WHERE k.kurs_nr IN ( SELECT t.kurs_nr FROM termin WHERE t.hunde_id IN ( SELECT h.hunde_id FROM hunde h WHERE h.rasse = 'Dogge' ) GROUP BY t.kurs_nr ) GROUP BY k.kurs_nr, k.kursname ORDER BY COUNT(*) DESC LIMIT 1; /* 2. Welcher Hund hat die meisten Zertifikate gesammelt? (Geordnet von den meisten bis zu den wenigsten Zertifikatsinhabern) */ SELECT hz.hunde_id, h.hunde_name, COUNT(*) anz_zertifikat FROM hunde_zertifikat hz INNER JOIN hunde h USING (hunde_id) GROUP BY hz.hunde_id, h.hunde_name ORDER BY COUNT(*) DESC; /* 3. Wer ist der Hundebesitzer mit den meisten Rassen? */ SELECT b.besitzer_id, b.name, b.vorname, COUNT(DISTINCT h.rasse) anz_rasse FROM besitzer b INNER JOIN hunde h USING (besitzer_id) GROUP BY b.besitzer_id, b.name, b.vorname ORDER BY COUNT(*) DESC; /* 4. Welches Rudel hat die Bestzeit im letzten Schlittenhunderennen erreicht und wie viele Shiba Inu sind in diesem Team? */ SELECT r.rudel_nr, r.rudelname, re.endzeit AS bestzeit, COUNT(CASE WHEN h.rasse = 'Shiba Inu' THEN h.rasse ELSE NULL END) AS anz_shibainu FROM rennerergebnis re INNER JOIN rudel r USING (rudel_nr) INNER JOIN rudel_mitglied rm USING (rudel_nr) INNER JOIN hunde h ON h.hunde_id = rm.mitglied_id WHERE re.zeitraum = '22/23' GROUP BY r.rudel_nr, r.rudelname, re.endzeit ORDER BY bestzeit ASC LIMIT 1; /* 5. Welcher Trainer bildet mehr als 1 Kurs aus? */ SELECT tr.trainer_id, tr.name FROM trainer tr INNER JOIN kurs k USING (trainer_id) GROUP BY tr.trainer_id, tr.name HAVING COUNT(*) > 1 ORDER BY tr.trainer_id ASC; /* 6. Zeigen Sie eine Liste der Durchschnittsnoten aller Hündinnen, die Zertifikate erhalten haben, von der höchsten bis zur niedrigsten. */ SELECT hz.hunde_id, CAST(AVG(hz.note) AS numeric(8,1)) AS durchschnittsnote FROM hunde_zertifikat hz INNER JOIN hunde h USING (hunde_id) WHERE h.geschlecht = 'w' GROUP BY hz.hunde_id; /* 7. Liste aller Hunde, die zu keinem Kurstermin angemeldet sind, nach Namen aufsteigend sortiert. */ SELECT h.hunde_name FROM hunde h WHERE h.hunde_id not in (SELECT t.hunde_id FROM termin t) ORDER BY h.hunde_name ASC; /* 8. Welcher Hund hat das "Anti-Giftkoeder-Training" absolviert? */ SELECT hz.hunde_id, h.hunde_name FROM hunde_zertifikat hz INNER JOIN hunde h ON h.hunde_id = hz.hunde_id INNER JOIN kurs k ON k.zertifikat_id = hz.zertifikat_id WHERE kursname = 'Anti-Giftkoeder-Training' GROUP BY hz.hunde_id, h.hunde_name; /* Trigger - create a trigger that checks if a specific dog has Bachelor certificate before registering Master course */ CREATE OR REPLACE FUNCTION check_zertifikat() RETURNS trigger AS $$ DECLARE kursnummer integer; BEGIN SELECT t.kurs_nr INTO kursnummer FROM termin t; IF kursnummer = '3' OR kursnummer = '4' THEN RAISE EXCEPTION 'Erforderliches Zertifikat fehlt --> 1, 2 oder 5' USING HINT = 'Bitte überprüfen Sie das Zertifikat Ihres Hundes'; END IF; RETURN NULL; END; $$ LANGUAGE PLPGSQL; CREATE OR REPLACE TRIGGER tg_zertifikat AFTER INSERT OR UPDATE ON termin FOR EACH ROW EXECUTE FUNCTION check_zertifikat(); /* Zum Testen */ UPDATE termin SET kurs_nr = '3' WHERE termin_nr = '3'; SELECT * FROM termin;