Beispiele und Übungen mit der Datenbank R
Beispiele und Übungsaufgaben mit der Übungsdatenbank R
Einführung in die Tabellen der Übungsdatenbank R
Die Tabelle PERSON enthält Angestellte.
Die in dieser Lektion verwendeten Personennamen und Personendaten sind frei erfunden (fiktiv), Ähnlichkeiten mit natürlichen Personen sind nicht beabsichtigt.
ABTEILUNG Entitätstabelle mit Eigenschafte einer Abteilung (->Abteilung, Name, Ort, Leiter(->Person/Person))
KURS Entitätstabelle mit Eigenschaften eines Kurses (->Kurs, Name, Beginn)
PERSON Entitätstabelle mit Eigenschaften eines Beschäftigten (->Person, Nachname, Vorname, Geschlecht, Geburtsdatum, Adresse, Rufnummer, Abteilung(->Abteilung/Abteilun))
WERDEGANG Entitätstabelle mit Eigenschaften einer Werdegangstufe eines Beschäftigten (Person(->Person/Person), Position, Anfang, Ende, Gehalt), Semantik von NULL in Spalte Ende ist zu kritisieren!, kein Primärschlüssel, Ergänzungstabelle zu Person
.----------------. .--------------.
| PERSON | | WERDEGANG |
|----------------| |--------------|
.---->- PERSON <----------x------ - PERSON |
| | NACHNAME | | - POSITION |
| | VORNAME | | ANFANG |
| | GESCHLECHT | | ENDE |
.-----------------. | | GEBURTSDATUM | | GEHALT |
| ABTEILUNG | | | ADRESSE | '--------------'
|-----------------| | | RUFNUMMER |
| - ABTEILUNG <------------ ABTEILUNG |
| NAME | | '----------------'
| ORT | |
| LEITER ----------'
'-----------------'
BESUCH Kursbesuch ist eine Beziehungstabelle, n:m-Relation (Person(->Person/Person), Kurs(->Kurs/Kurs)), Person <- Besuch -> Kurse
Natural Join Natural Join
Person.Person = Besuch.Person Besuch.Kurs = Kurs.Kurs
.--------------. .--------------.
| Person | | Besuch | .--------------.
|--------------| |--------------| | Kurs |
| Person <---------x------- Person | |--------------|
| Nachname | | Kurs -------------x-----> Kurs |
| Vorname | | | | Name |
| Geschlecht | '--------------' | Beginn |
| Geburtsdatum | | |
| Adresse | '--------------'
| Rufnummer |
| Abteilung -> |
| |
'--------------'
Beispiel zu korrelierter Unterabfrage
- Für jede Person Mittelwert des Gehalts (korrelierte Unterabfrage wird für jede Zeile wiederholt)(ungetestet)
select p.Nachname,( select avg( gehalt ) from werdegang where person = p.person ) from person p;
Beispiel zu GROUP_CONCAT
SELECT
NAME AS KURS,
GROUP_CONCAT(
CONCAT( VORNAME, ' ', NACHNAME )
ORDER BY NACHNAME, VORNAME
SEPARATOR ', ' )
AS BESUCHERFROM
BESUCH
NATURAL JOIN
PERSON
NATURAL JOIN
KURSGROUP BY NAME;
/ Übungsaufgabe
Es sollen alle Angestellten aufgelistet werden, die vor 1980 geboren wurden. (Man kann hierzu davon ausgehen, daß diese Datenbank nur Angestellte enthält, aber keine anderen Personen.)
/ Übungsaufgabe
Es sollen alle Angestellten aufgelistet werden, die in den 80er Jahren geboren wurden.
/ Übungsaufgabe
Jemand sucht einen Beschäftigten, aber er kann sich nur noch daran erinnern, daß dessen Nachname kein »e« enthält.
Ermitteln Sie die Nachnamen aller Beschäftigten, deren Nachname kein »e« enthält.
/ Übungsaufgabe
Wie viele Beschäftigte haben einen Vornamen, der mit »M« oder »A« beginnt?
/ Übungsaufgabe
Ermitteln Sie alle vorkommenden Tätigkeitsbezeichnungen (ohne mehrfache Nennung derselben Tätigkeit).
/ Übungsaufgabe
Ermitteln Sie alle vorkommenden Gehälter (ohne mehrfache Nennung desselben Gehalts).
/ Übungsaufgabe
Ermitteln Sie die Anzahl der Arbeitsverhältnisse, welche der Angestellte 22 hatte oder hat.
/ Übungsaufgabe
Es sollen die Nachnamen und Rufnummern aller Beschäftigten, absteigend nach der Rufnummer sortiert, ausgegeben werden.
/ Übungsaufgabe
Zeigen Sie aus der Tabelle »WERDEGANG« alle Zeilen, die mit Analyse zu tun haben (d.h., in deren Bezeichnung Bezug auf Analyse genommen wird), mit ihrer Positionsbezeichnung und ihrem Gehalt an.
/ Übungsaufgabe
Erstellen Sie eine Tabelle mit der Kursnummer und Personennummer aller Kursbesuche (Tabelle »BESUCH«) der Personen Nummer 7 und 14.
/ Übungsaufgabe
Ermitteln Sie das Durchschnittsgehalt der Buchhalter.
/ Übungsaufgabe
Wie hoch ist das derzeit niedrigste Gehalt?
/ Übungsaufgabe
Für diese Aufgabe soll angenommen werden, daß wir im Jahre 2025 leben!
Lassen Sie die Datenbank die Anzahl der Personen, welche laut der Tabelle »WERDEGANG« im Jahre 2025 als »analytischer Programmierer« tätig sind, zählen.
Dabei sollen also die Einträge nicht mitgezählt werden, welche zu schon beendeten Tätigkeiten gehören.
(Diesen Interpretation eines Nullwertes in der Spalte »ENDE« ist nicht vorbildlich, da aus dem Vorkommen eines Nullwertes keine Schlüsse zulässig sind, jedoch kommen solche Aufgabenstellungen doch manchmal vor. Besser wäre ein separates Feld, welches aussagt, ob eine Position zum Zeitpunkt der Gültigkeit der Datenbank noch andauert.)
/ Übungsaufgabe
(Stellen Sie sich für diese Aufgabe vor, daß wir im Jahre 2025 leben.) Listen Sie alle Beschäftigen mit Vor- und Nachnamen auf, die im Jahre 2018 einen Kurs besucht haben.
/ Übungsaufgabe
Listen Sie die Namen aller Beschäftigten auf, die einen Kurs besucht haben, der mit Buchhaltung zu tun hat.
/ Übungsaufgabe
Listen Sie die Namen aller Beschäftigten auf, die noch nie einen Kurs besucht haben (unter der Annahme, daß alle Kursbesuche in der Tabelle BESUCHE erfaßt wurden).
/ Übungsaufgabe
Zählen Sie die Anzahl unterschiedlicher Beschäftigter (es soll also nicht ein Beschäftigter doppelt gezählt werden, auch wenn er mehrere Kurse besucht hat), die jemals irgendeinen Kurs besucht haben.
/ Übungsaufgabe
Basierend auf der Tabelle »POSITION«: zu jeder Position Anfangs- und Enddatum untereinandersetzen.
1 BUCHHALTER ANFANG 2013-04-12
1 BUCHHALTER ENDE 2013-04-12
7 HILFSBUCHHALTER ANFANG 2013-04-12
8 HILFSBUCHHALTER ENDE 2013-04-12
/ Übungsaufgabe (praktisch wie vorhergehende)
Erstellen Sie aus der Werdegangtabelle eine abgeleitete Tabelle (Abfrage oder View), welche zu jeder Position eines Mitarbeiters Anfangs- und Enddatum untereinander auflistet.
(Die Daten der folgenden Tabelle stimmen nicht mit den realen Daten überein, sie ist nur beispielhaft zu verstehen.)
Name Position Eintrag Datum
Christian Zylka Buchhalter Anfangsdatum 2012-10-01
Christian Zylka Buchhalter Enddatum 2013-10-01
Christian Zylka Oberbuchhalter Anfangsdatum 2013-10-01
Christian Zylka Oberbuchhalter Enddatum NULL
... ... ... ...
/ Übungsaufgabe Transitionen
Erstellen Sie eine Tabelle in der alle bisher vorgekommenen Paare aufeinanderfolgender Positionen einer Person aufgelistet werden (z.B. vom Hilfsbuchhalter zum Buchhalter).
Dabei soll jeder in dem Unternehmen mögliche „Karriereschritt“ (z.B. vom Hilfsbuchhalter zum Buchhalter) nur einmal angezeigt wird. Andere Spalten als die Positionen brauchen im Ergebnis nicht angezeigt zu werden.