Assignment 4 - Soupify PhD Students (Due: Friday, November 16, 2018)
Write a Python program to retrieve data about PhD students in the Computer Science Department
from the following Web Page:
https://www.cs.gsu.edu/phdstudents/
and populate a MySQL table with the following definition (place this code in a file named phdtable.sql):
create table phdstudents (
name varchar(50),
email varchar(50),
advisor varchar(50),
office varchar(50),
primary key (name)
);
The Python program should produce an output as follows (in a file named phddata.sql):
insert into phdstudents values ('Abeysinghe, T. M. MiuruBhashithe','tabeysinghe2@student.gsu.edu','Sunderraman','1PP 612');
insert into phdstudents values ('Ahmadzadeh Esmaeilabadi,Mir Azim','aahmadzadeh1@student.gsu.edu','Angryk','25PP 645');
insert into phdstudents values ('Alkhoshi, Enas','ealkhoshi1@student.gsu.edu','Belkasim','25PP 652H');
insert into phdstudents values ('Ashraf, Khadija','kashraf1@student.gsu.edu','Ashok','25PP 641J');
insert into phdstudents values ('Bamunu Mudiyanselage, Thosini','tbamunumudiyanselag1@student.gsu.edu','Zhang','25PP 640K');
insert into phdstudents values ('Basodi, Sunitha','sbasodi1@student.gsu.edu','Pan','25PP 649');
insert into phdstudents values ('Binnion, David','dbinnion1@student.gsu.edu','Weeks','');
insert into phdstudents values ('Bolding, David','dbolding2@student.gsu.edu','Harrison','1PP 616');
insert into phdstudents values ('Camp, Brendan','bcamp2@student.gsu.edu','Estrada','1PP 633');
insert into phdstudents values ('Chen, Xucan','xchen41@student.gsu.edu','Wu','1PP 635');
insert into phdstudents values ('Desai, Heta','hdesai1@student.gsu.edu','Sunderraman','1PP 612');
insert into phdstudents values ('Filali Boubrahimi, Soukaina','sfilaliboubrahimi1@student.gsu.edu','Angryk','25PP 721');
insert into phdstudents values ('Freas, Christopher','cfreas1@student.gsu.edu','Harrison','1PP 615');
insert into phdstudents values ('Frederick, Chad','cfrederick1@student.gsu.edu','','25PP 646');
insert into phdstudents values ('Guler, Evrim','eguler1@student.gsu.edu','Cao','25PP 727');
insert into phdstudents values ('Guo, Juan','jguo13@student.gsu.edu','','');
insert into phdstudents values ('Hamdi, Shah Muhammad','shamdi1@student.gsu.edu','Angryk','25PP 645');
insert into phdstudents values ('Hassanzadeh, Reihaneh','rhassanzadeh1@student.gsu.edu','Pimentel-Alarcon','25PP 690');
insert into phdstudents values ('Hendrix, Renesha','rhendrix7@student.gsu.edu','Weeks','25PP 640H');
insert into phdstudents values ('Hosseini, Akram Sadat','ahosseini3@student.gsu.edu','Ashok','25PP 641C');
insert into phdstudents values ('Huang, Yan','yhuang30@student.gsu.edu','Bourgeois','25PP 648');
insert into phdstudents values ('Hussain, Syed Mohammad','shussain11@student.gsu.edu','','1PP 625');
insert into phdstudents values ('Icer, Pelin','picer1@student.gsu.edu','Skums','1PP 621');
insert into phdstudents values ('Islam, A. K. M. Kamrul','aislam6@student.gsu.edu','','1PP 640');
insert into phdstudents values ('Islam, Jyoti','jislam2@student.gsu.edu','Zhang','1PP 617');
insert into phdstudents values ('Islam, S. M. Towhidul','sislam9@student.gsu.edu','Ashok','25PP 641K');
insert into phdstudents values ('Jalalitabar, Maryamsadat','mjalalitabar1@student.gsu.edu','Cao','25PP 727');
insert into phdstudents values ('Ji, Chunyan','cji2@student.gsu.edu','Zhang','25PP 640G');
insert into phdstudents values ('Johnson, William','wjohnson6@student.gsu.edu','Bourgeois','25PP 648');
insert into phdstudents values ('Jones, Jillian','jjones136@student.gsu.edu','','');
insert into phdstudents values ('Khanal, Aashis','akhanal1@student.gsu.edu','Estrada','1PP 633');
insert into phdstudents values ('Kniazev, Sergei','skniazev1@student.gsu.edu','Skums','1PP 618');
insert into phdstudents values ('Ko, Euiseong','eko7@student.gsu.edu','Li, Yingshu','25PP 750');
insert into phdstudents values ('Kuzmin, Kiril','kkuzmin1@student.gsu.edu','','1PP 620');
insert into phdstudents values ('Lakshmanan, Dhanalakshmi','dlakshmanan1@student.gsu.edu','','');
insert into phdstudents values ('Le, Hai','hle49@student.gsu.edu','Hu','25PP 711');
insert into phdstudents values ('Li, Xiang','xli62@student.gsu.edu','Ji','1PP 627');
insert into phdstudents values ('Li, Yang','yli93@student.gsu.edu','Ji','1PP 627');
insert into phdstudents values ('Liao, Xueting','xliao3@student.gsu.edu','Wu','1PP 635');
insert into phdstudents values ('Luo, Shiqing','sluo10@student.gsu.edu','Yan','25PP 647');
insert into phdstudents values ('Ma, Ruizhe','rma1@student.gsu.edu','Angryk','25PP 721');
insert into phdstudents values ('Mahmood, Usman','umahmood1@student.gsu.edu','Pimentel-Alarcon','25PP 690');
insert into phdstudents values ('Mandivarapu, Jaya Krishna','jmandivarapu1@student.gsu.edu','Estrada','1PP 633');
insert into phdstudents values ('Manyam, Ramesh','rmanyam@student.gsu.edu','Hu','');
insert into phdstudents values ('Maradapu Vera Venkata Sai,Akshita','amaradapuveravenkat1@student.gsu.edu','Cai','25PP 725');
insert into phdstudents values ('McDermott, Michael','mmcdermott2@student.gsu.edu','Prasad','25PP 729');
insert into phdstudents values ('Melnyk, Andrii','amelnyk3@student.gsu.edu','Skums','1PP 621');
insert into phdstudents values ('Miao, Dongjing','dmiao1@student.gsu.edu','Cai','25PP 725');
insert into phdstudents values ('Mohebbi, Fatemeh','fmohebbi1@student.gsu.edu','','1PP 625');
insert into phdstudents values ('Motevalialamoti, Saeid','smotevalialamoti1@student.gsu.edu','Estrada','1PP 633');
insert into phdstudents values ('Mousavi, SeyedMohammad Mehdi','smousavi2@student.gsu.edu','Estrada','1PP 633');
insert into phdstudents values ('Nair, Hema','hnair1@student.gsu.edu','Harrison','1PP 615');
insert into phdstudents values ('Nguyen, Anh','anguyen139@student.gsu.edu','Yan','25PP 647');
insert into phdstudents values ('Panichvatana, Chan Aek','cpanichvatana1@student.gsu.edu','','1PP 619');
insert into phdstudents values ('Parameshwaran, Anuja','aparameshwaran1@student.gsu.edu','Weeks','1PP 614');
insert into phdstudents values ('Peng, Chengzong','cpeng7@student.gsu.edu','','25PP 640E');
insert into phdstudents values ('Podila, Sahithi','spodila1@student.gsu.edu','Zhu','CMII 204');
insert into phdstudents values ('Pokharel, Shiraj','spokharel3@student.gsu.edu','Zhu','CMII 204');
insert into phdstudents values ('Rahman, Md. Mahfuzur','mrahman21@student.gsu.edu','Pimentel-Alarcon','25PP 690');
insert into phdstudents values ('Rahman, Md. Rashed','mrahman19@student.gsu.edu','Ashok','25PP 642B');
insert into phdstudents values ('Ray, Bhaskar','bray14@student.gsu.edu','Banda','25PP 752');
insert into phdstudents values ('Rondel, Filipp','frondel1@student.gsu.edu','','1PP 620');
insert into phdstudents values ('Sadasivuni, Sudha Tushara','ssadasivuni1@student.gsu.edu','Zhang','25PP 640C');
insert into phdstudents values ('Saghaeiannejad Esfahani,Sayed Hossein','ssaghaeiannejadesfa1@student.gsu.edu','','1PP 625');
insert into phdstudents values ('Sarker, Krishanu','ksarker1@student.gsu.edu','Belkasim','1PP 617');
insert into phdstudents values ('Sarker, Toqi Tahamid','tsarker3@student.gsu.edu','Banda','25PP 752');
insert into phdstudents values ('Schiffer, Sheldon','schiffer@student.gsu.edu','Zhu','25PP 217');
insert into phdstudents values ('Shah, Dhara','dshah8@student.gsu.edu','Prasad','25PP 729');
insert into phdstudents values ('Siddula, Madhuri','msiddula1@student.gsu.edu','Li, Yingshu','25PP 750');
insert into phdstudents values ('Sims, Seth','ssims21@student.gsu.edu','Zelikovsky','');
insert into phdstudents values ('Sooksatra, Korn','ksooksatra1@student.gsu.edu','Li, Wei','25PP 644');
insert into phdstudents values ('Tekumalla, Venkata','rtekumalla1@student.gsu.edu','','25PP 752');
insert into phdstudents values ('Tsyvina, Viachaslau','vtsyvina1@student.gsu.edu','Zelikovsky','1PP 618');
insert into phdstudents values ('Umoja, Chinua','cumoja1@student.gsu.edu','Harrison','1PP 616');
insert into phdstudents values ('Wang, Peng','wpeng2@student.gsu.edu','Cai','25PP 725');
insert into phdstudents values ('Xiao, Xueli','xxiao2@student.gsu.edu','Pan','25PP 649');
insert into phdstudents values ('Xiong, Zuobin','zxiong2@student.gsu.edu','Cai','25PP 725');
insert into phdstudents values ('Yang, Xiulong','xyang22@student.gsu.edu','Ji','1PP 627');
insert into phdstudents values ('Ye, Yang','yye10@student.gsu.edu','Ji','1PP 627');
insert into phdstudents values ('Yi, Jun','jyi39@student.gsu.edu','Yan','25PP 647');
insert into phdstudents values ('Zhao, Fengpan','fzhao6@student.gsu.edu','Wu','1PP 635');
insert into phdstudents values ('Zheng, Danyang','dzheng5@student.gsu.edu','Cao','25PP 640F');
insert into phdstudents values ('Zhu, Saide','szhu5@student.gsu.edu','Li, Wei','25PP 644');
After creating the table and loading the data in the table, write SQL queries to answer the following queries:
- Retrieve the number of students who have offices in 25 Park Place
- Retrieve the number of students who have offices in 1 Park Place
- Retrieve the names of advisors and the number of students they supervise (order the answer in decreasing order of
number of students)