Chinook Examples
These examples depend on this database:
/anvil/projects/tdm/data/chinook/chinook.db
Using chinook, select all of the rows of the table called employees.
Click to see solution
SELECT * FROM Employee;| EmployeeId | LastName | FirstName | Title | ReportsTo | BirthDate | HireDate | Address | City | State | Country | PostalCode | Phone | Fax | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
1 |
Adams |
Andrew |
General Manager |
NA |
1962-02-18 00:00:00 |
2002-08-14 00:00:00 |
11120 Jasper Ave NW |
Edmonton |
AB |
Canada |
T5K 2N1 |
+1 (780) 428-9482 |
+1 (780) 428-3457 |
|
2 |
Edwards |
Nancy |
Sales Manager |
1 |
1958-12-08 00:00:00 |
2002-05-01 00:00:00 |
825 8 Ave SW |
Calgary |
AB |
Canada |
T2P 2T3 |
+1 (403) 262-3443 |
+1 (403) 262-3322 |
|
3 |
Peacock |
Jane |
Sales Support Agent |
2 |
1973-08-29 00:00:00 |
2002-04-01 00:00:00 |
1111 6 Ave SW |
Calgary |
AB |
Canada |
T2P 5M5 |
+1 (403) 262-3443 |
+1 (403) 262-6712 |
|
4 |
Park |
Margaret |
Sales Support Agent |
2 |
1947-09-19 00:00:00 |
2003-05-03 00:00:00 |
683 10 Street SW |
Calgary |
AB |
Canada |
T2P 5G3 |
+1 (403) 263-4423 |
+1 (403) 263-4289 |
|
5 |
Johnson |
Steve |
Sales Support Agent |
2 |
1965-03-03 00:00:00 |
2003-10-17 00:00:00 |
7727B 41 Ave |
Calgary |
AB |
Canada |
T3B 1Y7 |
1 (780) 836-9987 |
1 (780) 836-9543 |
|
6 |
Mitchell |
Michael |
IT Manager |
1 |
1973-07-01 00:00:00 |
2003-10-17 00:00:00 |
5827 Bowness Road NW |
Calgary |
AB |
Canada |
T3B 0C5 |
+1 (403) 246-9887 |
+1 (403) 246-9899 |
|
7 |
King |
Robert |
IT Staff |
6 |
1970-05-29 00:00:00 |
2004-01-02 00:00:00 |
590 Columbia Boulevard West |
Lethbridge |
AB |
Canada |
T1K 5N8 |
+1 (403) 456-9986 |
+1 (403) 456-8485 |
|
8 |
Callahan |
Laura |
IT Staff |
6 |
1968-01-09 00:00:00 |
2004-03-04 00:00:00 |
923 7 ST NW |
Lethbridge |
AB |
Canada |
T1H 1Y8 |
+1 (403) 467-3351 |
+1 (403) 467-8772 |
Using chinook, select the first 5 rows of the employees table.
Click to see solution
SELECT * FROM Employee LIMIT 5;| EmployeeId | LastName | FirstName | Title | ReportsTo | BirthDate | HireDate | Address | City | State | Country | PostalCode | Phone | Fax | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
1 |
Adams |
Andrew |
General Manager |
NA |
1962-02-18 00:00:00 |
2002-08-14 00:00:00 |
11120 Jasper Ave NW |
Edmonton |
AB |
Canada |
T5K 2N1 |
+1 (780) 428-9482 |
+1 (780) 428-3457 |
|
2 |
Edwards |
Nancy |
Sales Manager |
1 |
1958-12-08 00:00:00 |
2002-05-01 00:00:00 |
825 8 Ave SW |
Calgary |
AB |
Canada |
T2P 2T3 |
+1 (403) 262-3443 |
+1 (403) 262-3322 |
|
3 |
Peacock |
Jane |
Sales Support Agent |
2 |
1973-08-29 00:00:00 |
2002-04-01 00:00:00 |
1111 6 Ave SW |
Calgary |
AB |
Canada |
T2P 5M5 |
+1 (403) 262-3443 |
+1 (403) 262-6712 |
|
4 |
Park |
Margaret |
Sales Support Agent |
2 |
1947-09-19 00:00:00 |
2003-05-03 00:00:00 |
683 10 Street SW |
Calgary |
AB |
Canada |
T2P 5G3 |
+1 (403) 263-4423 |
+1 (403) 263-4289 |
|
5 |
Johnson |
Steve |
Sales Support Agent |
2 |
1965-03-03 00:00:00 |
2003-10-17 00:00:00 |
7727B 41 Ave |
Calgary |
AB |
Canada |
T3B 1Y7 |
1 (780) 836-9987 |
1 (780) 836-9543 |
Using chinook, select one or more specific columns from the employees table.
Click to see solution
SELECT LastName, FirstName FROM Employee;| LastName | FirstName |
|---|---|
Adams |
Andrew |
Edwards |
Nancy |
Peacock |
Jane |
Park |
Margaret |
Johnson |
Steve |
Mitchell |
Michael |
King |
Robert |
Callahan |
Laura |
Or, you could switch the order in which the columns are displayed.
SELECT FirstName, LastName FROM Employee;| FirstName | LastName |
|---|---|
Andrew |
Adams |
Nancy |
Edwards |
Jane |
Peacock |
Margaret |
Park |
Steve |
Johnson |
Michael |
Mitchell |
Robert |
King |
Laura |
Callahan |
Using chinook, select only unique values from a column.
Click to see solution
SELECT DISTINCT Title FROM Employee;| Title |
|---|
General Manager |
Sales Manager |
Sales Support Agent |
IT Manager |
IT Staff |
Using chinook, select only employees from the employees table with the first name "Steve".
Click to see solution
SELECT
*
FROM
Employee
WHERE
FirstName = 'Steve';| EmployeeId | LastName | FirstName | Title | ReportsTo | BirthDate | HireDate | Address | City | State | Country | PostalCode | Phone | Fax | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
5 |
Johnson |
Steve |
Sales Support Agent |
2 |
1965-03-03 00:00:00 |
2003-10-17 00:00:00 |
7727B 41 Ave |
Calgary |
AB |
Canada |
T3B 1Y7 |
1 (780) 836-9987 |
1 (780) 836-9543 |
Using chinook, select only employees with the first name "Steve" or first name "Laura".
Click to see solution
SELECT
*
FROM
Employee
WHERE
FirstName = 'Steve'
OR FirstName = 'Laura';| EmployeeId | LastName | FirstName | Title | ReportsTo | BirthDate | HireDate | Address | City | State | Country | PostalCode | Phone | Fax | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
5 |
Johnson |
Steve |
Sales Support Agent |
2 |
1965-03-03 00:00:00 |
2003-10-17 00:00:00 |
7727B 41 Ave |
Calgary |
AB |
Canada |
T3B 1Y7 |
1 (780) 836-9987 |
1 (780) 836-9543 |
|
8 |
Callahan |
Laura |
IT Staff |
6 |
1968-01-09 00:00:00 |
2004-03-04 00:00:00 |
923 7 ST NW |
Lethbridge |
AB |
Canada |
T1H 1Y8 |
+1 (403) 467-3351 |
+1 (403) 467-8772 |
Using chinook, select only employees with the first name "Steve" and the last name "Laura".
Click to see solution
SELECT
*
FROM
Employee
WHERE
FirstName = 'Steve'
AND LastName = 'Laura';| EmployeeId | LastName | FirstName | Title | ReportsTo | BirthDate | HireDate | Address | City | State | Country | PostalCode | Phone | Fax |
|---|
There are no results for this query, meaning there is nobody named "Steve Laura" in this database.
Using chinook, list the first 10 tracks from the tracks table.
Click to see solution
SELECT
*
FROM
Track
LIMIT 10;| TrackId | Name | AlbumId | MediaTypeId | GenreId | Composer | Milliseconds | Bytes | UnitPrice |
|---|---|---|---|---|---|---|---|---|
1 |
For Those About To Rock (We Salute You) |
1 |
1 |
1 |
Angus Young, Malcolm Young, Brian Johnson |
343719 |
11170334 |
0.99 |
2 |
Balls to the Wall |
2 |
2 |
1 |
NA |
342562 |
5510424 |
0.99 |
3 |
Fast As a Shark |
3 |
2 |
1 |
F. Baltes, S. Kaufman, U. Dirkscneider & W. Hoffman |
230619 |
3990994 |
0.99 |
4 |
Restless and Wild |
3 |
2 |
1 |
F. Baltes, R.A. Smith-Diesel, S. Kaufman, U. Dirkscneider & W. Hoffman |
252051 |
4331779 |
0.99 |
5 |
Princess of the Dawn |
3 |
2 |
1 |
Deaffy & R.A. Smith-Diesel |
375418 |
6290521 |
0.99 |
6 |
Put The Finger On You |
1 |
1 |
1 |
Angus Young, Malcolm Young, Brian Johnson |
205662 |
6713451 |
0.99 |
7 |
Let’s Get It Up |
1 |
1 |
1 |
Angus Young, Malcolm Young, Brian Johnson |
233926 |
7636561 |
0.99 |
8 |
Inject The Venom |
1 |
1 |
1 |
Angus Young, Malcolm Young, Brian Johnson |
210834 |
6852860 |
0.99 |
9 |
Snowballed |
1 |
1 |
1 |
Angus Young, Malcolm Young, Brian Johnson |
203102 |
6599424 |
0.99 |
10 |
Evil Walks |
1 |
1 |
1 |
Angus Young, Malcolm Young, Brian Johnson |
263497 |
8611245 |
0.99 |
Using chinook, figure out how many rows or records there are in the tracks table.
Click to see solution
SELECT COUNT(*) FROM Track;| COUNT(*) |
|---|
3503 |
And, of course, you could customize the headers using aliasing.
SELECT
COUNT(*) AS num_tracks
FROM
Track;| num_tracks |
|---|
3503 |
Using chinook, figure out what albums the artist with ArtistId 41 made.
Click to see solution
SELECT * FROM Album WHERE ArtistId = 41;| AlbumId | Title | ArtistId |
|---|---|---|
71 |
Elis Regina-Minha História |
41 |
Using chinook, list the tracks of the album with AlbumId 71. Order the results from most Milliseconds to least.
Click to see solution
SELECT
*
FROM
Track
WHERE
AlbumId = 71
ORDER BY
Milliseconds DESC;| TrackId | Name | AlbumId | MediaTypeId | GenreId | Composer | Milliseconds | Bytes | UnitPrice |
|---|---|---|---|---|---|---|---|---|
890 |
Aprendendo A Jogar |
71 |
1 |
7 |
NA |
290664 |
9391041 |
0.99 |
886 |
Saudosa Maloca |
71 |
1 |
7 |
NA |
278125 |
9059416 |
0.99 |
880 |
Dois Pra Lá, Dois Pra Cá |
71 |
1 |
7 |
NA |
263026 |
8684639 |
0.99 |
887 |
As Aparências Enganam |
71 |
1 |
7 |
NA |
247379 |
8014346 |
0.99 |
882 |
Romaria |
71 |
1 |
7 |
NA |
242834 |
7968525 |
0.99 |
883 |
Alô, Alô, Marciano |
71 |
1 |
7 |
NA |
241397 |
8137254 |
0.99 |
889 |
Maria Rosa |
71 |
1 |
7 |
NA |
232803 |
7592504 |
0.99 |
877 |
O Bêbado e a Equilibrista |
71 |
1 |
7 |
NA |
223059 |
7306143 |
0.99 |
884 |
Me Deixas Louca |
71 |
1 |
7 |
NA |
214831 |
6888030 |
0.99 |
878 |
O Mestre-Sala dos Mares |
71 |
1 |
7 |
NA |
186226 |
6180414 |
0.99 |
Using chinook, display the tracks for the album with AlbumId 71. Order the results from longest to shortest, and convert Milliseconds to seconds. Use aliasing to name the calculated field Seconds.
Click to see solution
SELECT
Milliseconds / 1000.0 AS Seconds,
*
FROM
Track
WHERE
AlbumId = 71
ORDER BY
Seconds DESC;| Seconds | TrackId | Name | AlbumId | MediaTypeId | GenreId | Composer | Milliseconds | Bytes | UnitPrice |
|---|---|---|---|---|---|---|---|---|---|
290.664 |
890 |
Aprendendo A Jogar |
71 |
1 |
7 |
NA |
290664 |
9391041 |
0.99 |
278.125 |
886 |
Saudosa Maloca |
71 |
1 |
7 |
NA |
278125 |
9059416 |
0.99 |
263.026 |
880 |
Dois Pra Lá, Dois Pra Cá |
71 |
1 |
7 |
NA |
263026 |
8684639 |
0.99 |
247.379 |
887 |
As Aparências Enganam |
71 |
1 |
7 |
NA |
247379 |
8014346 |
0.99 |
242.834 |
882 |
Romaria |
71 |
1 |
7 |
NA |
242834 |
7968525 |
0.99 |
241.397 |
883 |
Alô, Alô, Marciano |
71 |
1 |
7 |
NA |
241397 |
8137254 |
0.99 |
232.803 |
889 |
Maria Rosa |
71 |
1 |
7 |
NA |
232803 |
7592504 |
0.99 |
223.059 |
877 |
O Bêbado e a Equilibrista |
71 |
1 |
7 |
NA |
223059 |
7306143 |
0.99 |
214.831 |
884 |
Me Deixas Louca |
71 |
1 |
7 |
NA |
214831 |
6888030 |
0.99 |
186.226 |
878 |
O Mestre-Sala dos Mares |
71 |
1 |
7 |
NA |
186226 |
6180414 |
0.99 |
Using chinook, list the tracks that are at least 250 seconds long.
Click to see solution
SELECT
Milliseconds / 1000.0 AS Seconds,
*
FROM
Track
WHERE
Seconds >= 250;| Seconds | TrackId | Name | AlbumId | MediaTypeId | GenreId | Composer | Milliseconds | Bytes | UnitPrice |
|---|---|---|---|---|---|---|---|---|---|
343.719 |
1 |
For Those About To Rock (We Salute You) |
1 |
1 |
1 |
Angus Young, Malcolm Young, Brian Johnson |
343719 |
11170334 |
0.99 |
342.562 |
2 |
Balls to the Wall |
2 |
2 |
1 |
NA |
342562 |
5510424 |
0.99 |
252.051 |
4 |
Restless and Wild |
3 |
2 |
1 |
F. Baltes, R.A. Smith-Diesel, S. Kaufman, U. Dirkscneider & W. Hoffman |
252051 |
4331779 |
0.99 |
375.418 |
5 |
Princess of the Dawn |
3 |
2 |
1 |
Deaffy & R.A. Smith-Diesel |
375418 |
6290521 |
0.99 |
263.497 |
10 |
Evil Walks |
1 |
1 |
1 |
Angus Young, Malcolm Young, Brian Johnson |
263497 |
8611245 |
0.99 |
263.288 |
12 |
Breaking The Rules |
1 |
1 |
1 |
Angus Young, Malcolm Young, Brian Johnson |
263288 |
8596840 |
0.99 |
270.863 |
14 |
Spellbound |
1 |
1 |
1 |
Angus Young, Malcolm Young, Brian Johnson |
270863 |
8817038 |
0.99 |
331.180 |
15 |
Go Down |
4 |
1 |
1 |
AC/DC |
331180 |
10847611 |
0.99 |
366.654 |
17 |
Let There Be Rock |
4 |
1 |
1 |
AC/DC |
366654 |
12021261 |
0.99 |
267.728 |
18 |
Bad Boy Boogie |
4 |
1 |
1 |
AC/DC |
267728 |
8776140 |
0.99 |
Using chinook, list the tracks that are between 250 and 300 seconds long.
Click to see solution
SELECT
Milliseconds / 1000.0 AS Seconds,
*
FROM
Track
WHERE
Seconds BETWEEN 250 AND 300
ORDER BY
Seconds;| Seconds | TrackId | Name | AlbumId | MediaTypeId | GenreId | Composer | Milliseconds | Bytes | UnitPrice |
|---|---|---|---|---|---|---|---|---|---|
250.017 |
1992 |
Lithium |
163 |
1 |
1 |
Kurt Cobain |
250017 |
8148800 |
0.99 |
250.031 |
3421 |
Nimrod (Adagio) from Variations On an Original Theme, Op. 36 "Enigma" |
290 |
2 |
24 |
Edward Elgar |
250031 |
4124707 |
0.99 |
250.070 |
2090 |
Romance Ideal |
169 |
1 |
7 |
NA |
250070 |
8260477 |
0.99 |
250.122 |
2451 |
Ela Desapareceu |
199 |
1 |
1 |
Chico Amaral/Samuel Rosa |
250122 |
8289200 |
0.99 |
250.226 |
2184 |
Thumbing My Way |
180 |
1 |
1 |
Eddie Vedder |
250226 |
8201437 |
0.99 |
250.253 |
2728 |
Pulse |
220 |
1 |
4 |
The Tea Party |
250253 |
8183872 |
0.99 |
250.357 |
974 |
Edge Of The World |
77 |
1 |
4 |
Faith No More |
250357 |
8235607 |
0.99 |
250.462 |
1530 |
Sem Sentido |
123 |
1 |
7 |
NA |
250462 |
8292108 |
0.99 |
250.565 |
3371 |
Wooden Jesus |
269 |
2 |
23 |
NA |
250565 |
4302603 |
0.99 |
250.697 |
2504 |
Real Love |
202 |
1 |
4 |
Billy Corgan |
250697 |
8025896 |
0.99 |