View sql1x1-16.sql
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 |
View sql1x1-15.sql
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; |
View sql1x1-14.sql
SELECT | |
TrackId, | |
Name AS Trackname, | |
Track.AlbumId, | |
Title AS Albumname | |
FROM Track | |
INNER JOIN Album ON Track.AlbumId=Album.AlbumId; |
View sql1x1-13.sql
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); |
View sql1x1-12.sql
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; |
View sql1x1-11.sql
SELECT | |
avg(Milliseconds)/60000 AS MW | |
FROM Track; |
View sql1x1-10.sql
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; |
View sql1x1-09.sql
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; |
View sql1x1-08
SELECT DISTINCT | |
Composer | |
FROM Track | |
ORDER BY Composer; |
View sql1x1-07.sql
SELECT * | |
FROM Track | |
ORDER BY Composer DESC, Name; |
NewerOlder