SQL-Übungsaufgaben

SQL-Übungsaufgaben zu JOINs, Aggregation, Unterabfragen und Transaktionen

https://bildung.social/@oerinformatik/112633708392604185

https://oer-informatik.de/sql-uebungsaufgaben

tl/dr; (ca. 60 min Bearbeitungszeit): Am Beispiel einer Rechnungsdatenbank sollen SQL-Befehle mit Unterabfragen, Aggregationen, Joins und Transaktionen erstellt werden. Die Lösungen und Erläuterungen können eingeblendet werden. (Zuletzt geändert am 17.06.2024)

Übungsaufgabe Rechnungsdatenbank:

Gegeben ist das folgende Datenmodell:

1a) Selektion und Projektion (8 Punkte)

Erstelle eine Abfrage, die alle RechnungsNr des Kunden mit dem Namen “Fantasie GmbH” ausgibt.

Beispielausgabe:

SELECT K.Name AS Name, R.RechnungsNr AS RechnungsNr //2P
FROM Rechnung AS R                                  //1P
LEFT JOIN Kunde AS K ON R.KundenNr = K.KundenNr     //3P
WHERE K.Name LIKE “Fantasie GmbH“                   //2P

1b) Aggregieren (8 Punkte)

Erstelle eine Abfrage, die alle RechnungsNr ausgibt sowie, falls vorhanden, die jeweiligen Rechnungssummen (die Summe der zugehörigen GesamtpreisNetto, ohne Berücksichtigung der Rabatte).

Beispielausgabe:

SELECT RechnungsNr,                                 //1P
       SUM(GesamtpreisNetto) AS Rechnungsnumme      //3P
FROM Rechnungsposition                              //2P
GROUP BY RechnungsNr                                //2P

SELECT R.RechnungsNr AS RechnungsNr,                                 //1P
       SUM(RP.GesamtpreisNetto) AS Rechnungsnumme                    //2P
FROM Rechnung AS R                                                   //1P
LEFT JOIN Rechnungsposition AS RP ON R.RechnungsNr = RP.RechnungsNr  //2P
GROUP BY R.RechnungsNr                                               //2P

1c) Bedingtes Aggregieren (8 Punkte)

Erstellen Sie eine Abfrage, die Rechnungsnummer und Anzahl der zugehörigen Rechnungspositionen aller Rechnungen ausgibt, die mehr als 20 Rechnungspositionen haben. Die Liste soll nach Anzahl der Positionen absteigend sortiert sein.

Beispielausgabe:

SELECT RechnungsNr,                                    //1P
       COUNT(RP.RechnungsPositionNr) AS AnzahlPosition //2P
FROM Rechnungsposition                                 //1P
GROUP BY RechnungsNr                                   //1P
HAVING COUNT(RechnungsPositionNr)>20                   //2P
ORDER BY AnzahlPosition  DESC                          //1P

SELECT R.RechnungsNr AS RechnungsNr,                                 //0.5P
       COUNT(RP.RechnungsPositionNr) AS AnzahlPosition               //1P
FROM Rechnung AS R                                                   //0.5P
LEFT JOIN Rechnungsposition AS RP ON R.RechnungsNr = RP.RechnungsNr  //1P
GROUP BY R.RechnungsNr                                               //2P
HAVING COUNT(RP.RechnungsPositionNr)>20                              //2P
ORDER BY AnzahlPosition DESC                                         //1P

1d) Abfragen aus mehreren Tabellen (12 Punkte)

Erstelle eine Abfrage, die Kundennummern und Namen aller Kunden ausgibt, die Rechnungen mit mehr als 20 Rechnungspositionen haben. Alle Kunden sollen darin höchstens einmal vorkommen.

Beispielausgabe:

SELECT K2.KundenNr AS Kundenummer, K.Name AS Name                        //1P
FROM Kunde AS K2                                                         //1P
WHERE K2.KundenNr IN (                                                   //2P
   SELECT R.KundenNr,                                                    //1P
   FROM Rechnung AS R                                                    //1P
   INNER JOIN Rechnungsposition AS RP ON R.RechnungsNr = RP.RechnungsNr  //2P
   GROUP BY R.RechnungsNr, R.KundenNr                                    //2P
   HAVING COUNT(RP.RechnungsPositionNr)>20)                              //2P

SELECT DISTINCT                                                       //2P
     K.KundenNr AS Kundenummer, K.Name AS Name                        //1P
FROM Rechnung AS R                                                    //1P
INNER JOIN Rechnungsposition AS RP ON R.RechnungsNr = RP.RechnungsNr  //2P
INNER JOIN Kunde AS K ON K.KundenNr = R.KundenNr                      //2P
GROUP BY R.RechnungsNr, K.KundenNr, K.Name                            //2P
HAVING COUNT(RP.RechnungsPositionNr)>20)                              //2P

2a) Unterabfragen mit “vor jeder…” (8 Punkte)

Erstelle eine Abfrage, die alle Rechnungsnummern ausgibt, deren Rechnung (nach numerischen Rechnungsnummern sortiert) vor jeder Rechnung der Fantasie GmbH liegt.

Beispielausgabe:

SELECT R.RechnungsNr AS RechnungsNr,                //1P
FROM Rechnung AS R                                  //1P
WHERE R.RechnungsNr < ALL(                          //2P
SELECT R1.RechnungsNr AS RechnungsNr                //1P
FROM Rechnung AS R1                                 //1P
LEFT JOIN Kunde AS K ON R1.KundenNr = K.KundenNr    //1P
WHERE K.Name LIKE "Fantasie GmbH")                  //1P

SELECT R.RechnungsNr AS RechnungsNr,                //0.5P
FROM Rechnung AS R                                  //0.5P
WHERE R.Rechnungsdatum < ALL(                       //2P
SELECT R1.Rechnungsdatum AS Rechnungsdatum          //1P
FROM Rechnung AS R1                                 //1P
LEFT JOIN Kunde AS K ON R1.KundenNr = K.KundenNr    //0.5P
WHERE K.Name LIKE "Fantasie GmbH")                  //0.5P
ORDER BY RechnungsNr                                //0.5P

2b) Unterabfragen mit “vor einer…” (8 Punkte)

Erstelle eine Abfrage, die Rechnungsnummern ausgibt, deren Rechnung (nach numerischen Rechnungsnummern sortiert) vor irgendeiner Rechnung der Fantasie GmbH liegt.

SELECT R.RechnungsNr AS RechnungsNr,                //1P
FROM Rechnung AS R                                  //1P
WHERE R.RechnungsNr < ANY(                          //2P
SELECT R1.RechnungsNr AS RechnungsNr                //1P
FROM Rechnung AS R1                                 //1P
LEFT JOIN Kunde AS K ON R1.KundenNr = K.KundenNr    //1P
WHERE K.Name LIKE “Fantasie GmbH“)                  //1P

SELECT R.RechnungsNr AS RechnungsNr,                //0.5P
FROM Rechnung AS R                                  //0.5P
WHERE R.Rechnungsdatum < ANY(                       //2P
SELECT R1.Rechnungsdatum AS Rechnungsdatum          //1P
FROM Rechnung AS R1                                 //1P
LEFT JOIN Kunde AS K ON R1.KundenNr = K.KundenNr    //0.5P
WHERE K.Name LIKE “Fantasie GmbH“)                  //0.5P
ORDER BY RechnungsNr                                //0.5P

3a) Transaktionen starten (8 Punkte)

Es soll in einem sicheren Transaktions-Umfeld Veränderungen an der Datenbank vorgenommen werden, die sich noch nicht unmittelbar auswirken.

Erstelle alle Befehle, die nötig sind, um eine Transaktion zu starten und die (vorhandene) Rechnung „1234“ dem (vorhandenen) Kunden mit den KundenNr „234“ zuzuweisen.

START TRANSAKTION;
UPDATE TABLE Rechnung SET KundenNr = 234 WHERE RechnungsNr = 1234;

3b) Transaktionen abbrechen (2 Punkte)

Mit welchem Befehl werden die Befehle aus Aufgabe a nicht zur Anwendung gebracht und die Transaktion abgebrochen?

ROLLBACK;

3c) Transaktionen abschließen (2 Punkte)

Mit welchem Befehl werden die Befehle aus Aufgabe a persistent ausgeführt und die Transaktion abgeschlossen?

COMMIT;

Hinweis zur Nachnutzung als Open Educational Resource (OER)

Dieser Artikel und seine Texte, Bilder, Grafiken, Code und sonstiger Inhalt sind - sofern nicht anders angegeben - lizenziert unter CC BY 4.0. Nennung gemäß TULLU-Regel bitte wie folgt: “SQL-Übungsaufgaben zu JOINs, Aggregation, Unterabfragen und Transaktionen” von H. Stein (oer-informatik.de), Lizenz: CC BY 4.0. Der Artikel wurde unter https://oer-informatik.de/sql-uebungsaufgaben veröffentlicht, die Quelltexte sind in weiterverarbeitbarer Form verfügbar im Repository unter https://gitlab.com/oer-informatik/db-sql/dml-select-queries. Stand: 17.06.2024.

[Kommentare zum Artikel lesen, schreiben] / [Artikel teilen] / [gitlab-Issue zum Artikel schreiben]