SQL

library(RMariaDB)
library(RSQLite)
library(DBI)

# Establish a connection to sqlite databases
chinook <- dbConnect(RSQLite::SQLite(), "chinook.db")
lahman <- dbConnect(RSQLite::SQLite(), "lahman.db")

# Establish a connection to mysql databases
connection <- dbConnect(RMariaDB::MariaDB(),
                      host="your-host.com",
                      db="your-database-name",
                      user="your-username",
                      password="your-password")

Joins

Joins are SQL clauses that combine data from two tables. There are 4 primary types of SQL joins: INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, and FULL OUTER JOIN.

When talking about an SQL JOIN statement, sometimes the first table in the SQL statement is referred to as the "left" table, and the second table is referred to as the right table. For instance, in the following query, A is the left table and B is the right table.

SELECT * FROM A INNER JOIN B ON A.id=B.a_id;

While there can be cases where using RIGHT JOIN and FULL JOIN can make your SQL statement more concise, both RIGHT JOIN and FULL JOIN are redundant and can be fully emulated using LEFT JOIN and UNION ALL clauses.

For the purposes of illustration, we will be using a common example of a database for an online store. This online store has two primary tables, orders and customers, shown below.

orders

id	description	customer_id	value
1	Water bottle	1	15.00
2	Key chain	1	7.50
3	Computer	3	2000.00
4	Thumb drive	3	25.00
5	Notebook	4	9.00
6	Shampoo		5.00
7	Paper		4.00

customers

id	first_name	last_name	email
1	Natalie	Wright	wright@example.com
2	Ana	Sousa	sousa@example.com
3	Ben	Schwartz	schwartz@example.com
4	Chen	Xi	xi@example.com
5	Frank	Zhang	zhang@example.com
6	Tianchi	Liu	liu@example.com
7	Jake	Jons	jons@example.com

INNER JOIN

An INNER JOIN, often referred to as simply JOIN, returns rows/records where there is a match in the right table from the left table. Records from the left table that don't have a match in the right table are excluded. Records from the right table that don't have a match in the left table are also excluded.

This is appropriate any time you need data from two separate tables, but only when the two tables have something in common. For example, what if our online company decided it wanted to query the database to send an email of appreciation for all customers who have placed at least 1 order. In this case, we want only the emails of those who don't appear in both the customers and orders table.

SELECT customers.email FROM orders INNER JOIN customers ON orders.customer_id=customers.id;

Which would result in the following table.

email
wright@example.com
schwartz@example.com
xi@example.com

LEFT OUTER JOIN

A LEFT OUTER JOIN, often referred to as simply a LEFT JOIN, returns rows/records where every value in the left table is present in addition to additional data from the right table, when there exists a match in the right table.

This is appropriate any time you want all of the data from the left table, and any extra data from the right table if there happens to be a match. For example, what if our online company wanted a list of all orders placed, and if the order wasn't placed from a guest account, send an email to the customer thanking them for their purchase? In this case, it would make sense to append email information to the order when there is a match.

SELECT orders.description, orders.value, customers.email FROM orders LEFT JOIN customers ON order.customer_id=customers.id;

Which would result in the following table, enabling the employee to see orders as well as send out thank you emails.

description	value	first_name	last_name	email
Water bottle	15.00	Natalie	Wright	wright@example.com
Key chain	7.50	Natalie	Wright	wright@example.com
Computer	2000.00	Ben	Schwartz	schwartz@example.com
Thumb drive	25.00	Ben	Schwartz	schwartz@example.com
Notebook	9.00	Chen	Xi	xi@example.com
Shampoo	5.00
Paper	4.00

Had we instead used an INNER JOIN, our list would be missing critical order information.

SELECT orders.description, orders.value, customers.email FROM orders INNER JOIN customers ON order.customer_id=customers.id;

description	value	first_name	last_name	email
Water bottle	15.00	Natalie	Wright	wright@example.com
Key chain	7.50	Natalie	Wright	wright@example.com
Computer	2000.00	Ben	Schwartz	schwartz@example.com
Thumb drive	25.00	Ben	Schwartz	schwartz@example.com
Notebook	9.00	Chen	Xi	xi@example.com

Aliasing

Aliasing is the process of giving a table or a table column a temporary name. Aliases are commonly used to either make the query easier to write, or more readable. An example of using table aliases to make a query shorter would be the following.

SELECT orders.description, orders.value, customers.email FROM orders INNER JOIN customers ON order.customer_id=customers.id;

By using table aliases, this can be reduced greatly.

SELECT o.description, o.value, c.email FROM orders AS o INNER JOIN customers AS c ON o.customer_id=c.id;

Note that aliases only last for the duration of a single query. If we were to subsequently use the following query, it would fail.

SELECT o.description, o.value, c.email FROM o INNER JOIN c ON o.customer_id=c.id;

In addition to table aliases, we can give fields aliases as well. For example, we could reduce customer_id to just c_id.

SELECT orders.customer_id AS c_id FROM orders INNER JOIN customers ON order.c_id=customers.id;

Alternatively, we could change customer_id to Customer ID, however, whenever we want an alias to contain spaces, we need to use either double quotes or square brackets.

SELECT orders.customer_id AS "Customer ID" FROM orders INNER JOIN customers ON order."Customer ID"=customers.id;

RDBMS

SQL in R

Click here for video

Examples

Please see here for a variety of examples demonstrating using SQL within R.

SQL in Python

Examples

The following examples use the lahman.db sqlite database.

Display the first 10 ballparks in the ballparks table.

Click here for solution

SELECT * FROM parks LIMIT 10;

Table 1: Displaying records 1 - 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

Make a list of the names of all of the inactive teams in baseball history.

Click here for solution

Remove the LIMIT 10 for full results.

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

Table 2: Displaying records 1 - 10

franchName
Altoona Mountain City
Philadelphia Athletics
Buffalo Bisons
Buffalo Bisons
Baltimore Orioles
Baltimore Terrapins
Baltimore Monumentals
Boston Reds
Brooklyn Gladiators
Boston Reds

Find the player with the most Runs Batted In (RBIs) in a season in 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 here for solution

In addition to his RBI record, Hack Wilson also held the NL home run record for a long time as well with 56. In 1999, Manny Ramirez tried to pursue the RBI record, but only was able to accrue 165 RBIs.

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

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

Table 3: 1 records

playerID
wilsoha01

Who was the manager of the 1976 "Big Red Machine" (CIN)? Complete this in 2 queries.

Click here for 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';

Table 4: 1 records

playerID
andersp01

Make a list of the teamIDs that were managed by Tony LaRussa. Complete this in 2 queries.

Click here for 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';

Table 5: 1 records

playerID
larusto01

What was Cecil Fielder's salary in 1987? Display the teamID with the salary.

Click here for solution

Cecil Fielder was a power hitting DH in the 1980s and 1990s. His son, Prince Fielder, played in the major leagues as well.

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

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

Table 6: 1 records

playerID
fieldce01

Make a list of all the teams who have lost a World Series (WS) since 1990. Put the list in ascending order by yearID.

Click here for solution

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

Table 7: Displaying records 1 - 10

teamIDloser	yearID
OAK	1990
ATL	1991
ATL	1992
PHI	1993
CLE	1995
ATL	1996
CLE	1997
SDN	1998
ATL	1999
NYN	2000

Let's find out about Cal Ripken, Jr. What was his height and weight? Did he bat right or left handed? When did he play his final game? Find all of this information in one query.

Click here for solution

Cal Ripken, Jr's nickname is the "Iron Man" of baseball due to the fact that he started in 2,632 straight games. That means in just over 16 seasons, Cal Ripken, Jr. never missed a game!

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

Table 8: 1 records

height	weight	bats	finalGame
76	200	R	2001-10-06

Select all the playerIDs and yearIDs of the players who were inducted in the hall of fame and voted in by the Veterans committee, between 1990 and 2000. Put the list in descending order.

Click here for 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; 

Table 9: Displaying records 1 - 10

playerID	yearid
andersp01	2000
mcphebi01	2000
steartu99	2000
cepedor01	1999
chylane99	1999
seleefr99	1999
willijo99	1999
davisge01	1998
dobyla01	1998
macphle99	1998

Get a list of the attendance by season of the Toronto Blue Jays (TOR). What season was the highest attendance?

Click here for 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;

Table 10: Displaying records 1 - 10

yearkey	attendance
1993	4057747
1992	4028318
1991	4001526
1990	3884384
2016	3392099
2017	3203886
1994	2907949
1995	2826445
2015	2794891
1987	2778459

How many different leagues have represented Major League Baseball over time?

Click here for 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;

Table 11: 8 records

league
American Association
American League
Federal League
Major League
National Association
National League
Players' League
Union Association

Find the teams that have won the World Series.

Click here for solution

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

Table 12: Displaying records 1 - 10

teamID	yearID
PRO	1884
SL4	1886
DTN	1887
NY1	1888
NY1	1889
BOS	1903
NY1	1905
CHA	1906
CHN	1907
CHN	1908

List the top 10 season win totals of teams. Include the yearID and teamID.

Click here for solution

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

Table 13: Displaying records 1 - 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

List the pitchers with their teamID, wins (W), and losses (L) that threw complete games (CG) in the 1995 season. Include their number of complete games as well.

Click here for solution

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

Table 14: Displaying records 1 - 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

Get a printout of the Hits (H), and home runs (HR) of Ichiro Suzuki's career. Do this is in two queries. In the first query, find Ichiro Suzuki's playerID. In the second one list the teamID, yearID, hits and home runs.

Click here for 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';

Table 15: 1 records

playerID
suzukic01

How many walks (BB) and strikeouts (SO) did Mariano Rivera achieve in the playoffs? Which year did Mariano Rivera give up the most post-season walks?

Click here for 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;

Table 16: 1 records

playerID
riverma01

Find the pitcher with 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 batter, respectively.

Click here for 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";

Table 17: Displaying records 1 - 10

playerID	SO
klubeco01	269
scherma01	252
hernafe02	248
cuetojo01	242
strasst01	242
kershcl01	239
bumgama01	219
salech01	208
greinza01	207
kenneia01	207

How many different teams did Bartolo Colon pitch for?

Click here 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 DISTINCT teamID FROM pitching
  WHERE playerID=='colonba01';

Table 18: 1 records

playerID
colonba01

How many times did Trevor Bauer come to bat (AB) in 2016? How many hits (H) did he get?

Click here for 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";

Table 19: 1 records

playerID
bauertr01

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

Table 20: 1 records

AB	H
5	0

Let's compare Mike Trout and Giancarlo Stanton by season. Who has hit more RBIs in a season? Who has been caught stealing (CS) more in a season?

Click here for 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');

Table 21: 2 records

playerID	nameFirst	nameLast
stantmi03	Giancarlo	Stanton
troutmi01	Mike	Trout

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

Table 22: 1 records

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;

Table 23: 1 records

playerID	yearID	teamID	RBI	CS
troutmi01	2013	LAA	97	7

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 command in this queries. Use a second query to get the player's first and last name.

Click here for solution

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

Table 24: Displaying records 1 - 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';

Table 25: 1 records

nameFirst	nameLast
Willie	Randolph

How many different NL catchers (C) won gold glove winners between 1990 and 2000?

Click here for solution

There were 6 different catchers.

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

Table 26: 6 records

playerID
santibe01
pagnoto01
manwaki01
johnsch04
liebemi01
mathemi01

How many different 3rd Basemen played for the Seattle Mariners between 2000 and 2005? Who had the most Errors?

Click here for 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;

Table 27: Displaying records 1 - 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';

Table 28: 1 records

nameFirst	nameLast
Mike	Cameron

Craig Biggio was more known for his play at second base over his major league baseball career, but he didn't always play second base. What seasons did Craig Biggio play Catcher?

Click here for solution

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

Table 29: 1 records

playerID
biggicr01

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

Table 30: 5 records

teamID	yearID	POS
HOU	1988	C
HOU	1989	C
HOU	1990	C
HOU	1991	C
HOU	2007	C

Find the teams that have won the World Series that represented the National League. Display the list with the yearID and teamID in ascending order.

Click here for solution

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

Table 31: Displaying records 1 - 10

teamID	yearID
PRO	1884
DTN	1887
NY1	1888
NY1	1889
NY1	1905
CHN	1907
CHN	1908
PIT	1909
BSN	1914
CIN	1919

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 here for solution

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

Table 32: Displaying records 1 - 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';

Table 33: 1 records

nameFirst	nameLast
Greg	Maddux

Who was the most recent player manager?

Click here for solution

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

Table 34: Displaying records 1 - 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';

Table 35: 1 records

nameFirst	nameLast
Pete	Rose

Get the at-bats, homeruns, stolen bases for Roberto Clemente by year in ascending order.

Click here for solution

Roberto Clemente is known as being a leader for the Pittsburgh Pirates. He died in a 1972 plane crash on a humanitarian mission to Puerto Rico, where he grew up.

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

Table 36: 1 records

playerID
clemero01

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

Table 37: 5 records

yearID	AB	HR	SB
1960	29	0	0
1970	14	0	0
1971	18	0	0
1971	29	2	0
1972	17	1	0

Get a list of distinct World Series winners from the years Tom Lasorda managed the Los Angeles Dodgers (LAN). First find the years Tom Lasorda was the manager of the Los Angeles Dodgers, and then find the distinct teams that won a World Series in that time frame.

Click here for solution

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

Table 38: 1 records

playerID
lasorto01

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

Table 39: Displaying records 1 - 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;

Table 40: Displaying records 1 - 10

teamID
CIN
NYA
PIT
PHI
LAN
SLN
BAL
DET
KCA
NYN

Which teams did Kenny Lofton steal more than 20 bases in a season after the year 2000?

Click here for solution

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

Table 41: 1 records

playerID
loftoke01

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

Table 42: 4 records

teamID	yearID	SB
CHA	2002	22
PHI	2005	22
LAN	2006	32
TEX	2007	21

How much did the Tampa Bay Rays (TBL) pay Wade Boggs in 1998? Who paid Boggs the most in a season during his career?

Click here for solution

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

Table 43: 1 records

playerID
boggswa01

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

Table 44: 1 records

teamID	yearID	salary
TBA	1998	1150000

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

Table 45: Displaying records 1 - 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

Click here for solution

SELECT teamID, yearID, W, L, HR, HRA, attendance FROM teams
  WHERE teamID=='DET' AND (WSWin=='Y' OR LgWin=='Y');

Table 46: Displaying records 1 - 10

teamID	yearID	W	L	HR	HRA	attendance
DET	1907	92	58	11	8	297079
DET	1908	90	63	19	12	436199
DET	1909	98	54	19	16	490490
DET	1934	101	53	74	86	919161
DET	1935	93	58	106	78	1034929
DET	1940	90	64	134	102	1112693
DET	1945	88	65	77	48	1280341
DET	1968	103	59	185	129	2031847
DET	1984	104	58	187	130	2704794
DET	2006	95	67	203	160	2595937

The standings you would find in a newspaper often have Wins and Losses in order of most to least wins. There are often other numbers that are involved like winning percentage, and other team statistics, but we won't deal with that for now. Get the NL East Standings in 2015.

Click here for solution

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

Table 47: 5 records

teamID	W	L
NYN	90	72
WAS	83	79
MIA	71	91
ATL	67	95
PHI	63	99

Make a list of the teams, wins, losses, years for NL East teams that have won the World Series. Which team had the most wins?

Click here for solution

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

Table 48: Displaying records 1 - 10

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

Get a list of the playerIDs of managers who won more games than they lost between 1930 and 1950. Get the manager's name, and the name of the team of the manager with the most wins on the list.

Click here for solution

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

Table 49: Displaying records 1 - 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';

Table 50: 1 records

nameFirst	nameLast
Connie	Mack

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

Table 51: 1 records

franchName
Philadelphia Athletics

Get the top 5 seasons from Florida Teams (Florida Marlins, Tampa Bay Rays, and Miami Marlins) in attendance. How many have occured since 2000?

Click here for solution

Florida baseball teams are not known for their attendance for a variety of reasons. Both MLB franchises play in domed fields, but usually do not draw large crowds.

SELECT franchID, franchName FROM teamsfranchises
    WHERE franchName=='Tampa Bay Rays'
    OR franchName=='Florida Marlins';

Table 52: 2 records

franchID	franchName
FLA	Florida Marlins
TBD	Tampa Bay Rays

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

Table 53: Displaying records 1 - 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

What pitcher has thrown the most Shutouts (SHO) in the AL since 2010? What about the NL? Please get their first and last names respectively.

Click here for solution

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

Table 54: Displaying records 1 - 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;    

Table 55: Displaying records 1 - 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';

Table 56: 2 records

nameFirst	nameLast
Felix	Hernandez
Cliff	Lee

The following examples use the chinook.db sqlite database.

dbListTables(chinook)

##  [1] "advisors"        "albums"          "artists"         "customers"      
##  [5] "employees"       "genres"          "invoice_items"   "invoices"       
##  [9] "media_types"     "playlist_track"  "playlists"       "sqlite_sequence"
## [13] "sqlite_stat1"    "students"        "tracks"

How do I select all of the rows of a table called employees?

Click here for solution

SELECT * FROM employees;

Table 57: 8 records

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

How do I select the first 5 rows of a table called employees?

Click here for solution

SELECT * FROM employees LIMIT 5;

Table 58: 5 records

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

How do I select specific rows of a table called employees?

Click here for solution

SELECT LastName, FirstName FROM employees;

Table 59: 8 records

LastName	FirstName
Adams	Andrew
Edwards	Nancy
Peacock	Jane
Park	Margaret
Johnson	Steve
Mitchell	Michael
King	Robert
Callahan	Laura

You can switch the order in which the columns are displayed as well:

SELECT FirstName, LastName FROM employees;

Table 60: 8 records

FirstName	LastName
Andrew	Adams
Nancy	Edwards
Jane	Peacock
Margaret	Park
Steve	Johnson
Michael	Mitchell
Robert	King
Laura	Callahan

How do I select only unique values from a column?

Click here for solution

SELECT DISTINCT Title FROM employees;

Table 61: 5 records

Title
General Manager
Sales Manager
Sales Support Agent
IT Manager
IT Staff

How can I filter that match a certain criteria?

Click here for solution

Select only employees with a FirstName "Steve":

SELECT * FROM employees WHERE FirstName='Steve';

Table 62: 1 records

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

Select only employees with FirstName "Steve" OR FirstName "Laura":

SELECT * FROM employees WHERE FirstName='Steve' OR FirstName='Laura';

Table 63: 2 records

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

Select only employees with FirstName "Steve" AND LastName "Laura":

SELECT * FROM employees WHERE FirstName='Steve' AND LastName='Laura';

Table 64: 0 records

EmployeeId	LastName	FirstName	Title	ReportsTo	BirthDate	HireDate	Address	City	State	Country	PostalCode	Phone	Fax	Email

As expected, there are no results! There is nobody with the full name "Steve Laura".

List the first 10 tracks from the tracks table.

Click here for solution

SELECT * FROM tracks LIMIT 10;

Table 65: Displaying records 1 - 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

How many rows or records are in the table named tracks?

Click here for solution

SELECT COUNT(*) FROM tracks;

Table 66: 1 records

COUNT(*)
3503

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

Click here for solution

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

Table 67: 2 records

ArtistId	Name
41	Elis Regina
193	Seu Jorge

What albums did the artist with ArtistId of 41 make?

Click here for solution

SELECT * FROM albums WHERE ArtistId=41;

Table 68: 1 records

AlbumId	Title	ArtistId
71	Elis Regina-Minha História	41

What are the tracks of the album with AlbumId of 71? Order the results from most Milliseconds to least.

Click here for solution

SELECT * FROM tracks WHERE AlbumId=71 ORDER BY Milliseconds DESC;

Table 69: Displaying records 1 - 10

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

What are the tracks of the album with AlbumId of 71? Order the results from longest to shortest and convert Milliseconds to seconds. Use aliasing to name the calculated field Seconds.

Click here for solution

SELECT Milliseconds/1000.0 AS Seconds, * FROM tracks WHERE AlbumId=71 ORDER BY Seconds DESC;

Table 70: Displaying records 1 - 10

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

What are the tracks that are at least 250 seconds long?

Click here for solution

SELECT Milliseconds/1000.0 AS Seconds, * FROM tracks WHERE Seconds >= 250;

Table 71: Displaying records 1 - 10

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

What are the tracks that are between 250 and 300 seconds long?

Click here for solution

SELECT Milliseconds/1000.0 AS Seconds, * FROM tracks WHERE Seconds BETWEEN 250 AND 300 ORDER BY Seconds;

Table 72: Displaying records 1 - 10

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

What is the GenreId of the genre with name Pop?

Click here for solution

SELECT GenreId FROM genres WHERE Name='Pop';

Table 73: 1 records

GenreId
9

What is the average length (in seconds) of a track with genre "Pop"?

Click here for solution

SELECT AVG(Milliseconds/1000.0) AS avg FROM tracks WHERE genreId=9;

Table 74: 1 records

avg
229.0341

What is the longest Bossa Nova track (in seconds)?

Click here for solution

What is the GenreId of Bossa Nova?

SELECT GenreId FROM genres WHERE Name='Bossa Nova';

Table 75: 1 records

GenreId
11

SELECT *, MAX(Milliseconds/1000.0) AS Seconds FROM tracks WHERE genreId=11;

Table 76: 1 records

TrackId	Name	AlbumId	MediaTypeId	GenreId	Composer	Milliseconds	Bytes	UnitPrice	Seconds
646	Samba Da Bênção	52	1	11	NA	409965	13490008	0.99	409.965

Get the average price per hour for Bossa Nova music (genreId of 11).

Click here for solution

SELECT AVG(UnitPrice/Milliseconds/1000.0/3600) AS 'Price per Hour' FROM tracks WHERE genreId=11;

Table 77: 1 records

Price per Hour
0

Get the average time (in seconds) for tracks by genre.

Click here for solution

SELECT genreId, AVG(Milliseconds/1000.0) AS 'Average seconds per track' FROM tracks GROUP BY genreId;

Table 78: Displaying records 1 - 10

GenreId	Average seconds per track
1	283.9100
2	291.7554
3	309.7494
4	234.3538
5	134.6435
6	270.3598
7	232.8593
8	247.1778
9	229.0341
10	244.3709

We can use an INNER JOIN to get the name of each genre as well. {#sql-inner-join}

SELECT g.Name, track_time.'Average seconds per track' FROM genres AS g INNER JOIN (SELECT genreId, AVG(Milliseconds/1000.0) AS 'Average seconds per track' FROM tracks GROUP BY genreId) AS track_time ON g.GenreId=track_time.GenreId ORDER BY track_time.'Average seconds per track' DESC;

Table 79: Displaying records 1 - 10

Name	Average seconds per track
Sci Fi & Fantasy	2911.7830
Science Fiction	2625.5491
Drama	2575.2838
TV Shows	2145.0410
Comedy	1585.2637
Metal	309.7494
Electronica/Dance	302.9858
Heavy Metal	297.4529
Classical	293.8676
Jazz	291.7554

What is the average price per track for each genre?

Click here for solution

SELECT genreId, AVG(UnitPrice) AS 'Average seconds per track' FROM tracks GROUP BY genreId;

Table 80: Displaying records 1 - 10

GenreId	Average seconds per track
1	0.99
2	0.99
3	0.99
4	0.99
5	0.99
6	0.99
7	0.99
8	0.99
9	0.99
10	0.99

What is the average number of tracks per album?

Click here for solution

SELECT AVG(trackCount) FROM (SELECT COUNT(*) AS trackCount FROM tracks GROUP BY albumId) AS track_count;

Table 81: 1 records

AVG(trackCount)
10.0951

What is the average number of tracks per album per genre?

Click here for solution

SELECT genreId, AVG(trackCount) FROM (SELECT genreId, COUNT(*) AS trackCount FROM tracks GROUP BY albumId) AS track_count GROUP BY genreId;

Table 82: Displaying records 1 - 10

genreId	AVG(trackCount)
1	11.41379
2	10.00000
3	10.90625
4	14.43478
5	12.00000
6	13.85714
7	14.81579
8	15.00000
9	16.00000
10	10.75000

SELECT Name, avg_track_count.'Average Track Count' FROM genres AS g INNER JOIN (SELECT genreId, AVG(trackCount) AS 'Average Track Count' FROM (SELECT genreId, COUNT(*) AS trackCount FROM tracks GROUP BY albumId) AS track_count GROUP BY genreId) AS avg_track_count ON g.GenreId=avg_track_count.genreId;

Table 83: Displaying records 1 - 10

Name	Average Track Count
Rock	11.41379
Jazz	10.00000
Metal	10.90625
Alternative & Punk	14.43478
Rock And Roll	12.00000
Blues	13.85714
Latin	14.81579
Reggae	15.00000
Pop	16.00000
Soundtrack	10.75000

The following examples us the lahman.db sqlite database.

dbListTables(lahman)

##  [1] "allstarfull"         "appearances"         "awardsmanagers"     
##  [4] "awardsplayers"       "awardssharemanagers" "awardsshareplayers" 
##  [7] "batting"             "battingpost"         "collegeplaying"     
## [10] "divisions"           "fielding"            "fieldingof"         
## [13] "fieldingofsplit"     "fieldingpost"        "halloffame"         
## [16] "homegames"           "leagues"             "managers"           
## [19] "managershalf"        "parks"               "people"             
## [22] "pitching"            "pitchingpost"        "salaries"           
## [25] "schools"             "seriespost"          "teams"              
## [28] "teamsfranchises"     "teamshalf"