Your assignment is to compose SQL queries for a variety of tasks based on a pre-built database.
To start SQLite, enter the directory where the data file
is downloaded and issue the command
“sqlite3 graddata.db”.
The database includes three tables based on publicly available data at the U S Department of Education, including data reported by each college on how many students from the college graduated with each possible major. Rather than working with their full data, though, this database includes just nine small Southern private colleges (Birmingham-Southern College, Centenary College of Louisiana, Centre College, Hendrix College, Millsaps College Rhodes College, Sewanee — University of the South, Southwestern University, and Trinity University), with Bachelor's degree data only. It includes data for four years, academic year 2008–09 through 2011–12.
table columns colleges:ida numeric key for the college (integer) namethe name of the college (text) citythe name of city where the college is (text) statethe two-letter postal state abbrevation (text) zipthe zip code (text) majors:cipa numeric key for the major (integer) majorthe name of the major (text) grads:ida reference to colleges.id(integer)yearthe year the data is from, between 2009 and 2012 (integer) cipa reference to majors.cip(integer)isfirst1 if this count is of first majors, 0 for second majors (integer) numthe number of students in this category (integer)
Sketch the above table in Vertabelo, denoting all primary and foreign key relations. Export your schema both as SQL and a PNG.
Your assignment is to compose and test queries that accomplish each of the following using the above-described database.
For each college, list the number of 2009 graduates with a first major in computer science (CIP 110701), along with the college's name. (Don't worry about colleges with no computer science students.)
name total --------------------------- ---------- Birmingham Southern College 3 Hendrix College 9 Centre College 4 Millsaps College 7 Southwestern University 1 Trinity University 11
For each college, list the total number of students graduating across all majors (count first major only) over the four-year period, along with the college's name.
name total --------------------------- ---------- Birmingham Southern College 1128 Hendrix College 1092 Centre College 1121 Centenary College of Louisi 641 Millsaps College 881 Rhodes College 1520 Sewanee-The University of t 1378 Southwestern University 1140 Trinity University 2258
For each college, list the total number of students majoring in computer science (first or second major) over the four-year period, along with the college's name, sorted in increasing order by the total number of computer science graduates. (Don't worry about colleges with no computer science students.)
name total ----------------------------------- ---------- Sewanee-The University of the South 9 Southwestern University 13 Rhodes College 14 Birmingham Southern College 16 Millsaps College 22 Centre College 23 Hendrix College 30 Trinity University 62
List the number of students completing each major at Hendrix (ID 107080) over the four-year period, along with the major name, sorted by the number of students.
major total ------------------- ---------- Psychology, General 134 Biology/Biological 118 English Language an 80 Economics, General 78 Biochemistry and Mo 75 Multi-/Interdiscipl 58 International Relat 57 History, General 55 Sociology 42 Anthropology 39 Political Science a 38 Chemistry, General 32 Environmental Studi 31 Computer Science 30 Physics, General 30 Health Services/All 29 Art/Art Studies, Ge 28 Spanish Language an 28 Religion/Religious 26 Philosophy 25 Drama and Dramatics 21 Accounting 20 Kinesiology and Exe 18 Mathematics, Genera 14 Music, General 12 Philosophy and Reli 10 American/United Sta 9 Elementary Educatio 9 German Language and 8 Classics and Classi 7 Chemical Physics 5 French Language and 5
