Ausführliche Beispiele
* /SchwarzfahrtErkennung: Unterabfrage, Lückensuche, Join, effizienter Join
Texte in der Ausgabe
SELECT Name || ",", "PLZ=" || Land || "-" || PLZ FROM Kunden
Die Ausgabe könnte dann sein:
Schmid, D-80686 Huber, A-47110
Dies ist vor allem bei Export von Anfragen in Dateien brauchbar: Damit können z.B. auch CSV-Dateien erzeugt werden, (wenn man mit einem Filter noch die überflüssigen Leerzeichen entfernt).
Sortierungsanweisung
GROUP BY: im Beispiel MittelMaxMin wurden der Durchschnitt, das Maximum sowie das Minimum der Angebotspreise pro Artikelnummer ausgegeben, z.B.:
SELECT DISTINCTROW EkPreise.ArtNr, Avg(EKPreise.Einkaufspreis) AS Mittelpreis, Max(EKPreise.Einkaufspreis) AS MaxPreis, Min(EKPreise.Einkaufspreis) AS MinPreis FROM EKPreise GROUP BY EKPreise.ArtNr;
HAVING: Damit kann man bei der Gruppenbildung Bedingungen beachten, z.B.:
SELECT DISTINCTROW
Artikel_Lieferer.LiefNr,Count(Artikel_Lieferer.ArtNr) AS(Art-Anzahl)
FROM Artikel_Lieferer
GROUP BY Artikel_Lieferer.LiefNr
HAVING (((Count(Artikel_Lieferer.ArtNr))>3));
Kreuztabelle erstellen
In einer Kreuztabelle werden zwei Gruppenauswertungen in Tabellenform vorgenommen, deshalb sind die beiden Gruppenbegriffe sowie die mit Hilfe einer Aggregatfunktion auszuwertende Spalte anzugeben, z.B.:
TRANSFORM Aggregatfunktion (auszuwertende Spalte)
SELECT Spaltenname des 1. Gruppenbegriffs {Zeile}
FROM Tabelle
GROUP BY Spaltenname des Gruppenbegriffs
PIVOT Spaltenname des 2.Gruppenbegriffs {Spalte}
Unterabfragen
Unterabfragen mit relationalem Operator
Der Einkaufsleiter möchte wissen, bei welchen Lieferanten der Artikel 4630 weniger als der durchschnittliche Angebotspreis kostet:
SELECT avg(AngPreis) FROM Artikel_Lieferer WHERE ArtNr = 4630;
Die Abfrage liefert den Mittelwert 25,40 EUR. Dieser Wert wird in die folgende Abfrage eingesetzt:
SELECT ArtNr, LiefNr, AngPreis FROM Artikel_Lieferer WHERE ArtNr = 4630 AND AngPreis < 25,40;
Da diese Abfrage in eine andere einbezogen ist, nennt man sie Unterabfrage (sie steht in runden Klammern). Bei der Ausführung der Abfrageanweisung wird die Unterabfrage zuerst ausgewertet. Die übergeordnete SELECT Anweisung wird als Hauptabfrage bezeichnet.
SELECT ArtNr, LiefNr, AngPreis FROM Artikel_Lieferer Hauptabfrage WHERE ArtNr = 4630 AND AngPreis < (SELECT avg(AngPreis) FROM Artikel_Lieferer Unterabfrage WHERE ArtNr = 4630);
Unterabfragen nicht nur bei WHERE
Unterabfragen können auch in der Liste der auszugebenden Infos stehen:
Select Name, (SELECT sum (Summe) FROM Auftraege A WHERE K.Id=A.Kunde) As Auftragssumme FROM KUNDE K ORDER BY 2
- Hier werden alle Kunden mit Namen und der Summe ihrer Aufträge aufgelistet.
- Sortiert wird nach der 2. Spalte, also nach Auftragssumme. (ORDER BY 2)
- Die Spaltenüberschrift der 2. Spalte wird "Auftragssumme" (AS Auftragssumme).
Unterabfrage mit ALL oder ANY
Liefert eine Unterabfrage mehr als einen Ergebniswert, so sind die Operatoren ALL und ANY zusammen mit den relationalen Operatoren >, <, >= und <= zu verwenden. z.B.
<= ALL(SELECT AngPreis
FROM Artikel, Artikel_Lieferer
WHERE (GrupNr = 10 OR GrupNr = 30)
AND Artikel.ArtNr = Artikel_Lieferer.ArtNr)> ALL : In der Hauptabfrage werden alle Zeilen angezeigt, deren Vergleichswerte größer sind als der größte Wert, den die Unterabfrage liefert.
>= ALL : In der Hauptabfrage werden alle Zeilen angezeigt, deren Vergleichswerte größer oder gleich dem größten Wert sind, den die Unterabfrage liefert.
< ALL : In der Hauptabfrage werden alle Zeilen angezeigt, deren Vergleichswerte kleiner sind als der kleinste Wert, den die Unterabfrage liefert.
<= ALL : In der Hauptabfrage werden alle Zeilen angezeigt, deren Vergleichswerte kleiner oder gleich dem kleinsten wert sind, den die Unterabfrage liefert.
Korrelierte Unterabfrage
Unter Korrelieren versteht man das Herstellen einer Wechselbeziehung. In der Tat besteht zwischen der Hauptabfrage und der Unterabfrage eine Wechselbeziehung, die durch z.B. EKPreise.ArtNr = E.ArtNr gebildet wird.
UNION
Mit der UNION-Anweisung kann man die Ausgabe mehrerer SELECT-Anweisungen in einer Datenanzeige zusammenfassen, z.B.:
SELECT ArtNr, LiefNr , Einkaufspreis, Listenpreis FROM ArtVorTab WHERE Einkaufspreis < (Listenpreis * 0.85) UNION SELECT Lieferer.LiefNr, LiefName, Ort, Rabatt FROM Lieferer, ArtVorTab WHERE ArtVorTab.LiefNr = Lieferer.LiefNr AND Rabatt > 0.15
Gruppieren:
Die Klausel group by erlaubt das Bilden von Aggregaten auf Teilmengen. Zum Verständnis ein Beispiel:
Zwei Tabellen zum Speichern von Rechnungen und Rechnungsposten:
create table Rechnung (
Id integer,
Kunde VARCHAR (10),
Bearbeiter VARCHAR (10)
);
create table Posten (
Id integer,
Rechnung integer,
Menge integer,
Betrag Decimal(5,2),
Artikel VARCHAR (10)
);
insert into Rechnung (Id,Bearbeiter) values(1,'Huber');
insert into Posten (Id,Rechnung,Menge,Betrag,Artikel) values(1, 1, 1, 30.33, 'Tastatur');
insert into Posten (Id,Rechnung,Menge,Betrag,Artikel) values(2, 1, 1, 5, 'Maus');
insert into Posten (Id,Rechnung,Menge,Betrag,Artikel) values(3, 2, 1, 800, 'Computer');
insert into Rechnung (Id,Bearbeiter) values(2,'Huber');
insert into Posten (Id,Rechnung,Menge,Betrag,Artikel) values(4, 2, 1, 800, 'Computer');
insert into Rechnung (Id,Bearbeiter) values(3,'Meyer');
insert into Posten (Id,Rechnung,Menge,Betrag,Artikel) values(5, 3, 1, 30.33, 'Tastatur');
insert into Posten (Id,Rechnung,Menge,Betrag,Artikel) values(6, 3, 1, 5, 'Maus');
insert into Posten (Id,Rechnung,Menge,Betrag,Artikel) values(7, 3, 1, 800, 'Computer');
insert into Rechnung (Id,Bearbeiter) values(4,'Huber');
insert into Posten (Id,Rechnung,Menge,Betrag,Artikel) values(8, 4, 1, 30.33, 'Tastatur');Schauen wir uns die Datensätze an:
select * from Rechnung ID KUNDE BEARBEITER ==== =========== =========== 1 <null> Huber 2 <null> Huber 3 <null> Meyer 4 <null> Huber select * from Posten ID RECHNUNG MENGE BETRAG ARTIKEL ==== ======== ===== ====== =========== 1 1 1 30,33 Tastatur 2 1 1 5 Maus 3 2 1 800 Computer 4 2 1 800 Computer 5 3 1 30,33 Tastatur 6 3 1 5 Maus 7 3 1 800 Computer 8 4 1 30,33 Tastatur
Ich will die Umsätze jedes Bearbeiters sehen:
select sum(Betrag) As Umsatz, sum (Menge) As Menge, Bearbeiter from Rechnung R, Posten P where P.Rechnung=R.Id Group by Bearbeiter
Es werden die Summen über alle Datensätze gebildet, die jeweils den gleichen Eintrag bei Mitarbeiter haben:
Ausgabe:
UMSATZ MENGE BEARBEITER ======== ====== =========== 1665,66 5 Huber 835,33 3 Meyer
Jetzt will ich jetzt die Umsätze jedes Bearbeiters sehen, aufgeschlüsselt nach Artikeln:
select sum(Betrag) As Umsatz, sum (Menge) As Menge, Bearbeiter, Artikel from Rechnung R, Posten P where P.Rechnung=R.Id Group by Bearbeiter,Artikel
Jetzt erfolgt die Summenbildung in "zweistufigen" Gruppen: Je Bearbeiter werden für jeden Artikel einzeln die Summen gebildet:
Ausgabe:
UMSATZ MENGE BEARBEITER ARTIKEL
=========================== ===========
1600 2 Huber Computer
5 1 Huber Maus
60,66 2 Huber Tastatur
800 1 Meyer Computer
5 1 Meyer Maus
30,33 1 Meyer Tastatur