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;
Telegram: DB developers channel