databraineo

SELECT
  Genre,
  count(InvoiceId) AS Anzahl,
  avg(AnzahlProKauf) AS MWAnzahlProKauf
FROM (
  SELECT
    InvoiceId,
    c.Name AS Genre,
    count(a.TrackId) AS AnzahlProKauf
  FROM InvoiceLine a

databraineo

SELECT
  InvoiceId,
  c.Name as Genre,
  count(a.TrackId) AS AnzahlProKauf
FROM InvoiceLine a
INNER JOIN Track b on a.TrackId=b.TrackId
LEFT JOIN Genre c on b.GenreId=c.GenreId
GROUP BY InvoiceId, b.GenreId;

databraineo

SELECT
  TrackId,
  Name AS Trackname,
  Track.AlbumId,
  Title AS Albumname
FROM Track
INNER JOIN Album ON Track.AlbumId=Album.AlbumId;

databraineo

SELECT
  substr(Name,1,1) as [Index],
  count(TrackId) AS Anzahl,
  avg(Milliseconds)/600000 as MW
FROM Track
WHERE substr(Name,1,1) in ('A','E','I','O','U')
GROUP BY substr(Name,1,1)
HAVING count(TrackId) > 50
ORDER BY substr(Name,1,1);

databraineo

SELECT
  Composer,
  count(TrackId) as Anzahl,
  avg(Milliseconds)/60000 as MW,
  min(Milliseconds)/60000 as Minimum,
  max(Milliseconds)/60000 as Maximum
FROM Track
GROUP BY Composer
ORDER BY Composer;

databraineo

SELECT
  avg(Milliseconds)/60000 AS MW
FROM Track;

databraineo

SELECT
  Name,
  substr(Name,1,1) AS [Index],
  CASE WHEN Milliseconds > 360000 THEN '> 6min.'
       WHEN Milliseconds > 300000 THEN '5-6 min.'
       WHEN Milliseconds > 240000 THEN '4-5 min.'
	ELSE 'kurz' 
  END AS Länge 
FROM Track;

databraineo

SELECT
  Name,
  substr(Name,1,1) AS [Index],
  CASE WHEN Milliseconds > 300000 THEN '> 5min.' END AS Länge1,
  CASE WHEN Milliseconds > 300000 THEN '> 5min.' ELSE 'kurz' END AS Länge2 
FROM Track;

databraineo

SELECT	DISTINCT
  Composer
FROM Track
ORDER BY Composer;

databraineo

SELECT *
FROM Track
ORDER BY Composer DESC, Name;