moon sun
Lösungen zu den abschließenden SQL-Aufgaben

1.

SELECT Username
FROM Accounts
ORDER BY Username ASC;

2.

SELECT Email
FROM Accounts
WHERE Adresse ILIKE '%Berlin%'
ORDER BY Email DESC;

3.

SELECT Title, UploadDatum

FROM Videos

WHERE UploadDatum >= DATE '2025-10-01'
ORDER BY UploadDatum ASC;

 

4.

SELECT KommentarText
FROM Kommentare
WHERE ErstellungsDatum::date = DATE '2025-10-06';

 

5.

SELECT Title, Beschreibung
FROM Videos
WHERE CHAR_LENGTH(Beschreibung) > 20
ORDER BY Title ASC;

 

6.

SELECT ChannelName
FROM Channels
WHERE ChannelBeschreibung ILIKE '%Reise%'
ORDER BY ChannelName ASC;

 

7.

SELECT Username, Lebensalter
FROM Accounts
WHERE Lebensalter >= 20
ORDER BY Lebensalter DESC;

 

8.

SELECT Title
FROM Videos
WHERE Title LIKE 'A%'
ORDER BY Title ASC;

 

9.

SELECT KommentarText, ErstellungsDatum
FROM Kommentare
WHERE VideoID = 6;

 

10.

SELECT ChannelName, ChannelBeschreibung
FROM Channels
WHERE ChannelName LIKE 'Finn%'
ORDER BY ChannelName ASC;

 

11.

SELECT KommentarID, VideoID, AccountID, KommentarText, Rating, ErstellungsDatum
FROM Kommentare
WHERE Rating <= 2
ORDER BY Rating ASC, ErstellungsDatum DESC;

 

12.

SELECT KommentarID, Rating, (Rating::numeric / 5.0) AS NormalizedRating
FROM Kommentare;

 

13.

SELECT KommentarID, AccountID, KommentarText, Rating, ErstellungsDatum
FROM Kommentare
WHERE Rating = 1
  AND CHAR_LENGTH(KommentarText) < 15
ORDER BY ErstellungsDatum DESC;

 

14.

SELECT KommentarID,
       Rating,
       (Rating::numeric / 5.0) AS Normalized,
       POWER((Rating::numeric / 5.0), 2) AS Boosted
FROM Kommentare;



1.

SELECT C.ChannelName, A.Username
FROM Channels C
JOIN Accounts A ON C.AccountID = A.AccountID
ORDER BY C.ChannelName ASC, A.Username ASC;

 

2.
SELECT V.Title, K.KommentarText, K.ErstellungsDatum
FROM Kommentare K
JOIN Videos V ON K.VideoID = V.VideoID
ORDER BY V.Titel ASC, K.ErstellungsDatum ASC;

 

3.
SELECT CC.VideoID, V.Title, CC.ChannelID
FROM ChannelContent CC
JOIN Videos V ON CC.VideoID = V.VideoID
ORDER BY CC.VideoID ASC, CC.ChannelID ASC;

 

4.
SELECT C.ChannelName, V.Title, V.UploadDatum
FROM Channels C
JOIN ChannelContent CC ON C.ChannelID = CC.ChannelID
JOIN Videos V ON CC.VideoID = V.VideoID
ORDER BY C.ChannelName ASC, V.UploadDatum ASC, V.Titel ASC;

 

5.
SELECT A.Username, V.Title, K.KommentarText
FROM Kommentare K
JOIN Accounts A ON K.AccountID = A.AccountID
JOIN Videos V ON K.VideoID = V.VideoID
ORDER BY A.Username ASC, V.Titel ASC;

 

6.
SELECT CC.ChannelID, K.VideoID, V.Title, K.KommentarText, K.ErstellungsDatum
FROM ChannelContent CC
JOIN Videos V ON CC.VideoID = V.VideoID
JOIN Kommentare K ON V.VideoID = K.VideoID
ORDER BY CC.ChannelID ASC, K.VideoID ASC, K.ErstellungsDatum ASC;

 

7.
SELECT A.AccountID, A.Username, C.ChannelID, C.ChannelName, V.VideoID, V.Title, V.UploadDatum
FROM Channels C
JOIN Accounts A ON C.AccountID = A.AccountID
JOIN ChannelContent CC ON C.ChannelID = CC.ChannelID
JOIN Videos V ON CC.VideoID = V.VideoID
ORDER BY A.Username ASC, C.ChannelName ASC, V.UploadDatum ASC;

 

8.
SELECT A.Username AS Kommentierender, K.KommentarID, V.Title, CC.ChannelID, K.KommentarText
FROM Kommentare K
JOIN Accounts A ON K.AccountID = A.AccountID
JOIN Videos V ON K.VideoID = V.VideoID
JOIN ChannelContent CC ON V.VideoID = CC.VideoID
ORDER BY V.Titel ASC, K.KommentarID ASC;

 

9.
SELECT C.ChannelName, V.Title, K.KommentarText, K.ErstellungsDatum, Owner.Username AS ChannelOwner
FROM Kommentare K
JOIN Videos V ON K.VideoID = V.VideoID
JOIN ChannelContent CC ON V.VideoID = CC.VideoID
JOIN Channels C ON CC.ChannelID = C.ChannelID
JOIN Accounts Owner ON C.AccountID = Owner.AccountID
ORDER BY C.ChannelName ASC, V.Titel ASC, K.ErstellungsDatum ASC;

 

10.
SELECT KAccount.Username AS Kommentierender, C.ChannelName, V.Title, K.ErstellungsDatum, K.KommentarText
FROM Kommentare K
JOIN Accounts KAccount ON K.AccountID = KAccount.AccountID
JOIN Videos V ON K.VideoID = V.VideoID
JOIN ChannelContent CC ON V.VideoID = CC.VideoID
JOIN Channels C ON CC.ChannelID = C.ChannelID
ORDER BY K.ErstellungsDatum ASC, KAccount.Username ASC, C.ChannelName ASC;

 

11.
SELECT K.KommentarID, K.VideoID, K.KommentarText, K.Rating, K.ErstellungsDatum, V.UploadDatum
FROM Kommentare K
JOIN Videos V ON K.VideoID = V.VideoID
WHERE K.Rating <= 2
  AND V.UploadDatum >= CURRENT_DATE - INTERVAL '30 days'
ORDER BY K.Rating ASC, V.UploadDatum DESC;

 

12.
SELECT A.AccountID, A.Username, K.KommentarID, K.Rating, A.Lebensalter,
       (K.Rating + A.Lebensalter / 20.0) AS AgeAdjustedScore
FROM Accounts A
JOIN Kommentare K ON A.AccountID = K.AccountID
WHERE (K.Rating + A.Lebensalter / 20.0) >= 5
ORDER BY AgeAdjustedScore DESC;

 

13.
SELECT C.ChannelID, C.ChannelName, CC.VideoID, K.KommentarID, K.Rating, K.KommentarText
FROM Channels C
JOIN ChannelContent CC ON C.ChannelID = CC.ChannelID
JOIN Kommentare K ON CC.VideoID = K.VideoID
WHERE K.Rating <= 2
ORDER BY C.ChannelName ASC, CC.VideoID ASC;

 

14.
SELECT A.AccountID, A.Username, C.ChannelID, C.ChannelName, CC.VideoID, K.KommentarID, K.Rating, K.ErstellungsDatum
FROM Accounts A
JOIN Channels C ON C.AccountID = A.AccountID
JOIN ChannelContent CC ON CC.ChannelID = C.ChannelID
JOIN Kommentare K ON K.VideoID = CC.VideoID
WHERE K.AccountID = A.AccountID
  AND K.Rating = 5
ORDER BY A.Username ASC, K.ErstellungsDatum DESC;

1.

SELECT CC.ChannelID, C.ChannelName, COUNT(CC.VideoID) AS NumVideos
FROM ChannelContent CC
JOIN Channels C ON CC.ChannelID = C.ChannelID
GROUP BY CC.ChannelID, C.ChannelName
ORDER BY NumVideos DESC;

 

2.
SELECT K.VideoID, V.Title, COUNT(*) AS CommentCount
FROM Kommentare K
JOIN Videos V ON K.VideoID = V.VideoID
GROUP BY K.VideoID, V.Titel
ORDER BY V.Titel ASC;

 

3.
SELECT C.AccountID, A.Username, COUNT(C.ChannelID) AS NumChannels
FROM Channels C
JOIN Accounts A ON C.AccountID = A.AccountID
GROUP BY C.AccountID, A.Username
ORDER BY A.Username ASC;

 

4.
SELECT A.Adresse AS City, ROUND(AVG(A.Lebensalter)::numeric, 2) AS AvgAge
FROM Accounts A
GROUP BY A.Adresse
ORDER BY AvgAge DESC;

 

5.
SELECT K.AccountID, A.Username, COUNT(*) AS NumComments
FROM Kommentare K
JOIN Accounts A ON K.AccountID = A.AccountID
GROUP BY K.AccountID, A.Username
ORDER BY NumComments DESC;

 

6.
SELECT CC.ChannelID, V.UploadDatum, COUNT(*) AS NumVideosOnDate
FROM ChannelContent CC
JOIN Videos V ON CC.VideoID = V.VideoID
GROUP BY CC.ChannelID, V.UploadDatum
ORDER BY CC.ChannelID ASC, V.UploadDatum ASC;

 

7.
SELECT C.ChannelID, C.ChannelName, A.Adresse, ROUND(AVG(CHAR_LENGTH(V.Beschreibung))::numeric, 2) AS AvgDescriptionLength
FROM Channels C
JOIN Accounts A ON C.AccountID = A.AccountID
JOIN ChannelContent CC ON C.ChannelID = CC.ChannelID
JOIN Videos V ON CC.VideoID = V.VideoID
GROUP BY C.ChannelID, C.ChannelName, A.Adresse
ORDER BY C.ChannelName ASC, A.Adresse ASC;

 

8.
SELECT K.AccountID, A.Username, V.Title,
       MIN(K.ErstellungsDatum) AS FirstCommentDate,
       MAX(K.ErstellungsDatum) AS LastCommentDate
FROM Kommentare K
JOIN Accounts A ON K.AccountID = A.AccountID
JOIN Videos V ON K.VideoID = V.VideoID
GROUP BY K.AccountID, A.Username, V.Titel
ORDER BY A.Username ASC, V.Titel ASC;

 

9.
SELECT C.AccountID AS ChannelOwnerID, A.Username, COUNT(C.ChannelID) AS NumChannels
FROM Channels C
JOIN Accounts A ON C.AccountID = A.AccountID
GROUP BY C.AccountID, A.Username
ORDER BY A.Username ASC, COUNT(C.ChannelID) ASC;

 

10.
SELECT C.ChannelName, A.Username AS Kommentierender, COUNT(K.KommentarID) AS NumComments
FROM Channels C
JOIN ChannelContent CC ON C.ChannelID = CC.ChannelID
JOIN Videos V ON CC.VideoID = V.VideoID
JOIN Kommentare K ON V.VideoID = K.VideoID
JOIN Accounts A ON K.AccountID = A.AccountID
GROUP BY C.ChannelName, A.Username
ORDER BY C.ChannelName ASC, A.Username ASC;

 

11.
SELECT K.VideoID,
       COUNT(*) AS CommentCount,
       ROUND(AVG(K.Rating)::numeric, 2) AS AvgRating
FROM Kommentare K
GROUP BY K.VideoID
HAVING COUNT(*) >= 5
ORDER BY AvgRating DESC;

 

12.
SELECT K.AccountID, COUNT(*) FILTER (WHERE K.Rating = 5) AS FiveStarCount
FROM Kommentare K
GROUP BY K.AccountID
HAVING COUNT(*) FILTER (WHERE K.Rating = 5) >= 3
ORDER BY FiveStarCount DESC;

 

13.
SELECT CC.ChannelID, CC.VideoID,
       COUNT(K.KommentarID) AS CommentCount,
       ROUND(AVG(K.Rating)::numeric, 2) AS AvgRating
FROM ChannelContent CC
JOIN Kommentare K ON CC.VideoID = K.VideoID
GROUP BY CC.ChannelID, CC.VideoID
HAVING COUNT(K.KommentarID) >= 1
ORDER BY CC.ChannelID ASC, AvgRating DESC;

 

14.
SELECT K.AccountID, K.Rating, COUNT(*) AS NumComments, MAX(K.ErstellungsDatum) AS LastCommentDate
FROM Kommentare K
GROUP BY K.AccountID, K.Rating
HAVING COUNT(*) >= 2
ORDER BY K.AccountID ASC, K.Rating DESC;

1.

SELECT V.VideoID, V.Titel, V.UploadDatum
FROM Videos V
WHERE (
  SELECT AVG(K.Rating)
  FROM Kommentare K
  WHERE K.VideoID = V.VideoID
) >= 4.5
ORDER BY V.UploadDatum DESC;

 

2.
SELECT A.AccountID, A.Username, A.Email
FROM Accounts A
WHERE A.AccountID IN (
  SELECT K.AccountID
  FROM Kommentare K
  GROUP BY K.AccountID
  ORDER BY SUM(K.Rating) DESC
  LIMIT 10
)
ORDER BY A.Username;

 

3.
SELECT C.ChannelID, C.ChannelName, stats.AvgRating
FROM Channels C
JOIN (
  SELECT CC.ChannelID, AVG(K.Rating) AS AvgRating
  FROM ChannelContent CC
  JOIN Kommentare K ON CC.VideoID = K.VideoID
  GROUP BY CC.ChannelID
) AS stats ON C.ChannelID = stats.ChannelID
WHERE stats.AvgRating < 3.0
ORDER BY stats.AvgRating ASC;

 

4.
SELECT V.VideoID, V.Titel, V.UploadDatum
FROM Videos V
WHERE (
  SELECT K.Rating
  FROM Kommentare K
  WHERE K.VideoID = V.VideoID
  ORDER BY K.ErstellungsDatum DESC
  LIMIT 1
) = 5
ORDER BY V.UploadDatum DESC;

 

5.
SELECT V.VideoID, V.Titel
FROM Videos V
WHERE V.VideoID NOT IN (
  SELECT DISTINCT K.VideoID
  FROM Kommentare K
)
ORDER BY V.UploadDatum ASC, V.Titel ASC;

 

6.
SELECT C.ChannelID, C.ChannelName
FROM Channels C
WHERE C.ChannelID IN (
  SELECT CC.ChannelID
  FROM ChannelContent CC
  JOIN Videos V ON CC.VideoID = V.VideoID
  WHERE V.UploadDatum >= DATE '2025-10-01'
  GROUP BY CC.ChannelID
)
ORDER BY C.ChannelName ASC;

 

7.
SELECT C.ChannelID, C.ChannelName
FROM Channels C
WHERE C.ChannelID IN (
  SELECT CC.ChannelID
  FROM ChannelContent CC
  JOIN Videos V ON CC.VideoID = V.VideoID
  WHERE EXTRACT(YEAR FROM V.UploadDatum) = 2025
  GROUP BY CC.ChannelID
  HAVING COUNT(DISTINCT V.VideoID) >= 2
)
ORDER BY C.ChannelName ASC;

 

8.
SELECT C.ChannelID, C.ChannelName,
       (
         SELECT MAX(V.UploadDatum)
         FROM ChannelContent CC2
         JOIN Videos V ON CC2.VideoID = V.VideoID
         WHERE CC2.ChannelID = C.ChannelID
       ) AS LatestUploadDate
FROM Channels C
ORDER BY C.ChannelName ASC;

 

9.
SELECT K.KommentarID, K.KommentarText, A.Username,
       (
         SELECT COUNT(*)
         FROM Kommentare K2
         WHERE K2.AccountID = K.AccountID
       ) AS TotalCommentsByUser
FROM Kommentare K
JOIN Accounts A ON K.AccountID = A.AccountID
ORDER BY A.Username ASC, K.KommentarID ASC;

 

10.
SELECT DISTINCT A.Username
FROM Accounts A
WHERE A.AccountID IN (
  SELECT K.AccountID
  FROM Kommentare K
  JOIN ChannelContent CC ON K.VideoID = CC.VideoID
  JOIN Channels Ch ON CC.ChannelID = Ch.ChannelID
  WHERE Ch.AccountID = (
    SELECT AccountID FROM Accounts WHERE Username = 'EmmaT' LIMIT 1
  )
)
ORDER BY A.Username ASC;

 

11.
SELECT A.AccountID, A.Username, A.Email
FROM Accounts A
WHERE A.AccountID IN (
  SELECT AccountID
  FROM (
    SELECT K.AccountID, SUM(K.Rating) AS SumRating
    FROM Kommentare K
    GROUP BY K.AccountID
    ORDER BY SumRating DESC
    LIMIT 10
  ) AS top10
)
ORDER BY A.AccountID;

 

12.
SELECT V.VideoID, V.Titel, V.UploadDatum
FROM Videos V
WHERE (
  SELECT AVG(K.Rating)
  FROM Kommentare K
  WHERE K.VideoID = V.VideoID
) >= 4.5
ORDER BY V.UploadDatum ASC;

 

13.
SELECT C.ChannelID, C.ChannelName, stats.AvgRating
FROM Channels C
JOIN (
  SELECT CC.ChannelID, AVG(K.Rating) AS AvgRating
  FROM ChannelContent CC
  JOIN Kommentare K ON CC.VideoID = K.VideoID
  GROUP BY CC.ChannelID
) AS stats ON C.ChannelID = stats.ChannelID
WHERE stats.AvgRating < 3.0
ORDER BY C.ChannelName ASC;

 

14.
SELECT V.VideoID, V.Titel, V.UploadDatum
FROM Videos V
WHERE (
  SELECT K.Rating
  FROM Kommentare K
  WHERE K.VideoID = V.VideoID
  ORDER BY K.ErstellungsDatum DESC
  LIMIT 1
) = 5
ORDER BY V.UploadDatum ASC;