aufgabe 2

1 §

CREATE VIEW grosser_hbf AS
SELECT * FROM hbf
WHERE kategorie=1
WITH LOCAL CHECK OPTION;

2 §

a §

Nicht in grosser_hbf zu finden, da kategorie=2.

SELECT * FROM hbf
WHERE station LIKE '%egensburg%';

b §

DROP NOT NULL

Für diesen Insert müssen einige NOT NULL-Constraints entfernt werden.

ALTER TABLE bahnhoefe ALTER regionalbereich DROP NOT NULL;
ALTER TABLE bahnhoefe ALTER bahnhofsmanagement DROP NOT NULL;
ALTER TABLE bahnhoefe ALTER betriebsstelle DROP NOT NULL;
ALTER TABLE bahnhoefe ALTER strasse DROP NOT NULL;
ALTER TABLE bahnhoefe ALTER aufgabentraeger DROP NOT NULL;

INSERT INTO hbf (station, bundesland, id, kategorie)
VALUES ('Teststadt Nord', 'Hessen', 9999, 5);

c §

SELECT * FROM hbf WHERE id=9999; -- (0 rows)

Nein, wegen WHERE station LIKE '%Hbf'.

d §

DELETE FROM hbf WHERE id=9999; -- DELETE 0 (macht nichts)
DELETE FROM bahnhoefe WHERE id=9999; -- DELETE 1 (funktioniert)

e §

INSERT INTO grosser_hbf (station, bundesland, id, kategorie)
VALUES ('Teststadt Nord', 'Hessen', 9999, 5);

Error

ERROR:  new row violates check option for view "grosser_hbf"
DETAIL:  Failing row contains (Hessen, null, null, 9999, Teststadt Nord, null, 5, null, null, null, null).

f §

INSERT INTO grosser_hbf (station, bundesland, id, kategorie)
VALUES ('Teststadt Nord', 'Hessen', 9999, 1); -- funktioniert

g §

SELECT * FROM grosser_hbf WHERE id=9999; -- (0 rows)

Nein, wegen WHERE station LIKE '%Hbf'.

h §

DROP VIEW grosser_hbf;

CREATE VIEW grosser_hbf AS
SELECT * FROM hbf
WHERE kategorie=1
WITH CHECK OPTION; -- CASCADED is default

INSERT INTO grosser_hbf (station, bundesland, id, kategorie)
VALUES ('Teststadt Nord', 'Hessen', 9999, 1); -- funktioniert

Error

ERROR:  new row violates check option for view "hbf"
DETAIL:  Failing row contains (Hessen, null, null, 9999, Teststadt Nord, null, 1, null, null, null, null).

3 §

CREATE MATERIALIZED VIEW bahnhof_statistik AS
SELECT bundesland, COUNT(*) FROM bahnhoefe
GROUP BY bundesland;

SELECT * FROM bahnhof_statistik;
DELETE FROM bahnhoefe WHERE id=9999;
REFRESH MATERIALIZED VIEW bahnhof_statistik;
SELECT * FROM bahnhof_statistik;

431 $\to$ 430

4 §

a §

SELECT DISTINCT bundesland FROM bahnhoefe;

b §

SELECT ort, COUNT(*) FROM bahnhoefe
GROUP BY ort
HAVING COUNT(*) > 10;

c §

SELECT B.* FROM bahnhoefe U
JOIN bahnhoefe B USING(kategorie)
WHERE U.station='Ulm Hbf';

d §

WITH orte AS (
	SELECT bundesland, ort, COUNT(*) AS anzahl
	FROM bahnhoefe
	GROUP BY ort, bundesland
) -- Anzahl Stationen pro Ort
SELECT bundesland, AVG(anzahl) FROM orte
GROUP BY bundesland