Queries

Overview

An SQL query is any statement that returns rows or records. Typically, a query will start with the SELECT or SELECT DISTINCT keywords. For example, the following is a simple SQL query.

SELECT * FROM employees WHERE years_of_service > 5;

In this example:

  • SELECT is a statement keyword.

  • * is an identifier or selector that means "select all columns".

  • FROM is a clause that tells the engine from where (which table) we are selecting all of the columns.

  • employees is an identifier that specifies the "employees" table in the database.

  • WHERE is an optional clause that specifies under what conditions the engine is to return a record or row.

  • years_of_service is an identifier that specifies a specific column called "years_of_service" in the "employees" table.

  • > is an operator that compares two values, and returns TRUE or FALSE.

  • 5 is an integer.

  • Collectively, years_of_service > 5 is a predicate.

Ultimately, this query will return all columns from all rows from the "employees" table where the "years_of_service" column has a value greater than 5.

Examples

Using the SQLite chinook database here, select all of the rows of the table called employees.

Solution
SELECT * FROM employees;
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

[email protected]

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

[email protected]

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

[email protected]

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

[email protected]

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

[email protected]

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

[email protected]

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

[email protected]

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

[email protected]

Using the SQLite chinook database here, select the first 5 rows of the employees table.

Solution
SELECT * FROM employees 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

[email protected]

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

[email protected]

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

[email protected]

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

[email protected]

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

[email protected]

Using the SQLite chinook database here, select one or more specific columns from the employees table.

Solution
SELECT LastName, FirstName FROM employees;
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 employees;
FirstName LastName

Andrew

Adams

Nancy

Edwards

Jane

Peacock

Margaret

Park

Steve

Johnson

Michael

Mitchell

Robert

King

Laura

Callahan

Using the SQLite chinook database here, select only unique values from a column.

Solution
SELECT DISTINCT Title FROM employees;
Title

General Manager

Sales Manager

Sales Support Agent

IT Manager

IT Staff

Using the SQLite chinook database here, select only employees from the employees table with the first name "Steve".

Solution
SELECT
	*
FROM
	employees
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

[email protected]

Using the SQLite chinook database here, select only employees with the first name "Steve" or last name "Laura".

Solution
SELECT
	*
FROM
	employees
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

[email protected]

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

[email protected]

Using the SQLite chinook database here, select only employees with the first name "Steve" and the last name "Laura".

Solution
SELECT
	*
FROM
	employees
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. That means there was nobody named "Steve Laura" in this database.

Using the SQLite chinook database here, list the first 10 tracks from the tracks table.

Solution
SELECT
	*
FROM
	tracks
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 the SQLite chinook database here, figure out how many rows or records there are in the tracks table.

Solution
SELECT COUNT(*) FROM tracks;
COUNT(*)

3503

And, of course, you could customize the headers using aliasing.

SELECT
	COUNT(*) AS num_tracks
FROM
	tracks;
num_tracks

3503

Are there any artists with the names: "Elis Regina", "Seu Jorge" or "The Beatles" in this database?

Solution
SELECT
	*
FROM
	artists
WHERE
	Name = 'Elis Regina'
	OR Name = 'Seu Jorge'
	OR Name = 'The Beatles';
ArtistId Name

41

Elis Regina

193

Seu Jorge

Using the SQLite chinook database here, figure out what albums the artist with ArtistId 41 made.

Solution
SELECT * FROM albums WHERE ArtistId = 41;
AlbumId Title ArtistId

71

Elis Regina-Minha História

41

Using the SQLite chinook database here, list the tracks of the album with AlbumId 71. Order the results from most Milliseconds to least.

Solution
SELECT
	*
FROM
	tracks
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 the SQLite chinook database here, 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.

Solution
SELECT
	Milliseconds / 1000.0 AS Seconds,
	*
FROM
	tracks
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 the SQLite chinook database here, list the tracks that are at least 250 seconds long.

Solution
SELECT
	Milliseconds / 1000.0 AS Seconds,
	*
FROM
	tracks
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 the SQLite chinook database here, list the tracks that are between 250 and 300 seconds long.

Solution
SELECT
	Milliseconds / 1000.0 AS Seconds,
	*
FROM
	tracks
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 the SQLite chinook database here, find the GenreId of the genre with the name Pop.

Solution
SELECT GenreId FROM genres WHERE Name = 'Pop';
GenreId

9

Using the SQLite lahman database here, display the first 10 ballparks in the ballparks table.

Solution
SELECT * FROM parks LIMIT 10;
ID parkalias parkkey parkname city state country

1

NA

ALB01

Riverside Park

Albany

NY

US

2

NA

ALT01

Columbia Park

Altoona

PA

US

3

Edison Field; Anaheim Stadium

ANA01

Angel Stadium of Anaheim

Anaheim

CA

US

4

NA

ARL01

Arlington Stadium

Arlington

TX

US

5

The Ballpark in Arlington; Ameriquest Fl

ARL02

Rangers Ballpark in Arlington

Arlington

TX

US

6

NA

ATL01

Atlanta-Fulton County Stadium

Atlanta

GA

US

7

NA

ATL02

Turner Field

Atlanta

GA

US

8

NA

ATL03

Suntrust Park

Atlanta

GA

US

9

NA

BAL01

Madison Avenue Grounds

Baltimore

MD

US

10

NA

BAL02

Newington Park

Baltimore

MD

US

Using the SQLite lahman database here, make a list of the names of all of the inactive teams in baseball history.

Solution
SELECT franchName FROM teamsfranchises WHERE active=='N' LIMIT 10;
franchName

Altoona Mountain City

Philadelphia Athletics

Buffalo Bisons

Buffalo Bisons

Baltimore Orioles

Baltimore Terrapins

Baltimore Monumentals

Boston Reds

Brooklyn Gladiators

Boston Reds

Using the SQLite lahman database here, find the player with the most runs batted in (RBI’s) in a season, using 2 queries. In the first query, find the playerID of the player with the most RBI’s. In the second query find the player’s name in the people table.

Solution
-- Find the playerID
SELECT playerID FROM batting WHERE RBI==191;

-- Display the name
SELECT
	nameFirst,
	nameLast
FROM
	people
WHERE
	playerID == 'wilsoha01';
playerID

wilsoha01

Using the SQLite lahman database here, find out who was the manager of the 1976 "Big Red Machine" (CIN)? Answer this question using 2 queries.

Solution

The "Big Red Machine" was a famous nickname for the dominant Cincinnati Reds of the early 1970s. Many of its team members are Hall of Famers, including their manager, Sparky Anderson.

SELECT
	playerID
FROM
	managers
WHERE
	yearID == 1976
	AND teamID == 'CIN';

SELECT
	nameFirst,
	nameLast
FROM
	people
WHERE
	playerID == 'andersp01';
playerID

andersp01

Using the SQLite lahman database here, make a list of the `teamID’s that were managed by Tony LaRussa. Answer this question using 2 queries.

Solution

Tony LaRussa is very well known for being a manager that was involved in baseball for a very long time. He won the World Series with the St. Louis Cardinals and the Oakland Athletics.

SELECT
	playerID
FROM
	people
WHERE
	nameLast == 'LaRussa'
	AND nameFirst == 'Tony';

SELECT DISTINCT
	teamID
FROM
	managers
WHERE
	playerID == 'larusto01';
playerID

larusto01

Using the SQLite lahman database here, figure out what Cecil Fielder’s salary was in 1987. Display the teamID with the salary.

Solution
SELECT
	playerID
FROM
	people
WHERE
	nameFirst == 'Cecil'
	AND nameLast == 'Fielder';

SELECT
	teamID,
	salary
FROM
	salaries
WHERE
	playerID == 'fieldce01'
	AND yearID == 1987;
teamID salary

TOR

78500.0

Using the SQLite lahman database here, make a list of all the teams who’ve lost a world series (WS) since 1990. Put the list in ascending order by yearID.

Solution
SELECT
	teamIDloser,
	yearID
FROM
	seriespost
WHERE
	yearID >= 1990
	AND round == 'WS'
ORDER BY
	yearID ASC
LIMIT 10;
teamIDloser yearID

OAK

1990

ATL

1991

ATL

1992

PHI

1993

CLE

1995

ATL

1996

CLE

1997

SDN

1998

ATL

1999

NYN

2000

Using the SQLite lahman database here, find out what Cal Ripken, Jr.'s height and weight was. Did he bat right or left-handed? When did he play his final game? Find all of this information out in a single query.

Solution
SELECT
	height,
	weight,
	bats,
	finalgame
FROM
	people
WHERE
	nameFirst == 'Cal'
	AND nameLast == 'Ripken'
	AND deathState IS NULL;
height weight bats finalGame

76

200

R

2001-10-06

Using the SQLite lahman database here, select all of the `playerID’s and `yearID’s of the players who were inducted into the hall of fame and voted in by the veterans committee between 1990 and 2000. Put the list in descending order.

Solution

The veterans committee in the Hall of Fame voting process place players in the hall of fame that are forgotten by the writers, fans, etc. This is a way for players to recognize who they think were the greatest players of all time, or are skipped over for a variety of reasons. This is one reason why there is a lot of scrutiny in the process for how players are selected to the baseball hall of fame.

SELECT
	playerID,
	yearID
FROM
	halloffame
WHERE
	votedBy == 'Veterans'
	AND inducted == 'Y'
	AND yearID BETWEEN 1990 AND 2000
ORDER BY
	yearID DESC
LIMIT 10;
playerID yearid

andersp01

2000

mcphebi01

2000

steartu99

2000

cepedor01

1999

chylane99

1999

seleefr99

1999

willijo99

1999

davisge01

1998

dobyla01

1998

macphle99

1998

Using the SQLite lahman database here, get a list of attendance by season for the Toronto Blue Jays (TOR). What season had the highest attendance?

Solution

The Toronto Blue Jays were the 1993 season’s World Series champion. This means that, yes, a non-USA team has won the World Series for baseball!

SELECT
	yearkey,
	attendance
FROM
	homegames
WHERE
	teamkey == 'TOR'
ORDER BY
	attendance DESC
LIMIT 10;
yearkey attendance

1993

4057747

1992

4028318

1991

4001526

1990

3884384

2016

3392099

2017

3203886

1994

2907949

1995

2826445

2015

2794891

1987

2778459

Using the SQLite lahman database here, figure out how many different leagues have represented Major League Baseball over time.

Solution

Major League Baseball has had several leagues that have been represented in its history. There are only two current leagues: National League and the American League.

SELECT DISTINCT league FROM leagues;
league

American Association

American League

Federal League

Major League

National Association

National League

Players' League

Union Association

Using the SQLite lahman database here, find the teams that have won the world series.

Solution
SELECT teamID, yearID FROM teams WHERE WSWin=='Y' LIMIT 10;
teamID yearID

PRO

1884

SL4

1886

DTN

1887

NY1

1888

NY1

1889

BOS

1903

NY1

1905

CHA

1906

CHN

1907

CHN

1908

Using the SQLite lahman database here, list the top 10 season win totals for teams. Include the yearID and the teamID.

Solution
SELECT teamID, yearID, W FROM teams ORDER BY W DESC LIMIT 10;
teamID yearID W

CHN

1906

116

SEA

2001

116

NYA

1998

114

CLE

1954

111

PIT

1909

110

NYA

1927

110

NYA

1961

109

BAL

1969

109

BAL

1970

108

CIN

1975

108

Using the SQLite lahman database here, list the pitchers with their teamID, wins (W), and losses (L) that threw complete games (CG) in the 1995 season. Include the number of complete games as well.

Solution
SELECT
	playerID,
	teamID,
	W,
	L,
	CG
FROM
	pitching
WHERE
	CG > 0
	AND yearID == 1995
ORDER BY
	W DESC
LIMIT 10;
playerID teamID W L CG

maddugr01

ATL

19

2

10

mussimi01

BAL

19

9

7

johnsra05

SEA

18

2

6

schoupe01

CIN

18

7

2

martira02

LAN

17

7

4

rogerke01

TEX

17

7

3

glavito02

ATL

16

7

3

hershor01

CLE

16

6

1

nagych01

CLE

16

6

2

wakefti01

BOS

16

8

6

Using the SQLite lahman database here, get a printout of the hits (H), and home runs (HR) for Ichiro Suzuki’s career. Answer this question using 2 queries. In the first query, find his playerID. In the second query, list the teamID, yearID, hits (H), and home runs (HR).

Solution

Ichiro Suzuki is regarded as one of the greatest hitters of all time because of his prowess in both American and Japanese professional baseball.

SELECT
	playerID
FROM
	people
WHERE
	nameFirst == 'Ichiro'
	AND nameLast == 'Suzuki';

SELECT
	teamID,
	yearID,
	H,
	HR
FROM
	batting
WHERE
	playerID == 'suzukic01';
teamID yearID H HR

SEA

2001

242

8

SEA

2002

208

8

SEA

2003

212

13

SEA

2004

262

8

SEA

2005

206

15

SEA

2006

224

9

SEA

2007

238

6

SEA

2008

213

6

SEA

2009

225

11

SEA

2010

214

6

Using the SQLite lahman database here, figure out how many walks (BB) and strikeouts (SO) Mariano Rivera had in the playoffs. Which year did he give up the most post-season walks? Answer this question using 2 queries.

Solution

More men have walked on the moon than have scored a run on Mariano Rivera in a playoff game. Mariano Rivera made the hall of fame in 2019.

SELECT
	playerID
FROM
	people
WHERE
	nameFirst == 'Mariano'
	AND nameLast == 'Rivera';

SELECT
	yearID,
	teamID,
	BB,
	SO
FROM
	pitchingpost
WHERE
	playerID == 'riverma01'
ORDER BY
	BB DESC;
yearID teamID BB SO

1996

NYA

3

4

2004

NYA

2

6

2009

NYA

2

4

2009

NYA

2

3

1995

NYA

1

8

1996

NYA

1

5

1996

NYA

1

1

1998

NYA

1

5

1998

NYA

1

2

1999

NYA

1

3

Using the SQLite lahman database here, find the pitcher with the most strikeouts (SO), and the batter that struck out the most in the 2014 season. Get the first and last name of the pitcher and the batter. Use 3 queries to figure this out.

Solution

Corey Kluber is a two-time AL Cy Young winner. He is well known for his two-seam fastball that is difficult to hit.

SELECT
	playerID,
	SO
FROM
	pitching
WHERE
	yearID == 2014
ORDER BY
	SO DESC
LIMIT (10);

SELECT
	playerID,
	SO
FROM
	batting
WHERE
	yearID == 2014
ORDER BY
	SO DESC
LIMIT (10);

SELECT
	nameFirst,
	nameLast
FROM
	people
WHERE
	playerID == "klubeco01"
	OR playerID == "howarry01";
nameFirst nameLast

Ryan

Howard

Corey

Kluber

Using the SQLite lahman database here, figure out how many teams Bartolo Colon pitched for.

Solution

Bartolo Colon is a well-known journeyman pitcher in baseball. He has pitched with a lot of teams, but it wasn’t until he played for the New York Mets when he needed to come to the plate. He had a weird batting stance that is funny to watch. He even hit a home run one season!

SELECT
	playerID
FROM
	people
WHERE
	nameFirst == 'Bartolo'
	AND nameLast == 'Colon';

SELECT COUNT(DISTINCT teamID) FROM pitching WHERE playerID == 'colonba01';
COUNT(DISTINCT teamID)

12

Using the SQLite lahman database here, figure out how many times Trevor Bauer came to bat (AB) in 2016. How many hits did he get? Use 2 queries to answer this question.

Solution

Trevor Bauer is much more known for his pitching than he is known for hitting. This is common for pitchers, as many are not very good at hitting.

SELECT
	playerID
FROM
	people
WHERE
	nameFirst == "Trevor"
	AND nameLast == "Bauer";

SELECT
	AB,
	H
FROM
	batting
WHERE
	playerID == "bauertr01"
	AND yearID == "2016";
AB H

5

0

Using the SQLite lahman database here, compare Mike Trout and Giancarlo Stanton by season. Who hit more RBI’s in a season? Who has been caught stealing (CS) most in a season? Use 3 queries to answer these questions.

Solution

Mike Trout and Giancarlo Stanton are considered two of the of the best hitters in Major League Baseball for very different reasons. Trout is an all-around player known for being indispensible, where Stanton is known as a power hitter.

SELECT
	playerID,
	nameFirst,
	nameLast
FROM
	people
WHERE (nameFirst == 'Giancarlo'
	AND nameLast == 'Stanton')
	OR(nameFirst == 'Mike'
		AND nameLast == 'Trout');

SELECT
	playerID,
	yearID,
	teamID,
	RBI,
	CS
FROM
	batting
WHERE
	playerID == 'stantmi03'
	OR playerID == 'troutmi01'
ORDER BY
	RBI DESC
LIMIT 1;
playerID yearID teamID RBI CS

stantmi03

2017

MIA

132

2

SELECT
	playerID,
	yearID,
	teamID,
	RBI,
	CS
FROM
	batting
WHERE
	playerID == 'stantmi03'
	OR playerID == 'troutmi01'
ORDER BY
	CS DESC
LIMIT 1;
playerID yearID teamID RBI CS

troutmi01

2013

LAA

97

7

Using the SQLite lahman database here, make a list of players who walked (BB) more than they struck out (SO) between 1980 and 1985. Of these players, who walked the most? Use the BETWEEN clause in your queries. Use multiple queries.

Solution
SELECT
	playerID,
	yearID,
	teamID,
	BB,
	SO
FROM
	batting
WHERE
	BB > SO
LIMIT 10;
playerID yearID teamID BB SO

addybo01

1871

RC1

4

0

ansonca01

1871

RC1

2

1

barkeal01

1871

RC1

1

0

barnero01

1871

BS1

13

1

battijo01

1871

CL1

1

0

bealsto01

1871

WS3

2

0

bellast01

1871

TRO

9

2

berthha01

1871

WS3

4

2

biermch01

1871

FW1

1

0

birdge01

1871

RC1

3

2

SELECT nameFirst, nameLast FROM people WHERE playerID=='randowi01';
nameFirst nameLast

Willie

Randolph

Using the SQLite lahman database here, figure out how many different National League (lgID == 'NL') catchers (`C) won the gold glove between 1990 and 2000.

Solution
SELECT DISTINCT
	playerID
FROM
	awardsplayers
WHERE
	awardID == 'Gold Glove'
	AND notes == 'C'
	AND lgID == 'NL'
	AND yearID BETWEEN 1990 AND 2000;
playerID

santibe01

pagnoto01

manwaki01

johnsch04

liebemi01

mathemi01

Using the SQLite lahman database here, figure out how many different 3rd basemen played for the Seattle Mariners between 2000 and 2005. Who had the most errors?

Solution
SELECT DISTINCT
	playerID,
	yearID,
	E
FROM
	fielding
WHERE
	yearID BETWEEN 2000 AND 2005
	AND teamID == 'SEA'
	AND POS == '3B'
ORDER BY
	E DESC
LIMIT 10;
playerID yearID E

guillca01

2000

17

bellda01

2001

14

beltrad01

2005

14

bellda01

2000

12

cirilje01

2002

9

leoneju01

2004

8

mclemma01

2001

7

spiezsc01

2004

7

bloomwi01

2004

5

mabryjo01

2000

4

SELECT
	nameFirst,
	nameLast
FROM
	people
WHERE
	playerID == 'camermi01';
nameFirst nameLast

Mike

Cameron

Using the SQLite lahman database here, figure out what seasons Craig Biggio played catcher. Craig Biggio was known for playing second base, however, did not always play second base. Solve this problem using 2 queries.

Solution
SELECT
	playerID
FROM
	people
WHERE
	nameFirst == 'Craig'
	AND nameLast == 'Biggio';
playerID

biggicr01

SELECT
	teamID,
	yearID,
	POS
FROM
	fielding
WHERE
	playerID == 'biggicr01'
	AND POS == 'C';
teamID yearID POS

HOU

1988

C

HOU

1989

C

HOU

1990

C

HOU

1991

C

HOU

2007

C

Using the SQLite lahman database here, find the teams representing the National League, that have won the world series. Desplay the list with the yearID and the teamID in ascending order.

Solution
SELECT
	teamID,
	yearID
FROM
	teams
WHERE
	WSWin == 'Y'
	AND lgID == 'NL'
ORDER BY
	yearID ASC
LIMIT 10;
teamID yearID

PRO

1884

DTN

1887

NY1

1888

NY1

1889

NY1

1905

CHN

1907

CHN

1908

PIT

1909

BSN

1914

CIN

1919

Using the SQLite lahman database here, list the pitchers that threw at least one complete game (CG) in the 1995 season. Please include the wins and losses of the top 10 pitchers. Use the playerID of the pitcher who threw the most complete games to find out the name of the pitcher that had the most complete games.

Solution
SELECT
	playerID,
	W,
	L,
	CG
FROM
	pitching
WHERE
	CG > 0
	AND yearID == 1995
ORDER BY
	CG DESC
LIMIT 10;
playerID W L CG

maddugr01

19

2

10

mcdowja01

15

10

8

ericksc01

9

4

7

leitema01

10

12

7

mussimi01

19

9

7

johnsra05

18

2

6

valdeis01

13

11

6

wakefti01

16

8

6

coneda01

9

6

5

fernaal01

12

8

5

SELECT
	nameFirst,
	nameLast
FROM
	people
WHERE
	playerID == 'maddugr01';
nameFirst nameLast

Greg

Maddux

Who was the most recent player manager?

Solution
SELECT
	playerID,
	yearID
FROM
	managers
WHERE
	plyrMgr == 'Y'
ORDER BY
	yearID DESC
LIMIT 10;
playerID yearID

rosepe01

1986

rosepe01

1985

rosepe01

1984

kessido01

1979

torrejo01

1977

robinfr02

1976

robinfr02

1975

tappeel01

1962

bauerha01

1961

hemusso01

1959

SELECT
	nameFirst,
	nameLast
FROM
	people
WHERE
	playerID == 'rosepe01';
nameFirst nameLast

Pete

Rose

Using the SQLite lahman database here, get the at-bats, homeruns, stolen bases for Roberto Clements by year in ascending order.

Solution
SELECT
	playerID
FROM
	people
WHERE
	nameFirst == 'Roberto'
	AND nameLast == 'Clemente';
playerID

clemero01

SELECT
	yearID,
	AB,
	HR,
	SB
FROM
	battingpost
WHERE
	playerID == 'clemero01'
ORDER BY
	yearID ASC;
yearID AB HR SB

1960

29

0

0

1970

14

0

0

1971

18

0

0

1971

29

2

0

1972

17

1

0

Using the SQLite lahman database here, get a list of distinct world series winners during the years where Tom Lasorda managed the Los Angeles Dodgers (LAN). First, find the years Tom Lasorda was the manager of LAN, and then find the distinct teams that won a world series in that time frame.

Solution
SELECT
	playerID
FROM
	people
WHERE
	nameFirst == 'Tom'
	AND nameLast == 'Lasorda';
playerID

lasorto01

SELECT
	yearID
FROM
	managers
WHERE
	playerID == 'lasorto01'
LIMIT 10;
yearID

1976

1977

1978

1979

1980

1981

1982

1983

1984

1985

SELECT DISTINCT
	teamID
FROM
	teams
WHERE
	WSWin == 'Y'
	AND yearID BETWEEN 1976 AND 1996;
teamID

CIN

NYA

PIT

PHI

LAN

SLN

BAL

DET

KCA

NYN

Using the SQLite lahman database here, figure out which teams Kenny Lofton stole more than 20 bases from in a single season. Limit results to seasons after the year 2000.

Solution
SELECT
	playerID
FROM
	people
WHERE
	nameFirst == 'Kenny'
	AND nameLast == 'Lofton';
playerID

loftoke01

SELECT
	teamID,
	yearID,
	SB
FROM
	batting
WHERE
	playerID == 'loftoke01'
	AND SB > 20
	AND yearID > 2000;
teamID yearID SB

CHA

2002

22

PHI

2005

22

LAN

2006

32

TEX

2007

21

Using the SQLite lahman database here, figure out how much the Tampa Bay Rays paid Wade Boggs in 1998. Who paid Boggs the most in a single season during his career?

Solution
SELECT
	playerID
FROM
	people
WHERE
	nameFirst == 'Wade'
	AND nameLast == 'Boggs';
playerID

boggswa01

SELECT
	teamID,
	yearID,
	salary
FROM
	salaries
WHERE
	playerID == 'boggswa01'
	AND yearID == 1998;
teamID yearID salary

TBA

1998

1150000

SELECT
	teamID,
	yearID,
	salary
FROM
	salaries
WHERE
	playerID == 'boggswa01'
ORDER BY
	salary DESC
LIMIT 10;
teamID yearID salary

NYA

1995

4724316

NYA

1994

3200000

NYA

1993

2950000

BOS

1991

2750000

BOS

1992

2700000

NYA

1996

2050000

NYA

1997

2000000

BOS

1990

1900000

BOS

1989

1850000

BOS

1987

1675000

Using the SQLite lahman database here, get the teamID, wins (W), and losses (L) for the National League in 2015.

Solution
SELECT
	teamID,
	W,
	L
FROM
	teams
WHERE
	divID == 'E'
	AND lgID == 'NL'
	AND yearID == 2015
ORDER BY
	teamrank ASC;
teamID W L

NYN

90

72

WAS

83

79

MIA

71

91

ATL

67

95

PHI

63

99

Using the SQLite lahman database here, make a list of teams, wins, losses, and years for National League East teams that have won the world series. Which team had the most wins?

Solution
SELECT
	teamID,
	yearID,
	W,
	L
FROM
	teams
WHERE
	lgID == 'NL'
	AND divID == 'E'
	AND WSWin == 'Y'
ORDER BY
	W DESC;
teamID yearID W L

NYN

1986

108

54

NYN

1969

100

62

PIT

1979

98

64

PIT

1971

97

65

WAS

2019

93

69

SLN

1982

92

70

FLO

1997

92

70

PHI

2008

92

70

PHI

1980

91

71

FLO

2003

91

71

Using the SQLite lahman database here, get a list of `playerID’s of managers who won more games than they lost between 1930 and 1950. Get the manager’s name, and the name of the associated team for the winningest manager in that time period.

Solution
SELECT
	playerID,
	teamID,
	yearID,
	W,
	L
FROM
	managers
WHERE
	yearID BETWEEN 1930 AND 1950
	AND W > L
ORDER BY
	W DESC
LIMIT 10;
playerID teamID yearID W L

mackco01

PHA

1931

107

45

mccarjo99

NYA

1932

107

47

mccarjo99

NYA

1939

106

45

southbi01

SLN

1942

106

48

southbi01

SLN

1943

105

49

southbi01

SLN

1944

105

49

durocle01

BRO

1942

104

50

cronijo01

BOS

1946

104

50

mccarjo99

NYA

1942

103

51

mackco01

PHA

1930

102

52

SELECT
	nameFirst,
	nameLast
FROM
	people
WHERE
	playerID == 'mackco01';
nameFirst nameLast

Connie

Mack

SELECT
	franchName
FROM
	teamsfranchises
WHERE
	franchID == 'PHA';
franchName

Philadelphia Athletics

Using the SQLite lahman database here, get the top 5 seasons (for overall attendance) for Florida teams (Florida Marlins, Tampa Bay Rays, and Miami Marlins). How many of the top 5 seasons by attendance have been since 2000?

Solution
SELECT
	teamID,
	yearID,
	attendance
FROM
	teams
WHERE
	franchID == 'TBD'
	OR franchID == 'FLA'
ORDER BY
	attendance DESC
LIMIT 10;
teamID yearID attendance

FLO

1993

3064847

TBA

1998

2506293

FLO

1997

2364387

MIA

2012

2219444

FLO

1994

1937467

TBA

2009

1874962

FLO

2005

1852608

TBA

2010

1843445

TBA

2008

1811986

MIA

2015

1752235

Using the SQLite lahman database here, figure out which pitcher has thrown the most shutouts (SHO) in the American League since 2010? What about for the National League? Get the first and last names of the pitchers.

Solution
SELECT
	playerID,
	teamID,
	yearID,
	SHO
FROM
	pitching
WHERE
	yearID > 2010
	AND lgID == 'NL'
ORDER BY
	SHO DESC
LIMIT 10;
playerID teamID yearID SHO

leecl02

PHI

2011

6

dickera01

NYN

2012

3

alvarhe01

MIA

2014

3

wainwad01

SLN

2014

3

arrieja01

CHN

2015

3

kershcl01

LAN

2015

3

scherma01

WAS

2015

3

kershcl01

LAN

2016

3

carpech01

SLN

2011

2

garcija02

SLN

2011

2

SELECT
	playerID,
	teamID,
	yearID,
	SHO
FROM
	pitching
WHERE
	yearID > 2010
	AND lgID == 'AL'
ORDER BY
	SHO DESC
LIMIT 10;
playerID teamID yearID SHO

hernafe02

SEA

2012

5

hollade01

TEX

2011

4

shielja02

TBA

2011

4

harenda01

LAA

2011

3

vargaja01

SEA

2011

3

morrobr01

TOR

2012

3

colonba01

OAK

2013

3

masteju01

CLE

2013

3

porceri01

DET

2014

3

klubeco01

CLE

2017

3

SELECT
	nameFirst,
	nameLast
FROM
	people
WHERE
	playerID == 'leecl02'
	OR playerID == 'hernafe02';
nameFirst nameLast

Felix

Hernandez

Cliff

Lee

=== Resources