SQL Beispielabfrage

student (matrnr, name, gebjahr)
belegt (matrnr, lvanr, name, semester, stunden, note)

 

SELECT s.name, AVG(note) AS Schnitt
FROM student s, belegt b
WHERE s.matrnr = b.matrnr AND
gebjahr < 1980 AND
semester = `WS02'
GROUP BY s.matrnr, s.name
HAVING COUNT(*) > 5 AND
AVG(note) < 2
ORDER BY COUNT(*) DESC;

Nur die positiven Zeugnisse zählen:

COUNT(note < 5) > 5

Gewichteter Schnitt:

AVG(note * stunden) / SUM(stunden) < 2

Beispieldaten

 

personen

PNR	ZUNAME	VORNAME	GEHALT	GESCHLECHT
1	Bauer	Silvia	1200	`w'
2	Huber	Klaus	900	`m'
3	Maier	Markus	2400	`m'
4	Maier	Ulrike	1800	`w'
5	Huber	Klaus	1100	`m'

kinder

PNR	VORNAME	GEBJAHR	GESCHLECHT
3	Markus	1989	`m'
3	Martin	1999	`m'
1	Olivia	1982	`w'
1	Oskar	1995	`m'
2	Silvia	1988	`w'
5	Silvia	1993	`w'

 

SQL - einfaches Beispiel

SELECT vorname, zuname
FROM personen
WHERE gehalt < 2000 AND
gehalt > 1000;

SQL Statement - Arbeitsweise

SELECT attributes
FROM relations
WHERE conditions;

• Zunächst wird das kartesische (Kreuzprodukt) der angegebenen Relationen gebildet:
• Jede Zeile einer Relation wird mit jeder Zeile der anderen Relationen kombiniert
• gleichlautende Attribue kommen öfters vor
• Anzahl der entstehenden Zeilen: Produkt der Zeilen der Relationen
• Anzahl der entstehenden Spalten: Summe der SPalten der Relationen
• Dann wird für jedes Tupel extra überprüft, ob die Bedingungen erfüllt sind
• dabei kann weder auf einen andere Zeile des Kreuzproduktes noch auf die Relationen als ganzes zugegriffen werden
• Tupel, die die Bedingungen nicht erfüllen werden ignoriert, die anderen werden weiter bearbeitet
• Abschließend werden die Werte der angegebenen Attribute der verbliebenen Tupel ausgegeben in zufälliger Reihenfolge ausgegeben

SQL - weiteres Beispiel

Geben Sie Personen mit KIndern und deren Gehalt (der Personen) aus. Sortieren Sie das Ergebnis nach der Angestelltennummer.

SELECT DISTINCT personen.pnr, gehalt
FROM personen, kind
WHERE personen.pnr = kinder.pnr AND
gebjahr > 1985
ORDER BY personen.pnr ASC;

Um zu wissen, welche pnr gemeint ist, muss die Relation aus der sie stammt angegeben werden. gehalt und gebjahr kommen nur in einer Relation vor, deshalb muss die zugehörige Relation nicht vermerkt werden.

SQL - einfache Aggregatfunktion

Geben Sie nach Anzahl und Gesamtgehalt der Angestelten nach Geschlecht sortiert aus:

SELECT geschlecht, COUNT(*), SUM(gehalt)
FROM personen
GROUP BY geschlecht;

SQL Statement mit GROUP BY - Arbeitsweise

SELECT attributes
FROM relations
WHERE conditions
GROUP BY attributes_2
HAVING conditions_2;

• Die durch das Kreuzprodukt entstehenden Tupel werden wie bereits beschrieben gefiltert.
• Danach werden alle Tuppel, die in den angegebenen Attributen ( attributes_2 ) übereinstimmen zu Gruppen zusammengefasst.
• Von nun an kann auf die einzelnen Zeilen nicht mehr zugegriffen werden, sondern nur mehr auf die Gruppen als ganzes.
  Das heisst: über Aggregatfunktionen oder über Attribute nach denen gruppiert wurde
• Für jede der entstehenden Gruppen werden die Bedingeungen überprüft.
• Gruppen, welche die Bedingungen erfüllen werden ausgegeben
  Auch bei der Ausgabe kann nur auf eindeutige Attribute (weil danach gruppiert wurde) oder auf Aggregate von Attributen zugegriffen werden
  !! ACHTUNG !! In dieser Beziehung ist SQL "dumm" - auch wenn nach den Schlüsseln gruppiert wurde, kann nicht auf die (abhängigen) Attribute zugegriffen werden, obwohl diese ja eigentlich eindeutig sind.

Fehler bei Groupierungen, Aggregatfunktionen - Bsp 1

häufige Fehler bei GROUP BY :

SELECT zuname, vorname, AVG(gehalt)
FROM personen
WHERE gehalt >= 1000
GROUP BY zuname;

Es kann mehrere Personen mit demselben Zunamen aber verschiedenen Vornamen geben. Bei der Ausgabe "weiss" SQL nicht, welchen dieser Zunamen es ausgeben soll.

Fehler bei Groupierungen, Aggregatfunktionen - Bsp 2

vollständiger Name der Personen, die am meisten verdienen (davon kann es auch mehrere geben):

SELECT zuname, vorname, MAX(gehalt)
FROM personen;

Es wird jede Person extra ausgegeben (keine Bedingungen). Von jeder Person wird das Maximum des Gehaltes berechnet, was natürlich der Gehalt dieser Person ist.

Fehler bei Aggregatfunktionen

Personen, die mehr verdienen als der Durchschnitt - falsche Variante:

SELECT pnr
FROM personen
WHERE gehalt > AVG(gehalt);

Analog zum letzten Beispiel: es wird für jede Person überprüft, ob diese mehr verdient als der Durchschnitt ihres eigenen Gehalts -> die Query liefert nichts (verwendet man hier ein ` >= ' statt ` > ' wird die gesamte Relation ausgegeben).

SQL Statement mit Subselect - Arbeitsweise

SELECT attributes
FROM relations
WHERE attr rel ( SELECT attributes_2
FROM relations_2
WHERE conditions_2 );

• Abarbeitung des äußeren SQL-Statements wie gewohnt
• Das innere Statement wird für jede Zeile des äußeren Statements einmal ausgewertet.
• Im inneren Statement kann auf ein im äußeren Statement vorkommendes Attribut Bezug genommen werden.
  

SQL Beispiele - Teilbedingungen

Beispiel zu EXISTS :

Geben Sie die Namen aller Personen aus, die Kinder haben.

SELECT vorname, zuname
FROM personen p
WHERE EXISTS ( SELECT *
FROM kinder k
WHERE k.pnr = p.pnr);

SQL Statement mit Subselect - Beispiel

Geben Sie alle Angestellten aus, die weniger verdienen als jemand mit demselben Namen.

SELECT pnr, vorname, zuname
FROM personen p1
WHERE vorname, zuname = ANY
( SELECT vorname, zuname
FROM personen p2
WHERE p1.pnr <> p2.pnr AND
p1.gehalt<p2.gehalt);

@ Division

Eine Divsion stellt fest welche (Teil-)Tupel der 1. Relation mit allen Tuppeln der 2. Relation kombiniert sind.

Fliegt

PILOT	TYP
Bauer	707
Bauer	727
Huber	707
Huber	727
Huber	747
Müller	727

Typ

TYP
707
727
747

Hier wird berechnet, welche Piloten alle (Flugzeug)Typen fliegen. Ergebnis:
Bauer kommt nicht in Kombination mit 747 vor.
Müller wird nur mit 727 kombiniert.

PILOT
Huber

Beispiel

sammler (sammlernr, name, adresse, telefon)
maler (malernr, name, adresse, kontonr)
bild (malernr, bildnr, jahr, gewicht, groesse)
kauf (kaufnr, sammlernr, malernr, bildnr, preis, datum)

a) Schreiben Sie eine Abfrage in SQL, welche die Namen aller Maler ausgibt, von denen Bilder im Gesamtwert von mehr als 50.000 Euro angekauft wurden. Sortieren Sie die Liste aufsteigend nach dem (ebenfalls ausgegebenen) Gesamtwert der Bilder.

b) Schreiben Sie eine SQL-Abfrage, die den zweithöchsten Preis ausgibt, um den je ein Bild verkauft wurde.

Lösung Beispiel a)

 

 

 

 

 

 

SELECT m.name, (0-SUM(k.preis)) AS Gesamtwert
FROM maler m, kauf k
WHERE m.malernr = k.malernr AND
k.preis < 0
GROUP BY m.malernr, M.name
HAVING Gesamtwert > 50000
ORDER BY Gesamtwert DESC;

Lösung Beispiel b)

 

 

 

 

 

 

 

 

SELECT MAX(preis)
FROM kauf
WHERE preis > 0 AND
preis <( SELECT MAX(preis)
FROM kauf);

SELECT DISTINCT k.preis
FROM kauf k
WHERE k.preis > 0 AND
1 = ( SELECT COUNT(DISTINCT k2.preis)
FROM kauf k2
WHERE k2.preis > k.preis);