Skip to content

Instantly share code, notes, and snippets.

Avatar

Holger databraineo

View GitHub Profile
@databraineo
databraineo / sql1x1-16.sql
Created Aug 16, 2019
Code examples for SQL 1x1 blog-article - 16
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
@databraineo
databraineo / sql1x1-15.sql
Created Aug 16, 2019
Code examples for SQL 1x1 blog-article - 15
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;
@databraineo
databraineo / sql1x1-14.sql
Created Aug 16, 2019
Code examples for SQL 1x1 blog-article - 14
View sql1x1-14.sql
SELECT
TrackId,
Name AS Trackname,
Track.AlbumId,
Title AS Albumname
FROM Track
INNER JOIN Album ON Track.AlbumId=Album.AlbumId;
@databraineo
databraineo / sql1x1-13.sql
Created Aug 16, 2019
Code examples for SQL 1x1 blog-article - 13
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);
@databraineo
databraineo / sql1x1-12.sql
Created Aug 16, 2019
Code examples for SQL 1x1 blog-article - 12
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;
@databraineo
databraineo / sql1x1-11.sql
Created Aug 16, 2019
Code examples for SQL 1x1 blog-article - 11
View sql1x1-11.sql
SELECT
avg(Milliseconds)/60000 AS MW
FROM Track;
@databraineo
databraineo / sql1x1-10.sql
Created Aug 16, 2019
Code examples for SQL 1x1 blog-article - 10
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;
@databraineo
databraineo / sql1x1-09.sql
Created Aug 16, 2019
Code examples for SQL 1x1 blog-article - 09
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;
@databraineo
databraineo / sql1x1-08
Created Aug 16, 2019
Code examples for SQL 1x1 blog-article - 08
View sql1x1-08
SELECT DISTINCT
Composer
FROM Track
ORDER BY Composer;
@databraineo
databraineo / sql1x1-07.sql
Created Aug 16, 2019
Code examples for SQL 1x1 blog-article - 07
View sql1x1-07.sql
SELECT *
FROM Track
ORDER BY Composer DESC, Name;
You can’t perform that action at this time.