Structured Query Language

Aufgabe 1

Sie sollen SQL-Abfragen für folgende Zeiterfassungsdatenbank erstellen.



Hinweis: Jahresübergreifender Urlaub generiert zwei Datensätze.

a) Für den Mitarbeiter Ulrich Schmidt wurde in der Tabelle Fehlzeit ein Datensatz falsch erfasst: Statt einer zweitägigen "Dienstreise" vom 30.06. bis 01.07.2022 wurde versehentlich ein eintägiger "Urlaub" für den 30.06.2022 eingetragen (siehe Tabelle Fehlzeit).
Erstellen Sie eine SQL-Anweisung, mit der die Korrektur durchgefuhrt werden kann.

b) Erstellen Sie eine SQL-Abfrage, welche die Urlaubstage aller Mitarbeiter im Jahr 2021 ermittelt.

Beispielausgabe:

811 Müller Jens 15
812 Scholz Birgit 10
815 Schmidt Ulrich 0
817 Storck Hans 0
841 Ullmann Franz 21

c) Die bestehende Datenbank soll wie im Folgenden beschrieben verändert werden.
Erstellen Sie dazu jeweils die SQL-Anweisung.

ca) Die Tabelle Fehlzeit soll gelöscht werden.

cb) Es soll eine Tabelle Fehlzeitgrund mit folgenden Feldern erstellt werden.

Grund_ID Grund
1 Urlaub
2 Krank
3 Dienstreise

Hinweis: Es müssen keine Datensätze eingefügt werden.

cc) Die Tabelle Fehlzeit soll in der dargestellten Form neu erstellt werden.In die Tabelle Fehlzeit sollen in der Spalte Grund_lD nur solche Werte eingetragen werden können, die in der Tabelle Fehlzeitgrund als Primärschlüssel vorkommen.

FZ_ID MA_ID Von_Datum Bis_Datum Grund_ID Fehltage
1 811 18.10.2021 22.10.2021 1 5
2 902 18.04.2021 05.05.2021 2 16

Aufgabe 2

Ein gemeinnütziger Verein vermittelt Dienstleistungen von freiwilligen Helfern (hier die Mitglieder) an Hilfe suchende Bürger. Informationen zu Mitgliedern, ihren jeweiligen Angeboten und Bewertungen, stehen in folgender Datenbank zur Verfügung:



Formulieren Sie SQL-Abfragen für die nachstehenden Aufgabenstellungen.

a) Geben Sie alle Attribute des jüngsten Mitglieds aus.

b) Ermitteln Sie eine Mitgliederliste aufsteigend sortiert nach der durchschnittliche Bewertung für die Leistungsart "Kinderbetreuung".

idMitglied mitgliedName Durchschnitt
3 Müller 2.1
2 Maier 3.0
25 Spielmann 4.5
...



c) Erstellen Sie eine Angebotsliste, die alle Mitglieder und die entsprechende Leistungsart des Angebots ausgibt, welche donnerstags von 14:00 Uhr bis 16:00 Uhr zur Verfügung stehen.

Beispielausgabe:

idMitglied mitgliedName artBezeichnung wochentag vonZeit bisZeit
4 Hauser Gartenarbeit Donnerstag 14:00 16:00
4 Hauser Hausarbeit Donnerstag 14:00 16:00
2 Maier Kinderbetreuung Donnerstag 14:00 16:00


d) Erstellen Sie eine neue Tabelle MitgliedArchiv. Transferieren Sie alle Mitglieder, die kein Angebot eingestellt haben, in diese Tabelle. Löschen Sie diese inaktiven Mitglieder aus der Tabelle Mitglied.

Aufgabe 3


Gegeben sei folgendes Datenbankmodell eines Stromversorgers zur Speicherung von Kunden- und Verbrauchsdaten:


Zur Abfrage und Pflege der Daten sollen die folgenden SQL-Anweisungen erstellt werden:

a) Indizieren Sie in der Tabelle Zaehlerstand die Spalte ZST_Datum. Der Index soll die Bezeichnung Idx_Datum bekommen.

b) Löschen Sie alle Haushalte, bei denen kein Zähler hinterlegt wurde.

c) Erstellen Sie eine SQL-Abfrage, mit der Sie alle Haushalte mit der Anzahl der vorhandenen Zähler erhalten. Haushalte mit den wenigsten Zählern sollen unten aufgeführt werden.
Hinweis: Beachten Sie die Spaltenbezeichnungen.

Beispiel-Ergebnistabelle:

Nachname AnzZaehler
Meier 2
Hinz 2
Müller 1
Conrad 0


d) Erstellen Sie eine SQL-Abfrage, mit der Sie die Haushalte mit den vorhandenen Zählern und dem jeweiligen Verbrauch zwischen den beiden Ablesungen in den Jahren 2021 und 2022 erhalten. Die Ergebnisse sollen nach Nachnamen sortiert werden. Es findet pro Jahr nur eine Eintragung statt.
Hinweis: Beachten Sie die Spaltenbezeichnungen.

Beispiel-Ergebnistabelle:

Nachname Zaehlernummer Verbrauch
Hinz 0037889999 2701
Hinz 0037890053 8974
Meier 8885688977 2745
Meier 1666856771 2034
Müller 5658897888 NULL

Aufgabe 4

Als Mitarbeiter der AMAG Soft GmbH sind Sie mit dem Management der Datenbank NotaufnahmeManagement der FIT betraut.
Die nachfolgenden Tasks sollen in diesem Rahmen von Ihnen abgearbeitet werden.
Die Tabellenauszüge stehen für die Bearbeitung der Tasks zur Verfügung.

Erstellen Sie die SQL-Anweisungen für folgende Teilaufgaben:

a) Fügen Sie einen neuen Arzt mit den Angaben „Horner, Hugo, Allgemeinmedizin“ in die Tabelle Aerzte hinzu.

b) Dem bereits existierenden Benutzer hhorner sollen Schreibrechte auf die Tabelle Verschreibungen der Datenbank NotaufnahmeManagement gewährt werden.

c) Dem Benutzer sklinkel sollen die Schreibrechte für die Tabelle Verschreibungen der Datenbank NotaufnahmeManagement entzogen werden.

d) In der bereits existierenden Tabelle Aerzte soll die Spalte Email jetzt zum Pflichtfeld werden. Die Tabelle ist bereits mit Datensätzen gefüllt. Eventuell fehlt bei manchen Ärzten der Email-Eintrag, für diese Fälle soll die E-Mail-Adresse „info@fit.de“ eingetragen werden.
Schreiben Sie die notwendigen Anweisungen, um bei Bedarf die E-Mail-Adresse zu ergänzen und die Spalte Email zum Pflichtfeld zu machen.

e) Erstellen Sie eine Abfrage, die PID, Nachname, Vorname und die Anzahl_Verschreibungen von Patienten, die mindestens drei Verschreibungen erhalten haben, absteigend sortiert nach der Anzahl_Verschreibungen liefert.

Ergebnisbeispiel:
PID Nachname Vorname Anzahl_Verschreibungen
1 Keller Paul 4
2 Fischer Julia 4
6 Richter Nina 3


f) Lassen Sie den Durchschnitt der Anzahl der Verschreibungen pro Patienten berechnen.

Ergebnisbeispiel:
Durchschnitt_Verschreibungen_pro_Patient
2.0