In this and other sections we will be referring to the “books database”.
Click below to download a document that describes the books database and database technology in general. This document also contains many questions/answers to test your knowledge of SQL. You can also download the CSV files that contain the data for the books database.
# install/require the readr package. You need this package to be able to use
# the read_csv function below.
# Also install/require the sqldf package.
if(!require(readr)){install.packages("readr");require(readr);}Loading required package: readrif(!require(sqldf)){install.packages("sqldf");require(sqldf);}Loading required package: sqldfLoading required package: gsubfnLoading required package: protoLoading required package: RSQLite#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
# The books database contains information about various books, authors,
# publishers, etc.
#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
# Read in the data for the books database - see the
titles = read_csv("data/booksDatabase/titles.csv", na="NULL", show_col_types=FALSE)
authors = read_csv("data/booksDatabase/authors.csv", na="NULL", show_col_types=FALSE)
publishers = read_csv("data/booksDatabase/publishers.csv", na="NULL", show_col_types=FALSE)
title_authors = read_csv("data/booksDatabase/title_authors.csv", na="NULL", show_col_types=FALSE)
royalties = read_csv("data/booksDatabase/royalties.csv", na="NULL", show_col_types=FALSE)The document above booksDatabase description and Questions contains information about the contents of the tables and how the tables are related to each other.
sqldf("select * from titles") title_id title_name type pub_id pages price
1 T01 1977! history P01 107 21.99
2 T02 200 Years of German Humor history P03 14 19.95
3 T03 Ask Yor System Administrator computer P02 1226 39.95
4 T04 But I Did It Unconciously psychology P01 510 12.99
5 T05 Exchange of Platitudes psychology P01 201 6.95
6 T06 How About Never? biography P01 473 19.95
7 T07 I Blame My Mother biography P03 333 23.95
8 T08 Just Wait Until After School children P01 86 10.00
9 T09 Kiss My Boo Boo children P01 22 13.95
10 T10 Not Without My Fabrerge Egg biography P05 NA NA
11 T11 Perhaps It's a Glandular Problem psychology P05 826 7.99
12 T12 Spontaneous, Not Annoying biography P05 507 12.99
13 T13 What Are The Civilian Applications? history P03 802 29.99
sales pubdate
1 566 8/1/2000 0:00
2 9566 4/1/1998 0:00
3 25667 9/1/2000 0:00
4 13001 5/31/1999 0:00
5 201440 1/1/2001 0:00
6 11320 7/31/2000 0:00
7 1500200 10/1/1999 0:00
8 4095 6/1/2001 0:00
9 5000 5/31/2002 0:00
10 NA <NA>
11 94123 11/30/2000 0:00
12 100001 8/31/2000 0:00
13 10467 5/31/1999 0:00sqldf("select * from publishers") pub_id pub_name city state country
1 P01 Abatis Publishers New York NY USA
2 P02 Core Dump Books San Francisco CA USA
3 P03 Schandenfreude Press Hamburg <NA> Germany
4 P04 Tneterhooks Press Berkeley CA USA
5 P05 AAA Publishing Berkeley CA USAsqldf("select * from title_authors") title_id au_id au_order royalty_shares
1 T01 A01 1 1.0
2 T02 A01 1 1.0
3 T03 A05 1 1.0
4 T04 A03 1 0.6
5 T04 A04 2 0.4
6 T05 A04 1 1.0
7 T06 A02 1 1.0
8 T07 A02 1 0.5
9 T07 A04 2 0.5
10 T08 A06 1 1.0
11 T09 A06 1 1.0
12 T10 A02 1 1.0
13 T11 A03 2 0.3
14 T11 A04 3 0.3
15 T11 A06 1 0.4
16 T12 A02 1 1.0
17 T13 A01 1 1.0sqldf("select * from authors") au_id au_fname au_lname phone address city
1 A01 Sarah Buchman 718-496-7223 75 West 205 St Bronx
2 A02 Wendy Heydemark 303-986-7020 2922 Baseline Rd Boulder
3 A03 Hallie Hull 415-549-4278 3800 Waldo Ave, #14F San Francisco
4 A04 Klee Hull 415-549-4278 3800 Waldo Ave, #14F San Francisco
5 A05 Christian Kells 212-771-4680 114 Horatio St New York
6 A06 Harvey Kellsey 650-836-7128 390 Serra Mall Palo Alto
7 A07 Paddy O'Furniture 941-925-0752 1442 Main St Sarasota
state zip
1 NY 10468
2 CO 80303
3 CA 94123
4 CA 94123
5 NY 10014
6 CA 94305
7 FL 34236sqldf("select * from royalties") title_id advance royalty_rate
1 T01 10000 0.05
2 T02 1000 0.06
3 T03 15000 0.07
4 T04 20000 0.08
5 T05 100000 0.09
6 T06 20000 0.08
7 T07 1000000 0.11
8 T08 0 0.04
9 T09 0 0.05
10 T10 NA NA
11 T11 100000 0.07
12 T12 50000 0.09
13 T13 20000 0.06