📖DQL - Komplexere Abfragen

⚡️ Ausprobieren

Öffne diese Beispiel-Datenbank in einem neuen Fenster: https://www.w3schools.com/sql/trysqlserver.asp?filename=trysql_func_sqlserver_concat

Hier kannst du direkt im Browser SQL Statements ausprobieren. Probiere die Beispiele hier mit einem ⚡️-Symbol aus.

Aggregatfunktionen

Mit Aggregatfunktionen werden Werte über mehrere Datenzeilen hinweg zusammengefasst (Multiple-Row-Funktionen). NULL-Werte werden dabei ignoriert. Ohne Gruppierung: Alle Ergebniszeilen werden zu einem Wert zusammengefasst. Mit Gruppierung: Ein Ergebniswert pro Gruppe.

Funktion	Beschreibung
SUM()	Summe
MIN()	Kleinster Wert
MAX()	Grösster Wert
COUNT()	Anzahl (die nicht NULL sind)
AVG()	Durchschnittswert
STDEV()	Standardabweichung
VAR()	Varianz

-- Kleinster, grösster und durchschnittlicher Wert einer Spalte
SELECT AVG(spalte1) AS Durchschnitt,
       MIN(spalte1) AS Minimum,
       MAX(spalte1) AS Maximum
FROM tabelle;

-- Anzahl Werte einer Spalte
SELECT COUNT(*) AS Anzahl
FROM tabelle;


-- Anzahl Werte einer Spalte (inkl. NULL-Werte)
SELECT COUNT(ISNULL(spalte1,'leer')) AS Anzahl
FROM tabelle;

⚡️ Ausprobieren

Aufgabe

1. Schreibe ein SQL-Statement, welches den Durchschnittspreis, den kleinsten und grössten Preis aller Produkte anzeigt.

2. Schreibe ein SQL-Statement, welches die Anzahl der Produkte zurück gibt, die teurer als 40$ sind.

Lösung

-- 1. Kleinster, groesster und durchschnittlicher Preis
SELECT AVG(Price) Durchschnitt,
       MIN(Price) Minimum,
       MAX(Price) Maximum
FROM Products;

-- 2. Anzahl Produkte, die teurer als 40$ sind
SELECT COUNT(Price) Anzahl
FROM Products
WHERE Price > 40;

Gruppierung

• Gruppenbildung nach einer oder mehreren Spalten

• Für jede Ausprägung wird eine Gruppe und damit eine Ergebniszeile gebildet

• Jede Spalte ohne Aggregatfunktion in der SELECT-Klausel muss in der GROUP BY-Klausel enthalten sein

-- Kleinster, grösster und durchschnittlicher Wert einer Spalte pro Gruppe
SELECT spalte1 AS Gruppe
       AVG(spalte2) AS Durchschnitt,
       MIN(spalte2) AS Minimum,
       MAX(spalte2) AS Maximum
FROM tabelle
GROUP BY spalte1;

SELECT spalte1, spalte2
       AVG(spalte3) AS Durchschnitt,
       MIN(spalte3) AS Minimum,
       MAX(spalte3) AS Maximum
FROM tabelle
GROUP BY spalte1, spalte2;

Gruppierung einschränken

Um Gruppierte Werte einzuschränken, gibt es die HAVING Klausel. Dabei gilt zu merken:

• Die WHERE-Klausel kommt vor der Gruppierung

• HAVING schränkt nach der Gruppierung ein (Selektionskriterium auf Aggregate)

SELECT  spalte2 AS Gruppe,
        AVG(spalte1) AS Durchschnitt,
        MIN(spalte1) AS Minimum,
        MAX(spalte1) AS Maximum
FROM tabelle
WHERE spalte1 > 10
GROUP BY spalte2
HAVING AVG(spalte1) > 20;

⚡️ Ausprobieren

Aufgabe

1. Schreibe ein SQL-Statement, welches den Durchschnittspreis, den kleinsten und grössten Preis aller Produkte anzeigt, gruppiert nach Kategorie.

2. Ergänze das Statement, sodass nur noch die Gruppen angezeigt werden, bei welchen der Durchschnittspreis über 25$ liegt.

Lösung

-- 1. Kleinster, groesster und durchschnittlicher Preis pro Kategorie
SELECT AVG(Price) Durchschnitt,
       MIN(Price) Minimum,
       MAX(Price) Maximum
FROM Products
GROUP BY CategoryID;

-- 2. nur bei Durchscnitt > 25$
SELECT AVG(Price) Durchschnitt,
       MIN(Price) Minimum,
       MAX(Price) Maximum
FROM Products
GROUP BY CategoryID
HAVING AVG(Price) > 25;

Unterabfragen

Unterabfragen kommen immer dann zum Einsatz, wenn das Ergebnis einer Abfrage als Kriterium für eine andere Abfrage verwendet werden muss. Unterabfragen werden immer in runde Klammern gesetzt und stehen immer auf der rechten Seite des Vergleichsausdrucks.

SELECT spalte1, spalte2, spalte3
FROM tabelle1
WHERE spalte2 > (SELECT spalte1
                 FROM tabelle2);

Regeln für Unterabfragen

• Unterabfragen dürfen bei single-row-Vergleichsoperatoren nur eine Spalte und eine Ergebniszeile liefern (sonst kommt es zu einem Fehler)

• Bei Verwendung von IN, ANY und ALL können mehrere Ergebniszeilen geliefert werden

• Unterabfragen können auf mehreren Ebenen geschachtelt werden

• Die innere Abfrage wird immer einmal vor der äusseren Abfrage gestartet

• Ein Unterabfrage kann sich auf dieselbe Tabelle wie die Hauptabfrage oder auch auf beliebige andere Tabellen beziehen

Korrelierte Unterabfragen

Bei der korrelierten Unterabfrage wird in der Unterabfrage auf eine Spalte aus der Hauptabfrage verwiesen. Aus Performancegründen nicht immer sinnvoll (JOIN unter Umständen geeigneter).

SELECT spalte1, spalte2, spalte3
FROM tabelle1 AS a
WHERE spalte2 >	(SELECT spalte1 		
                 FROM tabelle2 AS b
                 WHERE a.spalte1 = b.spalte2); 

⚡️ Ausprobieren

Aufgabe

1. Schreibe ein SQL-Statement, welches alle Produkte ausgibt, die teurer als der Durchschnittspreis sind.

2. Alle Produkte, deren Supplier aus den USA kommt.

3. Produkte, welche total schon mehr als 350 mal bestellt wurden

Lösung

-- 1. Produkte, die überdurchschnittlich teuer sind.
SELECT * FROM Products
WHERE Price > (SELECT AVG(Price) 
               FROM Products);
               
-- 2. Produkte aus den USA
SELECT * FROM Products
WHERE SupplierID IN (SELECT SupplierID 
                     FROM Suppliers 
                     WHERE Country = 'USA')
                     
-- 3. Produkte, die schon mehr als 350 mal bestellt wurden
SELECT * 
FROM Products AS p
WHERE 350 < (SELECT SUM(Quantity) 
             FROM OrderDetails AS d 
             WHERE d.ProductID = p.ProductID);

Unterabfragen in der FROM-Klausel

JOIN mit einer Unterabfrage. Die gejointe Unterabfrage kann als virtuelle Tabelle betrachtet werden und wird auch als Inline-View bezeichnet. Wichtig ist, dass die Inline-View einen Alias-Namen erhält.

SELECT spalte1, spalte2, spalte3
FROM tabelle1
INNER JOIN (SELECT spalte1, spalte2 
            FROM tabelle2) AS tabelle3 
ON tabelle1.spalte1 = tabelle3.spalte1 
WHERE tabelle1.spalte3 < tabelle3.spalte2;

Mengen-Operationen

• UNION [ALL] Verbindet mehrere SELECT-Anweisungen zu einem Gesamtergebnis ALL: Duplikate aus den verschiedenen Anweisungen werden nicht unterdrückt.

• INTERSECT Bringt alle Zeilen, die jede der SELECT-Anweisungen zurückliefert. Dies ist die Schnittmenge.

• EXCEPT Bringt alles aus der ersten SELECT-Anweisung, das in der folgenden Anweisung nicht vorkommt. Unter Oracle MINUS.

Regeln für Mengen-Operationen

• Die erste SELECT-Anweisung gibt vor:

  • Spaltennamen

  • Reihenfolge

  • Datentypen

• Spaltenanzahl und Datentypen müssen bei allen Anweisungen übereinstimmen

• ORDER BY kommt erst nach der letzten Anweisung

• WHERE-Klausel in jeder Anweisung separat

SELECT spalte1, spalte2, spalte3
FROM tabelle1
UNION
SELECT spalte1, spalte2, spalte3
FROM tabelle2;

⚡️ Ausprobieren

Aufgabe

1. Gebe die Namen und Adressen von allen Kunden und Lieferanten in einer Tabelle aus.

2. Gebe alle Städte aus, in welchen Kunden leben, aber keine Lieferanten.

Lösung

-- 1. Namen und Adressen von Kunden und Lieferanten
SELECT CustomerName as Name, Address
FROM Customers
UNION
SELECT SupplierName as Name, Address
FROM Suppliers;

-- 2. Stadte, in welchen Kunden leben, aber keine Lieferanten
SELECT City
FROM Customers
EXCEPT
SELECT City
FROM Suppliers;