Übungsaufgaben SQL-DDL/DML

SQL-Übungsaufgaben (zur “Let’s Meet”-Datenbank)

https://oer-informatik.de/lets_meet-beispielloesung_uebungsaufgaben

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

tl/dr; Übungsaufgaben zu SQL-DDL- und DML-Befehlen am Beispiel der “Let’s Meet”-Datenbank. Da deren Daten als SQL-Importskript vorliegen, können die Aufgaben auch unmittelbar im DBMS ausprobiert werden… (Zuletzt geändert am 13.06.2023)

Ausgangslage

Ausgangsbasis der folgenden Aufgaben ist die Datenbank einer Dating/Meeting-App, die anhand des folgenden Relationenmodells erstellt wurde. Es sollen bei den Lösungen die hier abgedruckten Namen verwendet werden:

Die Fragen basieren auf der Datenbank aus der “Let’s Meet” Datenbank-Lernsituation, können aber auch ohne Hintergrundinfos nur mit obigem Relationenmodell bearbeitet werden.

Wer die Abfragen mit einer gefüllten Datenbank ausprobieren will, der kann die folgenden SQL-Skripte importieren. Es müssen alle drei SQL-Skripte der Reihe nach ausgeführt werden.

Für Hintergrundwissen lohnt sich ein Blick in die anderen Beiträge, in denen die Datenbank modelliert (Teil 1), importiert (Teil 2) und im Bestand normalisiert (Teil 3) wird.

Aufgaben

Erstellen der Tabelle tblHobby2User

1. Erstelle den SQL-Befehl, mit dem die Tabelle tblHobby2User wie oben angegeben erzeugt werden kann. Die IDs sind in tblHobby und tblUser als fortlaufende Nummern realisiert. Die Priorität soll als Gleitkommazahl umgesetzt werden. Es muss keine Lösch- und Änderungsweitergabe berücksichtigt werden. Antwort ein/ausblenden

CREATE TABLE tblHobby2User ( /* 1P */
 user_id INTEGER, /* 0,5P */
 hobby_id INTEGER, /* 0,5P */
 priority DOUBLE, /* 1P */
 PRIMARY KEY (hobby_id, user_id), /* 1P */
 FOREIGN KEY (hobby_id) REFERENCES letsMeetMigrationScheme.tblHobby(hobby_id), /*1P*/
 FOREIGN KEY (user_id) REFERENCES letsMeetMigrationScheme.tblUser(user_id) /*1P*/
);

2. Welche Anpassung muss am Befehl aus Aufgabe a) vorgenommen werden, wenn das Feld priority eingabepflichtig sein soll? (Wenn es oben bereits eingabepflichtig umgesetzt wurde: welcher Bestandteil des SQL-Befehls ist dafür verantwortlich?) Antwort ein/ausblenden

priority DOUBLE NOT NULL, //1P

Ausgabe von Userdaten

In der Tabelle tblUser sind beispielhaft folgende Daten enthalten (Auszug).

1. Nenne den SQL-Befehl, mit dem nur die Vornamen aller User ausgegeben werden, die mit Nachnamen “Hubert” heißen. Die Ausgabe soll nach der user_id absteigend sortiert sein. Antwort ein/ausblenden

SELECT firstname /* 1P */
FROM letsmeetmigrationscheme.tblUser /* 1P */
WHERE lastname LIKE 'Hubert' /* 2P */
ORDER BY user_id DESC /* 1P */

2. Die Telefonnummern sind auf zweierlei Art erfasst: “030 / 12345” und “(030) 12345”. Erstelle einen SQL-Befehl, mit dem alle Daten von Datensätzen ausgegeben werden, deren Telefonnummern in der Schreibweise mit Schrägstrich erfasst sind! Die Ausgabe soll nach Telefonnummern aufsteigend sortiert sein. Antwort ein/ausblenden

SELECT * /* 1P */
FROM letsmeetmigrationscheme.tblUser /* 1P */
WHERE telephone LIKE '%/%'/* 2P */
ORDER BY telephone ASC; /* 2P */

Auswertung der Alterstruktur

1. Es soll für jede Stadt ermittelt werden, an welchem Datum der jüngste und der älteste User (m/w/nb) geboren wurde. Die Ausgabe soll wie folgt aussehen (Sortierung egal):

SELECT city AS Stadt, /* Aliasse 1P */
MIN(birthday) AS Ältester, MAX(birthday) AS Jüngster
/* Aggregationen 2x1P */
FROM letsMeetMigrationScheme.tblUser /* 1P */
GROUP BY city /*2P*/

2. Mithilfe der Funktion extract(year from age(birthday)) kann beispielsweise in PostgreSQL das Alter eines Users in Jahren (als Zahl) ausgegeben werden:

SELECT extract(year from age(birthday)) AS Alter from tblUser; 

Erstelle mithilfe dieses Befehls eine Abfrage, die alle Städte auflistet, in denen mehr als 10 User leben, die 50 Jahre oder älter sind. Es soll die Stadt und die Anzahl der mindestens 50-jährigen wie folgt dargestellt werden, Sortierung egal:

SELECT city AS "Stadt", COUNT(*) AS "Anzahl" /* 4P */
FROM letsmeetmigrationscheme.tblUser /* 1P */
WHERE extract(year FROM age(birthday)) >=50 /* 2P */
GROUP BY city /* 1P */
HAVING COUNT(*) > 10; /* 2P */

Einfügen neuer Nutzer

In die Datenbank soll ein neuer Nutzer aufgenommen werden. Erstelle alle Abfragen, die nötig sind, um die angezeigten Daten zu speichern. Sofern Du nicht mit Subqueries arbeitest und zum Einfügen IDs aus anderen Tabellen benötigst, erstelle die Abfragen, mit denen Du diese IDs auslesen kannst und nenne fiktive Ergebnisse zum Weiterarbeiten. Es kann davon ausgegangen werden, dass es nur eine Userin “Martina Mustermann” und einen “Peter Meyer” gibt und dass das Hobby “Tanzen” bereits in der Datenbank unter diesem Namen angelegt ist.

/*ERSTE TABELLE 3P*/
INSERT INTO tblUser /*1P*/
(user_id, firstname, lastname, postcode , city) /*1P*/
VALUES (DEFAULT, 'Peter', 'Meyer', '31319', 'Sehnde'); /*1P*/

/*9P = ZWEITE TABELLE 3P, 2x SUBQUERY 3P*/
INSERT INTO tblHobby2User /*1P*/
(user_id, hobby_id, priority) /*1P*/
VALUES (
(SELECT user_id FROM tblUser WHERE firstname LIKE 'Peter%' AND lastname LIKE 'Meyer%'), /*2P*/
(SELECT hobby_id FROM tblHobby WHERE hobbyname like 'Tanzen%'),  /*4P*/
100); /*1P*/

/*7P = DRITTE TABELLE 3P, 2x SUBQUERY 2P*/
INSERT INTOo tblFriendship/*1P*/
(user_id, friend_user_id) /*1P*/
VALUES (
(SELECT user_id FROM tblUser WHERE firstname LIKE 'Peter%' AND lastname LIKE 'Meyer%'), /*2P*/
(SELECT user_id FROM tblUser WHERE firstname LIKE 'Martina%' AND lastname LIKE 'Mustermann%') /*2P*/
) /*1P*/;

Analyse Profilbilder und Freundschaften

1. Es sollen alle Nutzer per E-Mail angeschrieben werden, die über kein Profilbild (tblProfilePicture) verfügen. Hierzu soll eine Abfrage erstellt werden, die alle in der Datenbank befindlichen Adressen ausgibt, für die kein Profilbild hinterlegt ist.

SELECT email /*1P*/
FROM tblUser AS U /*1P*/
LEFT JOIN tblprofilepicture AS P /*1P*/
ON U.user_id = P.user_id /*1P*/
WHERE p.user_id IS NULL; /*1P*/

Alternativ
SELECT email /*1P*/
FROM tblUser /*1P*/
WHERE user_id NOT IN (/*1P*/
SELECT user_id  FROM tblprofilepicture /*2P*/
);

2. Ausgegeben werden sollen alle Einträge der tblUser und deren zugehörige Freunde (über tblFriendship) wie unten angegeben, Sortierung egal.

SELECT t.firstname AS Vorname1, t.lastname AS Nachname1, 
t2.firstname AS Vorname2, t2.lastname AS Nachname2/*2P*/
FROM tbluser t  /*1P*/
LEFT JOIN tblfriendship tfs  ON t.user_id = tfs.user_id  /*2P*/
LEFT JOIN tbluser t2 ON tfs.friend_user_id = t2.user_id  /*2P*/;

Anpassungen in der Datenbank

1. Der “Gerhard-Schröder-Platz” in München wurde umbenannt und heißt jetzt “Angela-Merkel-Forum”. Passe die betreffenden Adressen in tblUser entsprechend an!

|Antwort ein/ausblenden

UPDATE tbluser /*1P*/
SET street = 'Angela-Merkel-Forum' /*2P*/
WHERE street LIKE 'Gerhard-Schröder-Platz' AND city LIKE ’München’; /*2P*/

2. Die Priorisierung in tblHobby2User wurde bislang mit Zahlen von 0-100 umgesetzt. Um eine größere Bandbreite abzudecken sollen alle Werte jetzt um den Faktor 10 erhöht werden (zukünftig also zwischen 0 und 1000 sein). Erstelle eine Abfrage, um den Bestand anzupassen!

UPDATE tblhobby2user /*1P*/
SET priority = priority *10; /*3P*/

Links und weitere Informationen

• oer-Informatik Repositories auf GitLab

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 (zur "Let’s Meet"-Datenbank)” von oer-informatik.de (H. Stein), Lizenz: CC BY 4.0. Der Artikel wurde unter https://oer-informatik.de/lets_meet-beispielloesung_uebungsaufgaben veröffentlicht, die Quelltexte sind in weiterverarbeitbarer Form verfügbar im Repository unter https://gitlab.com/oer-informatik/db-sql/lets-meet-db. Stand: 13.06.2023.

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