2. Übung zu joins

PostgreSQL
27. November 2014
E3A
Übung
1. Ändere die Tabelle wein so ab, dass sie eine Spalte für Preise (5 Stellen, davon 2 Nachkomma)
erhält und füge Preise deiner Wahl ein.
2. Liste die Weinbauern und die Anzahl ihrer Weine aus.
3. Liste die Weinbauern und die Anzahl ihrer Weine aus, aber nur die, die mindestens 2 Weine im
Angebot haben.
4. Wie viele Rotweine und wie viele Weißweine gibt es?
5. Gib eine Liste aller Anbaugebiete aus plus die Anzahl der darin vorhandenen Erzeuger. Anbaugebiete ohne Erzeuger sollen mit einer 0 aufscheinen.
6. Erstelle eine Liste, die die Erzeuger und ihre Weine ausgibt, aber nur solche, deren Preis geringer
als der Durchschnitt aller Weine ist.
7. Welcher Weinbauern produziert den billigsten Wein?
8. Welche Erzeuger produzieren keinen ’grünen veltliner’ ?
9. Erstelle eine Liste der Erzeuger mit dem Wert (Summe Preis) ihrer Weine
10. Schreibe ein Statement, mit dem die Regionen und die Anzahl der Erzeuger pro Region gelistet
werden.
11. Welche Weine haben keine Erzeuger zugeordnet?
12. Liste alle Erzeuger auf, die keine Weine zugeordnet haben und liste alle Weine auf, die keinen
Erzeuger zugeordnet haben. Verwende nur ein Statement.
1
PostgreSQL
November 27, 2014
E3A
Listing 1:
alter table wein add column preis decimal(5,2);
Listing 2:
select e.name, count(we.enr) from erzeuger e join w_e we on e.enr = we.enr
group by e.enr;
--oder mit Weinbauern, die keine Weine haben
select e.name, count(we.enr) from erzeuger e left join w_e we on e.enr = we.enr
group by e.enr;
Listing 3:
select e.name, count(we.enr) from erzeuger e join w_e we on e.enr = we.enr
group by e.enr having count(we.enr) >= 2;
Unterschied having - where: having schränkt ein Ergebnis ein, das mit ’group by’ gruppiert wurde;
where wird eingesetzt, bevor ’group by’ zum Zug kommt
Listing 4:
select farbe, count(*) from wein group by farbe;
Listing 5:
select a.name, count(e.anr) from anbau a left join erzeuger e on a.anr = e.anr
group by a.anr;
Listing 6:
select e.name, w.name from erzeuger e join w_e we on e.enr = we.enr
join wein w on we.wnr = w.wnr
where w.preis < (select avg(preis) from wein);
Listing 7:
select e.name, w.name from erzeuger e join w_e we on e.enr = we.enr
join wein w on w.wnr = we.wnr
where w.preis = (select min(preis) from wein w);
Listing 8:
-- Zuerst alle Weinbauern, die 'Grünen Veltliner' produzieren
select e.name, w.name from erzeuger e join w_e we on e.enr = we.enr
join wein w on w.wnr = we.wnr
where w.name = 'grüner veltliner';
w.name kann weggelassen werden
dann diese Abfrage in eine Unterabfrage einbauen (ein != geht hier nicht, da diese Abfrage mehrere
Zeilen liefert, daher das NOT IN
select e.name from erzeuger e where e.name NOT IN
(
select e.name, w.name from erzeuger e join w_e we on e.enr = we.enr
join wein w on w.wnr = we.wnr
where w.name = 'grüner veltliner'
)
2
PostgreSQL
November 27, 2014
Listing 9:
select e.name, sum(preis) from erzeuger e join w_e we on e.enr = we.enr
join wein w on w.wnr = we.wnr
group by e.enr;
Listing 10:
select r.name,count(e.anr) from region r left join anbau a on r.rnr = a.rnr
left join erzeuger e on a.anr = e.anr
group by r.rnr;
Listing 11:
select w.name, we.enr from wein w left join w_e we on w.wnr = we.wnr
where we.enr is null;
Listing 12:
select w.name, e.name from wein w full join w_e we on w.wnr = we.wnr
full join erzeuger e on e.enr = we.enr
where e.name is null or w.name is null;
3
E3A