SQL Programming Language

This page demonstrates advanced SQL queries using the DBeaver sample database. Explore window functions, ranking, and aggregation features available in MySQL 8+.

SQL Examples

This page provides practical SQL query examples using the DBeaver sample database. The focus is on advanced SQL features such as window functions, ranking, and aggregation, with results displayed in interactive tables.

The following tables are available in the database:

Table	Description
Album	Music albums
Artist	Music artists
Customer	Customers
Employee	Employees
Genre	Music genres
Invoice	Invoices
InvoiceLine	Invoice line items
MediaType	Media types
Playlist	Playlists
PlaylistTrack	Tracks in playlists
Track	Music tracks

Basic SQL Queries (Click to expand)

List all artists


SELECT ArtistId, Name FROM Artist LIMIT 10;

ArtistId	Name
1	AC/DC
2	Accept
3	Aerosmith
4	Alanis Morissette
5	Alice In Chains
6	Antônio Carlos Jobim
7	Apocalyptica
8	Audioslave
9	BackBeat
10	Billy Cobham

List all albums by a specific artist (ArtistId = 1)


SELECT AlbumId, Title FROM Album WHERE ArtistId = 1;

AlbumId	Title
1	For Those About To Rock We Salute You
4	Let There Be Rock

List all tracks in a specific genre (GenreId = 1)


SELECT TrackId, Name FROM Track WHERE GenreId = 1 LIMIT 10;

TrackId	Name
1	For Those About To Rock (We Salute You)
2	Balls to the Wall
3	Fast As a Shark
4	Restless and Wild
5	Princess of the Dawn
6	Put The Finger On You
7	Let's Get It Up
8	Inject The Venom
9	Snowballed
10	Evil Walks

Get total invoice amount per customer


SELECT CustomerId, SUM(Total) as TotalSpent FROM Invoice GROUP BY CustomerId LIMIT 10;

CustomerId	TotalSpent
1	40
2	38
3	40
4	40
5	41
6	50
7	43
8	38
9	38
10	38

List employees and their managers


SELECT e.EmployeeId, e.FirstName, e.LastName, m.FirstName AS ManagerFirstName, m.LastName AS ManagerLastName
FROM Employee e
LEFT JOIN Employee m ON e.ReportsTo = m.EmployeeId
LIMIT 10;

EmployeeId	FirstName	LastName	ManagerFirstName	ManagerLastName
2	Nancy	Edwards	Andrew	Adams
6	Michael	Mitchell	Andrew	Adams
3	Jane	Peacock	Nancy	Edwards
4	Margaret	Park	Nancy	Edwards
5	Steve	Johnson	Nancy	Edwards
7	Robert	King	Michael	Mitchell
8	Laura	Callahan	Michael	Mitchell
1	Andrew	Adams

Advanced SQL Features (Window Functions)

ROW_NUMBER(): Rank tracks by duration within each album


SELECT AlbumId, TrackId, Name, Milliseconds,
       ROW_NUMBER() OVER (PARTITION BY AlbumId ORDER BY Milliseconds DESC) AS TrackRank
FROM Track
WHERE AlbumId IN (1,2)
LIMIT 10;

AlbumId	TrackId	Name	Milliseconds	TrackRank
1	1	For Those About To Rock (We Salute You)	343719	1
1	14	Spellbound	270863	2
1	10	Evil Walks	263497	3
1	12	Breaking The Rules	263288	4
1	7	Let's Get It Up	233926	5
1	8	Inject The Venom	210834	6
1	13	Night Of The Long Knives	205688	7
1	6	Put The Finger On You	205662	8
1	9	Snowballed	203102	9
1	11	C.O.D.	199836	10

Customers with invoices above average total


SELECT CustomerId, Total, AVG(Total) OVER () AS AvgTotal
FROM Invoice
WHERE Total > (SELECT AVG(Total) FROM Invoice)
LIMIT 10;

CustomerId	Total	AvgTotal
38	6	10.1397
41	6	10.1397
8	6	10.1397
14	6	10.1397
43	6	10.1397
15	6	10.1397
13	6	10.1397
44	6	10.1397
16	6	10.1397
42	6	10.1397

Running total of invoice amounts per customer


SELECT CustomerId, InvoiceId, Total,
       SUM(Total) OVER (PARTITION BY CustomerId ORDER BY InvoiceId) AS RunningTotal
FROM Invoice
ORDER BY CustomerId, InvoiceId
LIMIT 10;

CustomerId	InvoiceId	Total	RunningTotal
1	98	4	4
1	121	4	8
1	143	6	14
1	195	1	15
1	316	2	17
1	327	14	31
1	382	9	40
2	1	2	2
2	12	14	16
2	67	9	25

DENSE_RANK(): Employees by hire date


SELECT EmployeeId, FirstName, LastName, HireDate,
       DENSE_RANK() OVER (ORDER BY HireDate) AS HireRank
FROM Employee
LIMIT 10;

EmployeeId	FirstName	LastName	HireDate	HireRank
3	Jane	Peacock	2002-04-01 00:00:00	1
2	Nancy	Edwards	2002-05-01 00:00:00	2
1	Andrew	Adams	2002-08-14 00:00:00	3
4	Margaret	Park	2003-05-03 00:00:00	4
5	Steve	Johnson	2003-10-17 00:00:00	5
6	Michael	Mitchell	2003-10-17 00:00:00	5
7	Robert	King	2004-01-02 00:00:00	6
8	Laura	Callahan	2004-03-04 00:00:00	7

For more details on SQL window functions and advanced queries, see the MySQL documentation.

CustomerId	InvoiceId	Total	RunningTotal
1	98	4	4
1	121	4	8
1	143	6	14
1	195	1	15
1	316	2	17
1	327	14	31
1	382	9	40
2	1	2	2
2	12	14	16
2	67	9	25

CustomerId	InvoiceId	Total	RunningTotal
1	98	4	4
1	121	4	8
1	143	6	14
1	195	1	15
1	316	2	17
1	327	14	31
1	382	9	40
2	1	2	2
2	12	14	16
2	67	9	25

DBeaver SQL Examples