9 SQL

This BP chapter is a bit different from many of the DataCamp-based chapters you’ve done. For Econ 380, you need to understand R programming well, but you likely won’t need to use SQL at all. I’m having you learn about basic SQL because having a basic understanding of SQL might be useful for you in the future (e.g., to get an internship/job and/or to do well there). The purpose of this BP chapter is for you to become familiar with a few fundamentals of SQL. You will not know enough to develop a database for a large corporation, but you should know enough to be able to get data from a SQL database (e.g., an internship that has something like “familiarity with SQL queries” should apply to you).

There are several ways for you to achieve this goal. One is to go through part or all of the following DataCamp courses, along with the main things you should learn in each (note each title is a link to the course)):

Introduction to SQL
- What SQL is, and knowledge of the existence of different SQL flavors (e.g., SQL Server and PostgreSQL)
- Structure of relational databases (e.g., tables, fields)
- SELECT statement
Intermediate SQL
- DISTINCT
- WHERE
- AND, OR
- NULL values
- Aggregate Functions (AVG(), SUM(), MIN(), MAX(), COUNT())
- ORDER BY (sorting)
- GROUP BY
- HAVING (there WHERE that goes with GROUP BY)
Joining Data in SQL
- INNER JOIN
- LEFT JOIN (or RIGHT JOIN)

Note that these 3 courses are the first 3 courses in the DataCamp “SQL Fundamentals” skill track. If you are interested in learning more about SQL, this skill track has additional courses, as well as DataCcamp “projects” to practice your skills.

The other way to learn about these aspects of SQL is you can read about them in this tutorial: https://www.sqlitetutorial.net/sqlite-sample-database. I’m giving you this particular website/tutorial because it comes with a useful sample database that also works in R (as shown below). I want you to learn about all the things included in the outline above. You can learn about them through any combination of DataCamp and this online tutorial. For example, to learn about ORDER BY, you could go through that part of the Intermediate SQL DC course, and/or you could read through (and experiment with) the order by page in the online tutorial. You are also free to use other websites, videos, AI, etc. I don’t care how you learn about SQL, I just want you to be familiar with it.

The website explains how to do some things if you install SQLite on your computer. You do not need to do that. You can (and should) do everything in R via the RSQLite package, as explained below.

Note that the specifics on this website are for the SQLite version of SQL. DataCamp seems to be specific to PostgreSQL. But you’re just learning about the basics, and the basics work the same in all the different versions/flavors. There are a few exceptions. For example, you might want to use the keyword LIMIT to only output the top, say, 10 rows. This is done using LIMIT in SQLite, PostgreSQL, and MySQL. If instead you use Microsoft SQL Server or Access (not unlikely at an internship or job), the keyword is TOP instead of LIMIT. That’s the only one I can think of that you might want to use now. Other than that, they are the same. Thus, any website discussing the basics should be the same if you want to look beyond the DataCamp courses and website I’ve given you.

To demonstrate what you have learned, and to serve as a reference for you later, you are going to take notes here on the key features of SQL with which I want you to be familiar. You should use the RSQLite package with the chinook.db database. I demonstrate below how to do so. This means that you should have working examples of everything in SQL, just like you do for R. I provide you a working example of SELECT below.

I will add section headings to serve as a structure for your notes, and to serve as a checklist so you include everything I want you to include. As with all BP chapters, you are free to add more if you want.

9.1 Loading a SQL database in R via RSQLite

Packages used in this chapter:

## Load all packages used in this chapter
library(RSQLite)

Load the connection (that we have named con) to the chinook database (the one from the website tutorial):

con <- dbConnect(SQLite(), "data/chinook.db")

As the online documentation for the database shows, this database has the following tables:

dbListTables(conn = con)

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

# lists all tables in a database

You can also see the list of fields in a particular table like this:

dbListFields(conn = con, name = "customers")

##  [1] "CustomerId"   "FirstName"    "LastName"     "Company"      "Address"     
##  [6] "City"         "State"        "Country"      "PostalCode"   "Phone"       
## [11] "Fax"          "Email"        "SupportRepId"

You can then create working examples using SQL code chunks, like this:

SELECT FirstName, LastName FROM customers

Table 9.1: Displaying records 1 - 10
FirstName	LastName
Luís	Gonçalves
Leonie	Köhler
François	Tremblay
Bjørn	Hansen
František	Wichterlová
Helena	Holý
Astrid	Gruber
Daan	Peeters
Kara	Nielsen
Eduardo	Martins

The rest of this file should have your notes, including code chunks with working examples similar to the one above (the code chunk with a working example showing how to use SELECT). If you want the notes to go in a different order, feel free to rearange the headings. But make sure to include an example of everything with a level 3 (###) or 4 (####) heading below.

9.2 Selecting data

Fields(columns) should be lowercase without spaces, using underscores instead. Databases in SQL are structured with linked tables.

9.2.1 Select

SELECT can be used to indicate what fields should be selected. This can also be used with FROM to choose what table to come from. We can also select multiple field using commas between them. Using SELECT * selects all fields.

dbListFields(conn = con, name = "tracks")

## [1] "TrackId"      "Name"         "AlbumId"      "MediaTypeId"  "GenreId"     
## [6] "Composer"     "Milliseconds" "Bytes"        "UnitPrice"

# this lists the fields within a certain table


SELECT TrackId, Name, Composer, Milliseconds FROM tracks

-- comments in SQL code chunks start with --

Table 9.2: Displaying records 1 - 10
TrackId	Name	Composer	Milliseconds
1	For Those About To Rock (We Salute You)	Angus Young, Malcolm Young, Brian Johnson	343719
2	Balls to the Wall	NA	342562
3	Fast As a Shark	F. Baltes, S. Kaufman, U. Dirkscneider & W. Hoffman	230619
4	Restless and Wild	F. Baltes, R.A. Smith-Diesel, S. Kaufman, U. Dirkscneider & W. Hoffman	252051
5	Princess of the Dawn	Deaffy & R.A. Smith-Diesel	375418
6	Put The Finger On You	Angus Young, Malcolm Young, Brian Johnson	205662
7	Let’s Get It Up	Angus Young, Malcolm Young, Brian Johnson	233926
8	Inject The Venom	Angus Young, Malcolm Young, Brian Johnson	210834
9	Snowballed	Angus Young, Malcolm Young, Brian Johnson	203102
10	Evil Walks	Angus Young, Malcolm Young, Brian Johnson	263497


SELECT * FROM playlists

Table 9.3: Displaying records 1 - 10
PlaylistId	Name
1	Music
2	Movies
3	TV Shows
4	Audiobooks
5	90’s Music
6	Audiobooks
7	Movies
8	Music
9	Music Videos
10	TV Shows

9.2.2 Count()

COUNT() is used to return all values in a field. using the * you can count all records(rows)


SELECT COUNT(*) AS count_playlists
FROM playlists

Table 9.4: 1 records
count_playlists
18

9.2.3 Distinct

DISTINCT can be used to remove all unique values in a field as their often might be duplicates. It is often used in combination with COUNT. The below example returns a count of all the unique playlists types, removing all duplicates.


SELECT COUNT(DISTINCT Name) AS playlist_types
FROM playlists

Table 9.5: 1 records
playlist_types
14

9.2.4 Where

WHERE can be used to filter. With numbers we can use less than <,greater than >, equal to =, less than or equal to <=, greater than or equal to >=, and not equal to<>. It can also be used for characters then the character needs to be in single quotes ‘example’

-- this all songs over 300,000 milliseconds
SELECT Name, Milliseconds AS over_300K
FROM tracks
WHERE Milliseconds >300000

Table 9.6: Displaying records 1 - 10
Name	over_300K
For Those About To Rock (We Salute You)	343719
Balls to the Wall	342562
Princess of the Dawn	375418
Go Down	331180
Let There Be Rock	366654
Problem Child	325041
Overdose	369319
Whole Lotta Rosie	323761
Love In An Elevator	321828
What It Takes	310622

SELECT Name
FROM playlists
WHERE Name = 'Music'

Table 9.7: 2 records
Name
Music
Music

9.2.4.1 And, Or

AND and OR can be used with WHERE. OR only needs one of the criteria in the filter to be valid, while AND needs all of them to be. The below example returns all tracks that have one of two specific composers and are over 350,000 millisecond or under 200,000 milliseconds.

SELECT Name, Composer, Milliseconds
FROM tracks
WHERE (Composer = 'Angus Young, Malcolm Young, Brian Johnson' OR Composer =     'Alanis Morissette & Glenn Ballard')
  AND (Milliseconds > 350000 OR Milliseconds < 200000)

Table 9.8: 4 records
Name	Composer	Milliseconds
C.O.D.	Angus Young, Malcolm Young, Brian Johnson	199836
Perfect	Alanis Morissette & Glenn Ballard	188133
Right Through You	Alanis Morissette & Glenn Ballard	176117
You Oughta Know (Alternate)	Alanis Morissette & Glenn Ballard	491885

9.2.5 Order By (Sorting)

ORDER BY is used to sort results. Ascending is the automatic orientation, we can use ASC to validate that or use DESC to sort descending. It can also be used by multiple fields. The below example sorts by GenreId and then by Bytes within each GenreId.

SELECT Name, GenreId, Bytes
FROM tracks
ORDER BY GenreId, Bytes DESC

Table 9.9: Displaying records 1 - 10
Name	GenreId	Bytes
Dazed And Confused	1	52490554
Space Truckin’	1	39267613
Dazed And Confused	1	36052247
We’ve Got To Get Together/Jingo	1	34618222
Funky Piano	1	30200730
Going Down / Highway Star	1	29846063
Santana Jam	1	29207100
The Sun Road	1	29008407
Whole Lotta Love	1	28191437
Mistreated (Alternate Version)	1	27775442

9.3 Grouping and aggregating

9.3.1 Group By

GROUP BY allows us to provide summary statistics for certain subsections of the data. We can also use it on multiple fields. The below example returns the average number of bytes for each GenreId and sorts them in descending order.

SELECT GenreId, AVG(Bytes) AS avg_bytes
FROM tracks
GROUP BY GenreId
ORDER BY avg_bytes DESC

Table 9.10: Displaying records 1 - 10
GenreId	avg_bytes
20	532930426
18	507078984
21	506946967
19	340261678
22	316904466
15	10691926
2	9488137
13	9474752
3	9234573
1	9007374

9.3.2 Aggregate Functions

Aggregate functions can be utilized to gain a better understanding about the data you are working with. They all work with numerical data, while COUNT(), MIN(), and MAX() also work with strings.

9.3.2.1 Avg()

AVG() returns the average value of a specified field. The below example shows the average number of bytes of a track.

SELECT AVG(Bytes)
FROM tracks

Table 9.11: 1 records
AVG(Bytes)
33510207

9.3.2.2 SUM()

SUM() returns the sum of all the values of a specified field. The below example shows the total number of bytes of all tracks in the database.

SELECT SUM(Bytes)
FROM tracks

Table 9.12: 1 records
SUM(Bytes)
117386255350

9.3.2.3 MIN() and MAX()

MIN() returns the lowest value of a specified field and MAX() returns the highest value of a specified field. These functions also works with strings, unlike AVG() and SUM(). The below example shows the minimum and maximum number of bytes of a track in the database.

SELECT MIN(Bytes), MAX(Bytes)
FROM tracks

Table 9.13: 1 records
MIN(Bytes)	MAX(Bytes)
38747	1059546140

Here we also see the functionality with non-numerical data.

SELECT MIN(Name), MAX(Name)
FROM tracks

Table 9.14: 1 records
MIN(Name)	MAX(Name)
“40”	Último Pau-De-Arara

9.3.3 Having (where for groups)

In SQL we cannot filter aggregate functions with WHERE, so we utilize HAVING. HAVING filters grouped records while WHERE filters individual records. The below example builds off the earlier GROUP BY example, but now only returning those records where the avg_bytes is greater than 10,000,000.

SELECT GenreId, AVG(Bytes) AS avg_bytes
FROM tracks
GROUP BY GenreId
HAVING AVG(Bytes) > 10000000
ORDER BY avg_bytes DESC

Table 9.15: 6 records
GenreId	avg_bytes
20	532930426
18	507078984
21	506946967
19	340261678
22	316904466
15	10691926

9.4 Joining tables

9.4.1 Inner join

Just like in R INNER JOIN returns values where there is records in both tables.

SELECT TrackId, tracks.Name AS Name, tracks.AlbumId, albums.ArtistId
FROM tracks
INNER JOIN albums
ON tracks.AlbumId = albums.AlbumID

Table 9.16: Displaying records 1 - 10
TrackId	Name	AlbumId	ArtistId
1	For Those About To Rock (We Salute You)	1	1
6	Put The Finger On You	1	1
7	Let’s Get It Up	1	1
8	Inject The Venom	1	1
9	Snowballed	1	1
10	Evil Walks	1	1
11	C.O.D.	1	1
12	Breaking The Rules	1	1
13	Night Of The Long Knives	1	1
14	Spellbound	1	1

9.4.2 Left join (or right join)

LEFT JOIN returns all values in the left table and the corresponding ones in the right table. It’s functionality is very similar to in R.

SELECT 
    e.FirstName AS first, e.LastName AS last,
    c.CustomerId
FROM employees AS e
LEFT JOIN customers AS c
    ON e.EmployeeId = c.SupportRepId;

Table 9.17: Displaying records 1 - 10
first	last	CustomerId
Andrew	Adams	NA
Nancy	Edwards	NA
Jane	Peacock	1
Jane	Peacock	3
Jane	Peacock	12
Jane	Peacock	15
Jane	Peacock	18
Jane	Peacock	19
Jane	Peacock	24
Jane	Peacock	29

SELECT 
    e.FirstName AS first, e.LastName AS last,
    c.CustomerId
FROM employees AS e
RIGHT JOIN customers AS c
    ON e.EmployeeId = c.SupportRepId;

Table 9.18: Displaying records 1 - 10
first	last	CustomerId
Jane	Peacock	1
Jane	Peacock	3
Jane	Peacock	12
Jane	Peacock	15
Jane	Peacock	18
Jane	Peacock	19
Jane	Peacock	24
Jane	Peacock	29
Jane	Peacock	30
Jane	Peacock	33

9.4.3 Other types of joins

9.4.3.1 Full Join

FULL JOIN returns all values from both tables.

SELECT e.FirstName, e.LastName, e.Title, c.CustomerId
FROM employees AS e
FULL JOIN customers AS c
    ON e.EmployeeId = c.SupportRepId;

Table 9.19: Displaying records 1 - 10
FirstName	LastName	Title	CustomerId
Andrew	Adams	General Manager	NA
Nancy	Edwards	Sales Manager	NA
Jane	Peacock	Sales Support Agent	1
Jane	Peacock	Sales Support Agent	3
Jane	Peacock	Sales Support Agent	12
Jane	Peacock	Sales Support Agent	15
Jane	Peacock	Sales Support Agent	18
Jane	Peacock	Sales Support Agent	19
Jane	Peacock	Sales Support Agent	24
Jane	Peacock	Sales Support Agent	29

9.4.3.2 Cross Join

This returns all possible combinations of two tables. This matches every row in artist with every row in genre, without any matching term.

SELECT 
    a.Name AS artist,
    g.Name AS genre
FROM artists AS a
CROSS JOIN genres AS g;

Table 9.20: Displaying records 1 - 10
artist	genre
AC/DC	Rock
AC/DC	Jazz
AC/DC	Metal
AC/DC	Alternative & Punk
AC/DC	Rock And Roll
AC/DC	Blues
AC/DC	Latin
AC/DC	Reggae
AC/DC	Pop
AC/DC	Soundtrack

9.4.3.3 Self Join

This is for joining a table with itself. It can be useful for comparing different values in the same table. While self join is not a verb itself, it is a concept that can be very useful.

SELECT 
    e.EmployeeId,
    e.LastName AS employee_last_name,
    m.LastName AS manager_last_name
FROM employees AS e
LEFT JOIN employees AS m
    ON e.ReportsTo = m.EmployeeId;

Table 9.21: 8 records
EmployeeId	employee_last_name	manager_last_name
1	Adams	NA
2	Edwards	Adams
3	Peacock	Edwards
4	Park	Edwards
5	Johnson	Edwards
6	Mitchell	Adams
7	King	Mitchell
8	Callahan	Mitchell