---
tags: ucsd-carpentries
---

# 2022 UC Carpentries Fall Workshop (SQL)

**Workshop Details**
Dates: September 6th - 13th, 2022
Time: 9am - 12pm

**Workshop Agenda:**
https://ucsdlib.github.io/2022-09-06-carpentries-uc/

# Instructors:
**Reid Otsuji - UC San Deigo, [e-mail]**
**Ryan Gan - El Camino College, [e-mail]**

## Helpers:
**Sam Teplitzky - UC Berkeley**
**Leigh Phan - UC Los Angeles**

---

# Day 6: SQL (53)

# Resources:
**Today's Workshop lesson:** https://librarycarpentry.org/lc-sql/

**Indepth SQL lesson:**
http://swcarpentry.github.io/sql-novice-survey/

**SQL comprehensive keyword tutorial:**
https://www.w3schools.com/sql/

**Learning more about SQL Joins:**
https://www.geeksforgeeks.org/sql-join-set-1-inner-left-right-and-full-joins/

SQL in R example
https://dept.stat.lsa.umich.edu/~jerrick/courses/stat701/notes/sql.html

---

## **Software Installation:**
DB Browser for SQLlite
https://sqlitebrowser.org/dl/
* Windows install 64-bit version
* Mac Download latest version

---

## Download SQL Database file

Go to Zenodo:
https://zenodo.org/record/2822005#.YxuznfXPweY

**From Zenodo download and save the .zip file on your `Desktop`.**

* Open the downloaded zip file with the zip utlity on your computer.
* There should be a folder called `doaj-article-sample` with 2 files saved inside called `doaj-article-sample.db.sql` and `doaj-article-sample.db.db`

If you see the 2 files in your `doaj-article-sample-db` folder on you Desktop. You're all set!

---

## NOTES:
A copy of the instructor live session notes will be made available to participants upon request at the end of the workshop.

## Workshop Day 6
### First name and Last Name/Organization/Dept./Email
| Name (first & last) | Organization | Dept. | Email |
| ------------------------- | ------------ | ----- | --------------- |
| (example) Jane Doe | UCSD | IT | [e-mail] |
| Osika Tripathi | UCSD | Public Health | [e-mail] |
| Melodi Frey | UCSD | CMM | [e-mail] |
| Bruce Hamilton | UCSD |CMM |[e-mail] |
|Marri Atienza | UCB | Library |[e-mail] |
| Jay Chi | UCSB | ETS | [e-mail] |
| Sarina Qin | UCM | Nat Sci | [e-mail] |
| Leila Fattel | ISU | Agronomy | [e-mail] |
|Jun Tan |UCSD |Economics |[e-mail] |
| Andrea Ruan |UCR | Data Science | [e-mail] |
|Steven Krehel | | Economics | [e-mail] |
|Donald Zarate |UCR |Poli Sci and Psych | [e-mail] |
|Josiah Piceno |UCM |MBSE |[e-mail] |
| Junxiao Gao | UCSF | Epidemiology & Biostatistic | [e-mail] |
| Dane Whicker | UCSD | SIO | [e-mail] |
|Anghesom Ghebremedhi | UCSD | | [e-mail] |
| Govind Sah | UCSD | Pathology |[e-mail] |
| Haley Potts | UCSD | Math & Economics | [e-mail] |
|Ana C. Dantas Machado | UCSD | Medicine | [e-mail] |
|John Thompson |UC Merced |Molecular & Cell Biology | [e-mail] |
| Ivan Felix Rios | UCSD | Math & Econ | [e-mail] |
|Steven Chong | UCB | Library | [e-mail] |
|Andrew Chan |UCSD |IGPP |[e-mail] |
|Mario Cuaya |UCR |Computer Science|[e-mail] |
| Igor Aprelev | UCSD | Math and Econ | [e-mail] |
|Douglas Zhang | UCSD |Chemistry and Biochemistry |[e-mail] |
|Daryl Han | UC Irvine | Student Center and Event Services | [e-mail] |
|Vishakha Malhotra | UCSF | Biostatistics and Epidemiology | [e-mail] |
| Josue Duque | UC Merced | Quantitative and Systems Biology | [e-mail] |
|Vikram Jambulapati | UCSD |Economics | [e-mail] |
| Dexin Zhou | UCSD | Mathematics | [e-mail] |
| Zhaoning (Johnny) Wang |UCSD |CMME |[e-mail] |
| Benjamin Yeh | UCLA | Medicine | [e-mail]
|Sam Erickson | UC Merced | Physics | [e-mail] |
Alexander Frey | UCSD | Rady School of Management | [e-mail]
Amber Heidbrink |UCSD |Cell and Developmental Biology | [e-mail] |
| Jonathan Le | UCR | Mathematics | [e-mail] |
Caitlin Tribelhorn | UCSD | Medicine | [e-mail] |

## Day 6 Questions:
Please enter any questions not answered during live session here:
1.

### Exercise answers:

--exercise

SELECT title, First_Author, ISSNs, Citation_Count
FROM articles
ORDER BY Citation_Count DESC, Title ASC;

--exercise answers:

SELECT journals.Journal_Title, count(*), avg(articles.Citation_Count)
FROM articles
JOIN journals
ON articles.ISSNs = journals.ISSNs
GROUP BY articles.ISSNs;

--Exercise

SELECT journals.Journal_Title, publishers.Publisher, COUNT(*)
FROM articles
JOIN journals
ON articles.ISSNs = journals.ISSNs
JOIN publishers
ON publishers.id = journals.PublisherId
GROUP BY Journal_Title
ORDER BY COUNT(*) DESC;

### End Day 6