Noch ein paar Übungen

Aufgabe 1

Erstellen Sie aus der Tabelle "tblAngestellte" eine Geburtstagsliste, welche aufsteigend nach dem Vornamen sortiert ist. Beachten Sie auch, dass die Spaltenbezeichnungen umbenannt sind:

SELECT tVorname AS Vorname, tNachname AS Nachname, BirthDate AS GebDat FROM tblAngestellte
ORDER BY tVorname, tNachname;

Aufgabe 2

Diesmal erstellen Eine Liste, aufsteigend nach Jahrgang, Nachname und Vorname sortiert, welche nur den Jahrgang der Angestellten zeigt (nicht das ganze Geburtsdatum). Die Liste sieht etwa so aus:

SELECT tVorname AS Vorname, tNachname AS Nachname, strftime('%Y', BirthDate) AS Jahrgang FROM tblAngestellte
ORDER BY Birthdate, tNachname, tVorname;

Aufgabe 3

Erstellen Sie eine Liste aller Produktgruppen und geben Sie jeweils an, wie viele Produkte in dieser Produktgruppe vertreten sind:

SELECT tProduktGruppe AS Produktgruppe, COUNT(tblProdukte.tProduktGruppe) AS 'Anzahl Artikel' FROM tblProduktGruppen
LEFT OUTER JOIN tblProdukte
ON tblProduktGruppen.tProduktGruppeID = tblProdukte.tProduktGruppe
GROUP BY tblProduktGruppen.tProduktGruppe
ORDER BY tblProduktGruppen.tProduktGruppe;

Aufgabe 4

Erklären Sie der Lehrperson, warum in Aufgabe 3 ein LEFT OUTER JOIN besser geeignet ist als ein INNER JOIN.

Aufgabe 5

Bei der folgenden SQL-Abfrage hat sich die Lehrperson irgendwie vertan:

SELECT tLandesname, SUM(tKundenID) AS 'Anzahl Kunden' FROM tblLaender
INNER JOIN tblKunden
ON tblLaender.tLandID = tblKunden.tLandID
GROUP BY tLandesname;

Was ist schiefgelaufen?

SELECT tLandesname, COUNT(tKundenID) AS 'Anzahl Kunden' FROM tblLaender
INNER JOIN tblKunden
ON tblLaender.tLandID = tblKunden.tLandID
GROUP BY tLandesname;

Aufgabe 6

Erstellen Sie eine Liste mit den Ländern und der Anzahl Kund*innen pro Land. Sortieren Sie die Liste jedoch absteigend nach der Anzahl der Kund*innen. Hat es in mehreren Ländern die gleiche Anzahl, sollen die Länder aufsteigend sortiert werden. So sieht die Liste aus:

SELECT tLandesname AS Land, COUNT(tKundenID) AS 'Anzahl Kunden' FROM tblLaender
LEFT OUTER JOIN tblKunden
ON tblLaender.tLandID = tblKunden.tLandID
GROUP BY tLandesname
ORDER BY COUNT(tKundenID) DESC, tLandesname ASC;

Aufgabe 7

Erstellen Sie eine Liste aller Kunden, welche in einer Bestellungen Frachtkosten von mehr als CHF 200.- bezahlt haben. Die Kund*innen mit den höchsten Frachtkosten sollen zuoberst angezeigt werden.

SELECT tNachname, tVorname, tFrachtkosten FROM tblKunden
INNER JOIN tblBestellungen
ON tblKunden.tKundenID = tblBestellungen.tKundenID
WHERE tFrachtkosten > 200
ORDER BY tFrachtkosten DESC, tNachname, tVorname;

Aufgabe 8

Diesmal erstellen Sie eine Liste mit sämtlichen Bestellungen und Frachtkosten. Bei denjenigen Kund*innen, die mehr als CHF 200.- bezahlt haben, soll in einer neuen Spalte der Text "zu hoch" geschrieben werden. Die Liste wird nach Nach- und Vorname sortiert. Hier ein Ausschnitt der Abfrage:

SELECT tNachname, tVorname, tFrachtkosten, iif(tFrachtkosten > 200, 'zu hoch', '') AS Korrektur FROM tblKunden
INNER JOIN tblBestellungen
ON tblKunden.tKundenID = tblBestellungen.tKundenID
ORDER BY tNachname, tVorname;

Aufgabe 9

Sie haben bereits einmal eine ähnliche Abfrage erstellt. Damals wussen Sie aber noch nichts von einem JOIN. Drum versuchen Sie, folgende Abfrage hinzukriegen:

SELECT tNachname, tVorname, SUM(tFrachtkosten) AS Gesamtfrachtkosten FROM tblKunden
INNER JOIN tblBestellungen
ON tblKunden.tKundenID = tblBestellungen.tKundenID
GROUP BY tblKunden.tKundenID
ORDER BY tNachname, tVorname;

Aufgabe 10

Bei obiger Aufgabe werden aber voraussichtlich einige Zahlen mit hässlich vielen Nachkommastellen darstellen. Drum lohnt es sich, die Gesamtfrachtkosten noch auf 2 Nachkommastellen zu runden.

SELECT tNachname, tVorname, ROUND(SUM(tFrachtkosten),2) AS Gesamtfrachtkosten FROM tblKunden
INNER JOIN tblBestellungen
ON tblKunden.tKundenID = tblBestellungen.tKundenID
GROUP BY tblKunden.tKundenID
ORDER BY tNachname, tVorname;