12 SQL-Funktionen Übungsfragen

SQL-Funktionen ermöglichen es, Daten zu manipulieren, einschließlich Zeichenketten und numerische Werte. In diesem Artikel finden Sie 12 Übungsfragen zu SQL-Funktionen - mit Lösungen und ausführlichen Erklärungen - die Ihnen helfen werden, Ihr SQL-Wissen zu festigen.

SQL zu üben ist wichtig, wenn Sie Ihre Fähigkeiten verbessern wollen. Aber es ist schwer, SQL-Übungsaufgaben zu finden. Wir möchten Ihnen helfen und haben daher kostenlose Zusammenstellungen von SQL-Übungsaufgaben veröffentlicht. In diesem Artikel finden Sie 12 Übungsfragen zu SQL-Funktionen, komplett mit Lösungen und Erklärungen. Wenn Ihnen dieser Artikel gefällt, sehen Sie sich unseren umfassenden SQL-Praxis Track; Sie können die ersten paar Übungen kostenlos ausprobieren!

Okay, fangen wir an. Bevor wir mit den Übungen zu den SQL-Funktionen beginnen, sollten wir uns kurz die verschiedenen Arten von SQL-Funktionen ansehen und einen Blick auf unseren Datensatz werfen.

Was ist eine SQL-Funktion?

Eine SQL-Funktion nimmt ein oder mehrere Argumente als Eingabe entgegen, verarbeitet das/die Argumente und gibt eine Ausgabe zurück. SQL bietet eine Reihe von vordefinierten Funktionen, die mit String-Werten (z. B. UPPER(), SUBSTRING()), numerischen Werten (z. B. FLOOR(), GREATEST()) und NULL Werten (z. B. ISNULL()) arbeiten.

SQL-Funktionen können weiter in Skalar-, Aggregat- und Fensterfunktionen unterteilt werden. Skalare Funktionen nehmen ein einzelnes Argument und geben ein einzelnes Ergebnis zurück, z. B. würde LEN(name) die Anzahl der Zeichen in jedem Namen zurückgeben. Aggregatfunktionen geben einen einzigen Wert für eine Gruppe von Zeilen zurück (d. h. sie geben einen aggregierten Wert zurück). Zum Beispiel gibt die Funktion SUM(sales) die Summe aller Werte in der Spalte "Umsatz" zurück. Aggregatfunktionen werden mit GROUP BY gepaart, um Statistiken zu berechnen.

Fensterfunktionen arbeiten mit Gruppen von Zeilen (sogenannten Fenstern), die sich im Verhältnis zur aktuellen Zeile ändern können. Wie Aggregatfunktionen werden Fensterfunktionen auf eine Gruppe von Zeilen angewendet; im Gegensatz zu Aggregatfunktionen geben sie einen Wert pro Zeile zurück. Mehr über den Unterschied zwischen Aggregat- und Fensterfunktionen erfahren Sie hier.

Wenn Sie sich wirklich mit SQL-Funktionen beschäftigen wollen, empfehle ich Ihnen unseren Standard-SQL-Funktionen Kurs. Mit seinen 211 Programmieraufgaben werden Sie sicherlich eine Menge Übung bekommen!

Einführung in das Dataset

Lassen Sie uns nun schnell das Dataset kennenlernen. Für unsere Übungen zu den SQL-Funktionen verwenden wir einen Beispiel-Datensatz zum DVD-Verleih, den wir in eine PostgreSQL-Datenbank importiert haben. Hier ist das Schema des Datasets:

Und dies sind die Tabellen, die wir in unseren Übungen verwenden werden:

• Die Tabelle customer Tabelle speichert Informationen über DVD-Verleihkunden. Sie verbindet sich mit den Tabellen rental und payment verbunden; jeder Kunde kann null oder mehr Datensätze in diesen Tabellen haben.
• Die Tabelle rental speichert Informationen über jeden Verleih, der von Kunden vorgenommen wurde. Sie steht in Verbindung mit der Tabelle inventory da für jede Vermietung genau ein Inventarartikel benötigt wird. Sie ist auch mit der Tabelle payment verbunden, damit jeder Vermietung ein Zahlungssatz zugeordnet werden kann.
• Die Tabelle payment Tabelle speichert Informationen über die Zahlung für jede Miete. Sie ist auch mit den Tabellen rental und staff verbunden, um jeder Zahlung einen Mietvertrag und einen Mitarbeiter zuordnen zu können.
• Die Tabelle staff speichert Informationen über die Mitarbeiter des DVD-Ladens. Sie ist verbunden mit den Tabellen payment und rental um jeder Zahlung und jedem Verleih einen Mitarbeiter zuzuordnen.
• Die Tabelle inventory speichert alle Artikel, die im Bestand des DVD-Verleihs vorhanden sind. Sie wird mit der Tabelle rental um jedem Verleih ein einzelnes Inventarteil zuzuordnen. Außerdem wird eine Verbindung mit der Tabelle film Tabelle, um jeden Film einem Verleih zuzuordnen.
• Die Tabelle film speichert Details über alle Filme, die zum Verleih verfügbar sind. Sie ist mit der Tabelle inventory um jeden Film einer Filiale zuzuordnen. Sie stellt auch eine Verbindung zur Tabelle language um anzugeben, in welcher(n) Sprache(n) der Film verfügbar ist. Schließlich wird eine Verbindung mit der Tabelle film_actor Tabelle, um Schauspieler zu Filmen (und Filme zu Schauspielern) zuzuordnen.
• Die Tabelle language Tabelle speichert alle Sprachen der vom DVD-Verleih angebotenen Filme. Es wird eine Verbindung zur Tabelle film Tabelle, um jedem Film eine Sprache zuzuordnen.
• Die Tabelle film_actor Tabelle speichert die Viele-zu-Viele-Beziehungen zwischen der film und actor (weil jeder Schauspieler in vielen Filmen mitspielen kann und jeder Film viele Schauspieler haben kann). Sie stellt eine Verbindung zu den Tabellen film und actor um jeden Film allen Schauspielern zuzuordnen, die in ihm mitgespielt haben, und jeden Schauspieler jedem Film, in dem er mitgespielt hat.
• Die Tabelle actor speichert alle Schauspieler, die in den im DVD-Verleih verfügbaren Filmen mitgespielt haben. Sie steht in Verbindung mit der film_actor Tabelle, die die Schauspieler den Filmen zuordnet.

Jetzt, da wir den Datensatz kennen, können wir mit dem Üben der SQL-Funktionen beginnen!

SQL-Funktionen üben: Zeichenketten

SQL stellt zahlreiche Funktionen zur Verfügung, mit denen Sie String-Werte manipulieren können. Hier sind einige der gebräuchlichsten davon:

• CONCAT() Konkateniert zwei oder mehr String-Werte zu einem einzigen
• LENGTH() gibt die Anzahl der Zeichen zurück, die in dem String-Wert enthalten sind.
• LOWER() wandelt die Zeichenkette in Kleinbuchstaben um.
• REPLACE() Ersetzt einen Teil der Zeichenkette durch eine andere Zeichenkette.
• SUBSTRING() extrahiert einen Teil der Zeichenkette.
• UPPER() wandelt die Zeichenkette in Großbuchstaben um

Lesen Sie diesen Artikel, um mehr über alle String-Funktionen von SQL zu erfahren.

Übung 1: Aktualisieren von Mitarbeiter-E-Mails

Frage: Der DVD-Verleih Sakila hat Filialen in Großbritannien eröffnet. Aktualisieren Sie die E-Mail-Adressen der Mitarbeiter, indem Sie die aktuelle E-Mail-Domäne sakilastaff.com durch sakila.uk ersetzen; nennen Sie diese Spalte new_email.

Lösung:

SELECT REPLACE(email, 'sakilastaff.com', 'sakila.uk')
          AS new_email
FROM staff;

Erläuterung: Wir verwenden die Funktion REPLACE(), um einen Teil der Zeichenkette durch eine andere Zeichenkette zu ersetzen.

Jeder Wert aus der Spalte email der Tabelle staff Tabelle wird an die Funktion REPLACE() übergeben, die sakilastaff.com durch sakila.uk ersetzt.

Übung 2: Alle Kunden auflisten

Frage: Listen Sie die Namen aller aktiven Kunden in dem Format Nachname, erster Buchstabe des Vornamens auf. Nennen Sie diese Spalte customer_name und ordnen Sie die Liste alphabetisch.

(Tipp: Aktive Kunden haben eine 1 in der Spalte active.)

Lösung:

SELECT CONCAT(last_name, ', ', SUBSTRING(first_name, 1, 1))
          AS customer_name
FROM customer
WHERE active = 1
ORDER BY customer_name;

Erläuterung: Wir wählen den Vor- und Nachnamen aus der customer Tabelle. Um nur aktive Kunden auszuwählen, definieren wir eine WHERE Klausel, die besagt, dass der Wert von active 1 sein muss.

Um die Kundennamen in dem angegebenen Format zurückzugeben, verwenden wir die Funktion CONCAT(), um alle Teile zu einem String zu verketten:

• Der erste Teil ist die Spalte last_name.
• Der zweite Teil ist ein Komma.
• Der dritte Teil ist der erste Buchstabe des Vornamens, der von der Funktion SUBSTRING() extrahiert wird.

Schließlich ordnen wir die Liste alphabetisch nach der Spalte last_name.

Übung 3: Filme mit Kurzbeschreibungen auflisten

Frage: Zeigen Sie jeden Filmtitel und die ersten 100 Zeichen seiner Beschreibung an, gefolgt von drei Punkten, wenn die Beschreibung länger als 100 Zeichen ist. Nennen Sie diese Spalte truncated_description. Wenn die Beschreibung 100 oder weniger Zeichen hat, zeigen Sie die gesamte Beschreibung in derselben Spalte an.

Lösung:

SELECT title,
       CASE
         WHEN LENGTH(description) <= 100 THEN description
         ELSE SUBSTRING(description, 1, 100) || '...'
       END AS truncated_description
FROM film;

Erläuterung: Wir wählen die Spalten title und description aus der film Tabelle.

Wir verwenden die Anweisung CASE WHEN, um den Inhalt der Spalte truncated_description zu bestimmen. Sie entspricht der Anweisung IF…ELSE… aus anderen Programmiersprachen.

• Wir verwenden die Funktion LENGTH(), um die Anzahl der Zeichen in der Spalte description zu ermitteln. WHEN ist die Anzahl der Zeichen kleiner oder gleich 100, THEN zeigen wir die gesamte Beschreibung an.
• Wir verwenden die Funktion SUBSTRING(), um die ersten 100 Zeichen aus der Spalte description zu erhalten. Wir verketten sie mit drei Punkten, indem wir die Funktion || verwenden. Dieser Inhalt wird in der Spalte truncated_description gespeichert, wenn die Bedingung in der Klausel WHEN nicht erfüllt ist.

Übung 4: Auswahl von Filmen und Schauspielern

Frage: Zeigen Sie alle Filme mit dem Format Filmtitel (Erscheinungsjahr) in der Spalte film an. Zeigen Sie auch alle Schauspieler an, die den Filmen zugeordnet sind, indem Sie das Format Nachname, Vorname als Spalte actor verwenden. Ordnen Sie die Daten chronologisch nach Erscheinungsjahr und dann alphabetisch nach dem Filmtitel.

Lösung:

SELECT f.title || ' (' || f.release_year || ')' AS film,
       a.last_name || ', ' || a.first_name AS actor
FROM film f
JOIN film_actor fa
ON f.film_id = fa.film_id
JOIN actor a
ON fa.actor_id = a.actor_id
ORDER BY f.release_year, f.title;

Erläuterung:

Um die Namen der Schauspieler und die Filme, in denen sie mitgespielt haben, auszuwählen, müssen wir die Spalten actor, film, und film_actor über ihre gemeinsamen Spalten verknüpfen. In dieser SQL JOIN-Übung erfahren Sie mehr über die Verknüpfung mehrerer Tabellen.

In der Anweisung SELECT verknüpfen wir die Filmtitel und das Erscheinungsjahr der gesamten Tabelle film Tabelle mit den Vor- und Nachnamen der Schauspieler aus der actor Tabelle.

Schließlich ordnen wir die Daten zunächst nach film.release_year; innerhalb jedes Jahres ordnen wir die Daten weiter alphabetisch nach der Spalte title.

SQL-Funktion Praxis: Numerische Werte

SQL bietet zahlreiche Funktionen, mit denen Sie numerische Werte manipulieren können. Hier sind einige der gebräuchlichen Funktionen:

• ABS() gibt den absoluten Wert des Arguments zurück.
• CEILING() gibt den aufgerundeten Wert zurück.
• FLOOR() gibt den abgerundeten Wert zurück.
• GREATEST() gibt die größte Zahl aus einer Gruppe von Zahlen zurück.
• LEAST() gibt die kleinste Zahl aus einer Gruppe von Zahlen zurück.
• ROUND() rundet die Zahl auf eine bestimmte Anzahl von Dezimalstellen.

Lesen Sie diesen Artikel, um mehr über alle numerischen Funktionen von SQL zu erfahren.

Übung 5: Berechnen Sie die durchschnittliche Länge der Beschreibung

Frage: Geben Sie die durchschnittliche Länge aller Filmbeschreibungen an. Nennen Sie diese Spalte average_film_desc_length. Runden Sie das Ergebnis auf die nächste Ganzzahl ab.

Lösung:

SELECT 
  FLOOR(AVG(LENGTH(description)))
          AS average_film_desc_length
FROM film;

Erläuterung:

Wir wählen die Spalte description aus der film Tabelle. Wir verpacken sie in drei Funktionen, um die durchschnittliche Länge der Beschreibung auf die nächste ganze Zahl abgerundet zu erhalten:

• Die Funktion LENGTH(), mit description als Argument, ermittelt die Anzahl der Zeichen pro Beschreibung.
• Wir verpacken die Ausgabe von LENGTH() in die Funktion AVG(), um den Durchschnitt aller Beschreibungslängen zu berechnen.
• Schließlich wird AVG(LENGTH(description) in die Funktion FLOOR() eingebunden, um den Durchschnitt auf die nächste Ganzzahl abzurunden.

Übung 6: Die längsten Filme finden

Frage: Nennen Sie den/die Titel der Filme mit der längsten Laufzeit.

Lösung:

SELECT title
FROM film
WHERE length = (SELECT MAX(length) FROM film);

Erläuterung:

Wir wählen die Spalte title aus der film Tabelle.

Da wir nur den/die Film(e) mit der längsten Laufzeit auflisten wollen, definieren wir eine WHERE Klausel-Bedingung. Wir verwenden eine Unterabfrage, die den maximalen Längenwert aus der Spalte length zurückgibt. Die Bedingung besagt, dass der Längenwert der anzuzeigenden Filmtitel gleich dem Wert sein muss, der von der Unterabfrage zurückgegeben wird.

Weitere Übungen zu SQL-Subqueries finden Sie in diesen Subquery-Übungen.

Übung 7: Verleihstatistiken auflisten

Frage: Zeigen Sie die durchschnittliche Mietdauer in Tagen (in der Spalte avg_rental_duration_days ) und die durchschnittliche Zahlung pro Miete (in der Spalte avg_payment_per_rental ) an. Runden Sie beide Werte auf 2 Dezimalstellen.

Lösung:

SELECT 
  ROUND(AVG(EXTRACT(DAY FROM r.return_date - r.rental_date)), 2)
          AS avg_rental_duration_days,
  ROUND(AVG(p.amount), 2) 
          AS avg_payment_per_rental
FROM rental r
JOIN payment p
ON r.rental_id = p.rental_id;

Erläuterung:

Zuerst verbinden wir die Spalten rental und payment über ihre gemeinsame Spalte.

Um die durchschnittliche Mietdauer in Tagen zu erhalten, müssen wir die durchschnittliche Differenz zwischen den Spalten rental_date und return_date aus der Tabelle rental Tabelle. Dazu verwenden wir die Funktion EXTRACT(), um die Anzahl der Tage zu ermitteln, die die Anmietung gedauert hat (als ganzzahlige Werte); dann verwenden wir AVG(), um den Durchschnittswert zu berechnen. Schließlich wird dies in die Funktion ROUND() eingeschlossen, wobei der Durchschnittswert als erstes Argument und 2 als zweites Argument angegeben wird, um den gerundeten Durchschnittswert zu erhalten.

Um die durchschnittliche Zahlung pro Miete zu ermitteln, verwenden wir erneut AVG(), wobei wir dieses Mal die Betragsspalte aus der payment Tabelle als Argument. Wie zuvor umschließen wir sie mit ROUND(), um das Ergebnis auf zwei Dezimalstellen zu runden.

Übung 8: Diskontierte Mietpreise

Frage: Sakila bietet einen Sonderrabatt auf Leihfilme wie folgt an:

• 50% Rabatt auf Filme der Kategorie G.
• 40% Rabatt auf PG-Filme.
• 30% Rabatt auf PG-13-Filme.

Wählen Sie die Filmtitel zusammen mit den aktualisierten Verleihtarifen aus (nennen Sie diese Spalte new_rental_rate).

Lösung:

SELECT title,
       CASE
         WHEN rating = 'G' THEN rental_rate * 0.5
         WHEN rating = 'PG' THEN rental_rate * 0.6
         WHEN rating = 'PG-13' THEN rental_rate * 0.7
         ELSE rental_rate
       END AS new_rental_rate
FROM film;

Erläuterung:

Wir wählen die Spalte title aus der Tabelle film und die aktualisierten Verleihpreise auf der Grundlage der Spalte rating.

Mit der Anweisung CASE WHEN werden Rabatte auf der Grundlage der Werte in der Spalte rating angewendet:

• Wenn das Rating G ist, wird der Mietpreis um 50 Prozent reduziert (rental_rate * 0.5).
• Bei einer Einstufung von PG wird der Mietpreis um 40 Prozent reduziert (rental_rate * 0.6).
• Bei einer Einstufung von PG-13 wird der Mietpreis um 30 Prozent reduziert (rental_rate * 0.7).
• Andernfalls geben wir den rental_rate

SQL-Funktionspraxis: NULLs

SQL bietet zahlreiche Funktionen, mit denen Sie NULL Werte verwalten können. Hier sind einige der gängigen Funktionen:

• ISNULL() gibt true zurück, wenn ihr Argument ein NULL Wert ist; andernfalls gibt sie
• IFNULL() gibt einen angegebenen Wert zurück, wenn der ursprüngliche Wert NULL ist.
• COALESCE() gibt den ersten Wert, der nichtNULL ist, aus seinen Argumenten zurück.

Übung 9: Kunden mit aktuellen Mietpreisen finden

Frage: Wählen Sie Vor- und Nachnamen von Kunden aus, die laufende Mieten haben.

Lösung:

SELECT c.first_name, c.last_name
FROM customer AS c
JOIN rental AS r
ON c.customer_id = r.customer_id
WHERE r.return_date IS NULL;

Erläuterung:

Wir wählen die Vor- und Nachnamen der Kunden aus der Kundentabelle aus.

Um die Kunden mit laufenden Mietverträgen zu erhalten, müssen wir Datensätze in der Tabelle rental finden, in denen der Wert der Spalte return_date NULL ist. Zu diesem Zweck verknüpfen wir die Spalten customer und rental über ihre gemeinsame Spalte.

Übung 10: Filme und ihre Sprachen auswählen

Frage: Wählen Sie alle Filmtitel mit Informationen über die Sprache(n) aus, in denen der Film verfügbar ist. Erstellen Sie eine Spalte (mit dem Namen film_language_info) mit Werten in diesem Format:

<film title> is available in <language name>. 

Wenn der Name der Sprache NULL lautet, dann verwenden Sie anstelle des Namens der Sprache den Ausdruck eine unbekannte Sprache.

Lösung:

SELECT f.title || ' is available in ' || 
          COALESCE(l.name, 'an unknown language') AS film_language_info
FROM film AS f
JOIN language AS l
ON f.language_id = l.language_id;

Erläuterung:

Wir wählen die Spalte title aus der Tabelle film Tabelle und die Spalte name aus der language Tabelle. Daher müssen wir die Spalten film und language über ihre gemeinsame Spalte verbinden.

Wir verketten die Filmtitel mit der Phrase "is available in" und dem Namen der Sprache. Beachten Sie, dass wir bei der Auswahl des Sprachnamens die Funktion COALESCE() verwenden, um den Sprachnamen durch "unbekannte Sprache" zu ersetzen, wenn der Sprachname NULL lautet.

Übung 11: Kunden finden, die kürzlich eine DVD ausgeliehen haben

Frage: Das Marketingteam von Sakila möchte Kunden, die seit zwei Wochen keine DVDs ausgeliehen haben, per E-Mail kontaktieren. Listen Sie die E-Mail-Adressen aller Kunden auf, die ihre letzte Ausleihe vor weniger als zwei Wochen zurückgegeben haben. Kunden mit laufenden Verleihvorgängen sollen nicht berücksichtigt werden.

Lösung:

WITH filtered_customers AS (
    SELECT customer_id,
           MAX(return_date) AS most_recent_return_date
    FROM rental
    WHERE return_date IS NOT NULL
    GROUP BY customer_id
    HAVING MAX(return_date) < CURRENT_DATE - INTERVAL '2 weeks'
)

SELECT c.email
FROM customer c
JOIN filtered_customers fc
ON c.customer_id = fc.customer_id;

Erläuterung:

Zunächst erstellen wir einen gemeinsamen Tabellenausdruck (CTE), um die Kunden zu filtern; wir nennen ihn filtered_customers. In der CTE wählen wir die Spalte customer_id und den jüngsten Wert der Spalte return_date pro Kunde aus der Tabelle rental Tabelle aus; daher verwenden wir die GROUP BY-Klausel. Wir wählen nur die Kunden aus, die keine laufenden Vermietungen haben - d.h. wo die Spalte return_date IS NOT NULL . Und wir legen eine Bedingung fest, dass der Wert most_recent_return_date weniger als zwei Wochen zurückliegt.

Dann verknüpfen wir die von dieser CTE zurückgegebene Ausgabe mit der Tabelle customer Tabelle und wählen die Spalte email aus, so dass wir E-Mails an Kunden senden können, die in den letzten zwei Wochen nicht aktiv waren.

Übung 12: NULL-Mietendaten aktualisieren

Frage: Wählen Sie die Kunden-IDs und die Verleih-IDs aller Kunden sowie das Start- und Enddatum jedes Verleihs aus. Wenn das Enddatum des Verleihs NULL ist, ersetzen Sie es, indem Sie so viele Tage zum Startdatum des Verleihs addieren, wie in der Verleihdauer des jeweiligen Films angegeben sind.

Lösung:

SELECT r.customer_id, 
       r.rental_id, 
       r.rental_date, 
       COALESCE(r.return_date,
                r.rental_date + INTERVAL '1 day' * f.rental_duration)
          AS return_date
FROM rental AS r
JOIN inventory AS i
ON r.inventory_id = i.inventory_id
JOIN film AS f
ON i.film_id = f.film_id;

Erläuterung:

Wir wählen die Spalten customer_id, rental_id und rental_date aus der rental Tabelle. Dann verwenden wir die Funktion COALESCE() und übergeben die Spalte return_date als erstes Argument. Wenn die Spalte return_date NULL ist, dann ersetzen wir sie durch die Spalte rental_date plus so viele Tage, wie in der Spalte rental_duration der Tabelle film Tabelle angegeben sind.

Damit der Wert der Spalte f.rental_duration mit dem Wert der Spalte r.rental_id übereinstimmt, müssen wir die Tabelle rental Tabelle mit der film Tabelle über die inventory Tabelle über ihre gemeinsamen Spalten verbinden.

Möchten Sie weitere SQL-Funktionen üben?

In diesem Artikel wurden praktische Beispiele für die Verwendung von SQL-Funktionen vorgestellt, um wertvolle Dateneinblicke zu erhalten. Schauen Sie sich diesen Kurs auf Standard-SQL-Funktionen an, um mehr zu üben.

Wenn Sie nicht bereit sind, einen kompletten Kurs zu belegen, finden Sie hier einige fortgeschrittene SQL-Übungen, die Ihr SQL-Wissen auf die Probe stellen. Sie können auch diese Übungsaufgaben für SQL-Fensterfunktionen ausprobieren. Folgen Sie diesem SQL-Übungsleitfaden, um das Beste aus den Ressourcen von LearnSQL.de herauszuholen.

Viel Erfolg!