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	jdoe1@ucsd.edu
Osika Tripathi	UCSD	Public Health	otripathi@health.ucsd.edu
Melodi Frey	UCSD	CMM	mtastemel@health.ucsd.edu
Bruce Hamilton	UCSD	CMM	bah@ucsd.edu
Marri Atienza	UCB	Library	matienza@berkeley.edu
Jay Chi	UCSB	ETS	jaychi@ucsb.edu
Sarina Qin	UCM	Nat Sci	sqin@ucmerced.edu
Leila Fattel	ISU	Agronomy	lfattel@iastate.edu
Jun Tan	UCSD	Economics	j4tan@ucsd.edu
Andrea Ruan	UCR	Data Science	aruan011@ucr.edu
Steven Krehel		Economics	skrehel@ucsd.edu
Donald Zarate	UCR	Poli Sci and Psych	dzara016@ucr.edu
Josiah Piceno	UCM	MBSE	jpiceno3@ucmerced.edu
Junxiao Gao	UCSF	Epidemiology & Biostatistic	Junxiao.Gao@ucsf.edu
Dane Whicker	UCSD	SIO	dwhicker@ucsd.edu
Anghesom Ghebremedhi	UCSD		aghebremedhin@health.ucsd.edu
Govind Sah	UCSD	Pathology	gsah@health.ucsd.edu
Haley Potts	UCSD	Math & Economics	hpotts@ucsd.edu
Ana C. Dantas Machado	UCSD	Medicine	adantasmachado@ucsd.edu
John Thompson	UC Merced	Molecular & Cell Biology	jthompson44@ucmerced.edu
Ivan Felix Rios	UCSD	Math & Econ	ifelixrios@ucsd.edu
Steven Chong	UCB	Library	stevenchong@berkeley.edu
Andrew Chan	UCSD	IGPP	andrewchan@ucsd.edu
Mario Cuaya	UCR	Computer Science	mcuay001@ucr.edu
Igor Aprelev	UCSD	Math and Econ	iaprelev@ucsd.edu
Douglas Zhang	UCSD	Chemistry and Biochemistry	doz023@ucsd.edu
Daryl Han	UC Irvine	Student Center and Event Services	ddhan@uci.edu
Vishakha Malhotra	UCSF	Biostatistics and Epidemiology	vishakha.malhotra@ucsf.edu
Josue Duque	UC Merced	Quantitative and Systems Biology	jduque2@ucmerced.edu
Vikram Jambulapati	UCSD	Economics	vjambula@ucsd.edu
Dexin Zhou	UCSD	Mathematics	dzhou@ucsd.edu
Zhaoning (Johnny) Wang	UCSD	CMME	zhw063@health.ucsd.edu
Benjamin Yeh	UCLA	Medicine	benyeh@ucla.edu
Sam Erickson	UC Merced	Physics	serickson3@ucmerced.edu
Alexander Frey	UCSD	Rady School of Management	alexander.frey@rady.ucsd.edu
Amber Heidbrink	UCSD	Cell and Developmental Biology	aheidbrink@ucsd.edu
Jonathan Le	UCR	Mathematics	jle173@ucr.edu
Caitlin Tribelhorn	UCSD	Medicine	ctribelh@ucsd.edu

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;

2022 UC Carpentries Fall Workshop (SQL)

Instructors:

Helpers:

Day 6: SQL (53)

Resources:

Software Installation:

Download SQL Database file

NOTES:

Workshop Day 6

First name and Last Name/Organization/Dept./Email

Day 6 Questions:

Exercise answers:

End Day 6

Read more

UC Carpentries Workshop (Python)

2022 UC Carpentries Fall Workshop (Version Control w/ Git)

2023 UC Carpentries Fall Workshop (All Notes)

Day 2 - 2023 UC Carpentries Fall Workshop Series