⚙️ T-SQL Grundkurs – Teil 3
Fortgeschrittene Abfragen · Aggregationen · Gespeicherte Prozeduren · Benutzerdefinierte Funktionen
GROUP BY · HAVING · Stored Procedures · Scalar & Table-Valued Functions
📌
Grundlagen: Tabellen Kurse & Teilnehmer
Dieser Teil baut auf den bekannten Tabellen Kurse und Teilnehmer aus Teil 1 und 2 auf.
Wir nehmen an, dass bereits einige Daten vorhanden sind. Falls du die Struktur neu anlegen möchtest, findest du den Code in Teil 1.
✅ Voraussetzung: Grundkenntnisse in SELECT, INSERT, UPDATE und einfachen JOINs (wie in Teil 2 behandelt).
1
Aggregatfunktionen – SUM, AVG, COUNT, MIN, MAX
Aggregatfunktionen fassen mehrere Zeilen zu einem einzigen Wert zusammen. Sie werden oft mit GROUP BY verwendet.
-- Anzahl Kurse, Durchschnittspreis, teuerster Preis
SELECT
COUNT(*) AS AnzahlKurse,
AVG(Preis) AS Durchschnittspreis,
MAX(Preis) AS MaxPreis,
MIN(Preis) AS MinPreis
FROM Kurse;
-- Gesamtsumme aller Teilnehmer (einfach nur Zählen)
SELECT COUNT(TeilnehmerID) AS TeilnehmerGesamt FROM Teilnehmer;
💡 Wichtige Funktionen: SUM() (Summe), AVG() (Durchschnitt),
COUNT() (Anzahl), MIN()/MAX().
COUNT(*) zählt alle Zeilen, COUNT(Spalte) ignoriert NULL-Werte.
2
Daten gruppieren mit GROUP BY & HAVING
GROUP BY teilt Zeilen nach gleichen Werten in Gruppen ein. Danach kannst du Aggregatfunktionen pro Gruppe anwenden.
HAVING filtert Gruppen (ähnlich wie WHERE, aber für Gruppen).
-- Wie viele Teilnehmer pro Kurs? (INNER JOIN mit Kurse)
SELECT
k.Titel,
COUNT(t.TeilnehmerID) AS TeilnehmerAnzahl
FROM Kurse k
LEFT JOIN Teilnehmer t ON k.KursID = t.KursID
GROUP BY k.Titel
ORDER BY TeilnehmerAnzahl DESC;
-- Nur Kurse mit mehr als 1 Teilnehmer (HAVING)
SELECT
k.Titel,
COUNT(t.TeilnehmerID) AS Anzahl
FROM Kurse k
LEFT JOIN Teilnehmer t ON k.KursID = t.KursID
GROUP BY k.Titel
HAVING COUNT(t.TeilnehmerID) > 1;
-- Durchschnittspreis pro DauerTage (Gruppierung nach Dauer)
SELECT DauerTage, AVG(Preis) AS DurchschnittsPreis
FROM Kurse
GROUP BY DauerTage
HAVING AVG(Preis) > 250;
🧠 Merke: WHERE filtert Zeilen vor der Gruppierung, HAVING filtert nach der Gruppierung.
3
Unterabfragen – Abfragen in Abfragen
Eine Unterabfrage (Subquery) ist eine SELECT-Anweisung innerhalb einer anderen SQL-Anweisung. Sie kann in WHERE,
FROM oder SELECT verwendet werden.
📌 Skalarer Vergleich
-- Alle Kurse, die teurer sind als der Durchschnitt
SELECT Titel, Preis
FROM Kurse
WHERE Preis > (SELECT AVG(Preis) FROM Kurse);
🔍 IN mit Unterabfrage
-- Teilnehmer, die in Kursen mit Dauer > 2 Tagen sind
SELECT Vorname, Nachname
FROM Teilnehmer
WHERE KursID IN (
SELECT KursID FROM Kurse WHERE DauerTage > 2
);
-- Unterabfrage im SELECT (korreliert: pro Teilnehmer den Kursnamen)
SELECT
Vorname,
Nachname,
(SELECT Titel FROM Kurse k WHERE k.KursID = t.KursID) AS KursTitel
FROM Teilnehmer t;
4
Gespeicherte Prozeduren – Wiederkehrende Logik speichern
Eine gespeicherte Prozedur ist ein vorcompilierter SQL-Code, der auf dem Server gespeichert wird.
Sie kann Parameter entgegennehmen, komplexe Logik ausführen und Ergebnisse zurückliefern. Ideal für wiederkehrende Aufgaben.
-- Beispiel: Prozedur, die alle Kurse oberhalb eines Mindestpreises anzeigt
CREATE PROCEDURE sp_GetTeureKurse
@MinPreis DECIMAL(8,2)
AS
BEGIN
SELECT KursID, Titel, Preis
FROM Kurse
WHERE Preis >= @MinPreis
ORDER BY Preis DESC;
END;
GO
-- Ausführen der Prozedur
EXEC sp_GetTeureKurse @MinPreis = 250;
-- Prozedur mit mehreren Schritten: Teilnehmer für einen Kurs zählen
CREATE PROCEDURE sp_CountTeilnehmerInKurs
@KursID INT,
@Anzahl INT OUTPUT
AS
BEGIN
SELECT @Anzahl = COUNT(*)
FROM Teilnehmer
WHERE KursID = @KursID;
END;
GO
-- Aufruf mit Output-Parameter
DECLARE @TeilnehmerCount INT;
EXEC sp_CountTeilnehmerInKurs @KursID = 1, @Anzahl = @TeilnehmerCount OUTPUT;
SELECT @TeilnehmerCount AS TeilnehmerImKurs;
🚀 Vorteile: Wiederverwendbarkeit, Sicherheit (kein direktes SQL-Injection-Risiko bei Parametrisierung),
reduzierte Netzwerklast.
5
Benutzerdefinierte Funktionen – Skalar & Tabellenwert
Funktionen ähneln Prozeduren, aber sie geben einen Wert (skalar) oder eine Tabelle zurück und können in SELECT-Abfragen verwendet werden.
Es gibt skalare Funktionen (z.B. Formatierung, Berechnung) und Tabellenwertfunktionen (geben eine Tabelle zurück).
📐 Skalare Funktion
-- Funktion zur Berechnung der Mehrwertsteuer (19%)
CREATE FUNCTION fn_BerechneMwSt (@Preis DECIMAL(10,2))
RETURNS DECIMAL(10,2)
AS
BEGIN
RETURN @Preis * 0.19;
END;
GO
-- Verwendung im SELECT
SELECT Titel, Preis,
dbo.fn_BerechneMwSt(Preis) AS MwStBetrag
FROM Kurse;
📊 Inline-Tabellenwertfunktion
-- Gibt eine Tabelle mit Teilnehmern eines bestimmten Kurses zurück
CREATE FUNCTION fn_TeilnehmerImKurs (@KursID INT)
RETURNS TABLE
AS
RETURN
(
SELECT TeilnehmerID, Vorname, Nachname, Email
FROM Teilnehmer
WHERE KursID = @KursID
);
GO
-- Aufruf wie eine Tabelle
SELECT * FROM fn_TeilnehmerImKurs(2);
⚡ Wichtig: Skalare Funktionen können die Performance bei großen Datenmengen beeinträchtigen.
Tabellenwertfunktionen (inline) sind oft effizienter. Funktionen dürfen keine Seiteneffekte haben (kein INSERT/UPDATE/DELETE).
-- Multistatement-Tabellenwertfunktion (komplexer)
CREATE FUNCTION fn_KurseMitTeilnehmeranzahl()
RETURNS @KursStatistik TABLE (
KursID INT,
Titel NVARCHAR(100),
TeilnehmerCount INT
)
AS
BEGIN
INSERT INTO @KursStatistik
SELECT k.KursID, k.Titel, COUNT(t.TeilnehmerID)
FROM Kurse k
LEFT JOIN Teilnehmer t ON k.KursID = t.KursID
GROUP BY k.KursID, k.Titel;
RETURN;
END;
GO
SELECT * FROM fn_KurseMitTeilnehmeranzahl() WHERE TeilnehmerCount > 0;
🎯
Anwendungsfall: Dynamische Kursauswertung
Wir erstellen eine gespeicherte Prozedur, die eine skalare Funktion nutzt, und geben eine gefilterte Ausgabe zurück.
-- Skalare Funktion: Rabatt (10% wenn Dauer > 2 Tage)
CREATE FUNCTION fn_BerechneRabatt (@Dauer INT, @Preis DECIMAL(10,2))
RETURNS DECIMAL(10,2)
AS
BEGIN
DECLARE @Rabatt DECIMAL(10,2) = 0;
IF @Dauer > 2
SET @Rabatt = @Preis * 0.10;
RETURN @Rabatt;
END;
GO
-- Prozedur: Zeigt Kurse mit Rabattbetrag
CREATE PROCEDURE sp_KursRabatte
AS
BEGIN
SELECT
KursID,
Titel,
DauerTage,
Preis,
dbo.fn_BerechneRabatt(DauerTage, Preis) AS Rabatt,
Preis - dbo.fn_BerechneRabatt(DauerTage, Preis) AS PreisNachRabatt
FROM Kurse
ORDER BY Preis DESC;
END;
GO
EXEC sp_KursRabatte;
📖
Gegenüberstellung: Prozeduren vs. Funktionen
| Merkmal | Gespeicherte Prozedur | Benutzerdefinierte Funktion |
|---|---|---|
| Rückgabetyp | Kein erzwingender Rückgabetyp (kann über Output-Parameter) | Muss einen Wert oder eine Tabelle zurückgeben |
| Verwendung in SELECT | Nicht direkt in FROM oder WHERE möglich (nur mit EXEC) | Ja, skalare Funktionen in SELECT, Tabellenfunktionen in FROM |
| Seiteneffekte (INSERT/UPDATE) | Erlaubt | Nicht erlaubt (nur lesend) |
| Parameter | Input und Output möglich | Nur Input-Parameter |
💡 Faustregel: Verwende Prozeduren für Datenänderungen und komplexe Geschäftslogik, Funktionen für Berechnungen oder wiederkehrende Abfragen,
die in SELECT integriert werden sollen.
✍️
Übungen zum Selbermachen
Versuche, die folgenden Aufgaben mit T-SQL zu lösen (basierend auf Kurse & Teilnehmer):
- 1. Schreibe eine Abfrage, die die Anzahl der Teilnehmer pro Kurs nur für Kurse mit mindestens 2 Teilnehmern anzeigt (GROUP BY + HAVING).
- 2. Erstelle eine skalare Funktion fn_FormatKursname, die einen Titel entgegennimmt und in Großbuchstaben zurückgibt.
- 3. Entwickle eine gespeicherte Prozedur sp_TeilnehmerVerschieben, die einen Teilnehmer von einem Kurs in einen anderen verschiebt (Parameter: @TeilnehmerID, @NeueKursID).
- 4. Schreibe eine Tabellenwertfunktion, die alle Kurse mit einem Preis über einem Schwellwert zurückgibt.
-- Lösungsvorschläge (zum Ausklappen):
-- 1.
SELECT k.Titel, COUNT(t.TeilnehmerID) AS Anzahl
FROM Kurse k LEFT JOIN Teilnehmer t ON k.KursID = t.KursID
GROUP BY k.Titel
HAVING COUNT(t.TeilnehmerID) >= 2;
-- 2.
CREATE FUNCTION fn_FormatKursname (@Titel NVARCHAR(100))
RETURNS NVARCHAR(100)
AS BEGIN RETURN UPPER(@Titel); END;
-- 3.
CREATE PROCEDURE sp_TeilnehmerVerschieben @TeilnehmerID INT, @NeueKursID INT
AS
BEGIN
UPDATE Teilnehmer SET KursID = @NeueKursID WHERE TeilnehmerID = @TeilnehmerID;
END;
-- 4.
CREATE FUNCTION fn_KurseAbPreis (@MinPreis DECIMAL(10,2))
RETURNS TABLE
AS RETURN (SELECT * FROM Kurse WHERE Preis >= @MinPreis);
🎉 Herzlichen Glückwunsch! Du hast nun die wichtigsten Konzepte für fortgeschrittene T-SQL-Abfragen,
gespeicherte Prozeduren und Funktionen kennengelernt. Mit diesen Werkzeugen kannst du komplexe Datenbanklogik
umsetzen und Abfragen optimieren. Im nächsten Schritt empfehlen wir Themen wie Trigger, Transaktionen und Fehlerbehandlung.