--- tags: gps --- # 2022-GPS-Data-Skills-Course-Intro-to-Data-Management ## Sign in here: YaohongWang yaw045@ucsd.edu Hung-Yang (Jason) Chien, hchien@ucsd.edu John Kim, jok015@ucsd.edu Jaeyeon Park, jap013@ucsd.edu Kejun Chen, kec007@ucsd.edu Meghan Mattioli, mazavala@ucsd.edu TsuPing Wang, tsw002@ucsd.edu Sam Cohen, szcohen@ucsd.edu Nick Heimann, nheimann@ucsd.edu Emily Davalos, edavalos@ucsd.edu Salma Shaikh, sshaikh@ucsd.edu Chenhao Nie. cnie@ucsd.edu Ziyuan Zhu, ziz063@ucsd.edu Sora Park, sop006@ucsd.edu Tino Tirado, ttirado@ucsd.edu Haoran Jiang, haj005@ucsd.edu David Reimer, dreimer@ucsd.edu Junhui Xu, jux008@ucsd.edu Marissa Myers, maclan@ucsd.edu Jeffrey Myers, jmyers@ucsd.edu Wenjun Gong, w7gong@ucsd.edu Alayna Bone, abone@ucsd.edu Elise Spencer, Enspencer@ucsd.edu Yuting Wan y7wan@ucsd.edu Yue Yu yuy039@ucsd.edu Emily Irion, eirion@ucsd.edu Amanda Lee-Low, aleelow@ucsd.edu Rebecca Howard, r1howard@ucsd.edu Bing Rethy, brethy@ucsd.edu Bonnie Devenney, bdevenne@ucsd.edu Chengan Li, chl030@ucsd.edu Mariya Nikserest, mniksere@ucsd.edu Yuki Imura, yimura@ucsd.edu morgan cohen, m7cohen@ucsd.edu Yizhuo Liu, yil118@ucsd.edu Meiyu Su, m2su@ucsd.edu Chuyu Liu, chl082@ucsd.edu Nikki Qi, haqi@ucsd.edu Rawlins, Mackenna, mjrawlins@ucsd.edu Qihan huang q7huang@ucsd.edu Sunny Xu, qixu@ucsd.edu Patricia Resurreccion, paresurr@ucsd.edu Xinyi Du , x8du@ucsd.edu Shuting Wang, shw009@ucsd.edu Brenna Wayne bwayne@ucsd.edu Yuting Wan y7wan@ucsd.edu Zhibei Wang, zhw048@ucsd.edu Broderick Topil, btopil@ucsd.edu Merik Manzano, mmanzano@ucsd.edu Austin Brown, aubrown@ucsd.edu Nicholas Valle, njvalle@ucsd.edu Vorathip Plengpanit, vplengpa@ucsd.edu Stevinson Tendon, stendon@ucsd.edu Dan Bee Lee, dbl001@ucsd.edu Zizan Wang, ziw011@ucsd.edu Kelli Maples, kmaples@ucsd.edu Yuchen Wang, yuw147@ucsd.edu Hyun Ji Jung, hjjung@ucsd.edu Collin Boudreaux, cboudreaux@ucsd.edu Yunxin Liu, yul188@ucsd.edu Kevin Zhou, kezhou@ucsd.edu ## Collaborative Notes: # Git lesson Tuesday 2/22 ## please sign in ### full name, email address John Kim, jok015@ucsd.edu Sora Park, sop006@ucsd.edu Yuki Imura. yimura@ucsd.edu Mariya Nikseresht, mniksere@ucsd.edu Jaeyeon Park, jap013@ucsd.edu Patricia Resurreccion, paresurr@ucsd.edu Nick Heimann, nheimann@ucsd.edu Bonnie Devenney, bdevenne@ucsd.edu Bing Rethy, brethy@ucsd.edu Chenhao Nie. cnie@ucsd.edu Marissa Myers, maclan@ucsd.edu Rebecca Howard, r1howard@ucsd.edu Emily Davalos, edavalos@ucsd.edu Chengan Li, chl030@ucsd.edu Meiyu Su, m2su@ucsd.edu morgan cohen, m7cohen@ucsd.edu Yuchen Wang, yuw147@ucsd.edu Yizhuo Liu, yil118@ucsd.edu Wenjun Gong, w7gong@ucsd.edu Salma Shaikh, sshaikh@ucsd.edu Zhibei Wang, zhw048@ucsd.edu Emily Irion, eirion@ucsd.edu Dan Bee Lee, dbl001@ucsd.edu Junhui Xu, jux008@ucsd.edu Sunny Xu, qixu@ucsd.edu Austin Brown, aubrown@ucsd.edu Yue Yu yuy039@ucsd.edu Hyun Ji Jung, hjjung@ucsd.edu Chuyu Liu, chl082@ucsd.edu Nicholas Valle, njvalle@ucsd.edu Vorathip Plengpanit, vplengpa@ucsd.edu Yuting Wan y7wan@ucsd.edu Zizan Wang, ziw011@ucsd.edu Rawlins, Mackenna, mjrawlins@ucsd.edu Alayna Bone, abone@ucsd.edu Broderick Topil, btopil@ucsd.edu Jeffrey Myers, jmyers@ucsd.edu Ziyuan Zhu,ziz063@ucsd.edu Yaohong Wang,yaw045@ucsd.edu Brenna Wayne bwayne@ucsd.edu Tino Tirado, ttirado@Ucsd.edu Elise Spencer, Enspencer@ucsd.edu Shuting Wang, shw009@ucsd.edu Sam Cohen, szcohen@ucsd.edu Kelli Maples, kmaples@ucsd.edu Amanda Lee-Low, aleelow@ucsd.edu TsuPing Wang, tsw002@ucsd.edu Collin Boudreauxc, cboudreaux@ucsd.edu Meghan Mattioli, mazavala@ucsd.edu 43 ## Collaborative Notes: 43 # Sign in here John Kim, jok015@ucsd.edu Emily Davalos, edavalos@ucsd.edu Yizhuo Liu, yil118@ucsd.edu stevinson tendon, stendon@ucsd.edu Sora Park, sop006@ucsd.edu Wenjun Gong, w7gong@ucsd.edu Patricia Resurreccion, paresurr@ucsd.edu Nick Heimann, nheimann@ucsd.edu Collin Boudreaux, cboudreaux@ucsd.edu Sam Cohen, szcohen@ucsd.edu Bonnie Devenney, bdevenne@ucsd.edu Zhibei Wang, zhw048@ucsd.edu Amanda Lee-Low, aleelow@ucsd.edu Bing Rethy, brethy@ucsd.edu Chenhao Nie. cnie@ucsd.edu austin brown, aubrown@ucsd.edu Rebecca Howard, r1howard@ucsd.edu Emily Irion, eirion@ucsd.edu Meiyu Su, m2su@ucsd.edu Chuyu Liu, chl082@ucsd.edu yaohong wang， yaw045@ucsd.e du Brenna Wayne bwayne@ucsd.edu Jaeyeon Park, jap013@ucsd.edu Jeffrey Myers, jmyers@ucsd.edu Marissa Myers, maclan@ucsd.edu Dan Bee Lee, dbl001@ucsd.edu Mariya Nikseresht, mniksere@ucsd.edu alayna bone, abone@ucsd.edu Nicholas Valle, njvalle@ucsd.edu Vorathip Plengpanit, vplengpa@ucsd.edu Sunny Xu, qixu@ucsd.edu Junhui Xu, jux008@ucsd.edu Rawlins, Mackenna, mjrawlins@ucsd.edu Elise Spencer, Enspencer@ucsd.edu Zizan Wang, ziw011@ucsd.edu yue yu yuy039@ucsd.edu Salma Shaikh, sshaikh@ucsd.edu Meghan Mattioli, mazavala@ucsd.edu morgan cohen, m7cohen@ucsd.edu Yuki Imura, yimura@ucsd.edu Kelli Maples, kmaples@ucsd.edu Broderick Topil, btopil@ucsd.edu Hyun Ji Jung, hjjung@ucsd.edu Kevin Zhou, kezhou@ucsd.edu TsuPing, Wang, tsw002@ucsd.edu Shuting Wang, shw009@ucsd.edu # collabrative notes Tidy data * Keep Raw data Raw! * Keep record while cleaning data * Put all variables in columns ! * put observations in it's own row * don't combine multiple pieces of info in one cell * export to format like csv Take a look at the messy data. Describe how we would clean it up. Identify what is wrong with this spreadsheet. Discuss or try the steps you would need to take to clean up the spreadsheet, and to put data all together in one spreadsheet. https://openrefine.org library data sets: https://ucsd.libguides.com/data-statistics 46 # Sign in here: name, email morgan cohen, m7cohen@ucsd.edu Jaeyeon Park, jap013@ucsd.edu John Kim, jok015@ucsd.edu Yizhuo Liu, yil118@ucsd.edu zizan Wang, ziw011@ucsd.edu Yuki Imura, yimura@ucsd.edu Emily Irion, eirion@ucsd.edu Rebecca Howard, r1howard@ucsd.edu Sunny Xu,qixu@ucsd.edu Mariya Nikseresht, mniksere@ucsd.edu Bonnie Devenney, bdevenne@ucsd.edu Nick Heimann, nheimann@ucsd.edu Zhibei Wang, zhw048@ucsd.edu Nicholas Valle, njvalle@ucsd.edu Wenjun Gong, w7gong@ucsd.edu Stevinson Tendon, stendon@ucsd.edu Vorathip Plengpanit, vplengpa@ucsd.edu Patricia Resurreccion, paresurr@ucsd.edu Rawlins, Mackenna, mjrawlins@ucsd.edu Salma Shaikh, sshaikh@ucsd.edu Chenhao Nie. cnie@ucsd.edu Broderick Topil, btopil@ucsd.edu Emily Davalos, edavalos@ucsd.edu Tino Tirado, ttirado@ucsd.edu Meghan Mattioli, mazavala@ucsd.edu Jeffrey Myers, jmyers@ucsd.edu Marissa Myers, maclan@ucsd.edu Dan Bee Lee, dbl001@ucsd.edu Chuyu Liu, chl082@ucsd.edu Yuting Wan y7wan@ucsd.edu Amanda Lee-Low, aleelow@ucsd.edu Collin Boudreaux, cboudreaux@ucsd.edu Alayna Bone, abone@ucsd.edu Meiyu Su, m2su@ucsd.edu Bing Rethy, brethy@ucsd.edu Elise Spencer, Enspencer@ucsd.edu Kelli Maples, kmaples@ucsd.edu Brenna Wayne bwayne@ucsd.edu Hyun Ji Jung, hjjung@ucsd.edu Sora Park, sop006@ucsd.edu Kevin Zhou , kezhou@ucsd.edu Junhui Xu, jux008@ucsd.edu Sam Cohen, szcohen@ucsd.edu 41 # SQL Notes here: SQL realtional database IDs link tables Primary key foreign keys Data download: https://figshare.com/articles/dataset/Portal_Project_Teaching_Database/1314459 SQL phrases: SELECT FROM 41 # 3/3/2022 # SQL Day 2 - Last class for lesson for the Data Managment module. # Sign In here John Kim, jok015@ucsd.edu Nicholas Valle, njvalle@ucsd.edu Meghan Mattioli, mazavala@ucsd.edu Wenjun Gong, w7gong@ucsd.edu Vorathip Plengpanit, vplengpa@ucsd.edu Rebecca Howard, r1howard@ucsd.edu Sunny Xu, qixu@ucsd.edu Sora Park, sop006@ucsd.edu Broderick Topil, btopil@ucsd.edu Yizhuo Liu, yil118@ucsd.edu Jeffrey Myers, jmyers@ucsd.edu Emily Davalos, edavalos@ucsd.edu Collin Boudreaux, cboudreaux@ucsd.edu Marissa Myers, maclan@ucsd.edu Meiyu Su, m2su@ucsd.edu Emily Irion, eirion@ucsd.edu Jaeyeon Park, jap013@ucsd.edu Yue Yu yuy039@ucsd.edu Salma Shaikh, sshaikh@ucsd.edu Bonnie Devenney, bdevenne@ucsd.edu stevinson tendon, stendon@ucsd.edu Amanda Lee-Low, aleelow@ucsd.edu Nick Heimann, nheimann@ucsd.edu Zhibei Wang, zhw048@ucsd.edu morgan cohen, m7cohen@ucsd.edu Sam Cohen, szcohen@ucsd.edu Junhui Xu, jux008@ucsd.edu Chuyu Liu, chl082@ucsd.edu Tino Tirado, ttirado@ucsd.edu Yuting Wan y7wan@ucsd.edu Kevin Zhou, kezhou@ucsd.edu Elise Spencer, Enspencer@ucsd.edu Hyun Ji Jung, hjjung@ucsd.edu Mariya Nikseresht, mniksere@ucsd.edu Kelli Maples, kmaples@ucsd.edu TsuPing Wang, tsw002@ucsd.edu Alayna Bone, abone@ucsd.edu Dan Bee Lee, dbl001@ucsd.edu Rawlins, Mackenna mjrawlins@ucsd.edu Yuki Imura, yimura@ucsd.edu Zizan Wang, ziw011@ucsd.edu Patricia Resurreccion, paresurr@ucsd.edu Brenna Wayne bwayne@ucsd.edu ```sql= select * from surveys where year=2000; ``` #replace null with "U" ```sql= select species_id, sex, coalesce(sex, "U") #place U for unknown from surveys; ``` Creating tables: Creating a View: #like a subset ```sql= create TABLE surveys_bookmark AS select species_id, sex from surveys where year=2000; ``` ```sql= create view summer_2000 as select * from surveys where year=2000 and (month >4 and month <10); select * from summer_2000; ``` ```sql= select sum(weight), count(weight)/count(weight) from summer_2000 where species_id="PE"; ``` ````sql select * from surveys join species on surveys.species_id = species.species_id; ``` #another way to inner join ```sql= select * from surveys join species using(species_id); ``` ```sql= select surveys.year, surveys.month,surveys.day,species.genus, species.species from surveys join species using(species_id); ``` ```sql= ```
Last changed by  
 
Reid Otsuji
664
Published on HackMD