SQL Abfragen – Verknüpfte Abfragen

SQL Abfragen – Verknüpfte Abfragen - Lösung
WICHTIGER HINWEIS: Beim Kopieren der Lösung in das Formularfeld von sql.idv.edu
müssen die Anführungszeichen dort neu hineingeschrieben (überschrieben) werden.
Ansonsten funktioniert das Statement nicht.
Wiederholung: Wie heißt der Kunde Nr. 14 mit Vor- und Nachnamen?
SELECT Vorname, Nachname
FROM Kunde
WHERE Nr = 14
Bsp1: Gesucht:
- Nachname
- PLZ
- Ort
SELECT Kunde.Nachname, Kunde.Plz, PLZ.Ort
FROM Kunde, PLZ
WHERE (PLZ.Plz = Kunde.Plz)
Bsp2: Gesucht sind alle Bücher mit
- Buchtitel
- Verlag (= Kurzbezeichnung)
- Name (= Langbezeichnung)
SELECT Buch.Titel, Buch.Verlag, Verlag.Name
FROM Buch, Verlag
WHERE (Buch.Verlag = Verlag.Kurzbezeichnung)
Bsp3a: Wieviel Umsatz pro Buch (Preis * Menge) haben wir mit Auftrag Nr. 7 gemacht?
Gesucht:
- Buchtitel
- Buchnummer
- Umsatz pro Buch
- Auftragsnummer
Hinweis: Buchhändlter kaufen von 1 Buch meist größere Mengen (Großhandel)
SELECT Buch.Titel, Buch.Nr, Buch.Preis*Auftragspos.Menge, Auftragspos.Auftragsnummer
FROM Buch, Auftragspos
WHERE (Buch.Nr = Auftragspos.Buchnummer)
AND Auftragspos.Auftragsnummer = 7
Informationsverarbeitung, Uni-Linz, Ernst Rodlmayr 2013
-1-
Bsp3b: wie 3a erweitert um absteigende Sortierung nach Umsatz
SELECT Buch.Titel, Buch.Nr, Buch.Preis*Auftragspos.Menge AS Umsatz,
Auftragspos.Auftragsnummer
FROM Buch, Auftragspos
WHERE (Buch.Nr = Auftragspos.Buchnummer)
AND Auftragspos.Auftragsnummer = 7 ORDER BY Umsatz DESC
Bsp3c: Gesucht:
- Gesamteinkaufssumme von Auftrag Nr. 7
- Auftragsnummer
SELECT SUM(Buch.Preis*Auftragspos.Menge), Auftragspos.Auftragsnummer
FROM Buch, Auftragspos
WHERE (Buch.Nr = Auftragspos.Buchnummer)
AND Auftragspos.Auftragsnummer = 7
GROUP BY Auftragspos.Auftragsnummer
oder
SELECT SUM(Buch.Preis*Auftragspos.Menge), Auftragspos.Auftragsnummer
FROM Buch, Auftragspos
WHERE (Buch.Nr = Auftragspos.Buchnummer)
GROUP BY Auftragspos.Auftragsnummer HAVING Auftragspos.Auftragsnummer = 7
Bsp3d: wie 3c aber alle Auftragsnummern
SELECT SUM(Buch.Preis*Auftragspos.Menge), Auftragspos.Auftragsnummer
FROM Buch, Auftragspos
WHERE (Buch.Nr = Auftragspos.Buchnummer)
GROUP BY Auftragspos.Auftragsnummer
Bsp3e: wie 3d aber zusätzlich mit:
- Vorname
- Nachname des Kunden
SELECT SUM(Buch.Preis*Auftragspos.Menge), Auftragspos.Auftragsnummer,
Kunde.Vorname, Kunde.Nachname
FROM Buch, Auftragspos, Auftrag, Kunde
WHERE (Buch.Nr = Auftragspos.Buchnummer)
AND (Auftragspos.Auftragsnummer = Auftrag.Nr)
AND (Auftrag.Kundennummer = Kunde.Nr)
GROUP BY Auftragspos.Auftragsnummer, Kunde.Vorname, Kunde.Nachname
Hinweis: Group BY kommt zwingend zum Einsatz, sobald eine arithmetische Funktion mit
einer normalen Spalte in der SELECT-Anweisung enthalten ist. Alle Spalten die im SELECT
vorkommen, mit Ausnahme der arithmetischen Funktion(en), müssen auch im GROUP BY
vorkommen. Das wird als „Only Full GROUP BY“ bezeichnet. Bitte an diese Institutsvorgabe
halten! Meines Erachtens spräche aber auch nichts gegen partielle GROUP BYs, vgl:
http://mysql.lamphost.net/tech-resources/articles/debunking-group-by-myths.html
Informationsverarbeitung, Uni-Linz, Ernst Rodlmayr 2013
-2-
Bsp4: Welche Kunden haben im Jänner 2000 bei uns eingekauft? Gesucht:
- Kundennummer
- Nachname
SELECT Kunde.Nr, Kunde.Nachname
FROM Kunde, Auftrag
WHERE (Kunde.Nr = Auftrag.Kundennummer)
AND Auftrag.Datum BETWEEN '2000-01-01' AND '2000-01-31'
* Hinweis: Es gibt hier auch Lösungen mit LIKE oder MONTH / YEAR
Bsp5: Gesucht:
- Max Buchpreis
- Min Buchpreis
- Durschn. Buchpreis
- Summe aller Preise
- Anzahl d. versch. Bücher in der DB
SELECT MAX(Preis), MIN(Preis), AVG(Preis), SUM(Preis), COUNT(*)
FROM Buch
Bsp6a: Wie viele Exemplare hat Herr „Karl Risse Ritter“ vom Buch „Lorenzo“ beim Auftrag
Nr. 437 gekauft? Gesucht:
- Vorname
- Nachname
- Auftragsnummer
- Buchtitel
- Menge
SELECT Kunde.Vorname, Kunde.Nachname, Auftragspos.Auftragsnummer, Buch.Titel,
Auftragspos.Menge
FROM Buch, Auftragspos, Auftrag, Kunde
WHERE (Buch.Nr = Auftragspos.Buchnummer)
AND (Auftragspos.Auftragsnummer = Auftrag.Nr)
AND (Auftrag.Kundennummer = Kunde.Nr)
AND Kunde.Vorname = "Karl"
AND Kunde.Nachname = "Risse Ritter"
AND Buch.Titel = "Lorenzo"
AND Auftragspos.Auftragsnummer = 437
Bsp6b: wie 6a aber bei allen Aufträgen vom Buch „Lorenzo“ von Hr. Risse Ritter?
SELECT Kunde.Vorname, Kunde.Nachname, Buch.Titel, SUM(Auftragspos.Menge)
FROM Buch, Auftragspos, Auftrag, Kunde
WHERE (Buch.Nr = Auftragspos.Buchnummer)
AND (Auftragspos.Auftragsnummer = Auftrag.Nr)
AND (Auftrag.Kundennummer = Kunde.Nr)
AND Kunde.Vorname = "Karl"
AND Kunde.Nachname = "Risse Ritter"
AND Buch.Titel = "Lorenzo"
GROUP BY Kunde.Vorname, Kunde.Nachname, Buch.Titel
Informationsverarbeitung, Uni-Linz, Ernst Rodlmayr 2013
-3-
HINWEIS: Wenn wir ein einzeiliges Ergebnis möchten, darf das Feld Auftragsnummer nicht
im SELECT (und GROUP BY) enthalten sein, weil sonst das Ergebnis zusätzlich nach
Auftragsnummern aufgeschlüsselt wird => mehrzeilig.
Bsp7: Wieviele Bücher wurden von den verschiedenen Verlagen insgesamt verkauft, die mit
„Textverarbeitung“ zu tun haben? Gesucht:
- Verlag
- Menge
SELECT Buch.Verlag, SUM(Auftragspos.Menge)
FROM Buch, Auftragspos
WHERE (Buch.Nr = Auftragspos.Buchnummer)
AND Buch.Titel LIKE "%Textverarbeitung%"
GROUP BY Buch.Verlag
Bsp8: Wir wollen alle Aufträge ausgeben lassen, wo die Gesamteinkaufssumme > 20000.
Gesucht:
- Auftragsnummer
- Kundennummer
- Gesamteinkaufssumme
SELECT Auftragspos.Auftragsnummer, Auftrag.Kundennummer, SUM(Buch.Preis *
Auftragspos.Menge) AS GEKSumme
FROM Buch, Auftragspos, Auftrag
WHERE (Buch.Nr = Auftragspos.Buchnummer)
AND (Auftragspos.Auftragsnummer = Auftrag.Nr)
GROUP BY Auftragspos.Auftragsnummer, Auftrag.Kundennummer
HAVING SUM(Buch.Preis * Auftragspos.Menge) > 20000
HINWEIS: Eine Funktion kann nur über HAVING eingeschränkt werden. HAVING kann als
Teil des GROUP BY betrachtet werden.
HINWEIS 2: Vor der Serverumstellung (only full group by) im Dez. 2011 funktionierte noch
diese Lösungsversion: HAVING GEKSumme > 20000. Leider führt das jetzt zu einer
Fehlermeldung.
Informationsverarbeitung, Uni-Linz, Ernst Rodlmayr 2013
-4-