SQL Subqueries für die Datenanalyse

Einführung

In diesem Projekt lernst du, wie du Subabfragen verwendest, um relevante Informationen aus den Tabellen emp (Mitarbeiter) und dept (Abteilung) in der Personal-Datenbank abzurufen. Du wirst üben, komplexe SQL-Abfragen zu schreiben, um Daten aus mehreren Tabellen zuzugreifen und zu analysieren.

👀 Vorschau

🎯 Aufgaben

In diesem Projekt wirst du lernen:

• Wie du den MySQL-Server starten und die Personal-Datenbank importieren
• Wie du eine Subabfrage verwendest, um den Mitarbeiter mit der höchsten Gehalts zu finden
• Wie du den Anteil der Mitarbeiter in einer bestimmten Abteilung im Vergleich zur gesamten Firma berechnest
• Wie du alle Mitarbeiter abrufst, die an einem bestimmten Standort arbeiten, mithilfe einer Subabfrage
• Wie du Mitarbeiter findest, deren Gehalt den Durchschnittsgeldbetrag in ihrer Abteilung übersteigt

🏆 Errungenschaften

Nach Abschluss dieses Projekts wirst du in der Lage sein:

• Das Konzept und die Verwendung von Subabfragen in SQL zu verstehen
• Komplexe SQL-Abfragen zu schreiben, die Daten aus mehreren Tabellen kombinieren
• Daten aus einer Datenbank mithilfe von Subabfragen zu analysieren und sinnvolle Erkenntnisse zu extrahieren
• Deine SQL-Fähigkeiten in einem praktischen, realen Szenario zu demonstrieren

Starte MySQL und importiere die Datenbank

In diesem Schritt lernst du, wie du den MySQL-Server startest und die Personal-Datenbank importierst.

1. Starte den MySQL-Server mit dem Befehl sudo ohne Passwort:

sudo service mysql start

2. Greife auf die MySQL-Eingabeaufforderung zu:

sudo mysql

3. Importiere die Daten aus der Datei personnel.sql in die MySQL-Datenbank:

SOURCE /home/labex/project/personnel.sql;

Dadurch wird die Datenbank personnel erstellt und mit den erforderlichen Tabellen und Daten befüllt.

Abfrage des Mitarbeiters mit der höchsten Gehalts

In diesem Schritt lernst du, wie du eine Subabfrage verwendest, um den Namen des Mitarbeiters mit der höchsten Gehalts zu erhalten.

1. Erstelle eine neue Datei namens subquery.sql im Verzeichnis /home/labex/project.
2. Füge in der Datei subquery.sql die folgende SQL-Abfrage hinzu:

-- Abfrage, um den/die Mitarbeiter*in* mit der höchsten Gehalts zu erhalten
SELECT ename AS `Name` FROM emp WHERE sal = (SELECT MAX(sal) FROM emp);

Diese Abfrage findet zunächst das maximale Gehalt in der Tabelle emp mithilfe einer Subabfrage und wählt dann den/die ename (Mitarbeiterinnamen) der Mitarbeiter*innen aus, die dieses maximale Gehalt haben.

Berechne den Anteil der Mitarbeiter in Abteilung 10

In diesem Schritt lernst du, wie du eine Subabfrage verwendest, um den Anteil der Mitarbeiter in Abteilung 10 im Vergleich zur gesamten Firma zu berechnen.

1. Füge in der Datei subquery.sql die folgende SQL-Abfrage hinzu:

-- Abfrage, um den Anteil der Mitarbeiter in Abteilung 10 zu berechnen
SELECT d.dname AS Abteilung, COUNT(e.empno) / (SELECT COUNT(empno) FROM emp) AS Anteil
FROM emp e
JOIN dept d ON e.deptno = d.deptno
WHERE d.deptno = 10;

Diese Abfrage zählt zunächst die Anzahl der Mitarbeiter in Abteilung 10, indem sie die Tabellen emp und dept verknüpft. Anschließend dividiert sie diese Anzahl durch die Gesamtzahl der Mitarbeiter in der Tabelle emp (mithilfe einer Subabfrage), um den Anteil zu berechnen.

Abrufen von Mitarbeitern, die in New York arbeiten

In diesem Schritt lernst du, wie du eine Subabfrage verwendest, um alle Mitarbeiter abzurufen, die am Standort New York arbeiten.

1. Füge in der Datei subquery.sql die folgende SQL-Abfrage hinzu:

-- Abfrage, um alle Mitarbeiter abzurufen, die in New York arbeiten
SELECT e.*
FROM emp e
JOIN dept d ON e.deptno = d.deptno
WHERE d.loc = 'NEW YORK';

Diese Abfrage verknüpft die Tabellen emp und dept, um die Mitarbeiter abzurufen, deren Abteilung sich in New York befindet.

Abrufen von Mitarbeitern mit Gehältern, die über dem Durchschnitt der Abteilung liegen

In diesem Schritt lernst du, wie du eine Subabfrage verwendest, um Mitarbeiter abzurufen, deren Gehalt höher als der Durchschnittsgeldbetrag in ihrer jeweiligen Abteilung ist.

1. Füge in der Datei subquery.sql die folgende SQL-Abfrage hinzu:

-- Abfrage, um Mitarbeiter abzurufen, deren Gehalt höher als der Durchschnittsgeldbetrag in ihrer jeweiligen Abteilung ist
SELECT e.* FROM emp e
JOIN (SELECT deptno, AVG(sal) AS avg_sal FROM emp GROUP BY deptno) AS avg
ON e.deptno = avg.deptno
WHERE e.sal > avg.avg_sal;

Diese Abfrage berechnet zunächst den Durchschnittsgeldbetrag für jede Abteilung mithilfe einer Subabfrage. Anschließend verknüpft sie diese Subabfrage mit der emp-Tabelle, um die Mitarbeiter auszuwählen, deren Gehalt höher als der Durchschnittsgeldbetrag in ihrer Abteilung ist.

Nach Abschluss dieser Schritte sollte deine Datei subquery.sql alle erforderlichen SQL-Abfragen enthalten, um die Projektanforderungen zu erfüllen.

2. Speichere die Datei.
3. Im MySQL-Prompt führe den folgenden Befehl aus, um das Skript subquery.sql auszuführen:

SOURCE /home/labex/project/subquery.sql;

Du solltest die folgende Ausgabe sehen:

Zusammenfassung

Herzlichen Glückwunsch! Du hast dieses Projekt abgeschlossen. Du kannst in LabEx weitere Übungen absolvieren, um deine Fähigkeiten zu verbessern.