Angabe Musik - MultiAugustinum

PostgreSQL
20. Dezember 2011
E3A
Lege eine Datenbank musik an, markiere danach die Datenbank im pgAdmin3, öffne
das SQL-Fenster (Lupe) und spiele folgende Datei ein:
http://www.multiaugustinum.com/~guch/images/a/a8/Musik.txt
Löse folgende Aufgaben:
1. 1.1. Erstelle eine SQL-Abfrage , die ausgibt, welcher Komponist welches Werk
(populärname) geschrieben hat. Die Ausgabe gibt nur einen Ausschnitt wider.
80 Punkte
?column?
−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
bach johann sebastian hat 1. brandenburgische konzert komponiert
bach johann sebastian hat 2. brandenburgische konzert komponiert
bach johann sebastian hat 3. brandenburgische konzert komponiert
bach johann sebastian hat 4. brandenburgische konzert komponiert
bach johann sebastian hat 5. brandenburgische konzert komponiert
bach johann sebastian hat 6. brandenburgische konzert komponiert
...
1.2. Erstelle eine Abfrage, mit der ausgegeben wird, ob es Komponisten ohne zugeordnete Werke gibt oder nicht. Ausgabe beim aktuellen Datensatz:
gibt es komponisten ohne werke?
−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
nein
(1 Zeile)
1.3. Erstelle eine Abfrage, die ausgibt, welche Komponisten am ältesten geworden
sind.
vorname |nachname
franz | liszt
roman | haubenstock−ramati
(2 Zeilen)
1.4. Erstelle eine Abfrage, die ausgibt, aus welchem Land wie viele Komponisten
kommen. Beachte die Schreibweise der Wörter komponist und kommen in
Abhängigkeit von der Anzahl (unten stehende Ausgabe)!!!
?column?
−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
aus ungarn kommt 1 komponist
aus polen kommen 2 komponisten
aus österreich kommt 1 komponist
aus deutschland kommen 2 komponisten
(2 Zeilen)
1.5. Erzeuge eine Regel, die bei einem Löschvorgang auf Tabelle r_f zusätzlich die
Daten in eine Log-Tabelle überträgt (also eine Art backup erstellt). Die LogTabelle soll die gleichen Spalten mit den gleichen Datentypen aufweisen
2. 2.1. Führe die Befehle an, mit denen die Tabellen region, anbau, erzeuger
eines ER-Modells erstellt werden. Die grau hinterlegten Felder sind die Schlüsselfelder. Die Datentypen spielen keine Rolle und können frei gewählt werden.
Ebenso ist die Anzahl der rows nicht zu beachten.
1
20 Punkte
PostgreSQL
20. Dezember 2011
E3A
2.2. Korrigiere folgende SQL-Befehle, wobei die Logik des Programms nicht geändert werden darf.
2.2.1.
SELECT a.id, a.bezeichnung, (a.preis * sum(b.menge)) as umsatz
FROM produkte a LEFT JOIN bestellungen_produkte b where a.id = b.produkte_id
ORDER by a.id GROUP BY a.id, a.bezeichnung, a.preis
2.2.2.
ALTER TABLE bestellungen_produkte
SET menge = menge − 2
WHERE produkte_id = 2
AND bestellungen_id = 4;
2.2.3.
SELECT a.abteilungen_id, a.vorname, a.nachname, a.email, b.abteilung
FROM mitarbeiter a, abteilungen b
WHERE (a.abteilungen_id = 3 OR a.abteilungen_id = 2)
AND a.abteilungen_id = b.id;
2.2.4.
UPDATE TABLE produkte
ADD CHECK (preis > 0);
2.2.5.
create table bestell_position (
bid int,
aid int,
menge int,
primary key(bid, aid) references artikel(aid)
)
2
PostgreSQL
20. Dezember 2011
E3A
Lösungen
1. Lösung
select k.nachname||' '||k.vorname||' hat '||w.populärname ||' komponiert' from
komponist k, werk w where w.kpnum = k.kpnum;
2. Lösung
select
case when
(select count(k.kpnum) from komponist k left join werk w on k.kpnum = w.kpnum
where w.kpnum is null) > 0 then 'ja, es gibt komponisten ohne werke'
else 'nein'
end as "gibt es komponisten ohne werke?";
3. Lösung
select vorname, nachname from komponist where
tod_jahr − geb_jahr = (select max(tod_jahr − geb_jahr) from komponist);
4. Lösung
select 'aus '||land||
case when count(land) = 1 then ' kommt '||count(land)||' komponist'
else ' kommen '||count(*)||' komponisten'
end
from komponist group by land;
5. Lösung
create rule r_delrf as on delete to r_f do also insert into log_rf values(old.rid, old.fid);
3