SQL-Praxis für Einsteiger: AdventureWorks-Übungen

Verbessern Sie Ihre SQL-Kenntnisse mit praktischen Übungen unter Verwendung der AdventureWorks-Beispieldatenbank. Üben Sie wesentliche SQL-Funktionen anhand realer Szenarien.

Das Üben von SQL ist wichtig, wenn Sie die Anwendung von SQL verbessern wollen. Dieser Artikel enthält 20 anfängerfreundliche Übungen mit der AdventureWorks-Beispieldatenbank von Microsoft. Diese Datenbank wurde entwickelt, um zu zeigen, wie SQL Server funktioniert. Sie stellt ein fiktives Fahrradherstellerunternehmen namens AdventureWorks Cycles dar und enthält fünf Schemata: HumanResources, Person, Produktion, Einkauf und Verkauf. Dadurch eignet sie sich hervorragend zum Erlernen und Üben von SQL.

Die AdventureWorks-Datenbank deckt verschiedene Geschäftsszenarien ab, z. B. Produktion, Vertrieb, Einkauf, Produktmanagement, Kontaktmanagement und Personalwesen. Dies macht sie zu einer hervorragenden Ressource für das Erlernen und Üben von SQL, da sie ein breites Spektrum an Daten und Geschäftsprozessen bietet, mit denen Sie arbeiten können. Indem Sie mit dieser Datenbank üben, können Sie praktische Erfahrungen mit realen Daten und Szenarien sammeln; dies wird Ihnen helfen, effiziente Abfragen zu schreiben und Datenprobleme effektiv zu lösen.

Wenn Sie andere Beispieldatenbanken ausprobieren möchten, sehen Sie sich unsere Liste der interessantesten Datenquellen für SQL-Übungen an. Das Einrichten Ihrer eigenen SQL-Übungsumgebung kann jedoch einige Zeit in Anspruch nehmen. Wenn Sie schnell üben möchten, probieren Sie unseren SQL-Praxis Track oder unsere SQL-Übungsdatenbanken aus. Außerdem veröffentlichen wir jeden Monat einen kostenlosen SQL-Kurs, damit Sie weiter lernen können.

Beginnen wir nun mit dem Üben von SQL mit AdventureWorks-Übungen!

Überprüfung der AdventureWorks-Datenbank

Die AdventureWorks-Datenbank umfasst fünf Datenbankschemata (d. h. Datenbankstrukturen, die zur Gruppierung von Datentabellen verwendet werden), darunter HumanResources, Person, Production, Purchasing und Sales. In den folgenden Übungen werden wir das Schema HumanResources verwenden.

Beginnen wir mit der Analyse des Schemas:

Betrachten wir zunächst die Konzepte von Primär- und Fremdschlüsseln:

• Der Primärschlüssel (PK) ist eine Spalte (oder eine Gruppe von Spalten), die jede Zeile in einer Tabelle eindeutig identifiziert. Zum Beispiel ist die Spalte BusinessEntityID der Primärschlüssel der Tabelle Employee Tabelle, da jedem Mitarbeiter eine eindeutige ID-Nummer zugewiesen ist.
• Der Fremdschlüssel (FK) ist eine Spalte (oder ein Satz von Spalten), die zwei Tabellen miteinander verbindet. Beachten Sie, dass der Fremdschlüssel einer Tabelle der Primärschlüssel einer anderen Tabelle ist - auf dieser Grundlage sind die beiden Tabellen miteinander verknüpft. Zum Beispiel ist die Spalte BusinessEntityID der Primärschlüssel der Tabelle Employee Tabelle und auch ein Fremdschlüssel in der JobCandidate Tabelle. Dies verbindet beide Tabellen miteinander und lässt die JobCandidate Tabelle auf Zeilen in der Employee

Lassen Sie uns nun die Tabellen im Schema analysieren.

Die Tabelle Employee speichert Informationen über die Mitarbeiter und ist die Haupttabelle dieses Schemas. Sie ist mit den folgenden Tabellen verknüpft:

• Die Tabelle JobCandidate speichert Lebensläufe von Bewerbern. Die Spalte BusinessEntityID ist eine PK in der Tabelle Employee Tabelle und ein FK in der JobCandidate Der PK der Tabelle JobCandidate Tabelle ist die Spalte JobCandidateID.
• Die Tabelle EmployeePayHistory Tabelle speichert die Historie der Lohnsätze der Mitarbeiter. Die Spalte BusinessEntityID ist ein PK in der Tabelle Employee Tabelle und ein FK in der Tabelle EmployeePayHistory Beachten Sie, dass die Spalte BusinessEntityID in der Tabelle EmployeePayHistory Tabelle gleichzeitig ein FK und ein Teil der PK ist. Die PK der Tabelle EmployeePayHistory Tabelle umfasst sowohl die Spalten BusinessEntityID als auch RateChangeDate. Dies wird ein zusammengesetzter Primärschlüssel genannt.
• Die Tabelle EmployeeDepartmentHistory Tabelle speichert die Historie der Abteilungen der Mitarbeiter. Die Spalte BusinessEntityID ist ein PK in der Tabelle Employee Tabelle und ein FK in der EmployeeDepartmentHistory Beachten Sie, dass die Spalte BusinessEntityID in der Tabelle EmployeeDepartmentHistory Tabelle gleichzeitig ein FK und ein Teil des PK ist. Die PK der EmployeeDepartmentHistory Tabelle besteht aus den Spalten BusinessEntityID, DepartmentID, ShiftID und StartDate. Dies ist ein weiterer zusammengesetzter Primärschlüssel.

Die Tabelle EmployeeDepartmentHistory speichert Informationen über Mitarbeiter und ihre Abteilungen im Laufe der Zeit und kann als eine weitere Haupttabelle dieses Schemas betrachtet werden. Sie ist mit den folgenden Tabellen verknüpft:

• Die Tabelle Shift Tabelle speichert Informationen über verfügbare Schichten. Die Spalte ShiftID ist eine PK in der Tabelle Shift Tabelle und ein FK in der EmployeeDepartmentHistory Beachten Sie, dass die Spalte ShiftID sowohl ein FK als auch ein Teil der PK in der EmployeeDepartmentHistory Tabelle ist.
• Die Tabelle Department Tabelle speichert Informationen über Abteilungen. Die Spalte DepartmentID ist ein PK in der Tabelle Department Tabelle und ein FK in der Tabelle EmployeeDepartmentHistory Beachten Sie, dass die Spalte DepartmentID sowohl ein FK als auch ein Teil der PK in der EmployeeDepartmentHistory Tabelle ist.

Nun können wir mit unseren AdventureWorks-Übungen beginnen.

AdventureWorks-Übungen für Einsteiger

Im weiteren Verlauf dieses Artikels werden wir alle Grundlagen von SQL abdecken (und einige fortgeschrittene Funktionen ausprobieren):

• Einzelne Tabellenabfragen zum Abrufen und Filtern von Daten.
• Abfragen für mehrere Tabellen, die JOINs verwenden, um Daten aus zwei oder mehr Tabellen zu kombinieren.
• Gruppieren und Aggregieren von Daten, um mathematische Operationen mit ausgewählten Daten durchzuführen.
• Andere relevante SQL-Funktionen wie Unterabfragen, UNION und INTERSECT sowie gemeinsame Tabellenausdrücke (CTEs).

Teil 1: Abfragen für einzelne Tabellen

In diesem Teil werden wir Daten abrufen und filtern.

Hinweis: Um korrekte Abfragen zu schreiben, sollten Sie sich zunächst mit den in den Tabellen gespeicherten Daten vertraut machen.

Übung 1: Auswählen der Berufsbezeichnungen aller ledigen männlichen Angestellten

Übung: Selektieren Sie die Berufsbezeichnungen aller männlichen Angestellten, die nicht verheiratet sind.

Lösung:

SELECT JobTitle
FROM HumanResources.Employee
WHERE Gender = 'M'
AND MaritalStatus != 'M';

Erläuterung: Wir wählen die Spalte JobTitle aus der Tabelle Employee Tabelle. Hier kann die SQL-Syntax genau wie einfaches Englisch gelesen werden.

Die Filterbedingungen befinden sich in der WHERE Klausel:

• Wir wollen nur männliche Mitarbeiter auswählen, also setzen wir eine Bedingung auf die Spalte Gender: Gender = 'M'.
• Wir wollen nur Arbeitnehmer auswählen, die nicht verheiratet sind, also legen wir eine weitere Bedingung auf die Spalte MaritalStatus: MaritalStatus != 'M'.

Da beide Bedingungen gleichzeitig gelten sollen, verwenden wir den Operator AND, um sie zu kombinieren.

Übung 2: Mitarbeiter auswählen, deren Lohnsatz 50 oder mehr beträgt

Übung: Wählen Sie die Spalten BusinessEntityID, Rate und RateChangeDate für alle Mitarbeiter aus, deren Lohnsatz jemals 50 oder mehr betrug.

Lösung:

SELECT BusinessEntityID, Rate, RateChangeDate
FROM HumanResources.EmployeePayHistory
WHERE Rate >= 50;

Erläuterung: Wir selektieren drei Spalten - BusinessEntityID, Rate und RateChangeDate - aus der EmployeePayHistory Tabelle.

Dann geben wir eine Filterbedingung in der Klausel WHERE ein, um nur diejenigen abzurufen, deren Lohnsatz jemals größer oder gleich 50 war: Rate >= 50.

Übung 3: Wählen Sie alle Mitarbeiter aus, die im Jahr 2008 in neue Abteilungen eingetreten sind.

Übung: Wählen Sie die BusinessEntityID, DepartmentID und StartDate für jeden Mitarbeiter aus, der im Jahr 2008 in einer beliebigen Abteilung angefangen hat zu arbeiten.

Lösung:

SELECT BusinessEntityID, DepartmentID, StartDate
FROM HumanResources.EmployeeDepartmentHistory
WHERE StartDate BETWEEN '2008-01-01' AND '2008-12-31';

Erläuterung: Wir wählen die IDs der Mitarbeiter (BusinessEntityID), die IDs der Abteilungen (DepartmentID) und das Datum, an dem der Mitarbeiter in diese Abteilung eingetreten ist (StartDate), aus der EmployeeDepartmentHistory Tabelle.

Da wir nur die Mitarbeiter auflisten wollen, die im Jahr 2008 in neue Abteilungen eingetreten sind, setzen wir eine Bedingung in die Spalte StartDate: StartDate BETWEEN '2008-01-01' AND '2008-12-31'.

Diese Bedingung ist selbsterklärend, denn sie ist in einfachem Englisch verständlich. Der Wert StartDate muss zwischen dem 1.1.2008 und dem 31.12.2008 liegen, damit wir alle Tage des Jahres 2008 abdecken.

Übung 4: Abteilungen auswählen, deren Namen dem Muster entsprechen

Übung: Wählen Sie die Abteilungs-IDs, Namen und Gruppennamen für alle Abteilungen aus, deren Name mit "Prod" beginnt ODER für alle Abteilungen, deren Gruppenname mit "ring" endet.

Lösung:

SELECT DepartmentID, Name, GroupName
FROM HumanResources.Department
WHERE Name LIKE 'Prod%'
OR GroupName LIKE '%ring';

Erläuterung: Wir wählen die Spalten DepartmentID, Name, und GroupName aus der Department Tabelle.

Dann geben wir die Bedingungen der WHERE Klausel an:

• Wir wollen die Abteilungen auswählen, deren Name mit "Prod" beginnt, also geben wir eine Bedingung für die Spalte Name ein: Name LIKE 'Prod%'.
• Außerdem sollen Abteilungen ausgewählt werden, deren Gruppenname mit "Ring" endet, also wird eine Bedingung für die Spalte GroupName festgelegt: GroupName LIKE '%ring'.

Mit dem Schlüsselwort LIKE können wir das Muster definieren, dem der Spaltenwert entsprechen soll. Wir möchten beispielsweise, dass die Spalte Name mit "Prod" beginnt. Das Muster lautet also 'Prod%'; % und steht für eine beliebige Zeichenfolge.

Da wir alle Datensätze finden wollen, bei denen mindestens eine der Bedingungen erfüllt ist, verwenden wir das Schlüsselwort OR.

Übung 5: Abteilungen auswählen, die zu bestimmten Gruppen gehören

Übung: Wählen Sie Abteilungsnamen aus, die entweder zur Gruppe "Forschung und Entwicklung" oder zur Gruppe "Fertigung" gehören.

Lösung:

SELECT Name
FROM HumanResources.Department
WHERE GroupName IN ('Research and Development', 'Manufacturing');

Erläuterung: Wir wählen die Spalte Name aus der Tabelle Department Tabelle.

Da wir Abteilungen auflisten wollen, die zu definierten Gruppen gehören, verwenden wir das Schlüsselwort IN in der Bedingung der Klausel WHERE: GroupName IN ('Research and Development', 'Manufacturing').

Dies stellt sicher, dass wir alle Abteilungen ausgeben, die zu den im Schlüsselwort IN aufgeführten Gruppen gehören.

Teil 2: Abfragen für mehrere Tabellen

In diesem Teil werden wir JOINs verwenden, um Daten aus mehreren Tabellen zu kombinieren.

Hinweis: Um korrekte Abfragen zu schreiben, sollten Sie sich zunächst mit den in den jeweiligen Tabellen gespeicherten Daten vertraut machen.

Übung 6: Mitarbeiter und ihre Abteilungen auswählen

Übung: Wählen Sie die IDs der Mitarbeiter mit allen Namen der Abteilungen aus, in denen sie jemals gearbeitet haben.

Lösung:

SELECT edh.BusinessEntityID, d.Name
FROM HumanResources.EmployeeDepartmentHistory AS edh
JOIN HumanResources.Department AS d
ON edh.DepartmentID = d.DepartmentID;

Erläuterung: Wir wählen die Spalte BusinessEntityID aus der Tabelle EmployeeDepartmentHistory und die Spalte Name aus der Tabelle Department Tabelle.

Wir verbinden diese beiden Tabellen mit Hilfe der JOIN Klausel auf ihrer gemeinsamen Spalte DepartmentID.

Übung 7: Selektieren Sie die Berufsbezeichnungen der weiblichen Angestellten und das Datum des Abteilungswechsels

Übung: Wählen Sie für alle weiblichen Angestellten die Mitarbeiter-ID und die Berufsbezeichnung zusammen mit den Daten aus, an denen der Mitarbeiter die Abteilung gewechselt hat (StartDate).

Lösung:

SELECT e.BusinessEntityID, e.JobTitle, edh.StartDate
FROM HumanResources.Employee AS e
JOIN HumanResources.EmployeeDepartmentHistory AS edh
ON e.BusinessEntityID = edh.BusinessEntityID
WHERE e.Gender = 'F';

Erläuterung: Wir wählen die Spalten BusinessEntityID und JobTitle aus der Tabelle Employee Tabelle und die Spalte StartDate aus der Tabelle EmployeeDepartmentHistory Tabelle.

Wir verwenden die JOIN Klausel, um die Tabellen über ihre gemeinsame Spalte BusinessEntityID zu verbinden.

Da wir diese Informationen nur für weibliche Angestellte auflisten wollen, erzwingen wir eine Bedingung für die Spalte Gender: e.Gender = 'F'.

Übung 8: Stellenbezeichnungen pro Abteilung auswählen

Übung: Wählen Sie die Berufsbezeichnungen und die entsprechenden Abteilungsnamen aus, um alle Berufsbezeichnungen zu finden, die jemals in jeder Abteilung verwendet wurden. Schließen Sie keine Mehrfachnennungen der gleichen Berufsbezeichnung ein.

Lösung:

SELECT DISTINCT e.JobTitle, d.Name
FROM HumanResources.Employee AS e
JOIN HumanResources.EmployeeDepartmentHistory AS edh
ON e.BusinessEntityID = edh.BusinessEntityID
JOIN HumanResources.Department AS d
ON edh.DepartmentID = d.DepartmentID;

Erläuterung: Wir wählen die Spalte JobTitle aus der Tabelle Employee Tabelle und die Spalte Name aus der Department Tabelle.

Um eindeutige Stellenbezeichnungen auszuwählen (d. h. keine Duplikate), verwenden wir das Schlüsselwort DISTINCT vor den Spaltennamen.

Wir müssen die Tabelle Employee Tabelle mit der EmployeeDepartmentHistory Tabelle über ihre gemeinsame Spalte BusinessEntityID verbinden. Dann verknüpfen wir die EmployeeDepartmentHistoryTabelle mit der Department Tabelle über ihre gemeinsame Spalte, DepartmentID.

Übung 9: Mitarbeiter mit ihren Abteilungen und Schichten auswählen

Übung: Wählen Sie eindeutige Abteilungsnamen und Schichtnamen (keine doppelten Paare) aus, die Mitarbeiter in jeder Abteilung arbeiten. Benennen Sie den Abteilungsnamen in DepartmentName und den Schichtnamen in ShiftName um.

Lösung:

SELECT DISTINCT d.Name AS DepartmentName, s.Name AS ShiftName
FROM HumanResources.EmployeeDepartmentHistory AS edh
JOIN HumanResources.Department AS d
ON edh.DepartmentID = d.DepartmentID
JOIN HumanResources.Shift AS s
ON edh.ShiftID = s.ShiftID;

Erläuterung: Wir wählen die Spalte Name aus der Tabelle Department Tabelle und die Spalte Name aus der Tabelle Shift Tabelle aus und geben ihnen mit dem Schlüsselwort AS Aliasnamen. Wir verwenden DISTINCT, um eindeutige Paare auszuwählen.

Um Abteilungs- und Schichtnamen auszuwählen, müssen wir diese drei Tabellen verbinden:

• Wir verknüpfen die EmployeeDepartmentHistory Tabelle mit der Department Tabelle auf der DepartmentID
• Wir verknüpfen die EmployeeDepartmentHistory Tabelle mit der Shift Tabelle auf der ShiftID

Jede der JOIN -Klauseln hat eine ON -Klausel, die die gemeinsamen Spalten definiert, über die der Join ausgeführt wird.

Übung 10: Auswahl von Mitarbeitern, die nach 2010 eingestellt wurden, mit ihren Abteilungen und Schichten

Übung: Wählen Sie die IDs, Abteilungsnamen und Schichtnamen der Mitarbeiter aus. Berücksichtigen Sie nur Mitarbeiter, die nach dem 01.01.2010 eingestellt wurden und für Abteilungen innerhalb der Gruppen Fertigung und Qualitätssicherung arbeiten.

Lösung:

SELECT e.BusinessEntityID, d.Name, s.Name
FROM HumanResources.Employee AS e
JOIN HumanResources.EmployeeDepartmentHistory AS edh
ON e.BusinessEntityID = edh.BusinessEntityID
JOIN HumanResources.Department AS d
ON edh.DepartmentID = d.DepartmentID
JOIN HumanResources.Shift AS s
ON edh.ShiftID = s.ShiftID
WHERE e.HireDate > '2010-01-01'
AND d.GroupName IN ('Manufacturing', 'Quality Assurance');

Erläuterung: Wir wählen die Spalte BusinessEntityID aus der Tabelle Employees Tabelle, die Spalte Name Spalte aus der Tabelle Department und die Name Spalte aus der Tabelle Shift.

Um die Mitarbeiter-IDs zusammen mit den Abteilungs- und Schichtnamen auszuwählen, müssen wir drei Joins verwenden:

• Wir verknüpfen die Employee Tabelle mit der EmployeeDepartmentHistory Tabelle über die Spalte BusinessEntityID.
• Wir verknüpfen die EmployeeDepartmentHistory Tabelle mit der Department Tabelle in der Spalte DepartmentID
• Wir verbinden die EmployeeDepartmentHistory Tabelle mit der Shift Tabelle über die Spalte ShiftID.

Dann geben wir die Bedingungen der WHERE -Klausel wie folgt ein:

• Wir möchten alle Mitarbeiter auflisten, die nach dem 1. Januar 2010 eingestellt wurden, also erlegen wir der Spalte HireDate eine Bedingung auf: HireDate > '2010-01-01'.
• Wir möchten nur die Mitarbeiter auflisten, die zu bestimmten Abteilungsgruppen gehören, also verwenden wir das Schlüsselwort IN, um diese Bedingung zu erstellen: GroupName IN ('Manufacturing', 'Quality Assurance').

Teil 3: Gruppieren und Aggregieren von Daten

In diesem Teil werden wir Daten gruppieren und aggregieren, damit wir mathematische Operationen mit ausgewählten Daten durchführen können.

Hinweis: Um korrekte Abfragen zu schreiben, sollten Sie sich zunächst mit den in den entsprechenden Tabellen gespeicherten Daten vertraut machen.

Übung 11: Wählen Sie die höchsten und niedrigsten Krankenstandsstunden aus.

Übung: Ermitteln Sie die niedrigste und die höchste Anzahl von Krankheitsstunden, die von den Mitarbeitern in Anspruch genommen wurden.

Lösung:

SELECT MIN(SickLeaveHours) AS MinSickLeaveHours,
       MAX(SickLeaveHours) AS MaxSickLeaveHours
FROM HumanResources.Employee;

Erläuterung: Wir verwenden die Aggregatfunktionen MIN() und MAX(), wobei wir die Spalte SickLeaveHours aus der Tabelle Employee um die minimale und maximale Anzahl der von den Arbeitnehmern genommenen Krankheitsstunden zu ermitteln.

Aufgabe 12: Ermitteln Sie die durchschnittliche Anzahl der Urlaubsstunden pro Stellenbezeichnung

Übung: Wählen Sie die Berufsbezeichnungen und die durchschnittliche Anzahl der Urlaubsstunden pro Berufsbezeichnung aus.

Lösung:

SELECT JobTitle, AVG(VacationHours) AS AvgVacationHours
FROM HumanResources.Employee
GROUP BY JobTitle;

Erläuterung: Wir wählen die Spalte JobTitle aus der Employee Tabelle.

Wir verwenden die Aggregatfunktion AVG(), um die durchschnittliche Anzahl der Urlaubsstunden für jede Stellenbezeichnung zu ermitteln. Wir möchten Datengruppen auf der Grundlage der unterschiedlichen Werte in der Spalte JobTitle haben; dazu müssen wir die Klausel GROUP BY mit der Spalte JobTitle als Argument verwenden.

Übung 13: Wählen Sie die Anzahl der Mitarbeiter nach ihrem Geschlecht aus.

Übung: Wählen Sie das Geschlecht der Mitarbeiter und die Anzahl der Mitarbeiter jedes Geschlechts aus.

Lösung:

SELECT Gender, COUNT(*) AS Count
FROM HumanResources.Employee
GROUP BY Gender;

Erläuterung: Wir wählen die Spalte Gender aus der Tabelle Employee Tabelle.

Wir verwenden die Aggregatfunktion COUNT(), um die Anzahl der Mitarbeiter für jedes Geschlecht zu ermitteln. Dazu müssen wir die Klausel GROUP BY mit der Spalte Gender als Argument verwenden; wir möchten Datengruppen auf der Grundlage der unterschiedlichen Werte in der Spalte Gender haben.

Übung 14: Wählen Sie die Anzahl der Abteilungen in jeder Gruppe aus.

Übung: Ermitteln Sie die Anzahl der Abteilungen in jeder Abteilungsgruppe. Geben Sie nur die Namen der Abteilungsgruppen an, die mehr als zwei Abteilungen enthalten.

Lösung:

SELECT GroupName, COUNT(*) AS DepartmentsCount
FROM HumanResources.Department
GROUP BY GroupName
HAVING COUNT(*) > 2;

Erläuterung: Wir wählen die Spalte GroupName aus der Tabelle Abteilung aus.

Wir verwenden die Aggregatfunktion COUNT(), um die Anzahl der Abteilungen in jeder Abteilungsgruppe zu ermitteln. Dazu müssen wir die Klausel GROUP BY mit der Spalte GroupName als Argument verwenden.

Um der Aggregatfunktion eine Bedingung aufzuerlegen, verwenden wir die Klausel HAVING nach der Klausel GROUP BY: HAVING COUNT(*) > 2.

Übung 15: Wählen Sie die Summe der Krankheitsstunden für jede Abteilung aus.

Übung: Selektieren Sie die Namen der Abteilungen und die Summe der Krankheitsstunden der Mitarbeiter, die derzeit in jeder Abteilung arbeiten. Benennen Sie diese Spalte in SumSickLeaveHours um.

Lösung:

SELECT d.Name, SUM(e.SickLeaveHours) AS SumSickLeaveHours
FROM HumanResources.Employee AS e
JOIN HumanResources.EmployeeDepartmentHistory AS edh
ON e.BusinessEntityID = edh.BusinessEntityID
JOIN HumanResources.Department AS d
ON edh.DepartmentID = d.DepartmentID
WHERE edh.EndDate IS NULL
GROUP BY d.Name;

Erläuterung: Wir wählen die Spalte Name aus der Tabelle Department.

Wir verwenden die Aggregatfunktion SUM(), um die Summe der von den Mitarbeitern verbrauchten Krankheitsstunden für jede Abteilung zu ermitteln. Dazu müssen wir die GROUP BY Klausel mit der Spalte Name aus der Tabelle Department Tabelle als Argument zu verwenden.

Wir müssen die Employee Tabelle mit der EmployeeDepartmentHistory Tabelle über die Spalte BusinessEntityID verbinden. Dann verknüpfen wir die EmployeeDepartmentHistoryTabelle mit der Department Tabelle über die Spalte DepartmentID.

Da wir nur die Mitarbeiter berücksichtigen, die derzeit in einer beliebigen Abteilung arbeiten, legen wir die Bedingung fest, dass die Spalte EndDate der Tabelle EmployeeDepartmentHistory Tabelle NULL sein muss.

Teil 4: Andere SQL-Funktionen

In diesem Teil werden wir Unterabfragen (d. h. Abfragen, die in Abfragen verschachtelt sind), Operatoren wie UNION und INTERSECT und allgemeine Tabellenausdrücke (CTEs) behandeln.

Hinweis: Um korrekte Abfragen zu schreiben, sollten Sie sich zunächst mit den in den entsprechenden Tabellen gespeicherten Daten vertraut machen.

Übung 16: Auswählen von Mitarbeitern mit ihrem aktuellen Lohnsatz

Übung: Selektieren Sie die IDs der Mitarbeiter und ihre aktuellen Gehaltssätze.

Lösung:

SELECT e.BusinessEntityID, e.Rate AS CurrentPayRate
FROM HumanResources.EmployeePayHistory AS e
WHERE e.RateChangeDate = (
			SELECT MAX(e2.RateChangeDate)
			FROM HumanResources.EmployeePayHistory AS e2
			WHERE e2.BusinessEntityID = e.BusinessEntityID
		);

Erläuterung: Wir selektieren die Spalten BusinessEntityID und Tarif aus der EmployeePayHistory Tabelle.

Da die EmployeePayHistory Tabelle die Historie der Gehaltssätze für jeden Angestellten speichert, müssen wir eine Bedingung an den Wert der Spalte RateChangeDate knüpfen, damit dieser gleich dem letzten Datum der Gehaltsänderung eines Angestellten ist. Dazu wird in der Klausel WHERE eine Unterabfrage definiert, die das jüngste Datum auswählt, an dem der Lohnsatz geändert wurde. Diese Unterabfrage verwendet die Aggregatfunktion MAX(), um das jüngste Datum auszuwählen.

Wir wählen das jüngste RateChangeDate für jeden Mitarbeiter separat aus, also fügen wir eine WHERE Klausel in die Unterabfrage ein, um die Mitarbeiter-IDs zwischen äußerer und innerer Abfrage abzugleichen.

Übung 17: Auswahl des Mindest-, Durchschnitts- und Höchstlohns

Übung: Wählen Sie den Mindest-, Durchschnitts- und Höchstlohn aus den aktuellen Lohntarifen der Mitarbeiter aus.

Lösung:

SELECT MIN(CurrentPayRate) AS MinPayRate,
       AVG(CurrentPayRate) AS AvgPayRate,
       MAX(CurrentPayRate) AS MaxPayRate
FROM (
		SELECT e.BusinessEntityID, e.Rate AS CurrentPayRate
		FROM HumanResources.EmployeePayHistory AS e
		WHERE e.RateChangeDate = (
				SELECT MAX(e2.RateChangeDate)
				FROM HumanResources.EmployeePayHistory AS e2
				WHERE e2.BusinessEntityID = e.BusinessEntityID
			)
) AS cpr;

Erläuterung: Wir verwenden die Aggregatfunktionen MIN(), AVG() und MAX(), um den Mindest-, Durchschnitts- und Höchstlohn auszuwählen, wobei wir die Spalte CurrentPayRate aus der Unterabfrage als Argument angeben.

Wir verwenden die Unterabfrage in der FROM Klausel. Diese Unterabfrage wählt die aktuellen Gehaltssätze für jeden Mitarbeiter aus, wie in Übung 16 erläutert.

Beachten Sie, dass diese Lösung zwei Unterabfragen verschachtelt. Zunächst verwenden wir eine Unterabfrage in der FROM -Klausel. Und dann verwendet diese Unterabfrage eine Unterabfrage in ihrer eigenen WHERE Klausel.

Übung 18: Auswählen von Mitarbeiter-IDs mit deren Abwesenheitszeiten

Übung: Wählen Sie alle Mitarbeiter-IDs aus, die mehr als 60 Stunden Urlaub oder mehr als 60 Stunden Krankenstand genommen haben.

Lösung:

SELECT BusinessEntityID
FROM HumanResources.Employee
GROUP BY BusinessEntityID
HAVING SUM(VacationHours) > 60

UNION

SELECT BusinessEntityID
FROM HumanResources.Employee
GROUP BY BusinessEntityID
HAVING SUM(SickLeaveHours) > 60;

Erläuterung: Die erste Abfrage wählt die Spalte BusinessEntityID aus der Tabelle Employee und verwendet die Aggregatfunktion SUM(), um die Anzahl der von jedem Mitarbeiter genommenen Urlaubsstunden zu summieren. Wir gruppieren nach der Mitarbeiter-ID und legen eine Bedingung fest, dass die Summe größer als 60 sein muss.

Analog dazu wählt die zweite Abfrage die Spalte BusinessEntityID aus der Tabelle Employee aus und verwendet die Aggregatfunktion SUM(), um die Anzahl der von den einzelnen Mitarbeitern genommenen Krankheitsstunden zu summieren. Wir gruppieren nach der Mitarbeiter-ID und legen eine Bedingung fest, dass die Summe größer als 60 sein muss.

Der Operator UNION kombiniert die Ausgabe beider Abfragen.

Übung 19: Auswählen von Mitarbeiter-IDs mit bestimmten Berufsbezeichnungen und Abteilungen

Übung: Wählen Sie die IDs von Mitarbeitern aus, die die Berufsbezeichnungen "Vertriebsmitarbeiter" oder "Werkzeugdesigner" tragen und in den Abteilungen Vertrieb oder Marketing gearbeitet haben (oder arbeiten).

Lösung:

SELECT e.BusinessEntityID
FROM HumanResources.Employee AS e
WHERE e.JobTitle IN ('Sales Representative', 'Tool Designer')

INTERSECT

SELECT edh.BusinessEntityID
FROM HumanResources.EmployeeDepartmentHistory AS edh
JOIN HumanResources.Department AS d
ON edh.DepartmentID = d.DepartmentID
WHERE d.Name IN ('Sales', 'Marketing');

Erläuterung: Die erste Abfrage wählt die Spalte BusinessEntityID aus der Tabelle Employee Tabelle. Sie filtert die Spalte JobTitle so, dass sie nur Vertriebsmitarbeiter und Werkzeugkonstrukteure enthält.

Die zweite Abfrage wählt die Spalte BusinessEntityID aus der Tabelle EmployeeDepartmentHistory Tabelle aus. Sie filtert den Abteilungsnamen so, dass nur die Abteilungen Vertrieb und Marketing eingeschlossen sind.

Der Operator INTERSECT findet die gemeinsame Ausgabe der beiden Abfragen, d. h. er gibt nur die IDs der Mitarbeiter aus, die die Bedingungen der Klausel WHERE beider Abfragen erfüllen.

Übung 20: Auflisten von Vertriebsmitarbeitern und Marketingleitern mit ihren Abteilungen

Übung: Wählen Sie die IDs, Stellenbezeichnungen und Abteilungsnamen für Mitarbeiter aus, die mit den Rollen "Vertriebsmitarbeiter" oder "Marketingleiter" verbunden sind.

Lösung:

SELECT e.BusinessEntityID, e.JobTitle, d.Name AS DepartmentName
FROM HumanResources.Employee AS e
JOIN HumanResources.EmployeeDepartmentHistory AS edh 
ON e.BusinessEntityID = edh.BusinessEntityID
JOIN HumanResources.Department AS d
ON edh.DepartmentID = d.DepartmentID
WHERE e.JobTitle IN ('Sales Representative', 'Marketing Manager');

Erläuterung: Die Abfrage verwendet einen JOIN zur Verknüpfung der Employee Tabelle mit der EmployeeDepartmentHistory Tabelle auf der Grundlage der Spalte BusinessEntityID. Diese Verknüpfung stellt sicher, dass der Datensatz jedes Mitarbeiters mit seiner Abteilungshistorie verbunden ist.

Eine weitere JOIN verknüpft die EmployeeDepartmentHistory Tabelle mit der DepartmentDepartmentID Dies ermöglicht es der Abfrage, Abteilungsinformationen abzurufen, die sich auf den beruflichen Werdegang des Mitarbeiters beziehen.

Die WHERE Klausel filtert die Ergebnisse so, dass sie nur die Mitarbeiter enthalten, deren Jobtitel entweder "Vertriebsmitarbeiter" oder "Marketingleiter" lautet. Diese Filterung erfolgt mit dem Operator IN, der die gewünschten Berufsbezeichnungen angibt. Die Struktur der Abfrage kombiniert und filtert effizient Daten aus mehreren Tabellen, um die relevanten Stellenbezeichnungen der Mitarbeiter und die zugehörigen Abteilungen zurückzugeben.

Üben Sie SQL mit AdventureWorks-Übungen!

In diesem Artikel haben Sie sich anhand von Übungen mit der AdventureWorks-Datenbank mit den Grundlagen von SQL vertraut gemacht. Sie haben gelernt, wie Sie einzelne Tabellenabfragen mit SELECT durchführen, Tabellendaten mit JOIN kombinieren, Daten mit GROUP BY gruppieren und Aggregatfunktionen wie COUNT(), AVG(), SUM(), MIN() und MAX() verwenden. Außerdem haben Sie sich mit Unterabfragen, UNION und INTERSECT befasst.

Weitere Übungen mit Lösungen und detaillierten Erklärungen finden Sie in unseren Artikeln 10 Anfängerübungen SQL-Praxis mit Lösungen und SQL Joins: 12 Übungsfragen mit detaillierten Antworten. Vertiefen Sie die SQL-Übungsmöglichkeiten mit unserem umfassenden Leitfaden zu SQL-Praxis auf LearnSQL.com. Und wie bereits erwähnt, können Sie Ihr Training auch mit unserem SQL-Praxis -Track, den SQL-Übungsdatenbanken oder dem monatlichen kostenlosen SQL-Kurs fortsetzen.

Viel Erfolg auf Ihrer SQL-Reise!