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:
-
SELECTis a statement keyword. -
*is an identifier or selector that means "select all columns". -
FROMis a clause that tells the engine from where (which table) we are selecting all of the columns. -
employeesis an identifier that specifies the "employees" table in the database. -
WHEREis an optional clause that specifies under what conditions the engine is to return a record or row. -
years_of_serviceis 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. -
5is an integer. -
Collectively,
years_of_service > 5is 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
|
Here’s a list of the locations in Anvil for the databases used in the examples:
|
Using chinook, select all of the rows of the table called employees.
Click to see solution
SELECT * FROM employees;| 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 employees 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 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 chinook, select only unique values from a column.
Click to see solution
SELECT DISTINCT Title FROM employees;| 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
employees
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 last name "Laura".
Click to see solution
SELECT
*
FROM
employees
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
employees
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
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 chinook, figure out how many rows or records there are in the tracks table.
Click to see 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 |
Using chinook, figure out what albums the artist with ArtistId 41 made.
Click to see solution
SELECT * FROM albums 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
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 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
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 chinook, list the tracks that are at least 250 seconds long.
Click to see 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 chinook, list the tracks that are between 250 and 300 seconds long.
Click to see 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 chinook, find the GenreId of the genre with the name Pop.
Click to see solution
SELECT GenreId FROM genres WHERE Name = 'Pop';| GenreId |
|---|
9 |
Using lahman, display the first 10 ballparks in the ballparks table.
Click to see 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 lahman, make a list of the names of all of the inactive teams in baseball history.
Click to see 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 lahman, find the player with the most runs batted in (RBIs) in a season, using 2 queries. In the first query, find the playerID of the player with the most RBIs. In the second query find the player’s name in the people table.
Click to see 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 lahman, figure out the manager of the 1976 "Big Red Machine" (CIN)? Answer this question using 2 queries.
Click to see solution
The "Big Red Machine" was a famous nickname for the dominant Cincinnati Reds of the early 1970s. Many of its team members are HOFers, 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 lahman, make a teamID list for teams that were managed by Tony LaRussa. Answer this question using 2 queries.
Click to see solution
Tony LaRussa is renowned for being a very successful, long-time manager. 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 lahman, figure out what Cecil Fielder’s salary was in 1987. Display the teamID with the salary.
Click to see 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 lahman, make a list of all the teams who’ve lost a World Series (WS) since 1990. Put the list in ascending order by yearID.
Click to see 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 lahman, 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.
Click to see 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 lahman, select all playerID and yearID 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.
Click to see solution
The Hall of Fame Veterans Committee nominates Hall of Fame players that are forgotten, so to say, in baseball history. The committee allows players to recognize and give justice to their underrated, underviewed, or undervalued peers.
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 lahman, get a list of attendance by season for the Toronto Blue Jays (TOR). What season had the highest attendance?
Click to see solution
The Toronto Blue Jays were the 1993 World Series champion, meaning not all World Series-winning teams in MLB are American.
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 lahman, figure out how many different leagues have represented Major League Baseball over time.
Click to see solution
Major League Baseball has had several leagues that have been represented in its history, though the only leagues currently are the 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 lahman, find the teams that have won the World Series.
Click to see 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 lahman, list the top 10 seasons in terms of win totals. Include the yearID and the teamID.
Click to see 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 lahman, 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.
Click to see 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 lahman, 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).
Click to see 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 lahman, figure out how many walks (BB) and strikeouts (SO) Mariano Rivera had in the playoffs. Which year did he give up the most postseason walks? Answer this question using 2 queries.
Click to see solution
More men have walked on the moon than have scored a run on Mariano Rivera in a playoff game. He 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 lahman, 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.
Click to see 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 lahman, figure out how many teams Bartolo Colon pitched for.
Click to see 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 that 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 lahman, 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.
Click to see solution
As with many in his position, Bauer has a larger reputation as a pitcher than as a hitter.
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 lahman, 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.
Click to see solution
Mike Trout and Giancarlo Stanton are considered two of the of the best hitters in MLB 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 lahman, 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.
Click to see 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 lahman, figure out how many different National League (lgID == NL) catchers (C) won the gold glove between 1990 and 2000.
Click to see 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 lahman, figure out how many different 3rd basemen played for the Seattle Mariners between 2000 and 2005. Who had the most errors?
Click to see 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 lahman, figure out what seasons Craig Biggio played catcher. Biggio was known for playing second base, but as you’ll see, that’s not all he played. Solve this problem using 2 queries.
Click to see 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 lahman, find the teams representing the National League that have won the World Series. Display the list with the yearID and the teamID in ascending order.
Click to see 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 lahman, 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.
Click to see 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 |
From lahman, who was the most recent player manager?
Click to see 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 lahman, get the at-bats, home runs, and stolen bases for Roberto Clements by year in ascending order.
Click to see 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 lahman, get a list of distinct World Series winners during the years where Tom Lasorda managed the Los Angeles Dodgers (LAN). Find the years Tom Lasorda was the manager of LAN, then find the distinct teams that won a World Series during his tenure.
Click to see 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 lahman, figure out which teams Kenny Lofton stole more than 20 bases from in a single season. Limit results to seasons after the year 2000.
Click to see 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 lahman, 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?
Click to see 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 lahman, get the teamID, wins (W), and losses (L) for the National League in 2015.
Click to see 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 lahman, 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?
Click to see 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 lahman, get a playerID list of managers who won more games than they lost (better record than .500) between 1930 and 1950. Get the manager’s name and name of their team for the winningest (best record) manager in that time period.
Click to see 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 lahman, 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?
Click to see 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 lahman, 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.
Click to see 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 |