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 Email

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

andrew@chinookcorp.com

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

nancy@chinookcorp.com

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

jane@chinookcorp.com

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

margaret@chinookcorp.com

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

steve@chinookcorp.com

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

michael@chinookcorp.com

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

robert@chinookcorp.com

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

laura@chinookcorp.com

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 Email

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

andrew@chinookcorp.com

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

nancy@chinookcorp.com

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

jane@chinookcorp.com

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

margaret@chinookcorp.com

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

steve@chinookcorp.com

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 Email

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

steve@chinookcorp.com

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 Email

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

steve@chinookcorp.com

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

laura@chinookcorp.com

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 Email

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

Using chinook, find the GenreId of the genre with the name Pop.

Click to see solution
SELECT GenreId FROM Genre WHERE Name = 'Pop';
GenreId

9